postgresql - FOR EACH STATEMENT 触发器示例

Question

我一直在查看 documentation of postgresql triggers ，但它似乎只显示行级触发器的示例，但我找不到语句级触发器的示例。

特别是，由于 NEW 用于单个记录，因此不太清楚如何在单个语句中迭代更新/插入的行。

Answer 1

OLD 和 NEW 为空或未在语句级触发器中定义。 Per documentation:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable isnull in statement-level triggers and for DELETE operations.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.

大胆强调我的。

直到 Postgres 10，这读起来略有不同，但效果大致相同:

... This variable is unassigned in statement-level triggers. ...

虽然那些记录变量对于语句级触发器仍然没有用处，但一个非常新的特性是:

Postgres 10+ 中的转换表

Postgres 10 引入了转换表。这些允许访问整组受影响的行。 The manual:

AFTER triggers can also make use of transition tables to inspect the entire set of rows changed by the triggering statement.The CREATE TRIGGER command assigns names to one or both transitiontables, and then the function can refer to those names as though theywere read-only temporary tables. Example 43.7 shows an example.

点击手册链接获取代码示例。

示例语句级触发器没有转换表

在转换表出现之前，这些更不常见。一个有用的例子是发送 notifications在某些 DML 命令之后。
这是我使用的基本版本:

-- Generic trigger function, can be used for multiple triggers:
CREATE OR REPLACE FUNCTION trg_notify_after()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   PERFORM pg_notify(TG_TABLE_NAME, TG_OP);
   RETURN NULL;
END
$func$;

-- Trigger
CREATE TRIGGER notify_after
AFTER INSERT OR UPDATE OR DELETE ON my_tbl
<b>FOR EACH STATEMENT</b>
EXECUTE PROCEDURE trg_notify_after();

对于 Postgres 11 或更高版本，使用等效的、更容易混淆的语法:

...
EXECUTE FUNCTION trg_notify_after();

参见:

• Trigger function does not exist, but I am pretty sure it does