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

问题描述

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

创建或替换函数 f_log_datei()返回触发器为 $$开始插入日志 (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id);结尾;$$ LANGUAGE 'plpgsql';在插入或更新或删除后创建触发器 log_datei日期对于每个语句执行程序 f_log_datei();

我的表日志如下:

创建表日志(id int PRIMARY KEY DEFAULT NEXTVAL('logs_id_seq'),时代时间戳默认现在()，动作字符(6),tabelle varchar(32),alt varchar(256),新变量(256)，benutzer_id int 引用 benutzer(id));

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

错误:记录新"尚未分配详细信息:尚未分配的记录的元组结构是不确定的.上下文:SQL 语句INSERT INTO logs (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id)"PL/pgSQL 函数f_log_datei"第 3 行在 SQL 语句

为什么我会收到这个错误?我查看了文档，他们似乎和我一样使用 new.

解决方案

来自 精细手册:

<块引用>

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

来自触发程序:

<块引用>

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

注意它对行级触发器和语句级触发器的说明.

您有一个语句级触发器:

<代码>...对于每个语句执行程序 f_log_datei();

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

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

CREATE TRIGGER log_datei AFTER INSERT OR UPDATE OR DELETE日期每行执行程序 f_log_datei();

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

<小时>

您的触发器还应该返回某些内容::><块引用>

触发器函数必须返回 NULL 或记录/行值，该值与触发触发器的表的结构完全相同.
[...]
AFTER 触发的行级触发器或 BEFORE 或 AFTER 触发的语句级触发器的返回值总是被忽略；它也可能为空.但是，这些类型的触发器中的任何一种仍然可能通过引发错误来中止整个操作.

所以你应该 RETURN NEW; 或 RETURN NULL; 在你的触发器中.您有一个 AFTER 触发器，因此您使用哪个 RETURN 并不重要，但我会选择 RETURN 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();

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:

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.

解决方案

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.

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.

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.

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屋！