在多个WHEN条件下触发 [英] Trigger with multiple WHEN conditions

问题描述

如何包含我需要监视的列？即而不是一个 When 条件，我想要3个 When 条件：

How do I include the columns I need to monitor? I.e. instead of one WHEN condition I want to have 3 WHEN conditions:

CREATE  TRIGGER freeradius.insert_into_day_summations 
     BEFORE INSERT ON freeradius.day_guiding_usage
     FOR EACH ROW 
     WHEN (OLD.col1 IS DISTINCT FROM NEW.col1)
     WHEN (OLD.col2 IS DISTINCT FROM NEW.col2)
     WHEN (OLD.col3 IS DISTINCT FROM NEW.col3)
EXECUTE procedure update_sessioninfo();

推荐答案

与组成单个表达式OR 或 AND -取决于是否要满足 all 条件或任一条件时触发条件已满足：

Form a single expression with OR or AND - depending on whether you want to trigger when all conditions are met or when either one condition is met:

CREATE TRIGGER update_day_summations  -- see below
BEFORE UPDATE ON freeradius.day_guiding_usage
FOR EACH ROW 
WHEN (OLD.col1 IS DISTINCT FROM NEW.col1
   OR OLD.col2 IS DISTINCT FROM NEW.col2
   OR OLD.col3 IS DISTINCT FROM NEW.col3)
EXECUTE procedure update_sessioninfo();

这只是一个布尔表达式，可以包含该行的所有列。

但是，您的表达式仅对 UPDATE 有意义，对于 INSERT 。没有 OLD 插入记录。 创建触发器手册：

It's just a boolean expression, can involve all columns of the row.
However, your expressions only make sense for UPDATE, not for INSERT. There is no OLD record for inserts. The manual on CREATE TRIGGER:

条件

一个布尔表达式，它确定触发函数是否将实际执行
。如果指定了 WHEN ，则仅在 条件时才调用
。 返回 true 。在 FOR EACH ROW 触发器中，
When 条件可以引用旧列和/或新列通过写 OLD行值
。 列名 或 NEW。 列名 。当然，
INSERT 触发器不能引用 OLD 和 DELETE 触发器不能引用 NEW 。

A Boolean expression that determines whether the trigger function will actually be executed. If WHEN is specified, the function will only be called if the condition returns true. In FOR EACH ROW triggers, the WHEN condition can refer to columns of the old and/or new row values by writing OLD.column_name or NEW.column_name respectively. Of course, INSERT triggers cannot refer to OLD and DELETE triggers cannot refer to NEW.

和触发器名称本身不能被模式限定。再次引用该手册：

And the trigger name itself cannot be schema-qualified. Quoting the manual once more:

名称

给出新触发器的名称。此名称必须与同一表的任何其他触发器的名称不同。
名称不能通过模式限定

The name to give the new trigger. This must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified

粗体我的。

这篇关于在多个WHEN条件下触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！