Fire trigger on update of columnA or ColumnB or ColumnC
Solution 1
This is a misunderstanding. The WHEN
clause of the trigger definition expects a boolean
expression and you can use OR
operators in it. This should just work (given that all columns actually exist in the table account_details
). I am using similar triggers myself:
CREATE TRIGGER trigger_update_account_details
AFTER UPDATE ON account_details
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email
OR OLD.username IS DISTINCT FROM NEW.username
OR OLD.password IS DISTINCT FROM NEW.password)
EXECUTE PROCEDURE notify_insert_account_details();
Evaluating the expression has a tiny cost, but this is probably more reliable than the alternative:
CREATE TRIGGER ... AFTER UPDATE OF email, username, password ...
Because, per documentation:
A column-specific trigger (one defined using the
UPDATE OF
column_name
syntax) will fire when any of its columns are listed as targets in theUPDATE
command'sSET
list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents byBEFORE UPDATE
triggers are not considered. Conversely, a command such asUPDATE ... SET x = x ...
will fire a trigger on column x, even though the column's value did not change.
ROW
type syntax is shorter to check on many columns (doing the same):
CREATE TRIGGER trigger_update_account_details
AFTER UPDATE ON account_details
FOR EACH ROW
WHEN ((OLD.email, OLD.username, OLD.password, ...)
IS DISTINCT FROM
(NEW.email, NEW.username, NEW.password, ...))
EXECUTE PROCEDURE notify_insert_account_details();
Or, to check for every visible user column in the row:
...
WHEN (OLD IS DISTINCT FROM NEW)
...
Solution 2
I don't think you need the WHEN
clause. You can specify the columns in question in the UPDATE clause:
CREATE TRIGGER trigger_update_account_details
AFTER UPDATE OF email, username, password ON account_details
FOR EACH ROW
EXECUTE PROCEDURE notify_insert_account_details();
Solution 3
The above solutions were not working for me properly. So after reading through documentation again. I found few things to take note of. BEFORE UPDATE ON - AFTER UPDATE ON triggers are executed differently. Since my procedure was returning the NEW record with updated value. It was not working in AFTER trigger and in BEFORE trigger, the OR statements inside WHEN clause needed to be enclosed by braces.
CREATE TRIGGER check_update
BEFORE UPDATE ON some_table
FOR EACH ROW
WHEN ((OLD.colum_name_1 IS DISTINCT FROM NEW.colum_name_1) OR (OLD.colum_name_2 IS DISTINCT FROM NEW.colum_name_2))
EXECUTE PROCEDURE update_updated_at_column();
And the procedure
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ language 'plpgsql';
Related videos on Youtube
Martin
Adding a touch of jazz and a splodge of funk 1 LOC at a time
Updated on March 16, 2021Comments
-
Martin about 3 years
I have the code to fire a trigger only on an update of a single specific column. The trigger is used to fire a function that will raise a postgres "notify" event, which I am listening for and will need to test and validate the newly input details. There are many values on the account_details table which could be change which do not require an account validate, so a trigger on AFTER UPDATE only (without a when) is no good.
CREATE TRIGGER trigger_update_account_details AFTER UPDATE ON account_details FOR EACH ROW WHEN (OLD.email IS DISTINCT FROM NEW.email) EXECUTE PROCEDURE notify_insert_account_details();
But I want to fire the trigger if one of many columns change, something like
WHEN (OLD.email IS DISTINCT FROM NEW.email OR OLD.username IS DISTINCT FROM NEW.username OR OLD.password IS DISTINCT FROM NEW.password)
But OR is not a valid keyword for a trigger. Trying to search for the keyword to use instead of OR doesn't seem to bring up anything due the nature of the word OR :-(
-
Andrew Lazarus over 9 yearsAs I read the documentation for 9.3, this should work. Can you post version, and error message?
-
-
a_horse_with_no_name over 9 yearsI wonder if there is a difference in performance between using the
when
condition and explicitly listing the columns in theupdate on ...
clause -
Erwin Brandstetter over 9 years@a_horse_with_no_name: the
WHEN
condition is slightly more expensive (the expression has to be evaluated), but the results are also subtly different in a number of ways. I added a bit to my answer. -
pozs over 9 yearsIf someone worries about performance with the
WHEN
clause, but wants their triggers to be executed only if there was an actual change: these 2 solutions can be combined. -
Erwin Brandstetter over 9 years@pozs: Good idea. But correctness is the first concern: the above trigger may fire where the alternative would not (and the other way round) - as explained in the quote. When applying both, both conditions must be met. It depends on exact requirements.
-
pozs over 9 years@ErwinBrandstetter you're right other triggers can also change those columns (I didn't noticed it first, maybe some emphasize can help in the quote)
-
Martin over 9 yearsI have a feeling that this fires when the values are set via an UPDATE sql statement, irrespective if they are what may be thought of as "updated" i.e. different.
-
Martin over 9 yearsMany thanks. Not sure what I was doing wrong before, but as you say, it just works. Working when tired is not the one....
-
malthe about 6 years
WHEN row(new.*) IS DISTINCT FROM row(old.*)
– see stackoverflow.com/a/3084254/647151 -
Erwin Brandstetter about 6 years@malthe: Not applicable to this particular question, but I added it to make it complete. I suggest the shorter, equivalent syntax, though:
WHEN (OLD IS DISTINCT FROM NEW)
- and the parentheses are required. While being at it, I also added another answer over there ... -
Jeff over 5 yearsOnce again I learn something from @ErwinBrandstetter! It feels like
WHEN (OLD IS DISTINCT FROM NEW)
should be default behavior... I always start out my trigger functions with this check, didn't even know you could have it as part of the definition! -
The pyramid over 5 yearswhat if i want to send only the updated value in a row , pg_notify(row_to_json), not the whole row. when a cell is updated , i want to send that cell value to my node.js, without doing if conditions in my pg function
-
Tim about 3 yearsThis is basically the solution I went with (posted more info below). But it seems you should use
BEFORE UPDATE OF column_name_1, column_name_2 ON some_table
so that this trigger doesn't have to evaluate theWHEN
clause on every single update -
jian over 2 years@ErwinBrandstetter I am wondering how sophisticated this procedure/function notify_insert_account_details() Can become? so I asked an question. stackoverflow.com/questions/69631945/…
-
cpursley over 2 yearsDownvote for the random javascript stuff.
-
Tim about 2 years@cpursley AHHH!! JAVASCRIPT! KILL IT WITH FIRE! Thanks for saving the world from a valid solution with the vile stench of javascript on it. haha. I mentioned in the post I was in a rush. Taking a look at the embedded SQL isn't that hard for any developer that's not so afraid of JS that they can't stand to look at it. And it by no means is it so detrimental to the value of the post that it'd be better if this post didn't exist.