sql server2000 update触发器 加减问题
有两个表：
table1(id,amount)
table2(id,sum)
我想得到一个update触发器，当table1.amount改变时，table2.sum也改变。
table1.amount增加或着减少多少table2.sum也增加或着减少多少
table1.amount不等于table2.sum
table1.amount的累加也不是table2.sum
[解决办法]
create trigger 触发名 on table1
instead of update
as
--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
[解决办法]
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不等于table2.sum
table1.amount的累加也不是table2.sum
-----------??这是干什么？
[解决办法]
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
