事务经典例子
<div id="cnblogs_post_body">SET QUOTED_IDENTIFIER OFFGO
SET ANSI_NULLS OFF
GO
ALTERproc dbo.XT_JFDHZP
@djbh varchar(11)
as
--启动事务处理
declare @return int
set @return=0
declare @tran_point int--控制事务嵌套
set @tran_point=@@trancount--保存事务点
if @tran_point=0
begin tran tran_jfdh
else
save tran tran_jfdh
INSERT INTO (,,,,,,,,jtjf, zbjf,,syjf)
select ,,,,,,,,jtjf, zbjf,,syjf from where djbh=@djbh
if @@error <> 0 or @@rowcount <> 1 ---汇总超过1条就报错
begin
set @return=1
goto err_lab
end
INSERT INTO (,,,,,,,,)
select ,,,,,,,, from where djbh=@djbh
if @@error <> 0 or @@rowcount = 0
begin
set @return=2
goto err_lab
end
--纸张积分
declare @zzjf decimal
set @zzjf=(select sum() from where djbh=@djbh)
--兑换明细所需积分总和
declare @jifen decimal
set @jifen=(select sum(sxjf) from Lsb_jfdhmx where djbh=@djbh)-@zzjf
--判断所需积分为负数就为零，防止可以重加积分
if @jifen<=0
begin
set @return=3
goto err_lab
end
declare @cardid varchar(20)
set @cardid=(select cardid from Lsb_jfdhhz where djbh=@djbh)
declare @huiyuanjifen decimal
set @huiyuanjifen=(select jifen from lscard where cardid = @cardid)
update lscard set jifen =jifen-@jifen where cardid = @cardid and beactive = '是'
if @@error <> 0 or @@rowcount = 0
begin
set @return=4
goto err_lab
end
declare @huiyuanjifen2 decimal
set @huiyuanjifen2=(select jifen from lscard where cardid = @cardid)
if(@huiyuanjifen2+@jifen <> @huiyuanjifen)
if @@error <> 0
begin
set @return=5
goto err_lab
end
delete Lsb_jfdhhz where djbh=@djbh
if @@error <> 0
begin
set @return=6
goto err_lab
end
delete Lsb_jfdhmx where djbh=@djbh
if @@error <> 0
begin
set @return=7
goto err_lab
end
--事务处理
if @tran_point=0
commit tran tran_jfdh
goto return_lab
err_lab:
rollback tran tran_jfdh
return @return
return_lab:
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
页:
[1]