August 14, 2009 at 1:02 pm
Can someone tell me why this script doesn't work?
CREATE TABLE #temp (test INT)
INSERT INTO #temp VALUES (1)
BEGIN TRANSACTION t1
INSERT INTO #temp VALUES (2)
BEGIN TRANSACTION t2
INSERT INTO #temp VALUES (3)
ROLLBACK TRANSACTION t2 --this is the line that is changing
SELECT * FROM #temp
commit TRANSACTION t1
SELECT * FROM #temp
DROP TABLE #temp
But this script does work?
CREATE TABLE #temp (test INT)
INSERT INTO #temp VALUES (1)
BEGIN TRANSACTION t1
INSERT INTO #temp VALUES (2)
BEGIN TRANSACTION t2
INSERT INTO #temp VALUES (3)
commit TRANSACTION t2
SELECT * FROM #temp
commit TRANSACTION t1
SELECT * FROM #temp
DROP TABLE #temp
August 14, 2009 at 1:05 pm
You need to lookup SAVE TRANSACTION in BOL (Books Online) You need to create a TRANSACTION SAVE point in the nested transactions if you want to be able to rollback a transaction inside another one and still be able to commit the outer transaction.
August 14, 2009 at 1:09 pm
Wow, thank you. I have yet to use nested transactions like this and thought I was going crazy.
August 14, 2009 at 1:13 pm
So if you are doing nested transactions like this you don't need to have the inner "begin transaction" just a "save transaction" statement? I tested this and it works but I am trying to figure out if I am missing something by not using the "begin transaction"
EDIT: I just changed my outer "begin transaction" to be a "save transaction" and it worked fine. So I guess my question is why would you use a "begin transaction" statement?
Sorry I didn't check my @@trancount. I tried to close that window and apparently I had a few open transactions still.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply