触发器：订单通知单加单

最新推荐文章于 2020-04-12 12:33:44 发布

雁过留香

最新推荐文章于 2020-04-12 12:33:44 发布

阅读量303

点赞数

分类专栏： SQL触发器

本文链接：https://blog.csdn.net/qq_41969460/article/details/87879730

版权

SQL触发器专栏收录该内容

10 篇文章 0 订阅

订阅专栏

本文详细解析了一个SQL触发器的实现逻辑，该触发器用于维护数据库中订单通知单和销售订单之间的数据一致性。当更新特定状态时，触发器会检查是否存在重复的OrderNo和产品型号，防止数据冗余，并在满足条件时进行必要的数据插入和更新操作。

摘要由CSDN通过智能技术生成

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [GF_insert_ddtzd] on [dbo].[t_ddtzdIncrease]
for update
as

declare @FID int,@FStatus int,@FOldStatus int

select @FID = FID,@FStatus = FMultiCheckStatus from inserted
select @FOldStatus = FMultiCheckStatus from deleted

if update(FMultiCheckStatus) and @FStatus = 4 and @FOldStatus = 2
begin
       if exists (select 1 from t_bosddtzd d1
   inner join t_bosddtzdentry d2 on d1.FID = d2.FID
   inner join (select FBillNo_SRC1,b.FOrderNo,b.FItemID from t_ddtzdIncrease a
   inner join t_ddtzdIncreaseentry b on a.FID = b.FID
   where a.FID = @FID and a.FOrderType = '352190') t1
   on d1.FBillNo = t1.FBillNo_SRC1 and d2.FOrderNo = t1.FOrderNo and d2.FItemID = t1.FItemID)
       begin
               RAISERROR('原订单通知单存在相同OrderNo相同产品型号,不允许加单',16,1)
       end
end

if update(FMultiCheckStatus) and @FStatus = 16 and @FOldStatus = 4

begin
declare @FID_1 int ,@FMAXindex int

select @FMAXindex = max(Findex),@FID_1 = FID from t_bosddtzdentry
where FID in (select FID_SRC from t_ddtzdIncreaseentry where FID = @FID)
group by FID

declare @FinterID int
select @FinterID = a.FinterID from SEorder a
inner join SEorderentry b on a.FinterID = b.FinterID
where b.FSourceInterID in (select FID_SRC from t_ddtzdIncreaseentry where FID = @FID)

---插入订单通知单
Insert Into t_bosddtzdentry (FID,Findex,ForderNo,FSBUpo,Fitem,FitemID,FSBusku,FitemDesc,FBrand,Funit,Fqty,FPrice,FAmount,
FCasePack,FNoCartons,FDeliverTo,FFinalDest,FDateEstimate,FDateDue,FDateWG,FPackagCode,FLanguages,Fcontainer,Fstatus,FDateyh,FFree,FIncrease,FIncreaseID)

   select @FID_1,@FMAXindex+Findex,ForderNo,FSBUpo,Fitem,FitemID,FSBusku,FitemDesc,FBrand,Funit,Fqty,FPrice,FAmount,
   FCasePack,FNoCartons,FDeliverTo,FFinalDest,FDateEstimate,FDateDue,FDateWG,FPackagCode,FLanguages,Fcontainer,Fstatus,FDateyh,FFree,Findex,@FID
   from t_ddtzdIncrease a
   inner join t_ddtzdIncreaseentry b on a.FID = b.FID
   where a.FID = @FID

---更新加单源单行号
   update b set b.FEntryID_SRC = t1.FEntryID,b.FID_SRC = t1.FID,b.FBillNO_SRC = t2.FBillNO,b.FClassID_SRC = t2.FClassTypeID from t_ddtzdIncrease a
   inner join t_ddtzdIncreaseentry b on a.FID = b.FID
   inner join t_bosddtzdentry t1 on b.Findex = t1.FIncrease and b.FID = t1.FIncreaseID
   inner join t_bosddtzd t2 on t1.FID = t2.FID
   where a.FID = @FID
---更新加单行号标志
--   update t_bosddtzdentry set FIncrease = -1 from t_bosddtzdentry
--   where FID in (select FID_SRC from t_ddtzdIncreaseentry where FID = @FID)
--   and FIncrease = 0

---插入销售订单

       insert into seorderentry (fbrno,fentryid,finterid,fitemid,fqty,funitid,FPrice,FAmount,fauxqty,fdate,fadviceconsigndate,
   fsourcetrantype,fsourceinterid,fsourcebillno,fsourceentryid,
   FauxPrice,FallAmount,FallstdAmount,FauxPriceDiscount,FPriceDiscount,FauxtaxPrice,FtaxPrice,FBOMCategory,fentryselfs0176,FEntrySelfS0177)

   select '0',a.findex,@FInterID,a.FitemID,a.fqty,a.funit,a.FPrice,a.FAmount,a.fqty,a.FDatewg,a.FDatewg, b.fclasstypeid,b.fid,b.fbillno,a.findex,
   a.FPrice,a.FAmount,a.FAmount*Fexchangerate,a.FPrice,a.FPrice,a.FPrice,a.FPrice,'36820',a.FDatewg,a.FForecastNo
   from t_bosddtzdentry a
   inner join t_bosddtzd b on a.fid = b.fid
   inner join t_ddtzdIncreaseentry t1 on t1.Findex = a.FIncrease and t1.FID = a.FIncreaseID
   where a.FIncrease > 0 and a.FIncreaseID = @FID

end