Are constraints executed before or after customized trigger?

Question

What is the order of execution of triggers and constraint ? For example, if I insert a bad foreign key, will this fk be detected by pgsql before my own trigger (BEFORE or AFTER) ?

I know an exception inside a trigger will rollback any update/insert executed inside this same trigger (even for AFTER trigger) but is it the same for constraints ? Are foreign key constraints make rollback all my insert/update from my own trigger ?

thank you

Answer 1

I think the Postgres documentation is pretty clear on this (my highlighting of the relevant part):

The trigger can be specified to fire before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted); or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed); or instead of the operation (in the case of inserts, updates or deletes on a view). If the trigger fires before or instead of the event, the trigger can skip the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only). If the trigger fires after the event, all changes, including the effects of other triggers, are "visible" to the trigger.

So a "before" or "instead of" trigger does not do the constraints first.

Answer 2

If after trigger then execute constraint first then execute trigger.

If before trigger then execute trigger first then after execute constraint.