CREATE TABLE MyTable
MyId [INT] IDENTITY (1,1),
BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
IF (@@TRANCOUNT = 0)
PRINT 'All transactions were rolled back'
PRINT 'Outer transaction is rolling back...'
DROP TABLE MyTable
Here are the options:
1. All transactions were rolled back
2. Outer transaction is rolling back...
3. ERROR: Incorrect syntax near 'WORK'.
Correct answer: All transactions were rolled back
Explanation: Issuing a ROLLBACK WORK rolls back all the way to the outer BEGIN TRANSACTION