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

问题描述

我有代码仅在更新单个特定列时触发触发器.触发器用于触发一个函数，该函数将引发 postgres通知"事件，我正在侦听该事件，并且需要测试和验证新输入的详细信息.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 :-(

推荐答案

这是一个误解.触发器定义的 WHEN 子句需要 boolean 表达式，您可以在其中使用 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 OFcolumn_name 定义的触发器语法)将在其任何列被列为目标时触发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屋！