| Subject | Re: [firebird-support] Transactions & exception trapping |
|---|---|
| Author | Helen Borrie |
| Post date | 2012-08-10T04:35:06Z |
At 04:49 AM 10/08/2012, Rick Debay wrote:
When an exception occurs, control will pass down to the first WHEN... block it can find. If that WHEN block is located directly at the end of the block where the exception occurred, that block is executed (writes out your log record) and, provided it has no direction to raise the exception to an outer level, control passes back to the top of the same loop. The abandoned operation, including anything it did or was expected to do in triggers, is simply excluded from the list of stuff the transaction has to do when it finally commits. ACID.
Previous or subsequent operations will not be affected by the abandonment of the offending operation, unless something in the procedure logic makes the iterations interdependent. The database engine cannot undo changes made to local objects, such as variables and parameters so, when swallowing exceptions, there's more edge on the imperative to be explicit and correct with the initialisation and timing logic.
./hb
>I have a table with before and after insert triggers. I also have aRemember that an uncommitted transaction does not change database state. Whatever is happening inside a transaction is known only to that transaction.
>stored procedure that loops, inserting rows in to the table. The last
>statement in the loop is WHEN ANY to catch and log errors to an external
>table and continue looping.
>
>If an insert trigger throws an exception, will the insert be rolled back
>even though I'm catching the exception and continuing? Or will this
>subvert the triggers and allow inconsistent data?
When an exception occurs, control will pass down to the first WHEN... block it can find. If that WHEN block is located directly at the end of the block where the exception occurred, that block is executed (writes out your log record) and, provided it has no direction to raise the exception to an outer level, control passes back to the top of the same loop. The abandoned operation, including anything it did or was expected to do in triggers, is simply excluded from the list of stuff the transaction has to do when it finally commits. ACID.
Previous or subsequent operations will not be affected by the abandonment of the offending operation, unless something in the procedure logic makes the iterations interdependent. The database engine cannot undo changes made to local objects, such as variables and parameters so, when swallowing exceptions, there's more edge on the imperative to be explicit and correct with the initialisation and timing logic.
./hb