FOR ... BEGIN
INSERT_PROCEDURE RETURNING VALUE
UPDATE TABLE WITH VALUE
WHEN ... BEGIN
LOG
END
END

If the insert procedure directly or indirectly causes an exception the
insert will be abandoned, the update will be skipped, and all data from
previous or subsequent non-erroneous loop iterations will be preserved.

Now if the update fails, would the inserted data within the same loop
iteration be preserved? I've been assuming that everything within the
BEGIN...END was abandoned.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Friday, August 10, 2012 12:35 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Transactions & exception trapping

At 04:49 AM 10/08/2012, Rick Debay wrote:

>I have a table with before and after insert triggers. I also have a
>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?

Remember that an uncommitted transaction does not change database state.
Whatever is happening inside a transaction is known only to that
transaction.

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






------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links