AFTER INSERT trigger in separate transaction?

Question

Will an AFTER INSERT trigger (function written in pl/PGsql) fire in a separate transaction than the original insert?

What I'm concerned about is if the trigger experiences an exception of some kind.
Can the trigger be rolled back without the original insert being affected?

Will that separate transaction be able to be rolled back independently from the main transaction? — wildplasser, May 23 '12 at 20:30

Jirka Hanika · Accepted Answer · 2012-05-23T20:54:11.990

30

All PostgreSQL triggers execute in the same transaction as the transaction that has triggered them.

Edit: You can also use LISTEN + NOTIFY to send a message from your trigger to a code that executes outside of the transaction. In that case, the message will only be delivered at the point of a successful commit. Errors in listeners will not roll back the triggering transaction.

edited May 23 '12 at 20:54

answered May 23 '12 at 20:39

Jirka Hanika

13,301
3
46
75

score 10 · Answer 2 · edited May 24 '12 at 08:49

10

Trigger procedures run in the same transaction as associated triggering events. But the effects of a trigger procedure can be rolled back separately.

You have to add exception handling to the after trigger.

edited May 24 '12 at 08:49

Erwin Brandstetter

605,456
145
1,078
1,228

answered May 24 '12 at 02:37

Scott Marlowe

8,490
3
23
21

AFTER INSERT trigger in separate transaction?

2 Answers2

Linked