create trigger tg on B
for insert
as
update a
SET a.A3 = CASE WHEN SUM(b.B2)> a.A2 THEN 1
WHEN SUM(b.B2)= a.A2 THEN 2
WHEN SUM(b.B2) < a.A2 THEN 3
from a,inserted b
WHERE a.A1=b.B1
go
for insert
as
update a
SET a.A3 = CASE WHEN SUM(b.B2)> a.A2 THEN 1
WHEN SUM(b.B2)= a.A2 THEN 2
WHEN SUM(b.B2) < a.A2 THEN 3
from a,inserted b
WHERE a.A1=b.B1
go
for insert
as
update a
SET a.A3 = CASE WHEN SUM(b.B2)> a.A2 THEN 1
WHEN SUM(b.B2)= a.A2 THEN 2
else 3 end
from inserted b
WHERE a.A1=b.B1
gomodify
as
update a set a3=CASE WHEN b.b2> a.a2 THEN 1
WHEN b.b2= a.a2 THEN 2
WHEN b.b2<a.a2 THEN 3 end
from a,(select b1,b2=sum(b2) from b group by b1)b
where a.a1=b.b1
go
create trigger tri_a on b for insert,update,delete --应该是建在B表上。
as
update a set a3=CASE WHEN b.b2> a.a2 THEN 1
WHEN b.b2= a.a2 THEN 2
WHEN b.b2<a.a2 THEN 3 end
from a,(select b1,b2=sum(b2) from b group by b1)b
where a.a1=b.b1
go
drop table a
if OBJECT_ID('b') is not null
drop table b
if OBJECT_ID('tri_g') is not null
drop trigger tri_g
go
create table a (a1 int,a2 int,a3 int)
insert into a select 2007,500,0
union all select 2008,256,0
union all select 2009,361,0
create table b (b1 int ,b2 int)
go
create trigger tri_g
on b for insert ,update
as
begin
update a set a3 = case when b2>a2 then 1 when b2=a2 then 2 else 3 end from a join (select b1,sum(b2) b2 from inserted group by b1) b
on a.a1=b.b1
end
go
insert into b select 2007,250
insert into b select 2008,265
insert into b select 2007,100
insert into b select 2007,41
go
select * from a
1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
a1 a2 a3
----------- ----------- -----------
2007 500 3
2008 256 1
2009 361 0(3 行受影响)
for insert
as
update a
SET a.A3 = CASE WHEN b.B2> a.A2 THEN 1
WHEN b.B2= a.A2 THEN 2
else 3
end
from (select b1,b2=sum(b2) from b group by b1) b
WHERE a.A1=b.B1
goSorry.刚回快了.