存储过程中的事务:为什么一个事务中的某个INSERT语句失败了,数据不全部回滚,竟然删掉了???
存储过程内容如下:
CREATE PROCEDURE [op_carry_data]
@rtn int output
AS
declare @ErrorSave int
begin tran --开始事务了
insert into menuwater_c --执行成功了
select *
from menuwater where m_account=1
set @ErrorSave= @ErrorSave+@@error
insert into fmenuwater_c --执行成功了
select *
from fmenuwater where f_mcode in (select m_mcode from menuwater where m_account=1)
set @ErrorSave= @ErrorSave+@@error
delete from fmenuwater where f_mcode in (select m_mcode from menuwater where m_account=1)--执行成功了
set @ErrorSave= @ErrorSave+@@error
delete from menuwater where m_account=1--执行成功了
set @ErrorSave= @ErrorSave+@@error
if @ErrorSave<>0
begin
rollback tran
SELECT @rtn= - 10
print @rtn
return
end
--下面的INSERT出错了
insert into fgoodinout_c
select *
from fgoodinout
set @ErrorSave=@ErrorSave+@@error
insert into goodinout_c --这个INSERT也出错了
select *
from goodinout
set @ErrorSave=@ErrorSave+@@error
delete from goodinout --这个也删掉了
set @ErrorSave=@ErrorSave+@@error
delete from fgoodinout--这个也删掉了
set @ErrorSave=@ErrorSave+@@error
if @ErrorSave<>0
begin
rollback tran --可是这里已经ROLLBACK了?????
select @rtn= - 20
print @rtn
return
end
--结转完毕
--
UPDATE jstock
SET s_oldnum = isnull(s_currentnum,0) +isnull(s_oldnum,0 )
set @ErrorSave=@ErrorSave+@@error
if @ErrorSave<>0
begin
rollback tran
select @rtn= - 60
print @rtn
return
end
--结转完毕
--结账供应上的本期挂账
UPDATE victualer SET v_oldmoney=isnull( v_oldmoney,0) + isnull(v_money,0)
set @ErrorSave=@ErrorSave+@@error
if @ErrorSave<>0
begin
rollback tran
select @rtn= - 80
print @rtn
return
end
else
begin
SELECT @rtn=100
commit tran
end
return
GO
在执行到insert into fgoodinout_c时开始出错了,因为我没有每条语句都判断,所以连后面的DELETE也执行了。但是我已经set @ErrorSave=@ErrorSave+@@error,也判断了if @ErrorSave<>0 就ROLLBACK,可是为什么就是不ROLLBACK 呢???,包括上面所有执行成功的都应该一起ROLLBACK呀,为什么也没有?????
谢谢大家帮帮忙!到底问题出在那里了??????????