XACT_ABORT can be used for an error handling to roll back all changes to original if there is any error occur in transactions. One of the main problem with XACT_ABORT as an error handling is it is difficult to trap errors and capture error numbers and returning back to the calling application. If you are not familiar with error numbers please refer my previous blog post about error numbers using the following link (Error Numbers) .
Let us see how we could use XACT_ABORT as error control and a workaround to solve the previously stated problem in action:
--1 Intialization IF OBJECT_ID('TESTTABLE','U') IS NOT NULL DROP TABLE TESTTABLE; GO SELECT * INTO TESTTABLE FROM tContract; SELECT COUNT(* ) TotalCount FROM TESTTABLE; GO |
--2 With XACT_ABORT OFF SET XACT_ABORT OFF; GO BEGIN TRAN DELETE TOP(3) FROM TESTTABLE IF @@ROWCOUNT |
-- Check the test table SELECT COUNT(* ) TotalCount FROM TESTTABLE GO |
In this case the after transaction rollback when we check the number of rows we have 16 rows. That means the first 3 records deleted before the error were not rollback.
--3 With XACT_ABORT ON and without TRY-CHATCH -- SET XACT_ABORT ON; GO BEGIN TRAN DELETE TOP(3) FROM TESTTABLE IF @@ROWCOUNT |
-- Check the test table SELECT COUNT(*) TotalCount FROM TESTTABLE GO |
Now we have 19 rows and none of the rows are missing but we still can’t control the error or any statement in the batch after the error could not be executed.
To avoid this problem we could use XACT_ABORT with TRY-CATCH. In the catch statement we have to rollback to avoid this error. To check if there is an error or now we could use @@TRANCOUNT OR a scalar function XACT_STATE(). XACT_STATE() has three values (1) There is active user transaction that could commit or roll back, (0) There is no active user transaction and (-1) There is active user transaction and an error makes it become uncommittable.
--4 With XACT_ABORT ON and with TRY-CHATCH -- SET XACT_ABORT ON; GO BEGIN TRY BEGIN TRAN DELETE TOP(3) FROM TESTTABLE IF @@ROWCOUNT 0 ROLLBACK TRANSACTION; IF XACT_STATE() = -1 ROLLBACK TRANSACTION; THROW; END CATCH GO SET XACT_ABORT OFF; GO |
-- Check the test table SELECT COUNT(*) TotalCount FROM TESTTABLE GO |
Using the try catch we were able to roll back all changes back to the original and control the error message.