22,209
社区成员
发帖
与我相关
我的任务
分享
set @result = 1
begin tran
insert table1(a) values(1)
if @@error!=0
begin
set @result = 2
rollback tran
return
end
insert table2(b) values(2)
if @@error!=0
begin
set @result = 3
rollback tran
return
end
set @result=200
commit tran
set @result = 1
BEGIN TRY
begin tran
set @result = 2
insert table1(a,status) values(1,0)
set @result = 3
insert table2(b,id1) values(2,@@identity)
set @result=200
commit TRAN
END TRY
BEGIN CATCH
RAISERROR(N'出错了',16,1)
ROLLBACK TRAN
END CATCH
B 存储过程
set @result = 1
if not exists(select 1 from table1 where a=1)
begin
set @result = 20
return
end
select @id = id from table1 where a=1
if not exists(select 1 from table2 where id1=@id)
begin
set @result = 21
return
end
BEGIN TRY
begin tran
set @result = 2
update table1 set status=1 where a=1
set @result = 3
insert table3(c) values(1)
set @result = 4
update table2 set id2=@@identity where id1 = @id
set @result=200
commit TRAN
END TRY
BEGIN CATCH
RAISERROR(N'出错了',16,1)
ROLLBACK TRAN
END CATCH
类似上面这种逻辑,现在情况是AB存储过程都正常返回200 ,出现异常时table1 table2 table3 都没有记录,日志记录自增长id是生成的,但我俩存储过程都没有删除table1和table2的操作,如果table1和table2没记录的话,B存储过程应该都不会执行到事务才是。set @result = 1
BEGIN TRY
begin tran
set @result = 2
insert table1(a) values(1)
set @result = 3
insert table2(b) values(2)
set @result=200
commit TRAN
END TRY
BEGIN CATCH
RAISERROR(N'出错了',16,1)
ROLLBACK TRAN
END CATCH