日期:2014-05-18  浏览次数:20683 次

sql2000 事务问题
代码如下:其中三条语句,中间那条语句是会出错,我的想法是,如果任何一条语句出错,都回滚,但像现在这样写法,即使中间条语句出错,第一条和第三条还是照样运行,请教该如何写?
SQL code
begin tran
 delete from temp_001 where id>37000
 insert into temp_001 select null,0,0,0,0,0,0,0,0,0,0,0
 delete from temp_002 where id>37000

if @@error<>0 --判断如果三条语句有任何一条出现错误

begin
     rollback tran --–开始执行事务的回滚,
end else   --如何三条都执行成功
begin 
    commit tran --执行这个事务的操作
end





------解决方案--------------------
SQL code

begin tran
    delete from temp_001 where id>37000
    insert into temp_001 select null,0,0,0,0,0,0,0,0,0,0,0
    delete from temp_002 where id>37000
commit tran --执行这个事务的操作

if @@error<>0 --判断如果三条语句有任何一条出现错误     
    rollback tran --–开始执行事务的回滚,

------解决方案--------------------
SQL code
begin try
    begin tran
     delete from temp_001 where id>37000
     insert into temp_001 select null,0,0,0,0,0,0,0,0,0,0,0
     delete from temp_002 where id>37000
     commit tran
end try
begin catch
     rollback tran 
end  catch

------解决方案--------------------
SQL code
set xact_abort on
begin tran
 delete from temp_001 where id>37000
 insert into temp_001 select null,0,0,0,0,0,0,0,0,0,0,0
 delete from temp_002 where id>37000
commit tran