Postgresql execute procedure on update (value changed)

Question

I have the following trigger which calls the update_recipe_timestamp_proc function which updates the modified_on column. If the same values gets inserted/updated the trigger is still triggered although there were no new values supplied!

Trigger

CREATE TRIGGER update_recipes_timestamp_r 
BEFORE UPDATE OF "identifier", "name", "description"
ON recipes 
FOR EACH ROW EXECUTE PROCEDURE update_recipe_timestamp_proc();

Function

CREATE OR REPLACE FUNCTION update_recipe_timestamp_proc()
RETURNS TRIGGER AS $$
BEGIN
    NEW."modified_on" = now();
    RETURN NEW;   
END;
$$ language 'plpgsql';

Do I have to program the logic in my function and return NULL or NEW based on the result or is there a simple solution?

Laurenz Albe · Accepted Answer · 2017-07-17 08:56:33Z

2

An UPDATE is performed no matter if the new values are different from the old ones, and the same applies to triggers.

Three possible solutions:

As suggested by Sevanteri's comment, use

CREATE TRIGGER update_recipes_timestamp_r 
BEFORE UPDATE OF "identifier", "name", "description"
ON recipes FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)
EXECUTE PROCEDURE update_recipe_timestamp_proc();

Write the trigger procedure like this:

IF (OLD IS DISTINCT FROM NEW) THEN
   NEW."modified_on" = now();
   RETURN NEW;   
END IF;

make the UPDATE conditional:

UPDATE recipes
   SET "identifier" = val1, "name" = val2, "description" = val3
WHERE ...
  AND "identifier" <> val1 OR "name" <> val2 OR "description" <> val3;

edited Jul 17, 2017 at 8:56

answered Jul 14, 2017 at 10:40

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sevanteri Over a year ago

There is also the WHEN condition when creating the trigger. WHEN (OLD.* IS DISTINCT FROM NEW.*)

Laurenz Albe Over a year ago

Right, that's even better!

Dominic Jonas Over a year ago

@LaurenzAlbe I do not understand how I must use your solution. Maybe you could edit your or my answer. Thanks!

Laurenz Albe Over a year ago

Is this better?

Dominic Jonas · Accepted Answer · 2017-07-17 06:42:01Z

0

Solution with WHEN to only update the modified_on column on specific column changes.

CREATE TRIGGER update_recipes_timestamp_r BEFORE 
UPDATE ON recipes FOR EACH ROW
WHEN (
OLD."name" <> NEW."name" OR 
OLD."description" <> NEW."description" OR
OLD."identifier" <> NEW."identifier")
EXECUTE PROCEDURE update_recipe_timestamp_proc();

answered Jul 17, 2017 at 6:42

Dominic Jonas

5,0531 gold badge42 silver badges94 bronze badges

Collectives™ on Stack Overflow

Postgresql execute procedure on update (value changed)

Trigger

Function

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Trigger

Function

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related