sql server update trigger

jy01378524

There are two tables:
table1 (id, amount)
table2 (id, sum)

I want to get an update trigger. When table1.amount changes, table2.sum also changes.


How much table1.amount increases or decreases and how much table2.sum also increases or decreases
table1.amount is not equal to table2.sum
The accumulation of table1.amount is not table2.sum

德鲁伊

create trigger trigger name on table1
instead of update
as

   --update update
   declare @ old, @ new int
   if exists (select 1 from inserted) and exists (select 1 from deleted)
   begin
        set @ old = (select amount from deleted)
        set @ new = (select amount from inserted)
        update table2 set sum = sum + @ new- @ old where id = (selelct id from inserted)
   end

go

michealhenry

create trigger t_update on table1
for update
as
begin
update t2 set t2.sum = t2.sum + t1.addamount
from table2 t2 inner join
(select a.id, b.amount-a.amount as addamount
 from deleted a inner join inserted b on a.id = b.id)
t1 on t1.id = t2.id
end
go

德鲁伊

table1.amount is not equal to table2.sum
The accumulation of table1.amount is not table2.sum
----------- ?? What is this for?

jy01378524

table1.amount is not equal to table2.sum
The accumulation of table1.amount is not table2.sum
----------- ?? What is this for?


The demand is like this, no reason, I try, thank you so much

kxiaper

create trigger t_1 on table1 for update
as
select inserted.ID, diff = deleted.amount- inserted.amount into #tmp
from deleted, inserted
where deleted.ID = inserted.ID

update table2
set sum = sum + # tmp.diff
from table2, # tmp
where table2.ID = # tmp.ID
go

jy01378524

Thank you all

kxiaper

correct:
create trigger t_1 on table1 for update
as
select inserted.ID, diff = inserted.amount-deleted.amount into #tmp
from deleted, inserted
where deleted.ID = inserted.ID

update table2
set sum = sum + # tmp.diff
from table2, # tmp
where table2.ID = # tmp.ID
go

lplovezc520

Up, it turns out that instered and deleted tables have such benefits.