入库主表和入库明细表入库主表主要字段:InstoreID,warehouseID //入库单号和产品要入的仓库入库明细字段:InstoreID,ProductID,quantity //产品编号和产品数量库存表: productID,warehouseID,quantity //产品编号,仓库号,数量因为有多个仓库,一个产品可存在多个仓库中.我想在加入入库单后 自动更新该 入库单中的产品在库存信息中的数量,假若在库存信息表中没有该产品,则添加到库存表中。若有,则根据所入的仓库更新库存值
create trigger updateProductStore on instore
for insert
as
update productStore set quantity=quantity+a.totalsum
from (select productModNo,warehouseDesc,sum(quantity) totalsum from inserted group by productModNo) a where productModNo=a.productModNo and a.warehouseDesc=warehouseDescinsert productstore (productModNo,warehouseDesc,quantity) select productmodno,sum(quantity),wareHouseDesc from inserted where productModNo not in (select productmodNo from productStore) group by productModNo,wareHouseDesc
for insert
as
begin
update c
set
quantity=c.quantity+b.quantity
from
inserted a,入库主表 b,库存表 c
where
a.InstoreID=b.InstoreID and a.ProductID=c.ProductID and b.warehouseID=c.warehouseID
insert into 库存表
select
a.ProductID,b.warehouseID,a.quantity
from
inserted a,入库主表 b
where
a.InstoreID=b.InstoreID
and
not exists(select 1 from 库存表 where productID=a.productID and warehouseID=b.warehouseID)
end
go
create trigger trg_test on 入库明细表
for insert
as
begin
update d
set
quantity=d.quantity+c.quantity
from
(select
a.ProductID,b.warehouseID,sun(quantity) as quantity
from
inserted a,入库主表 b
where
a.InstoreID=b.InstoreID
group by
a.ProductID,b.warehouseID) c, 库存表 d
where
c.ProductID=d.ProductID and c.warehouseID=d.warehouseID
insert into 库存表
select
a.ProductID,b.warehouseID,sum(a.quantity)
from
inserted a,入库主表 b
where
a.InstoreID=b.InstoreID
and
not exists(select 1 from 库存表 where productID=a.productID and warehouseID=b.warehouseID)
group by
a.ProductID,b.warehouseID
end
go
for insert
as
update productStore
set quantity=quantity+a.totalsum
from
(
select productModNo,warehouseDesc,sum(quantity) totalsum
from inserted --貌似问题在这里,inserted中可能不包含quantity字段
group by productModNo
) a
where productModNo=a.productModNo and a.warehouseDesc=warehouseDesc insert productstore (productModNo,warehouseDesc,quantity)
select productmodno,sum(quantity),wareHouseDesc
from inserted
where productModNo not in (select productmodNo from productStore)
group by productModNo,wareHouseDesc处理办法:
1.将入库主表或者入库明细表合并,使用现有触发器。
2.使用存储过程插入数据,在存储过程中处理你的业务。