A表 A1 A2 A3 A4 A5
B表 B1 B2 B3 B4 B5 两表的关系是 A1=B1 A2=B2 A3=B3 A4=B4要求:第一次把数据A1 A2 A3 A4 A5 插入A表时候如果
B表没有A1 A2 A3 A4 这笔数据就是把A1 A2 A3 A4 A5 这数据也插入到B表。
当第二次把数据A1 A2 A3 A4 A5 插入A表时候因为B表已经有A1 A2 A3 A4 这数据
只须把第一次的A5加上第二次的A5相加把总和更新到B表B5
B表 B1 B2 B3 B4 B5 两表的关系是 A1=B1 A2=B2 A3=B3 A4=B4要求:第一次把数据A1 A2 A3 A4 A5 插入A表时候如果
B表没有A1 A2 A3 A4 这笔数据就是把A1 A2 A3 A4 A5 这数据也插入到B表。
当第二次把数据A1 A2 A3 A4 A5 插入A表时候因为B表已经有A1 A2 A3 A4 这数据
只须把第一次的A5加上第二次的A5相加把总和更新到B表B5
for insert
as
begin
if exists(select 1 from b,inserted i where i.A1=b.B1
and i.A2=b.B2
and i.A3=b.B3
and i.A4=b.B4)
update b set b.B5=b.B5+i.A5 from from b,inserted i where i.A1=b.B1
and i.A2=b.B2
and i.A3=b.B3
and i.A4=b.B4else insert into b select A1, A2, A3, A4, A5 from inserted
end
on a
for insert
as
begin
if exists
(select 1 from b,inserted i
where i.A1=b.B1 and
i.A2=b.B2 and
i.A3=b.B3 and
i.A4=b.B4)update b set b.B5=b.B5+i.A5
from from b,inserted i
where i.A1=b.B1 and
i.A2=b.B2 and
i.A3=b.B3 and
i.A4=b.B4
else
insert into b
select A1, A2, A3, A4, A5 from inserted
end
create trigger tri_insert on A
for insert
as
begin
merge into B
using inserted as A on A.a1=b1
when matched then update set b5=b5+A.a5
when not matched then insert (b1,b2,b3,b4,b5) values( a1,a2,a3,a4,a5);
end
for insert
as
begin
merge into B
using inserted as A on A.a1=b1 and A.a2=b2 and A.a3=b3 and A.a4=b4
when matched then update set b5=b5+A.a5
when not matched then insert (b1,b2,b3,b4,b5) values( a1,a2,a3,a4,a5);
end