数据库事务嵌套

begin tran--也可写成begin tran tranName，将@@trancount+1
save tran tranName---保存事务点。@@trancount不变，可以用rollback tran tranName回滚到保存点
rollback tran tranName---回滚事务到事务点，如果tranName为save tran tranName，则@@trancount不变，事务只回滚到tranName
                      ---如果tranName不是保存点，提交事务，@@trancount=0，不管现在@@trancount已经有几层事务
commit tran--也可写成commit tran tranName，将@@trancount-1
try....catch--错误级别<10的在try里继续往下执行，>10的中断try并跳到catch里，>20的并数据库还在连接状态的也在catch里执行
raiserror('消息',9,1)--用这个来做测试程序错误的测试
事务嵌套一般都是应对业务流程上的因素倒置的回滚


begin try
  begin tran
    raiserror('dfd',9,1)
    select @@error
    if @@error=0 select 'error0'
  commit tran
end try
begin catch
    if @@error=11 select 'error'
   select @@error,ERROR_MESSAGE()
end catch
/*
事务嵌套方法1：只有一个begin tran ，@@trancount永远为1，最外层根据里层的返回进行回滚，
如果外层没有数据，里层一定没有数据，如果外层有数据，里层可能有数据
*/
select 'try',@@error,ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE()
if object_id('p_out') is not null drop procedure p_out
if object_id('p_inner1') is not null drop procedure p_inner1
if object_id('p_inner2') is not null drop procedure p_inner2
if object_id('t_out') is not null drop table t_out
if object_id('t_inner1') is not null drop table t_inner1
if object_id('t_inner2') is not null drop table t_inner2
create table t_out(id int,name varchar(10)) select * into t_inner1 from t_out select * into t_inner2 from t_out
alter procedure p_out(@cmd1 varchar(10),@name1 varchar(20),@cmd2 varchar(10),@name2 varchar(20),@cmd3 varchar(10),@name3 varchar(20),@rs varchar(200) output)
as
begin try
    declare @tran int
    if @@trancount>0 set @tran=1 else set @tran=0
if @tran=1 save tran p_out else begin tran
        set XACT_ABORT off ---如果是on就会出错
        insert into t_out select 1,@name1
        exec p_inner1 @cmd2,@name2,@cmd3,@name3,@rs output
        if @cmd1='false' begin if @tran=1 rollback tran p_out else rollback tran select @rs='外层出错'+@rs return end
select @rs='外层成功'
if @tran=0 commit tran
end try
begin catch
    if @tran=1 rollback tran p_out else rollback tran 
select @rs='外层出错'+ERROR_MESSAGE()
end catch


alter procedure p_inner1(@cmd1 varchar(10),@name1 varchar(20),@cmd2 varchar(10),@name2 varchar(20),@rs varchar(200) output)
as
begin try
    declare @tran int
    if @@trancount>0 set @tran=1 else set @tran=0
if @tran>0 save tran p_inner1 else begin tran
        set XACT_ABORT off
        insert into t_inner1 select 1,@name1
        exec p_inner2 @cmd2,@name2,@rs output
        if @cmd1='false' begin if @tran=1 rollback tran p_inner1 else rollback tran select @rs='第一层出错'+@rs return end
    select @rs='第一层成功'+isnull(@rs,'')
if @tran=0 commit tran
end try
begin catch
    if @tran=1 rollback tran p_inner1 else rollback tran 
select @rs='第一层出错'+ERROR_MESSAGE()
end catch


alter procedure p_inner2(@cmd varchar(10),@name varchar(20),@rs varchar(200) output)
as
begin try
    declare @tran int
    if @@trancount>0 set @tran=1 else set @tran=0
if @tran=1 save tran p_inner2 else begin tran
        set XACT_ABORT off ---置为on时，如下下面insert代码出错，本存储过程就会出错
        insert into t_inner2 select 1,@name
        if @cmd='false' begin if @tran=1 rollback tran p_inner2 else rollback tran select @rs='第二层出错' return end
    select @rs='第二层成功'
if @tran=0 commit tran
end try
begin catch
    if @tran=1 rollback tran p_inner2 else rollback tran 
select @rs='第二层出错'+ERROR_MESSAGE()
end catch
--测试代码
declare @rs varchar(200)
exec p_out @cmd1='true',@name1='shengyu', @cmd2='true',@name2='shengyu', @cmd3='true',@name3='shengydfdsfdsfdsu',@rs=@rs output
select @rs


begin tran
declare @rs varchar(200)
exec p_inner1 @cmd1='true',@name1='dfgaf', @cmd2='tr',@name2='fdsfadfadadsdfu',@rs=@rs output
select @rs
commit tran


begin tran
declare @rs varchar(200)
 exec p_inner2 @cmd='false',@name='dfsff',@rs=@rs output
select @rs
commit tran