Yuzifu Studio

/*库存数量表*/
CREATE TABLE tb_GoodsInv (
   InvID                int                  identity,
   GoodsID              int                  NOT NULL,
   DeptID               smallint             NOT NULL,
   Qty                  int                  NOT NULL,
   constraint PK_TB_GOODSINV PRIMARY KEY (InvID)
)
go
 
/*订单明细表*/
CREATE TABLE tb_ROLine (
   ROLineID             int                  identity,
   ROID                 int                  NOT NULL,
   GoodsID              int                  NOT NULL,
   OrigQty              int                  NOT NULL,
   RealQty              int                  NOT NULL,
   constraint PK_TB_ROLINE PRIMARY KEY (ROLineID)
)
go

CREATE TRIGGER tri_ro
ON tb_ROLine
FOR INSERT
AS
   UPDATE tb_GoodsInv SET qty=tb_GoodsInv.Qty+t.realQty
   FROM (
    SELECT goodsid ,sum(realQty) AS realqty
    FROM inserted
    GROUP BY goodsid
   ) AS t
   WHERE tb_GoodsInv.goodsid=t.goodsid
 
   INSERT tb_GoodsInv(goodsid,Qty)
    SELECT goodsid ,sum(realQty) AS realqty
    FROM inserted i
    WHERE NOT EXISTS (
       SELECT 1
       FROM tb_GoodsInv
       WHERE goodsid=i.goodsid
       )
    GROUP BY goodsid
go