sql - 插入访问 NEW 后 Postgres 触发器

Question

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

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 中插入内容后，出现以下错误:

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。

Answer 1

来自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.

来自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.

请注意它所说的行级触发器和语句级触发器。

你有一个语句级触发器:

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

语句级触发器每条语句触发一次，并且一条语句可以应用于多行，因此受影响的行的概念(这就是NEW和OLD 是关于)根本不适用。

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

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。

---

你的触发器也应该是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.

因此您应该在触发器中RETURN NEW; 或RETURN NULL;。您有一个 AFTER 触发器，因此使用哪个 RETURN 并不重要，但我会选择 RETURN NEW;。