环境:MSQL2000
表申购单papplicebuy
申购单id,itemno,物料ID,物料数量 , 已下订数量 ,审核状态
billid,itemno,materialid,quantity,referqty ,billstate
1001 , 1 , 7090, ,300 , 0 , 1
1001 , 2 , 8900, ,800 , 0 , 1
采购订单porderdetail
订单ID,itemno,物料ID,物料数量,引用billid, ,引用itemno ,审核状态
billid,itemno,materialid,quantity,referbillid,referitemno ,billstate
申购单和采购订单的关系:pappliceby.billid=porderdetail.referbillid and
pappliceby.itemno=porderdetail.referitemno
billstate=0表示单据保存,billstate=1表示单据审核
现在的软件原逻辑时采购订单审核后才更新申购单referqty的数量。(保存、审核分两个按钮的)
需求结果:
在录入采购订单porderdetail时点击保存,要保证本次下订单时的数量
不能大于采购订单未下订单数量(包含已经下采购订单但未审数量),要求用触发器做。
------最佳解决方案--------------------
USE test
GO
---->生成表papplicebuy
--
--if object_id('papplicebuy') is not null
-- drop table papplicebuy
--Go
--Create table papplicebuy([billid] smallint,[itemno] smallint,[materialid] nvarchar(50),[quantity] smallint,[referqty] nvarchar(1),[billstate] smallint)
--Insert into papplicebuy
--Select 1001,1,'7090',300,N'0',1
--Union all Select 1001,2,'8900',800,N'0',1
--
--
---->生成表porderdetail
--
--if object_id('porderdetail') is not null
-- drop table porderdetail
--Go
--Create table porderdetail([billid] smallint,[itemno] smallint,[materialid] nvarchar(50),[quantity] smallint,[referbillid] smallint,[referitemno] smallint,[billstate] smallint)
Go
CREATE TRIGGER tr_cporderdetail
ON porderdetail
INSTEAD OF INSERT
AS
IF NOT EXISTS(
SELECT a.referbillid,a.referitemno,b.quantity FROM INSERTED AS a
INNER JOIN (SELECT
x.billid,x.itemno,SUM(x.quantity)-ISNULL(SUM(y.quantity),0) AS quantity
FROM papplicebuy AS x
LEFT JOIN porderdetail AS y ON x.billid=y.referbillid
AND x.itemno=y.referitemno
GROUP BY x.billid,x.itemno
) AS b ON a.referbillid=b.billid AND a.referitemno=b.itemno
GROUP BY a.referbillid,a.referitemno,b.quantity
HAVING SUM(a.quantity)>b.quantity
)
INSERT INTO porderdetail(billid,itemno,materialid,quantity,referbillid,referitemno,billstate)
SELECT
billid,itemno,materialid,quantity,referbillid,referitemno,billstate
FROM INSERTED
GO
--Insert into porderdetail
--Select 2001,1,'7090',100,1001,1,0
--Insert into porderdetail
--Select 2002,1,'7090',150,1001,1,0
--SELECT * FROM porderdetail
------其他解决方案--------------------
在录入采购订单porderdetail时点击保存,要保证本次下订单时的数量
不能大于申购单未下订单数量(包含已经下采购订单但未审数量),要求用触发器做。
是这样吧?
------其他解决方案--------------------