SET XACT_ABORT ON
BEGIN TRAN
--要执行的语句
COMMIT TRAN
GO

SET XACT_ABORT ON
BEGIN
--要执行的语句
END
GO

--创建测试表
use MyDB
CREATE TABLE student
(    
        stuid int NOT NULL PRIMARY KEY,
        stuname varchar(50)
)
CREATE TABLE score 
(
        stuid int NOT NULL REFERENCES student(stuid),
        score int
)
GO

--插入测试数据
INSERT INTO student VALUES (101,'zhangsan') 
INSERT INTO student VALUES (102,'wangwu') 
INSERT INTO student VALUES (103,'lishi') 
INSERT INTO student VALUES (104,'maliu') 
GO

---------------测试事务提交------------------
use MyDB
--只回滚错误行，语句还继续执行
SET XACT_ABORT OFF
BEGIN TRAN
INSERT INTO score  VALUES (101,90)
INSERT INTO score VALUES (102,78) 
INSERT INTO score VALUES (107,76) /* Foreign Key Error */ 
INSERT INTO score VALUES (103,81) 
INSERT INTO score VALUES (104,65) 
COMMIT TRAN
GO
/*
stuid       score
----------- -----------
101         90
102         78
103         81
104         65

(4 row(s) affected)
*/

use MyDB
--事务终止并全部回滚
SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO score  VALUES (101,90)
INSERT INTO score VALUES (102,78) 
INSERT INTO score VALUES (107,76) /* Foreign Key Error */ 
INSERT INTO score VALUES (103,81) 
INSERT INTO score VALUES (104,65) 
COMMIT TRAN
GO
/*
stuid       score
----------- -----------
(0 row(s) affected)
*/

use MyDB
--事务在错误行终止，错误行回滚，错误行之前的不回滚
SET XACT_ABORT ON
BEGIN
INSERT INTO score  VALUES (101,90)
INSERT INTO score VALUES (102,78) 
INSERT INTO score VALUES (107,76) /* Foreign Key Error */ 
INSERT INTO score VALUES (103,81) 
INSERT INTO score VALUES (104,65) 
END
GO
/*
stuid       score
----------- -----------
101         90
102         78
(2 row(s) affected)
*/