用触发器在进货表上,然后把二楼的代码添进去就可以了
create trigger trg1
on 单据明细
for insert,update,delete
as
begin
if exists(select 1 from deleted)
begin
update a
set
库存量=(select sum(数量) from 单据明细 where 编码=a.编码)
from
库存 a
where
exists(select 1 from deleted where 编码=a.编码)
end
if exists(select 1 from inserted)
begin
update a
set
库存量=(select sum(数量) from 单据明细 where 编码=a.编码)
from
单据明细 a
where
exists(select 1 from inserted where 编码=a.编码)
and
exists(select 1 from 库存 where 编码=a.编码)
insert into 库存(编码,库存量)
select
a.编码,sum(a.数量)
from
单据明细 a
where
exists(select 1 from inserted where 编码=a.编码)
and
not exists(select 1 from 库存 where 编码=a.编码)
end
end
go
create trigger trg1
on 单据明细
for insert,update,delete
as
begin
if exists(select 1 from deleted)
begin
update a
set
库存量=(select sum(数量) from 单据明细 where 编码=a.编码)
from
库存 a
where
exists(select 1 from deleted where 编码=a.编码)
end
if exists(select 1 from inserted)
begin
update a
set
库存量=(select sum(数量) from 单据明细 where 编码=a.编码)
from
单据明细 a
where
exists(select 1 from inserted where 编码=a.编码)
and
exists(select 1 from 库存 where 编码=a.编码)
insert into 库存(编码,库存量)
select
a.编码,sum(a.数量)
from
单据明细 a
where
exists(select 1 from inserted where 编码=a.编码)
and
not exists(select 1 from 库存 where 编码=a.编码)
end
end
go