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 theNEWrow forINSERTandUPDATEtriggers,and/or theOLDrow forUPDATEandDELETEtriggers. Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement.
NEW
Data typeRECORD; variable holding the new database row forINSERT/UPDATEoperations in row-level triggers. This variable isNULLin statement-level triggers and forDELETEoperations.
请注意它对行级触发器和语句级触发器的说明.
您有一个语句级触发器：
... 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
NULLor a record/row value having exactly the structure of the table the trigger was fired for.
[…]
The return value of a row-level trigger firedAFTERor a statement-level trigger firedBEFOREorAFTERis 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;