Last night I wrote a bit about an experience I had where the transaction blocked access.
So I decided to quickly write about how to rollback a transaction.
Suppose we have two tables:



I run the following SQL to illustrate wraping some INSERTs in a transaction.

BEGIN TRAN

BEGIN try

INSERT INTO TableOne VALUES ('aa')
INSERT INTO TableTwo  VALUES (SCOPE_IDENTITY(), 'bb')
INSERT INTO TableTwo  VALUES (-1, 'cc')
INSERT INTO TableOne VALUES ('aa')

END try
BEGIN catch
ROLLBACK TRAN
END catch

IF @@TRANCOUNT = 1 
   COMMIT TRAN

What happens here is the first insert will insert 'aa' into TableOne.
Then I use the identity value from TableOne and inserts into TableTwo in the subsequent insert. This will succeed. The third insert will try to insert a value -1 which will throw a foreign key restraint error.

Because I have wrapped in a transaction, and utilize the Try/Catch functionality (like in most other programming languages) – I can within rollback nicely within the "catch", and make sure nothing gets committed to the database.

The end of the query tests the transaction counter (@@TRANCOUNT) and whether or not the transaction should be committed. This way I avoid having open transactions, and I avoid trying to close a transaction which have already been handled. Each BEGIN TRAN(saction) will increment @@TRANCOUNT, whereas ROLLBACK and COMMIT will decrement it.