MSSQL2000触发器更新上游单据已执行数量
环境:MSSQL2000
表申购单papplicebuy
申购单id,itemno,物料ID,物料数量 , 已下订数量
billid,itemno,materialid,quantity,vipreferqty
1001 , 1 , 7090, ,300 , 0
1001 , 2 , 8900, ,800 , 0 采购订单porderdetail
订单ID,itemno,物料ID,物料数量,引用billid, ,引用itemno
billid,itemno,materialid,quantity,referbillid,referitemno条件:pappliceby.billid=porderdetail.referbillid and
pappliceby.itemno=porderdetail.referitemno需求:采购订单保存后更新申购单vipreferqty的数量,
当保存后的采购单订单修改数量后重新更新申购单中的vipreferqty的数量,
当保存后的采购订单删除某项时要重新更新申购单中的vipreferqty的数量(减少执行数量)
(保存、修改、删除)
注:申购单可以是分多次下采购订单的。要求用触发器做。
环境:MSSQL2000
表申购单papplicebuy
申购单id,itemno,物料ID,物料数量 , 已下订数量
billid,itemno,materialid,quantity,vipreferqty
1001 , 1 , 7090, ,300 , 0
1001 , 2 , 8900, ,800 , 0 采购订单porderdetail
订单ID,itemno,物料ID,物料数量,引用billid, ,引用itemno
billid,itemno,materialid,quantity,referbillid,referitemno条件:pappliceby.billid=porderdetail.referbillid and
pappliceby.itemno=porderdetail.referitemno需求:采购订单保存后更新申购单vipreferqty的数量,
当保存后的采购单订单修改数量后重新更新申购单中的vipreferqty的数量,
当保存后的采购订单删除某项时要重新更新申购单中的vipreferqty的数量(减少执行数量)
(保存、修改、删除)
注:申购单可以是分多次下采购订单的。要求用触发器做。
on porderdetail
for insert,delete,update
as
set nocount on ;
select quantity,referbillid,referitemno
into #t
from inserted
union all
select -quantity,referbillid,referitemno
into #t
from deletedselect sum(quantity) as quantity,referbillid,referitemno
into #t1
from #t
group by referbillid,referitemnoupdate papplicebuy set
vipreferqty = isnull(papplicebuy.vipreferqty,0) + t.quantity
from #t t
where pappliceby.billid=t.referbillid and
pappliceby.itemno=t.referitemnogo没有测试,没有考虑不存在申购单的采购订单记录操作,需要的话自己慢慢改
on porderdetail
for insert,delete,update
as
set nocount on ;
select quantity,referbillid,referitemno
into #t
from inserted
union all
select -quantity,referbillid,referitemno
from deleted select sum(quantity) as quantity,referbillid,referitemno
into #t1
from #t
group by referbillid,referitemno update papplicebuy set
vipreferqty = isnull(papplicebuy.vipreferqty,0) + t.quantity
from #t t
where pappliceby.billid=t.referbillid and
pappliceby.itemno=t.referitemno go