Postgres trigger after insert accessing NEW

by

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

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. Is SELECT or INSERT in a function prone to race conditions?
  3. Difference between language sql and language plpgsql in PostgreSQL functions
  4. How to return result of a SELECT inside a function in PostgreSQL?
  5. Pass multiple values in single parameter
  6. GROUP BY and aggregate sequential numeric values
  7. PL/pgSQL checking if a row exists
  8. PostgreSQL parameterized Order By / Limit in table function
  9. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  10. PostgreSQL IF statement
  11. Call a set-returning function with an array argument multiple times
  12. Record returned from function has columns concatenated
  13. Postgres Dynamic Query Function
  14. Return setof record (virtual table) from function
  15. Window Functions or Common Table Expressions: count previous rows within range
  16. DROP FUNCTION without knowing the number/type of parameters?
  17. Postgres function returning table not returning data in columns
  18. Return multiple columns and rows from a function PostgreSQL instead of record
  19. COPY with dynamic file name
  20. pseudo_encrypt() function in plpgsql that takes bigint
  21. How to perform a select query in a DO block?
  22. Function with SQL query has no destination for result data
  23. How do I query using fields inside the new PostgreSQL JSON datatype?
  24. PostgreSQL: using a calculated column in the same query
  25. Optimise PostgreSQL for fast testing
  26. How to delete duplicate rows without unique identifier
  27. SQL update fields of one table from fields of another one
  28. Table or column name cannot start with numeric?
  29. PostgreSQL column ‘foo’ does not exist
  30. Join a count query on generate_series() and retrieve Null values as ‘0’

Leave a Comment