SQL Server 的 SET XACT_ABORT ON | OFF;

 一、当 SET XACT_ABORT ON; 时，一个事务（BEGIN TRANSACTION; ......(SQL语句)......COMMIT TRANSACTION;）中，如果有报错，就回滚整个事务。

USE AdventureWorks2008R2;GOIF OBJECT_ID(N't2', N'U') IS NOT NULL    DROP TABLE t2;GOIF OBJECT_ID(N't1', N'U') IS NOT NULL    DROP TABLE t1;GOCREATE TABLE t1    (a INT NOT NULL PRIMARY KEY);CREATE TABLE t2    (a INT NOT NULL REFERENCES t1(a));GOINSERT INTO t1 VALUES (1);INSERT INTO t1 VALUES (3);INSERT INTO t1 VALUES (4);INSERT INTO t1 VALUES (6);GOSET XACT_ABORT OFF;GOBEGIN TRANSACTION;INSERT INTO t2 VALUES (1);INSERT INTO t2 VALUES (2); -- Foreign key error.INSERT INTO t2 VALUES (3);COMMIT TRANSACTION;GOSET XACT_ABORT ON;GOBEGIN TRANSACTION;INSERT INTO t2 VALUES (4);INSERT INTO t2 VALUES (5); -- Foreign key error.INSERT INTO t2 VALUES (6);COMMIT TRANSACTION;GO-- SELECT shows only keys 1 and 3 added. -- Key 2 insert failed and was rolled back, but-- XACT_ABORT was OFF and rest of transaction-- succeeded.-- Key 5 insert error with XACT_ABORT ON caused-- all of the second transaction to roll back.SELECT *    FROM t2;GO

二、当 SET XACT_ABORT ON; 的根据XACT_STATE()的状态判断是否回滚事务。

USE AdventureWorks2008R2;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 TRYBEGIN 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