CODEWITHSUNDEEP
postgresqltriggers
Diogo
Diogo

Reputation: 529

Postgres: Which is the order execution of triggers that are specified for different releations but activated by the same event?

I'm new to database triggers and I'm stuck with this:

Suppose that I have two different relations, R1 and R2, for which I specify two "independent" triggers. I mean, trigger TR1 for R1 and TR2 for R2. And suppose that both triggers are fired for the same event.

Which will be their execution order?

PostrgresSQL documentation states what happens for triggers defined on the same relation. But it's not clear about triggers defined on different relations, yet fired by the same event. I wonder if, in such situation, triggers are fired in a random order.

36.1. Overview of Trigger Behavior: 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. In the case of before triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any before trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired.

Upvotes: 3

Views: 2498

Answers (1)

pozs
pozs

Reputation: 36224

... suppose that both triggers are fired for the same event.

This can only happen in 2 cases (in PostgreSQL):

  1. With referential integrity constraint(s) (such as foreign keys)
  2. Cascade/recursive trigger firing

Referential integrity constraints

These type of constraints will create a trigger to ensure referential integrity. In case of foreign keys, the (automatically) created trigger will have names like RI_ConstraintTrigger... SQLFiddle. The same rule applies to them (they will be fired in alphabetical order by trigger name amongst other triggers) except in one case: they can be deferred (globally, or initially deferred per constraints). Which means that they will be executed only at the end of the current transaction (or when SET CONSTRAINTS changes the mode of a constraint from DEFERRED to IMMEDIATE).

You can also create custom constraint triggers with CREATE CONSTRAINT TRIGGER ..., which will behave the same.

Cascade/recursive trigger firing

This is explicitly mentioned on the docs:

If a trigger function executes SQL commands then these commands might fire triggers again. This is known as cascading triggers. There is no direct limitation on the number of cascade levels. It is possible for cascades to cause a recursive invocation of the same trigger; for example, an INSERT trigger might execute a command that inserts an additional row into the same table, causing the INSERT trigger to be fired again. It is the trigger programmer's responsibility to avoid infinite recursion in such scenarios.

Their order are pretty straightforward, f.ex:

If UPDATE on R1 will trigger TR1 that UPDATEs R2, which will trigger TR2 (and both BEFORE triggers):

1) BEFORE triggers of R1 (f.ex. TR1)
   1.1) BEFORE triggers of R2 (f.ex. TR2)
   1.2) INSTEAD OF triggers of R2 (or do the actual operation)
   1.3) AFTER triggers of R2
2) INSTEAD OF triggers of R1 (or do the actual operation)
3) AFTER triggers of R1

You could think, there is one more: what about inheritance?

Inheried tables not just doesn't inherit triggers, they won't fire their ancestors' triggers neither.

SQLFiddle

Upvotes: 2

Related Questions