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.
