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.<\/p>\n
<\/p>\n
Let’s create a simple setup:<\/p>\n
\npostgres=# create table t1 ( a int );\nCREATE TABLE\npostgres=# insert into t1 values (1),(2),(3);\nINSERT 0 3\npostgres=# create table t1_order ( a text, b timestamptz );\nCREATE TABLE\n<\/pre>\nThe 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.<\/p>\n
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:<\/p>\n
\npostgres=# create or replace function a_trg_func()\n returns trigger as\n$$\nbegin\n insert into t1_order values ('a_trg_func', clock_timestamp() );\n return new;\nend;\n$$ language plpgsql;\nCREATE FUNCTION\npostgres=# create or replace function b_trg_func()\n returns trigger as\n$$\nbegin\n insert into t1_order values ('b_trg_func', clock_timestamp() );\n return new;\nend;\n$$ language plpgsql;\nCREATE FUNCTION\n<\/pre>\nAs you can see, both functions will create a row in the second table, recording the current timestamp. The two triggers:<\/p>\n
\npostgres=# create or replace trigger a_trg\n before update\n on t1\n for each row\n execute procedure a_trg_func();\nCREATE TRIGGER\npostgres=# create or replace trigger b_trg\n before update\n on t1\n for each row\n execute procedure b_trg_func();\nCREATE TRIGGER\npostgres=# \n<\/pre>\nDoing a simple update on the first table will fire both triggers, and we should be able to compare the timestamps:<\/p>\n
\npostgres=# update t1 set a = 5 where a = 1;\nUPDATE 1\npostgres=# select * from t1_order;\n a | b \n------------+-------------------------------\n a_trg_func | 2021-09-15 13:49:26.468139+02\n b_trg_func | 2021-09-15 13:49:26.468324+02\n(2 rows)\n<\/pre>\nThe “a_trg” trigger was executed first, but maybe this was luck. Lets repeat the test:<\/p>\n
\npostgres=# truncate t1_order;\nTRUNCATE TABLE\npostgres=# update t1 set a = 8 where a = 5;\nUPDATE 1\npostgres=# select * from t1_order;\n a | b \n------------+-------------------------------\n a_trg_func | 2021-09-15 13:51:09.821264+02\n b_trg_func | 2021-09-15 13:51:09.823611+02\n(2 rows)\n<\/pre>\nSame 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:<\/p>\n
\npostgres=# alter trigger a_trg on t1 rename to z_trg;\nALTER TRIGGER\npostgres=# truncate t1_order;\nTRUNCATE TABLE\npostgres=# update t1 set a = -1 where a = 8;\nUPDATE 1\npostgres=# select * from t1_order;\n a | b \n------------+-------------------------------\n b_trg_func | 2021-09-15 13:53:13.942374+02\n a_trg_func | 2021-09-15 13:53:13.942723+02\n(2 rows)\n<\/pre>\n