FOR EACH STATEMENT trigger example in PostgreSQL

by

OLD and NEW are null or not defined in a statement-level trigger. Per documentation:

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.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.

Bold emphasis mine.

Up to Postgres 10 this read slightly different, much to the same effect, though:

… This variable is unassigned in statement-level triggers. …

While those record variables are still of no use for statement level triggers, a new feature very much is:

Transition tables in Postgres 10+

Postgres 10 introduced transition tables. Those allow access to the whole set of affected rows. The manual:

AFTER triggers can also make use of transition tables to inspect the entire set of rows changed by the triggering statement.
The CREATE TRIGGER command assigns names to one or both transition
tables, and then the function can refer to those names as though they
were read-only temporary tables. Example 43.7 shows an example.

Follow the link to the manual for code examples.

Example statement-level trigger without transition tables

Before the advent of transition tables, those were even less common. A useful example is to send notifications after certain DML commands.
Here is a basic version of what I use:

-- Generic trigger function, can be used for multiple triggers:
CREATE OR REPLACE FUNCTION trg_notify_after()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   PERFORM pg_notify(TG_TABLE_NAME, TG_OP);
   RETURN NULL;
END
$func$;

-- Trigger
CREATE TRIGGER notify_after
AFTER INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_notify_after();

For Postgres 11 or later use the equivalent, less confusing syntax:

...
EXECUTE FUNCTION trg_notify_after();

See:

  • Trigger function does not exist, but I am pretty sure it does

Leave a Comment

tech