Fire trigger on update of columnA or ColumnB or ColumnC

Question

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 :-(

Answer 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 OFcolumn_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET 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 by BEFORE UPDATE triggers are not considered. Conversely, a command such as UPDATE ... 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) ...