抱歉,更正一下上面的INSERT触发器:
----插入时
create trigger trg_insert on tableA
for insert
as
UPDATE b set 积分 = 积分 + a.积分
FROM tableB b
INNER JOIN
(
select 姓名,isnull(sum(比例*消费),0) as 积分
from inserted group by 姓名
) a ON b.姓名 = a.姓名
GO
----插入时
create trigger trg_insert on tableA
for insert
as
UPDATE b set 积分 = 积分 + a.积分
FROM tableB b
INNER JOIN
(
select 姓名,isnull(sum(比例*消费),0) as 积分
from tableA x inner join inserted i on x.姓名 = i.姓名 group by 姓名
) a ON b.姓名 = a.姓名
GO
----更新时
create trigger trg_insert on tableA
for update
as
UPDATE b set 积分 = 积分 + a.积分
FROM tableB b
INNER JOIN
(
select i.姓名,isnull(sum(i.比例*i.消费),0) - isnull(sum(d.比例*d.消费),0) as 积分
from deleted d inner join inserted i on d.姓名 = i.姓名 group by i.姓名
) a ON b.姓名 = a.姓名
GO
CREATE TRIGGER my_trig
ON A
FOR insert
AS
update B
set 积分=t.l
from B inner join
(select 姓名,sum( (case 比例 when NULL then 0 else 比例 end) * 消费) as l
from inserted
group by 姓名) as t
on B.姓名=t.姓名
go