CODEWITHSUNDEEP
postgresqlnpgsql
Dominic Jonas
Dominic Jonas

Reputation: 5015

Postgresql execute procedure on update (value changed)

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?

Upvotes: 1

Views: 720

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247260

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;

Upvotes: 2

Dominic Jonas
Dominic Jonas

Reputation: 5015

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

Upvotes: 0

Related Questions