SET XACT_ABORT ON/OFF测试

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 行受影响)
*/