create trigger tr_delete on b表
for deleted
as
update a表 set all_count=a.all_count-b.aa
from a表 a join(
select name,aa=sum([count]) from deleted group by name
)b on a.name=b.name
for deleted
as
update a表 set all_count=a.all_count-b.aa
from a表 a join(
select name,aa=sum([count]) from deleted group by name
)b on a.name=b.name
for deleted
as
update 表a set all_count=a.all_count-b.[count]
from 表a a join(
select name,[count]=sum([count]) from deleted group by name
)b on a.name=b.name
明天你还会来问更改的????create trigger tr_b
on b
for insert,delete,update
as
set nocount onselect name,sum([count]) as [count]
into #t
from (
select name,[count]
from inserted
union all
select name,-[count]
from deleted
) as x
group by nameupdate a
set all_count=all_count+x.[count]
from a,#t as x
where a.name=x.nameinsert a(name,all_count)
select name,[count]
from #t x
where not exists (
select 1 from a
where name=x.name
)go
on b
for insert,delete,update
as
set nocount onselect name,sum([count]) as [count]
into #t
from (
select name,[count]
from inserted
union all
select name,-[count]
from deleted
) as x
group by nameupdate a
set all_count=all_count+x.[count]
from a,#t as x
where a.name=x.nameinsert a(name,all_count)
select name,[count]
from #t x
where not exists (
select 1 from a
where name=x.name
)--以下实际不加也可以,不加再删改后可能有all_count=0的数据delete a
where all_count=0
go