postgresql – 更新columnA或ColumnB或ColumnC时触发的触发器

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

WHEN (OLD.email IS DISTINCT FROM NEW.email OR 
OLD.username IS DISTINCT FROM NEW.username OR 
OLD.password IS DISTINCT FROM NEW.password)

这是一种误解. WHEN clause of the trigger definition expects a boolean expression并且您可以根据需要使用尽可能多的OR运算符.
这应该工作(假设所有列实际存在于表account_details中)我自己使用类似的触发器：

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

评估表达式的成本很低,但这可能比替代方案更精确：

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

因为,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 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类型语法缩短语法(执行相同操作)：

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

或者,如果要检查行中的每个可见用户列：

...
WHEN (OLD IS DISTINCT FROM NEW)
...