sqlserver XACT_ABORT事物与回滚
--创建测试表
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
--插入测试数据
Java代码
---------------测试事务提交------------------
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
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
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
use MyDB
--事务在错误行终止，错误行回滚，错误行之前的不回滚，错误行之后的不执行
SET XACT_ABORT ON
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)
GO