Re: SPL and transaction question

Timothy Jones wrote: > > Problem: > > I have 4 tables A, B, C, D > > I want to call a stored procedure (search) to search on A for a > particular column, pass the last row found from that search to procedure > (put) that puts each column into separate B, C tables. After the insert > I want to call another procedure (delete) to delete the row from D that > matches our insert. Then go back to original search for the second to > last row found in A until I reach the 1st row of the search. > > But, What if an error occured in Delete and I want to cancel all calls. WHEW! I assume your database has trnasactions. As I recall, all operations you execute in a stored procedure are treated as a single statement. (Sorta like the savepoints within a transaction y'all have been calling in feature requests about.) Ignoring the "ON EXCEPTION" stuff, if any procedure hits an error, it aborts what it did and propagates the error to whatever procedure called it. If the procedure *you* called receives an error (either its own on one propagated up), you get the error in your SQLCA (or SQLCODE for you ODBC weenies.. ;-). More important: The entire operation from the time you issued that call (the savepoint) has been rolled back by the time you see the error. Your transaction is still alive and you may chooses to commit work you already did before your fatefull SPL call. BTW, it is syntactically possible to issue "BEGIN WORK" in a stored procedure. I usually recommend against this because most SPL calls are issued from within a transaction. BTW-2: It is always a run-time error to issue a BEGIN or COMMIT from a procedure that was called from a trigger. This is actually a corrolary of the above BTW but it's not obvious. You have to realize that even a singleton operation (in non-ANSI) outside a procedure is automatically in its own transaction. Quite a mouthful.. -- -- Jake (In persuit of undomesticated aquatic avians) +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+