--第一个用来控制销售表,如果增加了一个纪录,也就是说,xssl增加假设为5,那么产品表中cpkc字段
create trigger tr_process on 销售表
for insert,update,delete
as
select 产品编号,sl=sum(xssl) into #t
from(
select 产品编号,xssl from inserted
union all
select 产品编号,-xssl from deleted
)a group by 产品编号update a set cpkc=isnull(a.cpkc,0)-isnull(b.sl,0)
from 产品表 a,#t b
where a.产品编号=b.产品编号
go
create trigger tr_process on 销售表
for insert,update,delete
as
select 产品编号,sl=sum(xssl) into #t
from(
select 产品编号,xssl from inserted
union all
select 产品编号,-xssl from deleted
)a group by 产品编号update a set cpkc=isnull(a.cpkc,0)-isnull(b.sl,0)
from 产品表 a,#t b
where a.产品编号=b.产品编号
go
create trigger tr_process on 进货表
for insert,update,delete
as
select 产品编号,sl=sum(xssl) into #t
from(
select 产品编号,xssl from inserted
union all
select 产品编号,-xssl from deleted
)a group by 产品编号update a set cpkc=isnull(a.cpkc,0)+isnull(b.sl,0)
from 产品表 a,#t b
where a.产品编号=b.产品编号
go