求一库存的触发器的写法

[lyjone2003]

有两张表
表一入库表
商品编号，数量，价格，金额，入库单号

表二库存表
商品编号，库存数量，

如何用触发器实现在入库表改变时，修改库存表中的库存记录啊，库存开始可能为空

[pengdali]

CREATE TRIGGER 名1 ON 入库表
FOR INSERT
AS
update 库存表 set 库存数量=库存数量+tem.数量 from inserted tem where tem.商品编号=库存表.商品编号
insert 库存表 select 商品编号,sum(数量) from inserted where 商品编号 not in (select 商品编号 from 库存表) group by 商品编号
go

[pengdali]

CREATE TRIGGER 名1 ON 入库表
FOR INSERT
AS
update 库存表 set 库存数量=库存数量+tem.数量 from (select 商品编号,sum(数量) 数量 from inserted) tem where tem.商品编号=库存表.商品编号
insert 库存表 select 商品编号,sum(数量) from inserted where 商品编号 not in (select 商品编号 from 库存表) group by 商品编号
go

[lyjone2003]

谢谢大力，但是如果对入库表进行更新时，改变了入库表中的数量，怎么办啊，这时库存表要先删除原来的数量，然后再加上现在的数量，那么如何获得原来记录的数量呢？

[pengdali]

CREATE TRIGGER 名1 ON 入库表
FOR update
AS
update 库存表 set 库存数量=库存数量-tem.数量 from (select 商品编号,sum(数量) 数量 from deleted) tem where tem.商品编号=库存表.商品编号
update 库存表 set 库存数量=库存数量+tem.数量 from (select 商品编号,sum(数量) 数量 from inserted) tem where tem.商品编号=库存表.商品编号
go

[pengdali]

CREATE TRIGGER tigInMaterialIN ON KC_MaterialIN
FOR INSERT
AS

update KC_MaterialStock set
Quantity=KC_MaterialStock.Quantity+tem.Quantity ,
KC_MaterialStock.LastPurchaseDate = tem.Indate
from inserted tem where tem.MaterialID=KC_MaterialStock.MaterialID

insert KC_MaterialStock (MaterialID,Quantity,LastPurchaseDate) select MaterialID,sum(Quantity),max(Indate)
from inserted where MaterialID not in (select MaterialID from KC_MaterialStock) group by MaterialID
go

[lyjone2003]

to 大力

大虾，我的库存表中还有安全存量，最近入库日，最近出库日和一个自动增加的索引字段，我按你的写法，在查询分析中，总是通不过，这几个字段都可以为空，我不赋值，他报错，赋值也报错。请您帮我看看，不胜感激！！！

[lyjone2003]

CREATE TRIGGER tigInMaterialIN ON KC_MaterialIN
FOR INSERT
AS
update KC_MaterialStock set Quantity=KC_MaterialStock.Quantity+tem.Quantity ,LastPurchaseDate = tem.Indate from
(select MaterialID, Quantity ,Indate from inserted ) tem where tem.MaterialID=KC_MaterialStock.MaterialID
insert KC_MaterialStock select MaterialID, Quantity,Indate as LastPurchaseDate
from inserted where MaterialID not in (select MaterialID from KC_MaterialStock)
group by MaterialID
go

[lyjone2003]

赋值后，也还是不行啊
CREATE TRIGGER tigInMaterialIN ON KC_MaterialIN
FOR INSERT
AS
update KC_MaterialStock set Quantity=KC_MaterialStock.Quantity+tem.Quantity ,LastPurchaseDate = tem.Indate ,LastDeliveryDate='',SafeStock=500 from
(select MaterialID, Quantity ,Indate from inserted ) tem where tem.MaterialID=KC_MaterialStock.MaterialID
insert KC_MaterialStock select MaterialID, Quantity,Indate as LastPurchaseDate ,LastDeliveryDate='',SafeStock=500
from inserted where MaterialID not in (select MaterialID from KC_MaterialStock)
group by MaterialID
go