Trigger fire when updating column A or column B or column C

Question

I have code to trigger only when updating one specific column. The trigger is used to trigger a function that fires the postgres "notify" event, which I am listening to, and you will need to check and confirm the new input data. There are many values ​​in the account_details table that can be changed, which do not require account verification, so the default trigger after (without) is not suitable.

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 trigger the trigger if one of the many columns changes, 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 find a keyword used instead of OR does not seem to cause anything due to the nature of the word OR: - (

Answer 1

This is some kind of misunderstanding. Offer WHEN trigger definition expects a Boolean expression , and you can use it as many operators OR , as you want.
This should just work (given that all columns really exist in the account_details table). I myself use similar triggers:

 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 an expression has a tiny cost, but it is likely to be more accurate than an alternative:

  CREATE TRIGGER ... AFTER UPDATE OF email, username, password ... 

Because for the documentation :

A column-specific trigger (one is defined using the UPDATE OF column_name syntax) will fire when any of its columns is specified as targets in the UPDATE SET command. The value of the column may be equal to the change even when the trigger is triggered, since changes made to the contents of rows through UPDATE triggers are not considered. Conversely, a command such as UPDATE ... SET x = x ... triggers a trigger on column x, although the value of the column has not changed.

Since you mentioned a lot of columns. You can shorten the syntax with syntax like ROW (do 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(); 

Answer 2

I do not think you need a WHEN clause. You can specify the appropriate columns 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();