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?

Answer 1

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:

1. 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();
   

2. Write the trigger procedure like this:

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

3. make the UPDATE conditional:

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