XACT_STATE is like @@TRANCOUNT function to detect if there is active transaction. But it has advantage over @@TRANCOUNT since it gives us more information whether the transaction is committable or not.
Let for example I have an delete operation and I want to insert the deleted rows to a log table inside a transaction and I want to make sure the delete succeeds even if the insert fails. Then XACT_STATE() comes handy.
--Let us get prepare our table and the log table CREATE TABLE table1 ( col1 INT IDENTITY(1, 1), col2 VARCHAR(20) ); go CREATE TABLE logtable ( col1 INT, col2 VARCHAR(20) ); go TRUNCATE TABLE table1; INSERT INTO table1(col2) VALUES ('To be deleted'); --Now let us start our transaction and move on SET XACT_ABORT ON; BEGIN try BEGIN TRANSACTION DELETE table1 WHERE col1 = 1; INSERT INTO logtable (col1, col2) SELECT col1, col2 FROM table1 WHERE col1 = 1; COMMIT TRANSACTION END try BEGIN catch IF ( Xact_state() ) = 1 BEGIN PRINT 'This is Committable transaction.'; COMMIT TRANSACTION; END ELSE IF ( Xact_state() ) = -1 BEGIN PRINT 'This is Uncommenting transaction.'; ROLLBACK TRANSACTION; END; END catch go |
Read More
http://msdn.microsoft.com/en-us/ms189797.aspx
http://msdn.microsoft.com/en-us/ms187967.aspx