可以用触发器。
入库
CREATE TRIGGER TR_MATMIOLIST_INSERT ON 入库表
FOR INSERT
AS
if not exists(select * from 库存表,inserted /*判断库存表中是否有入库产品信息*/
where 库存表_code=inserted.库存表_code
and 库存表_名称=inserted.库存表_名称
and 库存表_类别=inserted.库存表_类别)
insert into 库存
select 列1,.........................
from inserted
else
update 库存表
set 库存数量=库存数量+inserted.入库数,
stock_money=stock_money+inserted.rdepot_money
from inserted
where 库存表_code=inserted.库存表_code
and 库存表_名称=inserted.库存表_名称
and 库存表_类别=inserted.库存表_类别
出库
CREATE TRIGGER TR_MATMIOLIST_INSERT1 ON 出库表
FOR INSERT
AS
update 库存表
set 库存数量=库存数量-inserted.出库数,
from inserted
where 库存表_code=inserted.库存表_code
and 库存表_名称=inserted.库存表_名称
and 库存表_类别=inserted.库存表_类别
入库
CREATE TRIGGER TR_MATMIOLIST_INSERT ON 入库表
FOR INSERT
AS
if not exists(select * from 库存表,inserted /*判断库存表中是否有入库产品信息*/
where 库存表_code=inserted.库存表_code
and 库存表_名称=inserted.库存表_名称
and 库存表_类别=inserted.库存表_类别)
insert into 库存
select 列1,.........................
from inserted
else
update 库存表
set 库存数量=库存数量+inserted.入库数,
stock_money=stock_money+inserted.rdepot_money
from inserted
where 库存表_code=inserted.库存表_code
and 库存表_名称=inserted.库存表_名称
and 库存表_类别=inserted.库存表_类别
出库
CREATE TRIGGER TR_MATMIOLIST_INSERT1 ON 出库表
FOR INSERT
AS
update 库存表
set 库存数量=库存数量-inserted.出库数,
from inserted
where 库存表_code=inserted.库存表_code
and 库存表_名称=inserted.库存表_名称
and 库存表_类别=inserted.库存表_类别
isnull(SELECT SUM(num)
FROM t_out
WHERE name = t_in.name),0) AS t_out_sum
FROM t_in
GROUP BY name
SELECT name, SUM(num) -
isnull((SELECT SUM(num)
FROM t_out
WHERE name = t_in.name),0) AS t_out_sum
FROM t_in
GROUP BY name