插入访问新后的Postgres触发器

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();

My table logs is the following:

我的表日志如下:

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)
);

After inserting something in dateien I get the following error:

在dateien中插入一些东西后，我得到以下错误:

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

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

为什么会出现这个错误?我查看了文档，发现他们使用new的方式和我一样。

1 个解决方案

#1

From the fine manual:

从精美的手册:

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.

36.1。触发行为概述[…]对于行级触发器，输入数据还包括插入和更新触发器的新行，以及/或更新和删除触发器的旧行。语句级触发器目前没有任何方法来检查由语句修改的单个行。

And from Trigger Procedures:

和触发程序:

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.

新的数据类型记录;在行级触发器中保存用于插入/更新操作的新数据库行。在语句级触发器和删除操作中，该变量为NULL。

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();

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.

每个语句触发一次语句级别的触发器，一个语句可以应用到多个行，因此受影响的行(这就是新旧的含义)的概念根本不适用。

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();

I just changed FOR EACH STATEMENT to FOR EACH ROW.

我只是把每一行的每个语句都更改了。

Your trigger should also be returning something:

你的触发器也应该返回一些东西:

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.

触发器函数必须返回NULL或记录/行值，该值具有触发器触发的表的结构。[…]在或之后被触发的行级触发器的返回值总是被忽略;它也可能是空的。然而，任何这类触发器都可能通过引发错误而中止整个操作。

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;.

所以你应该返回新的;或返回NULL;在你的触发器。你有一个AFTER触发器，所以你用哪个返回并不重要，但是我用RETURN NEW;

#1