(it has been around since SQL 2000)
SET XACT_ABORT ON
If a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
SET XACT_ABORT OFF
Only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
XACT_STATE is new to SQL 2012.
It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
XACT_STATE() = 1 - Committing the transaction will suceed
XACT_STATE() = -1 - Committing the transaction will fail
XACT_STATE() = 0 - No user transaction for request.
SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- Do a DML operation - Insert, Delete or Update COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN PRINT 'Failed, Rollback occuring' ROLLBACK TRANSACTION; END; IF (XACT_STATE()) = 1 BEGIN PRINT 'Succeeded, Transaction committed' COMMIT TRANSACTION; END; END CATCH; GO