Postgres trigger after insert accessing NEW

Question

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.

Solution

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