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:

Tables for Transaction example

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.