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

Fire trigger on update of columnA or ColumnB or ColumnC

提问by Martin

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.

我有代码仅在更新单个特定列时触发触发器。触发器用于触发一个函数，该函数将引发 postgres“通知”事件，我正在侦听该事件，并且需要测试和验证新输入的详细信息。account_details 表上有许多值可以更改，不需要帐户验证，因此仅在 AFTER UPDATE 上触发（没有时间）是不好的。

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

但是 OR 不是触发器的有效关键字。由于单词 OR 的性质，尝试搜索要使用的关键字而不是 OR 似乎没有提出任何内容:-(

回答by Erwin Brandstetter

This is a misunderstanding. The WHENclause of the trigger definition expects a booleanexpressionand you can use ORoperators in it. This should just work (given that all columns actually exist in the table account_details). I am using similar triggers myself:

这是一种误解。WHEN触发器定义boolean的子句需要一个表达式，您可以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();

Evaluating the expression has a tiny cost, but this is probably more reliablethan the alternative:

评估表达式的成本很小，但这可能比替代方案更可靠：

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

Because, per documentation:

因为，根据文档：

A column-specific trigger (one defined using the UPDATE OFcolumn_namesyntax) will fire when any of its columns are listed as targets in the UPDATEcommand's SETlist. 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 UPDATEtriggers 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.

UPDATE OFcolumn_name当任何列在UPDATE命令SET列表中作为目标列出时，将触发特定于列的触发器（使用语法定义的触发器） 。即使没有触发触发器，列的值也可能发生更改，因为BEFORE UPDATE不会考虑触发器对行内容所做的更改。相反，UPDATE ... SET x = x ...即使列的值没有更改，诸如此类的命令也会在列 x 上触发触发器。

ROWtype syntax is shorter to check on many columns (doing the same):

ROWtype 语法更短，可以检查许多列（做同样的事情）：

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 everyvisible user column in the row:

或者，检查行中的每个可见用户列：

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

回答by a_horse_with_no_name

I don't think you need the WHENclause. You can specify the columns in question in the UPDATE clause:

我认为你不需要这个WHEN条款。您可以在 UPDATE 子句中指定有问题的列：

CREATE TRIGGER trigger_update_account_details
    AFTER UPDATE OF email, username, password ON account_details
    FOR EACH ROW
    EXECUTE PROCEDURE notify_insert_account_details();

回答by Sandeep PC

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.

上述解决方案对我不起作用。所以在再次阅读文档后。我发现有几件事需要注意。BEFORE UPDATE ON - AFTER UPDATE ON 触发器的执行方式不同。由于我的程序正在返回具有更新值的 NEW 记录。它在 AFTER 触发器和 BEFORE 触发器中不起作用，WHEN 子句中的 OR 语句需要用大括号括起来。

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';