Question 218546 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – PgSQL trigger function on update and insert

I have two tables time_info and category. I want to update the temp_id of time_info table whenever there is insertion of record or update of duration column of time_info table.

Here is the schema

time_info

  • id
  • name – text
  • duration – smallint NOT NULL
  • temp_id – integer

category

  • id
  • temp_id – integer
  • duration – smallint

The category table has some static records and those record’s temp_ids need to be populated to time_info table based the condition time_info.duration = category.duration.

Example
category

id temp_id duration
001 123 30
002 234 60
003 345 90

So if a record is added in time_info like

id name duration temp_id
101 abc 60

Then trigger function should run and update the row as following

id name duration temp_id
101 abc 60 234

Now if someone changes the duration in time_info to 90 then trigger function should run again and update the temp_id to 345.

WHat I tried

CREATE FUNCTION update_time_info_temp_id() RETURNS trigger AS $update_time_info_temp_id$
    BEGIN
        UPDATE NEW  SET NEW.temp_id = subquery.temp_id
        FROM (SELECT * from category) AS subquery
        WHERE NEW.duration = subquery.duration; 

        RETURN NEW;
    END;
$update_time_info_temp_id$ LANGUAGE plpgsql;

CREATE TRIGGER update_time_info_temp_id AFTER INSERT OR UPDATE ON time_info
    FOR EACH ROW EXECUTE FUNCTION update_time_info_temp_id();

But it doesn’t seem to be working as expected

Tags:

2

Answers


  1. 0

    Check this out. This might fix the problem.

    CREATE OR REPLACE FUNCTION update_time_info_temp_id()
RETURNS TRIGGER AS $$
DECLARE
    value_to_update integer;
BEGIN
    SELECT temp_id INTO value_to_update
    FROM category
    WHERE duration = NEW.duration;
      
    UPDATE time_info
    SET temp_id = value_to_update
    WHERE id = NEW.id;

    RETURN NEW;
END;

CREATE TRIGGER update_time_info_temp_id_trigger
AFTER INSERT OR UPDATE OF duration ON time_info
FOR EACH ROW
EXECUTE FUNCTION update_time_info_temp_id();
    Login or Signup to reply.
  2. 0

    You probably want a BEFORE trigger. That lets you avoid the UPDATE and just modify NEW. You can also avoid any work when the duration hasn’t changed.

    BEGIN;

CREATE TABLE category (id int PRIMARY KEY, temp_id int NOT NULL, duration int NOT NULL);

CREATE TABLE time_info (id int, name text, duration int NOT NULL, temp_id int NULL);

INSERT INTO category VALUES
    (1, 123, 30)
    , (2, 234, 60)
    , (3, 345, 90)
;

CREATE OR REPLACE FUNCTION tr_set_temp_id()
RETURNS TRIGGER
AS $$
BEGIN
    IF TG_OP = 'INSERT' OR NEW.duration IS DISTINCT FROM OLD.duration THEN
        NEW.temp_id := (SELECT temp_id FROM category WHERE duration = NEW.duration);
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trig_set_temp_id BEFORE INSERT OR UPDATE ON time_info
FOR EACH ROW EXECUTE PROCEDURE tr_set_temp_id();


INSERT INTO time_info VALUES (100, 'a', 30, NULL);
SELECT * FROM time_info;
UPDATE time_info SET duration = 60 WHERE id = 100;
SELECT * FROM time_info;

ROLLBACK;

    Note that your schema, as defined, has a couple of oddities. The category table’s id seems redundant – the way you are using it suggests to me duration is the actual primary key. In any case you definitely want to put a unique constraint on it. You normally wouldn’t copy the temp_id over too – just join from the time_info if you needed it.

    Oh, you’ve not said what should happen if the duration isn’t in the category table either – you’ll want to consider that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search