http://msdn.microsoft.com/en-us/library/ms189797(v=sql.90).aspx USE AdventureWorks; GO -- SET XACT_ABORT ON will render the transaction uncommittable -- when the constraint violation occurs. SET XACT_ABORT ON; 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. -- If 1, the transaction is committable. -- If -1, the transaction is uncommittable and should -- be rolled back. -- XACT_STATE = 0 means there is no transaction and -- a commit or rollback operation would generate an error. -- 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; GO
