FOR EACH STATEMENT and FOR EACH ROW triggers order of execution

user_13193213 0
Viewed 409

In a postgres database, say i have 3 tables, tb1, tb2 and tb3.

tb2 gets updated after insert on tb1 for each row using trigger T1, and tb3 gets updated after insert on tb1 for each statement using trigger T2.

my situation is I don't want tb3 to get updated until tb1 and tb2 finish updating, because she uses both.

now if I execute a query that inserts 10k lines on tb1 after the last line is inserted in tb1 the statement will end. and trigger T2 will fire. My question is, will T2 fire before tb2 gets its last 10k-th line or after?

If before, can you propose a solution so that tb3 doesn't get updated until after the two tables get both all the inserts finished?

plpgsql triggers postgresql
1 Answers

The documentation has the desired information:

Statement-level BEFORE triggers naturally fire before the statement starts to do anything, while statement-level AFTER triggers fire at the very end of the statement. These types of triggers may be defined on tables, views, or foreign tables. Row-level BEFORE triggers fire immediately before a particular row is operated on, while row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers).

[...]

If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name.

So you can rely on t2 running after t1.

user_64643080
Related