Menu
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?
759
In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back.
The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the firing action will be rolled back too.
An AFTER INSERT Trigger means that MySQL will fire this trigger after the INSERT operation is executed.
Trigger functions invoked by per-row triggers can return a table row (a value of type HeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices: It can return NULL to skip the operation for the current row.
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.
103
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.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
