我有代码只在更新单个特定列时触发触发器.触发器用于触发一个函数,该函数将引发postgres“notify”事件,我正在侦听并需要测试并验证新输入的详细信息. 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();
但是如果许多列中的一个发生变化,我想触发触发器
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的性质:-(
这是一种误解.
这应该工作(假设所有列实际存在于表account_details中)我自己使用类似的触发器：
原文链接：https://www.f2er.com/postgresql/192827.htmlWHEN 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 ...
A column-specific trigger (one defined using the
UPDATEOF column_name
Syntax) will fire when any of its columns are listed as targets in the
UPDATEcommand’sSETlist. 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 asUPDATE ... 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) ...
