38

I've been looking at the documentation of postgresql triggers, but it seems to only show examples for row-level triggers, but I can't find an example for a statement-level trigger.

In particular, it is not quite clear how to iterate in the update/inserted rows in a single statement, since NEW is for a single record.

diffeomorphism
  • 991
  • 2
  • 10
  • 27

2 Answers2

49

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 14
    I was hoping that the statement-trigger had some *equivalent* of `NEW` and `OLD` for the multiple-record case, and some way to iterate over those.. – diffeomorphism Jun 13 '14 at 01:40
  • 1
    That's really too bad, because it means you can only do RBAR operations in triggers. – gerrard00 Jun 30 '16 at 20:31
  • It appears that statement trigger is executed also if there are no rows affected. I don't need to analyze data inside, but I would like to know if there was any record updated. Any way to do it? – pankleks Mar 01 '17 at 20:40
  • @user1038334: Instead of a trigger, you could encapsulate the query in a function and check results. Details: http://stackoverflow.com/a/19393268/939860 – Erwin Brandstetter Mar 01 '17 at 23:52
  • 5
    @ErwinBrandstetter I think this has changed in newer versions... I believe there are now NEW and OLD tables accessible inside statement level triggers – user1760150 Mar 27 '19 at 15:38
  • 2
    @user1760150: True, a game-changer for this old question. I updated. – Erwin Brandstetter Mar 28 '19 at 01:28
  • @ErwinBrandstetter, Sorry, I can't post a comment with full format. I have some confuse with your example. Can we do like this one? CREATE OR REPLACE FUNCTION update_employee_ref() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN DELETE FROM emp_ref WHERE id in (SELECT ins_upd_del.id FROM TG_TABLE_NAME ins_upd_del WHERE ins_upd_del.emp_type = 'vip'); RETURN NULL; END; $$; CREATE TRIGGER update_employee_ref AFTER INSERT OR UPDATE OR DELETE ON employee FOR EACH STATEMENT EXECUTE FUNCTION update_employee_ref(); – Thinh Phan Aug 27 '19 at 15:03
9

Well, here are some examples of statement-level triggers.

Table:

CREATE TABLE public.test (
    number integer NOT NULL,
    text   character varying(50)
);

Trigger function:
OLD and NEW are still NULL
The return value can also be always left NULL.

CREATE OR REPLACE FUNCTION public.tr_test_for_each_statement()
   RETURNS trigger
   LANGUAGE plpgsql
AS
$$
DECLARE
    x_rec record;
BEGIN
    raise notice '=operation: % =', TG_OP;
    IF (TG_OP = 'UPDATE' OR TG_OP = 'DELETE') THEN
        FOR x_rec IN SELECT * FROM old_table LOOP
            raise notice 'OLD: %', x_rec;
        END loop;
    END IF;
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
        FOR x_rec IN SELECT * FROM new_table LOOP
            raise notice 'NEW: %', x_rec;
        END loop;
    END IF;

    RETURN NULL;
END;
$$;

Settings statement-level triggers
Only AFTER and only one event is supported.

CREATE TRIGGER tr_test_for_each_statement_insert
   AFTER INSERT ON public.test
   REFERENCING NEW TABLE AS new_table
   FOR EACH STATEMENT
   EXECUTE PROCEDURE public.tr_test_for_each_statement();

CREATE TRIGGER tr_test_for_each_statement_update
   AFTER UPDATE ON public.test
   REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table
   FOR EACH STATEMENT
   EXECUTE PROCEDURE public.tr_test_for_each_statement();

CREATE TRIGGER tr_test_for_each_statement_delete
   AFTER DELETE ON public.test
   REFERENCING OLD TABLE AS old_table
   FOR EACH STATEMENT
   EXECUTE PROCEDURE public.tr_test_for_each_statement();

Examples: 

INSERT INTO public.test(number, text) VALUES (1, 'a');

=operation: INSERT =
NEW: (1,a)

INSERT INTO public.test(number, text) VALUES (2, 'b'),  (3, 'b');

=operation: INSERT =
NEW: (2,b)
NEW: (3,b)

UPDATE public.test SET number = number + 1 WHERE text = 'a';

=operation: UPDATE =
OLD: (1,a)
NEW: (2,a)

UPDATE public.test SET number = number + 10 WHERE text = 'b';

=operation: UPDATE =
OLD: (2,b)
OLD: (3,b)
NEW: (12,b)
NEW: (13,b)

DELETE FROM public.test;

=operation: DELETE =
OLD: (2,a)
OLD: (12,b)
OLD: (13,b)

svasty
  • 181
  • 1
  • 2