Tag: SQL

此代码为一个触发器，应用环境如下：

/*库存数量表*/
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