LUCAB (9/14/2010)

---

I was wrong answer: "Outer transaction is still open....rolling back... ".
I learned that my wrong answer can be obtained with a SAVEPOINT:

SAVE TRAN InnerTranSavePoint    -- NEW CODE: SAVEPOINT

LUCAB (9/14/2010)

---

I was wrong answer: "Outer transaction is still open....rolling back... ".
I learned that my wrong answer can be obtained with a SAVEPOINT:

CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
                      MyCity NVARCHAR(50))

BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')

BEGIN TRANSACTION InnerTran
SAVE TRAN InnerTranSavePoint    -- NEW CODE: SAVEPOINT
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN InnerTranSavePoint  -- MODIFIED CODE: RETURN TO SAVE TRAN
 
IF (@@TRANCOUNT = 0)
BEGIN
   PRINT 'All transactions were rolled back'
 END
 ELSE
 BEGIN
   PRINT 'Outer transaction is still open....rolling back...'
 ROLLBACK TRANSACTION OuterTran
 END

DROP TABLE MyTable

BEGIN TRANSACTION InnerTran