SET XACT_ABORT
Specifies whether Microsoft® SQL ServerTM automatically returns the current transaction when the Transact-SQL statement generates an error error.
grammar
SET XACT_ABORT {on | OFF}
Comment
When the SET XACT_ABORT is ON, if the Transact-SQL statement generates an error, the entire transaction will terminate and roll back. When Off is OFF, only the incorrect Transact-SQL statement is generated, and the transaction will continue to process. Compilation errors (such as syntax errors) are not affected by Set XACT_ABORT.
For most OLE DB providers (including SQL Server), the data modification statement in the hidden or explicit transaction must set the XACT_ABORT to ON. The only thing that doesn't need this option is that the provider supports nested transactions. For more information, see Distributed query and distributed transactions.
Set XACT_ABORT settings are set when executed or runtime, not when analyzing.
Example
The following example causes a violation of foreign key errors in a transaction containing other Transact-SQL statements. In the first statement set, an error is generated, but other statements are successfully executed and transaction is successfully submitted. In the second statement set, SET XACT_ABORT is set to ON. This leads to statement errors to terminate batch, and roll back the transaction.
CREATE TABLE T1 (a Int Primary Key)
Create Table T2 (A Int References T1 (a))
Go
INSERT INTO T1 VALUES (1)
INSERT INTO T1 VALUES (3)
INSERT INTO T1 VALUES (4)
INSERT INTO T1 VALUES (6)
Go
SET XACT_ABORT OFF
Go
Begin TRAN
INSERT INTO T2 VALUES (1)
INSERT INTO T2 VALUES (2) / * Foreign Key Error * /
INSERT INTO T2 VALUES (3)
Commit TRAN
Go
SET XACT_ABORT ON
Go
Begin TRAN
INSERT INTO T2 VALUES (4)
INSERT INTO T2 VALUES (5) / * FOREIGN Key Error * /
INSERT INTO T2 VALUES (6)
Commit TRAN
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
DROP TABLE T2
Drop Table T1
Go
