Using multiple triggers on the same table in PostgreSQL

Posted on 2023-11-07 by Hans-Jürgen Schönig

“Developers, developers, developers, …” – let us not remind ourselves of this painful Microsoft presentation which was given by Mr Ballmer some time ago. But what happens if we deploy triggers, triggers, triggers in PostgreSQL? And what happens if we deploy them all on the very same table? That’s an interesting question, and important to consider when writing triggers. What order do the multiple triggers fire in? Let’s understand trigger execution order, but first, the syntax.

The syntax of `CREATE TRIGGER`

The CREATE TRIGGER command has become really powerful over the years. It offers a ton of functionality which we can use to leverage the power of our applications, here’s the syntax from the documentation:

postgres=# \h CREATE TRIGGER
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

As you can see, PostgreSQL will call a function when a trigger is executed. The beauty here is that PostgreSQL allows us to deploy as many triggers as we want on the very same table. The logical question then is: In which order are triggers executed? Can we expect deterministic execution order, or are things simply random?

The execution order of triggers in PostgreSQL

The good news is: triggers in PostgreSQL are not random. They follow a certain execution order.

The rule is:

PostgreSQL first executes statement-level BEFORE triggers, then row-level BEFORE triggers, then row-level AFTER triggers and finally statement-level AFTER triggers. If more than one trigger in the same category exists, they are executed in alphabetical order according to the trigger name.

(this holds true since PostgreSQL 7.3, which is around 20 years old).

In short: We can rely on a deterministic order.

The following code shows a basic example:

CREATE TABLE t_demo (id int);

CREATE FUNCTION trig_func() 
RETURNS trigger AS
$$
	BEGIN
		RAISE NOTICE 'name of trigger: %', TG_NAME;
		RETURN NEW;
	END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER c_trigger_bef 
	BEFORE INSERT 
	ON t_demo
	FOR EACH ROW 
	EXECUTE PROCEDURE trig_func();

CREATE TRIGGER b_trigger_bef 
	BEFORE INSERT 
	ON t_demo
	FOR EACH ROW 
	EXECUTE PROCEDURE trig_func();

CREATE TRIGGER a_trigger_bef 
	BEFORE INSERT 
	ON t_demo
	FOR EACH ROW 
	EXECUTE PROCEDURE trig_func();

CREATE TRIGGER c_trigger_aft 
	AFTER INSERT
	ON t_demo
	FOR EACH ROW 
	EXECUTE PROCEDURE trig_func();

CREATE TRIGGER b_trigger_aft 
	AFTER INSERT
	ON t_demo
	FOR EACH ROW 
	EXECUTE PROCEDURE trig_func();

What happens here is that we have a ton of triggers on the same table. It’s also important to see that we deploy the triggers in non-alphabetical order (we can deploy them in any order but to prove the point we used the “wrong” order).

This code snippet shows what really happens:

postgres=# INSERT INTO t_demo VALUES (1);
NOTICE:  name of trigger: a_trigger_bef
NOTICE:  name of trigger: b_trigger_bef
NOTICE:  name of trigger: c_trigger_bef
NOTICE:  name of trigger: b_trigger_aft
NOTICE:  name of trigger: c_trigger_aft
INSERT 0 1

The most important observation is that PostgreSQL does not care about the deployment order. As stated before, the entire order is fully deterministic. The TG_NAME variable which is set in every trigger by default shows us the name of the trigger that’s about to execute.

Note: the PostgreSQL execution order diverges from the order decreed by the SQL standard. That states that the triggers should execute in the order they were defined.

Finally …

The “How to write a trigger” post explains all you need to know about this very much underappreciated topic.
For information about simplifying code for row level triggers, see “What to return from a PostgreSQL row level trigger?“
See also “Triggers to enforce constraints in PostgreSQL“.
In case you need any assistance, please feel free to contact us.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.