CREATE trigger updatezongjs
on dbo.jihua_xijie
for update,insert, delete -- 删除要起作用, 得把删除的触发加上
as-- 处理不用写得那么复杂, 直接这样即可
update a set js = a.js + b.js
from jihua_zg a, (
select zongid, js = sum(js)
from(
select zongid, js from inserted
union all
select zongid, -js from deleted
)a group by zongid
)b
where a.id = b.zongid
on dbo.jihua_xijie
for update,insert, delete -- 删除要起作用, 得把删除的触发加上
as-- 处理不用写得那么复杂, 直接这样即可
update a set js = a.js + b.js
from jihua_zg a, (
select zongid, js = sum(js)
from(
select zongid, js from inserted
union all
select zongid, -js from deleted
)a group by zongid
)b
where a.id = b.zongid
on dbo.jihua_xijie
for update,insert,delete
as
if not exists (select 1 from deleted) and exists (select 1 from inserted)
begin
update zg set js=(zg.js+it.js) from jihua_zg as zg inner join Inserted AS it on it.zongid=zg.id
end
if exists (select 1 from inserted) and exists (select 1 from deleted)
begin
update zg set js=(zg.js-de.js) from jihua_zg as zg inner join Deleted AS de on de.zongid=zg.id
update zg set js=(zg.js+it.js) from jihua_zg as zg inner join Inserted AS it on it.zongid=zg.id
end
if exists(select 1 from deleted) and not exists(select 1 from inserted)
begin
update zg set js=(zg.js-de.js) from jihua_zg as zg inner join Deleted AS de on de.zongid=zg.id
end