create proc ... as begin begin tran insert table1 ... if @@error<>0 ROLLBACK TRAN insert table2 ... if @@error<>0 ROLLBACK TRAN commit tran end
--因为出现不严重错误时,sql会忽略错误继续执行下去,所以用楼主的方法不能保证数据一致性--可以这样改 create proc ... as set xact_abort on --加上这句,这样在出现错误时,会终止并回滚事务 begin begin tran insert table1 ... insert table2 ... commit tran end
--或者这样写:create proc ... as begin begin tran insert table1 ... if @@error<>0 begin ROLLBACK TRAN return --出错时,要回滚事务并退出处理,否则一样会执行后面的语句 end insert table2 ... if @@error<>0 begin ROLLBACK TRAN return end commit tran end
create proc ... as begin begin tran insert table1 ... if @@error<>0 goto sqlerr insert table2 ... if @@error<>0 goto sqlerr commit tran end return sqlerr: rollback tran return
create proc ... as begin begin tran insert table1 ... if @@error<>0 goto sqlerr insert table2 ... if @@error<>0 goto sqlerr commit tran return sqlerr: rollback tran returnend
as
begin
begin tran
insert table1 ...
if @@error<>0
ROLLBACK TRAN
insert table2 ...
if @@error<>0
ROLLBACK TRAN
commit tran
end
create proc ...
as
set xact_abort on --加上这句,这样在出现错误时,会终止并回滚事务
begin
begin tran
insert table1 ...
insert table2 ...
commit tran
end
as
begin
begin tran
insert table1 ...
if @@error<>0
begin
ROLLBACK TRAN
return --出错时,要回滚事务并退出处理,否则一样会执行后面的语句
end insert table2 ...
if @@error<>0
begin
ROLLBACK TRAN
return
end
commit tran
end
as
begin
begin tran
insert table1 ...
if @@error<>0 goto sqlerr
insert table2 ...
if @@error<>0
goto sqlerr
commit tran
end
return
sqlerr:
rollback tran
return
as
begin
begin tran
insert table1 ...
if @@error<>0 goto sqlerr
insert table2 ...
if @@error<>0
goto sqlerr
commit tran
return
sqlerr:
rollback tran
returnend
它可能嵌套在其他transaction中,我看rollback是取消到最开始的beign tran那里,
这里rollback会不会影响上层的transaction?导致调用这个存储过程之前的没有提交,而它之后的被提交了这种?