环境: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.referitemnobillstate=0表示单据保存,billstate=1表示单据审核现在的软件原逻辑时采购订单审核后才更新申购单referqty的数量。(保存、审核分两个按钮的)需求结果:
在录入采购订单porderdetail时点击保存,要保证本次下订单时的数量
不能大于采购订单未下订单数量(包含已经下采购订单但未审数量),要求用触发器做。
表申购单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.referitemnobillstate=0表示单据保存,billstate=1表示单据审核现在的软件原逻辑时采购订单审核后才更新申购单referqty的数量。(保存、审核分两个按钮的)需求结果:
在录入采购订单porderdetail时点击保存,要保证本次下订单时的数量
不能大于采购订单未下订单数量(包含已经下采购订单但未审数量),要求用触发器做。
在录入采购订单porderdetail时点击保存,要保证本次下订单时的数量
不能大于申购单未下订单数量(包含已经下采购订单但未审数量),要求用触发器做。是这样吧?
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
这个要程序那边做,数据库做不了控件.如果你想要这样的话,最好用存储过程,把你想要判断的内容写进去,例如数量是否超过申购数量、ItemNo是否有问题.然后在程序那边调用这个存储过程,再次判断、弹出控件之类的.