110,557
社区成员
发帖
与我相关
我的任务
分享
--刪除數據時,減少庫存數量
Update TA_STOGoodsBalance
Set STO_004 = isnull(STO_004,0) - TDel.Qty
FROM TA_STOGoodsBalance as TBal Inner Join
(Select INT_003, INT_004,INT_005 ,SUM(isnull(INT_006,0)) as Qty
From Deleted
Group by INT_003, INT_004,INT_005) as TDel
ON TBal.STO_001 = TDel.INT_003 and
TBal.STO_002 = TDel.INT_004 and
TBal.STO_003 = TDel.INT_005
--插入數據時,增加庫存數量
Update TA_STOGoodsBalance
Set STO_004 = isnull(STO_004,0) + TIns.Qty
FROM dbo.TA_STOGoodsBalance as TBal Inner Join
(Select INT_003, INT_004,INT_005 ,SUM(isnull(INT_006,0)) as Qty
From Inserted
Group by INT_003, INT_004,INT_005) as TIns
ON TBal.STO_001 = TIns.INT_003 and
TBal.STO_002 = TIns.INT_004 and
TBal.STO_003 = TIns.INT_005
Insert Into dbo.TA_STOGoodsBalance(STO_001,STO_002,STO_003,STO_004)
Select INT_003, INT_004, ltrim(rtrim(INT_005)) ,SUM(isnull(INT_006,0))
From Inserted
Where CheckSUM(INT_003,INT_004,ltrim(rtrim(INT_005)) ) NOT IN
(Select CheckSUM( STO_001, STO_002,ltrim(rtrim(STO_003)))
From dbo.TA_STOGoodsBalance )
Group by INT_003, INT_004,ltrim(rtrim(INT_005))