更新columnA或ColumnB或ColumnC时触发触发器 [英] Fire trigger on update of columnA or ColumnB or ColumnC

问题描述

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

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) 

但OR并非触发器的有效关键字。由于单词OR的性质，试图搜索要使用的关键字代替OR似乎没有带来任何好处：-（

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 运算符它应该可以正常工作（假设所有列实际上都存在于表 account_details 中）。我自己也在使用类似的触发器：

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 ...

因为每个文档：

特定于列的触发器（一个使用 UPDATE OF column_name 定义的触发器） em>
语法）将在
UPDATE 命令的中将其任何列列为目标时触发SET 列表。即使未触发触发器，列的值也可能会更改为
，因为 BEFORE UPDATE 触发器对
行的内容所做的更改是不考虑。
相反，诸如 UPDATE ... SET x = x ... 的命令将在列x上触发
触发器，即使该列的值没有改变。

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 类型语法较短，可以检查许多列（执行相同操作）：

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)
...

这篇关于更新columnA或ColumnB或ColumnC时触发触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！