sql – 在插入访问NEW后的Postgres触发器

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,NEW.benutzer_id)"
PL/pgSQL function "f_log_datei" line 3 at SQL statement

为什么我得到这个错误？我查看了文档,似乎他们以同样的方式使用新的文件.

解决方法

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

我刚刚将每个声明改为“每一个道路”.

你的触发器也应该是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;在你的触发器.你有一个AFTER触发器,所以你使用哪个RETURN并不重要,但我会与RETURN NEW;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!