Nested Transaction in Sql Server

The variable @@TranCount is used check the count of running transaction(s)

In nested transactions, the variable @@TranCount can be used to check the count of level of nested transactions.

In nested transaction, commit works only on the same level as of transaction (current transaction) i.e. all the statements under that level of transaction will be committed where as rollback always works on parent transaction i.e. all the statements of all the levels will be rolled back.

Rollback reverts all the changes even if any nested transaction was committed.

Example 1 -

SQL Command :

begin tran
print 'Tran Count on level 1st: ' + cast(@@trancount as varchar)
    insert into tbl_Employee_M select 1,'Mohan' 
    begin tran
    print 'Tran Count on level 2nd: ' + cast(@@trancount as varchar)
        insert into tbl_Employee_M select 2,'Sohan'     
    commit tran
    print 'Tran Count after 2nd leve commit : ' + cast(@@trancount as varchar)
rollback tran
print 'Tran Count after 1st level rollback : ' + cast(@@trancount as varchar)
select * from tbl_Employee_M
end tran



Note : Under this example, rollback revert back all transaction event level 2nd transaction is committed.

Example 2 -

SQL Command :

begin tran
print 'Tran Count on level 1st : ' + cast(@@trancount as varchar)
    insert into tbl_Employee_M select 1,'Mohan' 
    begin tran
    print 'Tran Count on level 2nd : ' + cast(@@trancount as varchar)
        insert into tbl_Employee_M select 2,'Sohan'     
    rollback tran
    print 'Tran Count after 2nd leve commit : ' + cast(@@trancount as varchar)
commit tran
print 'Tran Count after 1st level rollback : ' + cast(@@trancount as varchar)
select * from tbl_Employee_M
End tran



Note : Under this example this, it has rolled back all the transaction that is why there is no transaction left to commit.