[SQL]事务回滚详解及示例

存储过程中的 SET XACT_ABORT ON 和事务

在存储过程中写SET XACT_ABORT ON 有什么用？

SET XACT_ABORT ON是设置事务回滚的！
当为ON时，如果你存储中的某个地方出了问题，整个事务中的语句都会回滚
为OFF时，只回滚错误的地方
-----------------------------------------------------------------------------------------------
第一种情况：每次成功执行一条语句就立刻进行提交事务 （注意commit tran的位置）
use sales --指定数据库
go
 
alter table T_UserInfoTwo
add constraint ck_id check(id between 1 and 15) --给T_UserInfoTwo表的Id添加约束
go
 
if exists(select * from sys.objects where name='proc_userinfotwo_insert')
    drop proc proc_userinfotwo_insert --如果存在此存储过程则删除
go
 
create proc proc_userinfotwo_insert --创建存储过程
as
begin
    declare @id int
    set @id=1
    while @id<20
    begin
        begin try
            begin tran --开启事务（设置反悔点）
            insert into T_UserInfoTwo values(@id,'小雅',21,0,'18620005006','123@qq.com',' 湖南常德','朋友',1,'坏心眼女巫');        
            commit tran --提交事务(不反悔，将数据插入到表中)
        end try
        begin catch
            rollback tran --抛出异常后回滚
        end catch
 
        set @id    =@id+1;    --变量自增1
    end
end
go

-----------------------------------------------------------------------------------------------

第二种情况，当循环插入数据的时候，只要抛出异常，之前所有的 操作都进行回滚 （注意commit tran的位置与第一种情况是不一样的）

use sales --指定数据库
go
 
alter table T_UserInfoTwo
add constraint ck_id check(id between 1 and 15) --给T_UserInfoTwo表的Id添加约束
go
 
if exists(select * from sys.objects where name='proc_userinfotwo_insert')
    drop proc proc_userinfotwo_insert --如果存在此存储过程则删除
go
 
create proc proc_userinfotwo_insert --创建存储过程
as
begin
    declare @id int
    set @id=1
    begin                
        begin try
            begin tran --开启事务（设置反悔点）
            while @id<20
            begin                                                        
                insert into T_UserInfoTwo values(@id,'小雅',21,0,'18620005006','123@qq.com',' 湖南常德','朋友',1,'坏心眼女巫');    
                set @id    =@id+1;    --变量自增1                    
            end    
            commit tran --提交事务(不反悔，将数据插入到表中) 特别要注意这个commit tran的位置，不如果不想每执行完一条数据就提交事务，就应该讲这个commit tran放到while循环外面来。
        end try
        begin catch            
            begin
                rollback tran --抛出异常后回滚
            end            
        end catch        
    end
end
go