SET XACT_ABORT的功能演示

SET XACT_ABORT的SQL语句执行情况演示：

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 (1,'a') 
INSERT INTO student VALUES (2,'b') 
INSERT INTO student VALUES (3,'c') 
INSERT INTO student VALUES (4,'d') 
GO

--1.无事务，只回滚当前行
SET XACT_ABORT off
BEGIN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
END
go
select * from score
go
truncate table score
go

--2.有事务，只回滚当前行
SET XACT_ABORT OFF
BEGIN TRAN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
COMMIT TRAN
go
select * from score
go
truncate table score
go
--3.无事务，中止执行
SET XACT_ABORT on
BEGIN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
END
go
select * from score
go
truncate table score
go
--4.有事务，回滚整个事务
SET XACT_ABORT on
BEGIN TRAN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
COMMIT TRAN
go
select * from score
go
truncate table score
go
drop table score
drop table student

存储过程情况演示：

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 (1,'a') 
INSERT INTO student VALUES (2,'b') 
INSERT INTO student VALUES (3,'c') 
INSERT INTO student VALUES (4,'d') 
GO

--1.无事务，只回滚当前行
create procedure dbo.xact_test
as
SET XACT_ABORT off
BEGIN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
END
select * from score
go
exec dbo.xact_test
go
truncate table score
go
--2.有事务，只回滚当前行
alter procedure dbo.xact_test
as
SET XACT_ABORT OFF
BEGIN TRAN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
COMMIT TRAN
select * from score
go
exec dbo.xact_test
go
truncate table score
go
--3.无事务，中止执行
alter procedure dbo.xact_test
as
SET XACT_ABORT on
BEGIN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
END
select * from score
go
exec dbo.xact_test
go
truncate table score
go
--4.有事务，回滚整个事务
alter procedure dbo.xact_test
as
SET XACT_ABORT on
BEGIN TRAN
    INSERT INTO score VALUES (1,1)
    INSERT INTO score VALUES (2,2) 
    INSERT INTO score VALUES (5,5) /* Foreign Key Error */ 
    INSERT INTO score VALUES (3,3) 
    INSERT INTO score VALUES (4,4) 
COMMIT TRAN
select * from score
go
exec dbo.xact_test
go
truncate table score
go
drop proc dbo.xact_test
drop table score
drop table student

SQL语句和存储过程的不同在于第3个测试中。