前提条件:在物料资料上创建一个字段 用于存储即时库存,后续有表单需要用的话,根据物料资料自动带出该物料的即时库存字段即可
根据物料资料自动带出该物料的即时库存字段
USE 数据库名
--创建触发器 当库存数量变动的时候 更新物料资料表上的 库存数据
--创建触发器
create TRIGGER icinventoryupdate_mx
ON ICInventory
FOR INSERT, UPDATE, DELETE
AS
begin
update t_ICItemCustom set f_109 =
isnull((select sum(fqty) from ICInventory where ICInventory.fitemid=t_ICItemCustom.fitemid
and ICInventory.fstockid not in (select fitemid from t_stock where fnumber in ('11','12'))--如果不需要统计这些仓库,启用该语句
),0)
where fitemid in (select fitemid from inserted)
update t_ICItemCustom set f_109 =
isnull((select sum(fqty) from ICInventory where ICInventory.fitemid=t_ICItemCustom.fitemid
and ICInventory.fstockid not in (select fitemid from t_stock where fnumber in ('11','12'))--如果不需要统计这些仓库,启用该语句
),0)
where fitemid in (select fitemid from deleted)
end;
--初始化更新库存数量
update t_ICItemCustom set f_109 =
isnull((select sum(fqty) from ICInventory where ICInventory.fitemid=t_ICItemCustom.fitemid
and ICInventory.fstockid not in (select fitemid from t_stock where fnumber in ('11','12'))--如果不需要统计这些仓库,启用该语句
),0)