Informix triggers and rollback -- a curious result.

I've been experimenting a bit with rollback and return codes where Informix 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

Blair McKay wrote: > 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 (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>