Postgresql FOR EACH STATEMENT 触发器示例

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

NEW

数据类型RECORD；在行级触发器中为INSERT/UPDATE操作保存新数据库行的变量.This variable is null in statement-level triggers和DELETE次行动.

OLD

数据类型记录；在行级触发器中为UPDATE/DELETE操作保存旧数据库行的变量.This variable is null in statement-level triggers和INSERT次手术.

我的.

到了第10期博士后，这种解读略有不同，但效果基本相同:

... 此变量在语句级触发器中为unassigned...

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

Postgres 10中的过渡表+

Postgres 10引入了过渡表.这些允许访问整个受影响的行集.The manual:

101触发器还可以使用transition tables判断由触发语句更改的整组行.

按照手册的链接查看代码示例.

Example statement-level trigger without transition tables

Before the advent of transition tables, those were even less common. A useful example is to send notifications after certain DML commands.
Here is a basic version of what I use:

-- 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
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_notify_after();

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

...
EXECUTE FUNCTION trg_notify_after();

见:

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