nesting tran

[lookie]

please look this:
begin tran
select @@trancount
begin tran
select @@trancount
rollback tran
select @@trancount
...select @@trancount
begin tran
select @@trancount
print 'hehe'
select * from table22
select @@trancount
rollback tran
select @@trancount
after rollback,I find the trancount==1.why the ROLLBACK cancel all level tran?can I only rollback the sub transaction?

 

[foxdev]

This is by design - though to me it seems quirky; a COMMIT in a nested transaction only commits that inner transaction, whereas an unqualified ROLLBACK rolls back to the outermost transaction. My WAG is that Microsoft figured that if you encountered a situation requiring a rollback anywhere in the process, its better to start over.

From BOL:

When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0.
...
In stored procedures, ROLLBACK TRANSACTION statements without a savepoint_name or transaction_name roll back all statements to the outermost BEGIN TRANSACTION.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org