在多个WHEN条件下触发

本文介绍了在多个WHEN条件下触发的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何包含我需要监视的列？即而不是一个 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

粗体我的。