插入访问NEW后的Postgres触发器 [英] Postgres trigger after insert accessing NEW

问题描述

我有一个非常简单的触发器:

I have a pretty simple trigger:

CREATE OR REPLACE FUNCTION f_log_datei()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO logs (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id);
END; $$ LANGUAGE 'plpgsql';

CREATE TRIGGER log_datei AFTER INSERT OR UPDATE OR DELETE
ON dateien
FOR EACH STATEMENT
EXECUTE PROCEDURE f_log_datei();

我的表日志如下:

CREATE TABLE logs(
    id int PRIMARY KEY DEFAULT NEXTVAL('logs_id_seq'),
    zeit timestamp DEFAULT now(),
    aktion char(6),
    tabelle varchar(32),
    alt varchar(256),
    neu varchar(256),
    benutzer_id int references benutzer(id)
);

在dateien中插入某些内容后，出现以下错误:

After inserting something in dateien I get the following error:

ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  SQL statement "INSERT INTO logs (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id)"
PL/pgSQL function "f_log_datei" line 3 at SQL statement

为什么会出现此错误?我查看了文档，似乎它们以与我相同的方式使用new.

Why did I get this error? I looked into the documentation and it seems they use new in the same way I do.

推荐答案

来自

36.1.触发行为概述
[...]
对于行级触发器，输入数据还包括用于INSERT和UPDATE触发器的NEW行，和/或用于UPDATE和DELETE触发器的OLD行.语句级触发器当前无法检查该语句修改的单个行.

36.1. Overview of Trigger Behavior
[...]
For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers. Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement.

并从触发程序:

NEW
数据类型RECORD;变量，用于保存行级触发器中INSERT/UPDATE操作的新数据库行.在语句级触发器和DELETE操作中，此变量为NULL.

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

请注意有关行级触发器和语句级触发器的内容.

Note what it says about row-level triggers and statement-level triggers.

您有一个语句级触发器:

You have a statement-level trigger:

...
FOR EACH STATEMENT
EXECUTE PROCEDURE f_log_datei();

每个语句一次触发语句级触发器，并且一条语句可以应用于多行，因此受影响的行(这是NEW和OLD所涉及的)的概念根本不会执行"不适用.

Statement-level triggers are triggered once per statement and a statement can apply to multiple rows so the notion of affected row (which is what NEW and OLD are about) simply doesn't apply.

如果要在触发器中使用NEW(或OLD)，则希望对每个受影响的行执行该触发器，这意味着您需要一个行级触发器:

If you want to use NEW (or OLD) in a trigger then you want the trigger to execute for each affected row and that means you want a row-level trigger:

CREATE TRIGGER log_datei AFTER INSERT OR UPDATE OR DELETE
ON dateien
FOR EACH ROW
EXECUTE PROCEDURE f_log_datei();

我刚刚将FOR EACH STATEMENT更改为FOR EACH ROW.

您的触发器还应该返回某些内容:

Your trigger should also be returning something:

触发器函数必须返回NULL或具有准确触发触发器的表结构的记录/行值.
[...]
触发AFTER的行级触发器或触发BEFORE或AFTER的语句级触发器的返回值总是被忽略；它也可能为空.但是，这些类型的触发器中的任何一个都可能仍然会因引发错误而中止整个操作.

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.
[...]
The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.

所以您应该在触发器中输入RETURN NEW;或RETURN NULL;.您有一个AFTER触发器，因此您使用哪个RETURN都没有关系，但我会选择RETURN NEW;.

So you should RETURN NEW; or RETURN NULL; in your trigger. You have an AFTER trigger so it doesn't matter which RETURN you use but I'd go with RETURN NEW;.

这篇关于插入访问NEW后的Postgres触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！