SET XACT_ABORT (T-SQL)

Specifies whether Microsoft® SQL Server™ automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error.

Syntax

SET XACT_ABORT{ON | OFF}

Remarks

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

It is recommended that XACT_ABORT be set ON when executing data modification statements against remote tables in an implicit or explicit transaction. For more information, see Distributed Queries and Distributed Transactions.

The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

Examples

This example causes a foreign key violation error in a transaction that has other Transact-SQL statements. In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. In the second set of statements, the SET XACT_ABORT setting is turned ON. This causes the statement error to terminate the batch and the transaction is rolled back.

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

See Also