May 29, 2007 at 4:49 pm
I'm trying to import data into a dozen different tables, and if I hit any kind of error, I issue a rollback (to the beginning). My problem is that there are audit triggers on several of the tables and my rollback doesn't affect them. Consequently, whenever I hit a snag and have to do a rollback, the audit tables no longer match (and I have to clean them out as well). Is there a workaround for this?
May 30, 2007 at 12:09 pm
It's been my experience that triggers are rolled back along with the rest of the tran. What kind of import are you doing? What kind of triggers are you using?
May 31, 2007 at 9:03 am
Do you have an over-arching BEGIN TRAN before you do the first insert? If so, and as I understand it, a rollback will roll back EVERYTHING that occurred up to that point, including any trigger activity that may have occurred since the BEGIN TRAN statement.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2007 at 10:46 am
If you're nesting transactions, be aware that only the last commit actually does anything.
Here's how it works in SQL/Server:
Begin transaction - If @@trancount = 0, sets up transaction processing
Always increments @@trancount by 1
Commit Transaction - If @@trancount > 1, decrements @@trancount by 1
If @@trancount = 1 commits everything to the database.
Rollback transaction - Rolls back all work so far
sets @@trancount to 0
See http://msdn2.microsoft.com/en-us/library/ms187844.aspx for a description of transaction nesting and a warning about using Begin/end transaction in triggers
When unrolling nested transactions you'll need code like this in your error handler
IF @@TRANCOUNT > 1
COMMIT -- Looks crazy, but all it does is decrement @@trancount
IF @@TRANCOUNT = 1
ROLLBACK -- Rollback only at last level
This will prevent errors like 266 (Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN
is missing.)
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply