Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
Syntax:
SET XACT_ABORT { ON | OFF }
Example
IF OBJECT_ID(N'tbl1', N'U') IS NOT NULL
DROP TABLE tbl1;
GO
IF OBJECT_ID(N'tbl2', N'U') IS NOT NULL
DROP TABLE tbl2;
GO
CREATE TABLE tbl1
(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE tbl2
(fld2 INT NOT NULL REFERENCES tbl1(fld1));
GO
INSERT INTO tbl1 VALUES (1);
INSERT INTO tbl1 VALUES (2);
INSERT INTO tbl1 VALUES (3);
INSERT INTO tbl1 VALUES (4);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO tbl2 VALUES (1);
INSERT INTO tbl2 VALUES (2);
INSERT INTO tbl2 VALUES (5); -- Foreign key error. SELECT shows only values 1 and 2 inserted. -- Values 5 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO tbl2 VALUES (3);
INSERT INTO tbl2 VALUES (5); -- Foreign key error.
-- Values 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
INSERT INTO tbl2 VALUES (4);
COMMIT TRANSACTION;
GO
SELECT * FROM tbl2;
GO
