更新columnA或ColumnB或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 ...

因为：

类型语法较短，可以检查许多列（执行相同操作）：

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时触发触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！