Informix triggers and rollback -- a curious result.
Blair McKay
triggers are concerned. As I understand it, if a trigger fails, both the
triggered statements it issues and the triggering statement that fired it
will fail and rollback. But if the triggering statement that fires it is
part of a larger multi-statement transaction, the other statements in that
transaction will NOT automatically rollback -- you need to detect the
failure and manually rollback.
My sources for this are a 10/18/1999 posting by Art Kagel ("Re: SE
Trigger/Exception problem") and a brief statement in "Informix Guide to
SQL - Syntax Version 7.1" (p. 1-190).
When I first set about to toy with this, to make sure I understood how to
catch the failure code with SQLCA, I found that the other statements in the
transaction were indeed rolling back.
Now, I'm extremely new to ESQL/C, and when I first constructed this, I put
the BEGIN WORK and COMMIT WORK statement right inside the transaction, and
sent the whole transaction to the DB in one statement, like in this
pseudocode:
gacBuffer =
"BEGIN WORK;
(statementA, which fires no trigger)
(statementB, which fires a trigger that fails)
COMMIT WORK"
EXEC SQL Prepare (gacBuffer)
EXEC SQL Execute (gacBuffer)
etc..., check SQLCA
In this case, SQLCA came back with the correct error code and the name of
the constraint violated by the trigger. StatementB did not excecute, and
StatementA ALSO did not execute.
So, I re-read my original sources, scratched my head, hauled out the ESQL/C
book, and realized that I wasn't quite constructing the transaction
correctly. So next I did this:
EXEC SQL BEGIN WORK;
gacBuffer =
"(statementA, which fires no trigger)
(statementB, which fires a trigger that fails)"
EXEC SQL Prepare (gacBuffer)
EXEC SQL Execute (gacBuffer)
check and print SQLCA
EXEC SQL COMMIT WORK
Certainly, this makes more sense, in that I only want to commit if SQLCA
shows that the transaction was successful.
So, I now see what should be done, but I'm curious about why my strange
"mistake" was able to succeed. I understand that a singleton transaction
will be rolled back if any trigger fired by it fails, and I guess that in
the first case, Informix took everything as a singleton transaction.
If so, would this not be an effective way to bundle a transaction, where a
trigger might require a full rollback? I know I did it wrong, but I'm not
seeing the downside. Again, I'm very new to Informix and ESQL/C, so forgive
me if the answer is well-known or obvious.
-Blair McKay
Jonathan Leffler
> I've been experimenting a bit with rollback and return codes where Informix
> triggers are concerned.
You don't tell us which version of the server you're using,
nor whether it is from the OnLine family or SE. Especially
for SE, the answers are different.
> As I understand it, if a trigger fails, both the
> triggered statements it issues and the triggering statement that fired it
> will fail and rollback.
Basically correct for OnLine and IDS etc, assuming you've got
a database with logging on tables with logging. It isn't a
ROLLBACK in the sense of terminating the transaction in general;
it is a ROLLBACK in the sense that either the statement as a
whole completes successfully or the database is left unchanged
as if the statement was never executed at all.
> But if the triggering statement that fires it is
> part of a larger multi-statement transaction, the other statements in that
> transaction will NOT automatically rollback -- you need to detect the
> failure and manually rollback.
Correct.
> My sources for this are a 10/18/1999 posting by Art Kagel ("Re: SE
> Trigger/Exception problem") and a brief statement in "Informix Guide to
> SQL - Syntax Version 7.1" (p. 1-190).
>
> When I first set about to toy with this, to make sure I understood how to
> catch the failure code with SQLCA, I found that the other statements in the
> transaction were indeed rolling back.
>
> Now, I'm extremely new to ESQL/C, and when I first constructed this, I put
> the BEGIN WORK and COMMIT WORK statement right inside the transaction, and
> sent the whole transaction to the DB in one statement, like in this
> pseudocode:
Hmmm; we have a terminologyt problem. A transaction is the
set of SQL statements between BEGIN WORK and either COMMIT WORK
or ROLLBACK WORK. The set of SQL statements might all be part
of a single PREPARE/EXECUTE job, or they might be multiple
separate statements. I'm ignoring the complications of a MODE
ANSI database!
> gacBuffer =
> "BEGIN WORK;
> (statementA, which fires no trigger)
> (statementB, which fires a trigger that fails)
> COMMIT WORK"
> EXEC SQL Prepare (gacBuffer)
> EXEC SQL Execute (gacBuffer)
> etc..., check SQLCA
One of the major problems with such a multi-statement prepare
is precisely the error handling. In general, IMNSHO, this is
a very bad idea. Use at least 4 separate statements.
> In this case, SQLCA came back with the correct error code and the name of
> the constraint violated by the trigger. StatementB did not excecute, and
> StatementA ALSO did not execute.
This is what I'd expect. I note that you would still be in
a transaction since the BEGIN WORK succeeded. If you did not
commit the transaction before terminating the program, it would
be rolled back. If you did commit the TX, then the changes
made by StatementA would be permanent but those from StatementB
would lost.
> So, I re-read my original sources, scratched my head, hauled out the ESQL/C
> book, and realized that I wasn't quite constructing the transaction
> correctly. So next I did this:
>
> EXEC SQL BEGIN WORK;
> gacBuffer =
> "(statementA, which fires no trigger)
> (statementB, which fires a trigger that fails)"
> EXEC SQL Prepare (gacBuffer)
> EXEC SQL Execute (gacBuffer)
> check and print SQLCA
> EXEC SQL COMMIT WORK
>
> Certainly, this makes more sense, in that I only want to commit if SQLCA
> shows that the transaction was successful.
Better, but in general, you have difficulty finding out
which record sets were updated and which were not.
> So, I now see what should be done, but I'm curious about why my strange
> "mistake" was able to succeed. I understand that a singleton transaction
> will be rolled back if any trigger fired by it fails, and I guess that in
> the first case, Informix took everything as a singleton transaction.
Each statement within a transaction either
completes succssfully or fails totally.
> If so, would this not be an effective way to bundle a transaction, where a
> trigger might require a full rollback? I know I did it wrong, but I'm not
> seeing the downside. Again, I'm very new to Informix and ESQL/C, so forgive
> me if the answer is well-known or obvious.
--
Jonathan Leffler (jlef...@informix.com, jlef...@earthlink.net)
Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN
#include <disclaimer.h>