CREATE TABLE MyTable
(
MyId [INT] IDENTITY (1,1),
MyCity [NVARCHAR](50)
)
BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
ROLLBACK WORK
IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is rolling back...'
ROLLBACK WORK
END
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'.