MS SQL server Transaction commit rollback
To know your transaction is getting rolled back you need to generate an exception.
This will ensure that transcation is rolled back.
Trick is very simple, just write statement select 1/0 at the end bo transaction block as shown in below query.
select 1/0 will generate an exception and your transaction needs to be rolled back.
Begin transaction MyTransactionName
-- Delete some rows from sql server
-- insert deleted rows into other table
select 1/0 -- This will generate an exception and your transaction needs to be rolled back
commit transaction MyTransactionName
rollback transaction MyTransactionName
If you are using linked server, you need to enable MSDTC services (Microsoft distributed transaction co-ordinates).