XACT_STATE() is a scalar function that gives the user transaction state of a current running request. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not.
XACT_STATE returns the following three values
1: The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
0: There is no active user transaction for the current request.
-1: The current request has an active user transaction, but an error has occurred that has
caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.
So before commit or rollback always test XACT_STATE for 0, 1, or -1.
BEGIN TRY BEGIN TRANSACTION; -- A FOREIGN KEY constraint exists on this table. -- This statement will generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980; -- If the delete operation succeeds, commit the transaction. The CATCH -- block will not execute. COMMIT TRANSACTION; END TRY BEGIN CATCH -- Test XACT_STATE for 0, 1, or -1. -- Test whether the transaction is uncommittable. IF (XACT_STATE()) = -1 BEGIN PRINT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test whether the transaction is active and valid. IF (XACT_STATE()) = 1 BEGIN PRINT 'The transaction is committable.' + ' Committing transaction.' COMMIT TRANSACTION; END; END CATCH
Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction.
@@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.
Note: XACT_STATE cannot be used to determine whether there are nested transactions.
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|By Ali Adravi On 28 Dec, 12 Viewed: 7,127|
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 783
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 844
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 2,408
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 582
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 11,100