create trigger tri_name on 积分日志表 for delete as begin update a set a.jifen=a.jifen-b.jifen from user a join( select userid,sum(jifen) as jifen from deleted ) b on a.userid=b.userid end
SELECT @id=thingsid,@userid=userid,@jifen=fengshu FROM deletED update user set jifeng=jifeng-@jifen where userid=@userid 改为SELECT userid=userid,@jifen=sum(fengshu) FROM deletED group by userid update user set jifeng=jifeng-@jifen where userid=@userid
create table tb1(id int, jifen int, userid int) insert into tb1 values(1 , 10 , 110 ) insert into tb1 values(2 , 20 , 110 ) create table tb2(userid int, jifen int) insert into tb2 values(110 , 100 ) gocreate trigger my_trig on tb1 for delete as update tb2 set jifen = jifen - (select jifen from deleted where userid = tb2.userid) godelete from tb1 where id = 1 select * from tb2 /* userid jifen ----------- ----------- 110 90(所影响的行数为 1 行) */delete from tb1 where id = 2 select * from tb2 /* userid jifen ----------- ----------- 110 70(所影响的行数为 1 行) */drop table tb1 , tb2
sorrycreate trigger tr_del on ta for delete as begin update a set a.jifen=a.jifen-d.jifen from [user] as a right join deleted as d on a.userid = d.userid end go
on 积分日志表
for delete
as
begin
update a set a.jifen=a.jifen-b.jifen
from user a join(
select userid,sum(jifen) as jifen from deleted
) b on a.userid=b.userid
end
update user set jifeng=jifeng-@jifen where userid=@userid
改为SELECT userid=userid,@jifen=sum(fengshu) FROM deletED group by userid
update user set jifeng=jifeng-@jifen where userid=@userid
insert into tb1 values(1 , 10 , 110 )
insert into tb1 values(2 , 20 , 110 )
create table tb2(userid int, jifen int)
insert into tb2 values(110 , 100 )
gocreate trigger my_trig on tb1 for delete
as
update tb2 set jifen = jifen - (select jifen from deleted where userid = tb2.userid)
godelete from tb1 where id = 1
select * from tb2
/*
userid jifen
----------- -----------
110 90(所影响的行数为 1 行)
*/delete from tb1 where id = 2
select * from tb2
/*
userid jifen
----------- -----------
110 70(所影响的行数为 1 行)
*/drop table tb1 , tb2
on ta
for delete
as
begin
update a
set a.jifen=a.jifen-d.jifen
from [user] as a
right join deleted as d
on a.userid = d.userid
end
go