postgresql FOR EACH STATEMENT 触发器示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24193567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
FOR EACH STATEMENT trigger example
提问by diffeomorphism
I've been looking at the documentation of postgresql triggers, but it seems to only show examples for row-level triggers, but I can't find an example for a statement-level trigger.
我一直在查看postgresql triggers的文档，但它似乎只显示了行级触发器的示例，但我找不到语句级触发器的示例。
In particular, it is not quite clear how to iterate in the update/inserted rows in a single statement, since NEWis for a single record.
特别是，由于NEW是针对单个记录，因此不太清楚如何在单个语句中迭代更新/插入的行。
回答by Erwin Brandstetter
OLDand NEWare null or not defined in a statement-level trigger. Per documentation:
OLD并且NEW为 null 或未在语句级触发器中定义。根据文档：
NEWData type
RECORD; variable holding the new database row forINSERT/UPDATEoperations in row-level triggers. This variable is null in statement-level triggersand forDELETEoperations.
OLDData type RECORD; variable holding the old database row for
UPDATE/DELETEoperations in row-level triggers. This variable is null in statement-level triggersand forINSERToperations.
NEW数据类型
RECORD；保存行级触发器中INSERT/UPDATE操作的新数据库行的变量。此变量在语句级触发器和DELETE操作中为空。
OLD数据类型记录；在行级触发器中保存用于
UPDATE/DELETE操作的旧数据库行的变量。此变量在语句级触发器和INSERT操作中为空。
Bold emphasis mine.
大胆强调我的。
Up to Postgres 10 this read slightly different, much to the same effect, though:
直到 Postgres 10，这读起来略有不同，但效果大致相同：
... This variable is unassignedin statement-level triggers. ...
... 该变量在语句级触发器中未分配。...
While those record variables are still of no use for statement level triggers, a new feature very much is:
虽然这些记录变量仍然没有用于语句级触发器，但一个非常重要的新特性是：
Transition tables in Postgres 10+
Postgres 10+ 中的转换表
Postgres 10 introduced transition tables. Those allow access to the whole set of affected rows. The manual:
Postgres 10 引入了转换表。这些允许访问整个受影响的行集。手册：
AFTERtriggers can also make use of transition tablesto inspect the entire set of rows changed by the triggering statement. TheCREATE TRIGGERcommand assigns names to one or both transition tables, and then the function can refer to those names as though they were read-only temporary tables. Example 43.7shows an example.
AFTER触发器还可以利用转换表来检查由触发语句更改的整个行集。该CREATE TRIGGER命令将名称分配给一个或两个转换表，然后该函数可以引用这些名称，就好像它们是只读临时表一样。例 43.7 给出了一个例子。
Follow the link to the manual for code examples.
按照代码示例的手册链接。
Example statement-level trigger without transition tables
没有转换表的语句级触发器示例
Before the advent of transition tables, those were even less common. A useful example is to send notificationsafter certain DML commands.
Here is a basic version of what I use:
在转换表出现之前，这些更不常见。一个有用的例子是在某些 DML 命令之后发送通知。
这是我使用的基本版本：
-- Generic trigger function, can be used for multiple triggers:
CREATE OR REPLACE FUNCTION trg_notify_after()
RETURNS trigger AS
$func$
BEGIN
PERFORM pg_notify(TG_TABLE_NAME, TG_OP);
RETURN NULL;
END
$func$ LANGUAGE plpgsql;
-- Trigger
CREATE TRIGGER notify_after
AFTER INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_notify_after();