Home Library Learn Downloads Support Community
Sign in | United States - English | |
3 out of 5 rated this helpful - Rate this topic

SET XACT_ABORT (Transact-SQL)

SQL Server 2008

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Topic link iconTransact-SQL Syntax Conventions

Copy 

SET XACT_ABORT { ON | OFF }

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 SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. 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

The following code 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, SET XACT_ABORT is set to ON. This causes the statement error to terminate the batch and the transaction is rolled back.

Copy 
USE AdventureWorks;
GO
IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (a INT NOT NULL 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 TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN 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
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
XACT_ABORT is by default ON in triggers
This documentation should tell that XACT_ABORT is by default ON in triggers and OFF everywhere else ...
I found the information in technet.


By default, triggers execute with the XACT_ABORT setting ON. If a statement within a trigger causes an error while the Distribution Agent is applying changes at the Subscriber, the entire batch of changes will fail, rather than the individual statement. In transactional replication, you can use the -SkipErrors parameter of the Distribution Agent to skip statements that cause errors. If -SkipErrors is used with XACT_ABORT ON, the entire batch of changes is skipped if a statement causes an error. Unless you require XACT_ABORT to be set to ON in triggers, we recommend that you set it to OFF if you are using the -SkipErrors parameter. To set the option off, specifySET XACT_ABORT OFF in the trigger definition. For more information about XACT_ABORT, see SET XACT_ABORT (Transact-SQL). For more information about the -SkipErrors parameter, see Skipping Errors in Transactional Replication.

Benjamin.

History