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