CREATE TRIGGER Trig触发器.doc

CREATE TRIGGER Trig_C2进货单明细_D1库存表_UPDATEON C2进货单明细AFTER UPDATEAS SET NOCOUNT ON;WITH BAS(SELECT 商品ID, SUM(CASE WHEN 仓库=1 THEN 数量 ELSE 0 END) AS 仓1库存, SUM(CASE WHEN 仓库=2 THEN 数量 ELSE 0 END) AS 仓2库存, SUM(CASE WHEN 仓库=3 THEN 数量 ELSE 0 END) AS 仓3库存, SUM(CASE WHEN 仓库=4 THEN 数量 ELSE 0 END) AS 仓4库存, SUM(CASE WHEN 仓库=5 THEN 数量 ELSE 0 END) AS 仓5库存, SUM(CASE WHEN 仓库=6 THEN 数量 ELSE 0 END) AS 仓6库存(SELECT 入库仓库 AS 仓库,数量,入库通知ID,商品ID FROM INSERTED UNION SELECT 出库仓库 AS 仓库,-数量,入库通知ID,商品ID FROM INSERTED UNION ALL SELECT 出库仓库,数量,入库通知ID,商品ID FROM DELETED UNION ALL SELECT 入库仓库,-数量,入库通知ID,商品ID FROM DELETED )a INNER JOIN C6入库通知 c ON a.入库通知ID=c.入库通知IDGROUP BY 商品ID)UPDATE aSET 仓1库存 = ISNULL(a.仓1库存,0) +b.仓1库存, 仓2库存 = ISNULL(a.仓2库存,0) +b.仓2库存, 仓3库存 = ISNULL(a.仓3库存,0) +b.仓3库存, 仓4库存 = ISNULL(a.仓4库存,0) +b.仓4库存, 仓5库存 = ISNULL(a.仓5库存,0) +b.仓5库存, 仓6库存 = ISNULL(a.仓6库存,0) +b.仓6库存FROM b INNER JOIN D1库存表 a ON b.商品ID=a.商品IDCREATE TRIGGER Trig_C2进货单明细_D1库存表_UPDATEON C2进货单明细AFTER UPDATEAS SET NOCOUNT ONBEGIN UPDATE D1库存表 SET 仓1库存 = ISNULL(仓1库存,0) + CASE WHEN i.入库仓库 = 1 THEN ISNULL(i.数量,0) ELSE 0 END) - case when i.出库仓库 = 1 THEN ISNULL(i.数量,0) ELSE 0 END), 仓2库存 = ISNULL(仓2库存,0) + CASE WHEN i.入库仓库 = 2 THEN ISNULL(i.数量,0) ELSE 0 END) - case when i.出库仓库 = 2 THEN ISNULL(i.数量,0) ELSE 0 END), 仓3库存 = ISNULL(仓3库存,0) + CASE WHEN i.入库仓库 = 3 THEN ISNULL(i.数量,0) ELSE 0 END) - case when i.出库仓库 = 3 THEN ISNULL(i.数量,0) ELSE 0 END), 仓4库存 = ISNULL(仓4库存,0) + CASE WHEN i.入库仓库 = 4 THEN ISNULL(i.数量,0) ELSE 0 END) - case when i.出库仓库 = 4 THEN ISNULL(i.数量,0) ELSE 0 END), 仓5库存 = ISNULL(仓5库存,0) + CASE WHEN i.入库仓库 = 5 THEN ISNULL(i.数量,0) ELSE 0 END) - case when i.出库仓库 = 5 THEN ISNULL(i.数量,0) ELSE 0 END), 仓6库存 = ISNULL(仓6库存,0) + CASE WHEN i.入库仓库 = 6 THEN ISNULL(i.数量,0) ELSE 0 END) - case when i.出库仓库 = 6 THEN ISNULL(i.数量,0) ELSE 0 END) FROM D1库存表 A inner join INSERTED i on A.商品ID = i.商品ID WHERE (SELECT isnull(c.归档人,) FROM C6入库通知 c,INSERTED i WHERE c.入库通知ID = i.入库通知ID) != )ENDBEGIN UPDATE D1库存表 SET 仓1库存 = ISNULL(仓1库存,0) - CASE WHEN d.入库仓库 = 1 THEN ISNULL(i.数量,0) ELSE 0 END) + case when d.出库仓库 = 1 THEN ISNULL(i.数量,0) ELSE 0 END), 仓2库存 = ISNULL(仓2库存,0) - CASE WHEN d.入库仓库 = 2 THEN ISNULL(i.数量,0) ELSE 0 END) + case when d.出库仓库 = 2 THEN ISNULL(i.数量,0) ELSE 0 END), 仓3库存 = ISNULL(仓3库存,0) - CASE WHEN d.入库仓库 = 3 THEN ISNULL(i.数量,0) ELSE 0 END) + case when d.出库仓库 = 3 THEN ISNULL(i.数量,0) ELSE 0 END), 仓4库存 = ISNULL(仓4库存,0) - CASE WHEN d.入库仓库 = 4 THEN ISNULL(i.数量,0) ELSE 0 END) + case when d.出库仓库 = 4 THEN ISNULL(i.数量,0) ELSE 0 END), 仓5库存 = ISNULL(仓5库存,0) - CASE WHEN d.入库仓库 = 5 THEN ISNULL(i.数量,0) ELSE 0 END) + case when d.出库仓库 = 5 THEN ISNULL(i.数量,0) ELSE 0 END), 仓6库存 = ISNULL(仓6库存,0) - CASE WHEN d.入库仓库 = 6 THEN ISNULL(i.数量,0) ELSE 0 END) + case when d.出库仓库 = 6 THEN ISNULL(i.数量,0) ELSE 0 END) FROM D1库存表 A inner join INSERTED