In which order do triggers fire in PostgreSQL?

When you are working with triggers it might be important to know in which order they fire. Of course, a “before” triggers fires before an “after” trigger. But what happens if you have two or more triggers for the same event? Maybe you want to make sure that a specific trigger is executed first (for whatever reason) or you want to make sure that another trigger is executed last. The good news is: You can make your triggers fire in the order you want, you just have to know the rules.

Let’s create a simple setup:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# insert into t1 values (1),(2),(3);
INSERT 0 3
postgres=# create table t1_order ( a text, b timestamptz );
CREATE TABLE

The first table will be used to attach triggers and the second table will be used to record the timestamp when a trigger fired. This should give as the answer about the order of execution.

Before we can create a trigger in PostgreSQL we need a trigger function, and we’ll create two, as we will create two triggers for the same event:

postgres=# create or replace function a_trg_func()
  returns trigger as
$$
begin
    insert into t1_order values ('a_trg_func', clock_timestamp() );
    return new;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# create or replace function b_trg_func()
  returns trigger as
$$
begin
    insert into t1_order values ('b_trg_func', clock_timestamp() );
    return new;
end;
$$ language plpgsql;
CREATE FUNCTION

As you can see, both functions will create a row in the second table, recording the current timestamp. The two triggers:

postgres=# create or replace trigger a_trg
  before update
  on t1
  for each row
  execute procedure a_trg_func();
CREATE TRIGGER
postgres=# create or replace trigger b_trg
  before update
  on t1
  for each row
  execute procedure b_trg_func();
CREATE TRIGGER
postgres=# 

Doing a simple update on the first table will fire both triggers, and we should be able to compare the timestamps:

postgres=# update t1 set a = 5 where a = 1;
UPDATE 1
postgres=# select * from t1_order;
     a      |               b               
------------+-------------------------------
 a_trg_func | 2021-09-15 13:49:26.468139+02
 b_trg_func | 2021-09-15 13:49:26.468324+02
(2 rows)

The “a_trg” trigger was executed first, but maybe this was luck. Lets repeat the test:

postgres=# truncate t1_order;
TRUNCATE TABLE
postgres=# update t1 set a = 8 where a = 5;
UPDATE 1
postgres=# select * from t1_order;
     a      |               b               
------------+-------------------------------
 a_trg_func | 2021-09-15 13:51:09.821264+02
 b_trg_func | 2021-09-15 13:51:09.823611+02
(2 rows)

Same picture, so it seams they are executed in alphabetical order. We can easily confirm this be renaming the “a_trg” trigger and then do the same test again:

postgres=# alter trigger a_trg on t1 rename to z_trg;
ALTER TRIGGER
postgres=# truncate t1_order;
TRUNCATE TABLE
postgres=# update t1 set a = -1 where a = 8;
UPDATE 1
postgres=# select * from t1_order;
     a      |               b               
------------+-------------------------------
 b_trg_func | 2021-09-15 13:53:13.942374+02
 a_trg_func | 2021-09-15 13:53:13.942723+02
(2 rows)

The order of execution switched, so you can force the order of execution by following a naming convention. The documentation is clear about this behavior: “SQL specifies that multiple triggers should be fired in time-of-creation order. PostgreSQL uses name order, which was judged to be more convenient..