Infrastructure at your Service

Daniel Westermann

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure