此代码为一个触发器,应用环境如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | /*库存数量表*/ 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 |
要求订单明细表每增加一条记录,即同步向库存数量中相同的商品累加数量,若无记录则创建记录。
以下是这个让我看了三遍才明白的代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 |