CODEWITHSUNDEEP
postgresql
Geert-Jan
Geert-Jan

Reputation: 18905

when should I use an After trigger instead of a Before trigger?

Afaik, although I'm pretty new to Postgres, Before-triggers are less expensive then After-triggers.

After all, if you want to change the current record (using NEW), you can change the record before it is written. In contrast, with After-triggers you need two writes: 1 verbatim write and 1 as a result of the after-trigger.

At the same time, all functionality that is available in after-triggers seems to be available in before-triggers. If I'm not mistaken.

So why would you ever use After-triggers to begin with?

Upvotes: 2

Views: 1132

Answers (1)

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7541

If you're changing the record upon which the trigger is acting use a BEFORE trigger. If you're doing some complex logic that may prevent the record from being changed, use a BEFORE trigger.

Almost anything else, use an AFTER trigger. An example might be where you're inserting child records which rely upon the primary key of a record being inserted. For example, if you're adding an entry to a history table for a newly inserted row. The parent row won't exist in the BEFORE trigger, so would fail foreign key checks.

Upvotes: 3

Related Questions