SQL2008中事务TRANSACTION案例

来源：互联网发布：ubuntu升级系统编辑：程序博客网时间：2024/06/06 05:57

ALTER PROCEDURE [dbo].[usp_ins_dzdmx]
@djbh VARCHAR(20)
AS

   BEGIN TRANSACTION

   DELETE FROM DZDMX WHERE DJBH = @djbh
   IF @@ERROR <> 0
       begin
           ROLLBACK TRANSACTION
           RETURN
       END

    INSERT INTO DZDMX(DJBH,WLBH,COLOR,ZDW,FZDW,FSL,ZJ,SJ,ZSL,SL,DELDATE,BZ,   --pzj,   --psj,   --pzsl,   --psl,
   jtbh,
   pdate)
    select   @djbh,wlbh,color,zdw,fzdw,fsl,zj,sj,zsl,sl,delDate,bz,'','' FROM DZDMXtemp
    IF @@ERROR <> 0
       begin
           ROLLBACK TRANSACTION
           RETURN
       END

COMMIT TRANSACTION

===============================================================================================

ALTER PROCEDURE [dbo].[usp_ins_dzdmxtemp]
@djbh VARCHAR(20)
AS
BEGIN
BEGIN TRANSACTION

INSERT INTO DZDMXtemp
(
   wlbh,
   wlmc,
   ggxh,
   color,
   zperg,
   kgperm,
   zdw,
   zdwmc,
   fzdw,
   fzdwmc,
   fsl,
   zj,
   sj,
   zsl,
   sl,
   delDate,
   bz
)
select
   a.WLBH,
   b.wlmc,
   b.ggxh,
   a.color,
   b.zperg,
   b.kgperm,
   a.ZDW,
   (select dwmc from Unit where Unit.dwdm = a.zdw) zdwmc,
   a.FZDW,
   (select dwmc from Unit where Unit.dwdm = a.fzdw) fzdwmc,
   a.FSL,
   a.ZJ,
   a.SJ,
   a.ZSL,
   a.SL,
   a.DELDATE,
   a.BZ
FROM DZDMX a,dzdwl b
WHERE a.WLBH = b.wlbh
AND a.DJBH = @djbh

IF @@ERROR <> 0
       begin
           ROLLBACK TRANSACTION
           RETURN
       END
COMMIT TRANSACTION
end

0 0