Transaction error control - XACT_ABORT & XACT_STATE (SQL 2012)

XACT_ABORT can be used to control the behaviour of SQL when an error in a transaction occurs.
(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

Links :
XACT_STATE (Transact-SQL)
XACT_ABORT (Transact-SQL)