In SQL Server 2000, we used to handle the errors while performing the DML queries by means of global variable @@Error. These errors may occur due to many reasons like data type mismatch, referential integrity violation etc. The @@error variable tells us whether the last statement (immediate before the @@error variable is used) caused any error or executed successfully. We could check its value for the same. If it is more then 0, then there is some error. But using @error variable & handling the error this way was not handy in solving many problems/requirements. For example if we have multiple DML statements being executed in a sequence and we want to take some action as soon as any of these statements causes any error. In that case after each & every transaction we have to check for @@error variable.
In SQL Server 2005, TRY-CATCH block was introduced to enhance the error handling process of TSQL. In this we can specify a TRY block and can catch the error(s) in CATCH block as caused by any of those queries defined in the TRY block. And this way we can either COMMIT or ROLLBACK them in TRY & CATCH respectively.
Now sometimes we need nested transactions to be configured. Nested transactions mean we may require having some inner transactions with one or more outer transaction. In that scenario, we must have to be very careful in Committing or rolling back the transaction.
Points to be remembered:
- A COMMIT statement in the Outer transaction will commit all the open inner transactions.
- A Commit statement in the Outer transaction will not commit the changes rolled back by the inner transactions.
- A Rollback statement in the Outer transaction will rollover all the inner transaction, irrespective of the Commit/Rollback fired in the inner transaction(s).
SELECT * FROM TESTTABLE
BEGIN TRANSACTION –Outer TRAN
BEGIN TRAN A –INNER TRAN
INSERT INTO TESTTABLE VALUES (11111)
COMMIT TRAN A
ROLLBACK TRAN A
ROLLBACK TRAN –Rollback all
Though we have committed the inner transaction (‘A’) after insertion, all data got rolled back by the Rollback statement of the outer transaction.
Note: In general, only open transactions can be committed or rolled back. So check it XACT_STATE() <> 0 before commit or rollback.