begin tran begin try if(...) begin insert into... end if(...) begin insert into... end if(...) begin insert into... update a set x=..... end end try begin cathc rollback tran end catch commit tran
declare @StrSql varchar(4000)begin tran if(...) begin set @StrSql ='insert into...' end if(...) begin set @StrSql ='insert into...' end if(...) begin set @StrSql ='insert into... update a set x=.....' end exec (@StrSql ) if @@error <>0 begin rollback tran end commit tran
set xact_abort on BEGIN TRANSACTION tran_start --操作 if(@@error>0 or @@rowcount=0) begin goto err_lab end --操作 if(@@error>0 or @@rowcount=0) begin goto err_lab end commit tran tran_start err_lab: rollback tran tran_start
谢谢了,问完结贴给分,为什么 if(@@error>0 or @@rowcount=0)里面有个 or @@rowcount=0 insert和update也会得到@@rowcount=0的?
declare @t table(ID int ) insert @t select 1 insert @t select 2 insert @t select 3 update @t set id=4 where id=5 print @@rowcount /* (影響 0 個資料列)0*/
我对数据回滚一窍不通啊,有知道的请告诉一下
declare @StrSql varchar(4000)begin tran
if(...)
begin
set @StrSql ='insert into...'
end
if(...)
begin
set @StrSql ='insert into...'
end
if(...)
begin
set @StrSql ='insert into... update a set x=.....'
end
exec (@StrSql )
if @@error <>0
begin
rollback tran
end
commit tran
BEGIN TRANSACTION tran_start
--操作
if(@@error>0 or @@rowcount=0)
begin
goto err_lab
end
--操作
if(@@error>0 or @@rowcount=0)
begin
goto err_lab
end
commit tran tran_start
err_lab:
rollback tran tran_start
if(@@error>0 or @@rowcount=0)里面有个 or @@rowcount=0
insert和update也会得到@@rowcount=0的?
insert @t select 1
insert @t select 2
insert @t select 3
update @t set id=4 where id=5
print @@rowcount
/*
(影響 0 個資料列)0*/
然后用rollback transaction savepoint1回滚到指定保存点的位置。