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;
SELECT * FROM t2
--SET XACT_ABORT OFF
--1,3符合要求所以插入了,2不符合所以报错。但提交时不是一个整体
/*
a
1
3
*/
GO
SET XACT_ABORT ON;
GO
--SET XACT_ABORT ON
--4,5,6都没有插入。因为提交时是一个整体
--注:必须设置显式事务
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
SELECT * FROM t2;
/*
a
1
3
*/
GO
注:加了TRY CATCH之后出错再回滚,不管怎么设置,还是整体回滚的
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
BEGIN TRY
BEGIN TRANSACTION;
SET XACT_ABORT OFF;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
--加了 TRY CATCH 之后,回滚事务不受 SET XACT_ABORT OFF 影响,全部都没有插入
SELECT * FROM t2
/*
(0 行受影响)
*/