日期:2014-05-17 浏览次数:20643 次
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