有个表 TABLE1
ID T1 T2 T3 T4 T5
1 021 02 3.10 200601 0
2 021 02 2.10 200601 0
3 022 01 2.30 200601 0
4 021 01 2.30 200603 0
...................
TABLE1 主键 (ID)
我想通过触发器实现对TABLE1 的汇总,当我改变T5变成1时,把汇总记录写入TABLE2
另外一个表 TABLE2
T1 T2 T3 T4
021 02 5.20 200601
022 01 2.30 200601
021 01 2.30 200603
................
TABLE2主键(T1,T2,T3) 如何写触发器????????
当汇总的数据在TABLE2中有数据,就把汇总数据加到有主键的列
ID T1 T2 T3 T4 T5
1 021 02 3.10 200601 0
2 021 02 2.10 200601 0
3 022 01 2.30 200601 0
4 021 01 2.30 200603 0
...................
TABLE1 主键 (ID)
我想通过触发器实现对TABLE1 的汇总,当我改变T5变成1时,把汇总记录写入TABLE2
另外一个表 TABLE2
T1 T2 T3 T4
021 02 5.20 200601
022 01 2.30 200601
021 01 2.30 200603
................
TABLE2主键(T1,T2,T3) 如何写触发器????????
当汇总的数据在TABLE2中有数据,就把汇总数据加到有主键的列
on table1
for insert,delete,update
as
update table2
set t3=a.t3+t.t3
from (
select t1,t2,sum(t3) as t3,t4 from (
select t1,t2,t3,t4 from inserted where t5=1
union all
select t1,t2,-t3,t4 from deleted where t5=1
) as t1
group by t1,t2,t4
) as t,table2 a
where t.t1=a.t1
and t.t2=a.t2
and t.t4=a.t4insert table2(t1,t2,t3,t4)
select t1,t2,sum(t3) as t3,t4 from (
select t1,t2,t3,t4 from inserted where t5=1
union all
select t1,t2,-t3,t4 from deleted where t5=1
) as t
where not exists (
select 1 from table2 a
where t.t1=a.t1
and t.t2=a.t2
and t.t4=a.t4
)
group by t1,t2,t4go
1、table2的主键按我理解应该是(t1,t2,t4),而不是(t1,t2,t3)
2、考虑插入t5=1的数据以及删除t5=1的数据,我写的是全的触发器。
3、楼主没要求,但是为了触发器的完整性,我写了当table2没有的数据被修改的时候的insert代码。
on table1
for update
as
insert table2 select a.t1,a.t2,sum(a.t3) as t3,a.t4 from table1 a ,deleted b
where a.id=b.id and b.t5=1
on table1
for update
as
insert table2 select a.t1,a.t2,sum(a.t3) as t3,a.t4 from table1 a ,deleted b
where a.id=b.id and b.t5=1 group by a.t1,a.t2,a.t4
insert a
select 1, '021', '02', 3.10 , '200601', 0 union all
select 2, '021', '02', 2.10 , '200601', 0 union all
select 3, '022', '01' , 2.30 , '200601' , 0 union all
select 4 , '021', '01' , 2.30 , '200603', 0
--select t1,t2,t3=sum(t3),t4 into b from a group by t1,t2,t4 order by t4create trigger tr1 on afor INSERT, UPDATE, DELETE
asupdate b
set t3=t3-isnull((select sum(case t5 when 0 then t3 when 1 then -t3 else 0 end) from deleted where t1=b.t1 and t2=b.t2 and t4=b.t4),0)insert b
select t1,t2,t3,t4
from inserted i where not exists(select *from b where t1=i.t1 and t2=i.t2 and t4=i.t4)
update b
set t3=t3+isnull((select sum(case t5 when 0 then t3 when 1 then -t3 else 0 end) from inserted where t1=b.t1 and t2=b.t2 and t4=b.t4),0)--drop trigger tr1
--drop table bselect * from a
select * from b
update a
set t5=0
where id=2
结果为
id t1 t2 t3 t4 t5
----------- ---- ---- ------------ ------ ----
1 021 02 3.10 200601 0
2 021 02 2.10 200601 0
3 022 01 2.30 200601 0
4 021 01 2.30 200603 0(所影响的行数为 4 行)t1 t2 t3 t4
---- ---- ---------------------------------------- ------
021 02 5.20 200601
022 01 2.30 200601
021 01 2.30 200603(所影响的行数为 3 行)
on table1
for update
as
update table2
set t3=b.t3+a.t3
from (select t1,t2,sum(t3) as t3,t4 from deleted where t5=1 group by t1,t2,t4) a,table2 b
where a.t1=b.t1 and a.t2=b.t2 and a.t4=b.t4 insert table2 select a.t1,a.t2,sum(a.t3) as t3,a.t4 from table1 a ,deleted b
where a.id=b.id and b.t5=1
and not exists
(select * from table2 where t1=a.t1 and t2=a.t2 and t4=a.t4 )
go
最好不要用deleted来做更新,因为很可能用
update table1 set t3=...,t5=1 where ...
就是说同时更新t3和t5,这种情况用inserted和用deleted是不同的写触发器需要考虑周全,不要认为有些情况是不可能的
create trigger tr1 on afor INSERT, UPDATE, DELETE
asupdate b
set t3=t3-isnull((select sum(case t5 when 0 then t3 when 1 then -t3 else 0 end) from deleted where t1=b.t1 and t2=b.t2 and t4=b.t4),0)insert b
select t1,t2,t3,t4
from inserted i where not exists(select *from b where t1=i.t1 and t2=i.t2 and t4=i.t4)
update b
set t3=(select sum(case t5 when 0 then t3 when 1 then -t3 else 0 end) from a where t1=i.t1 and t2=i.t2 and t4=i.t4)
from inserted i where b.t1=i.t1 and b.t2=i.t2 and b.t4=i.t4
测试过完美:
楼主测试一下在你的版本里能不能通过
insert a
select 1, '021', '02', 3.10 , '200601', 0 union all
select 2, '021', '02', 2.10 , '200601', 0 union all
select 3, '022', '01' , 2.30 , '200601' , 0 union all
select 4 , '021', '01' , 2.30 , '200603', 0
--select t1,t2,t3=sum(t3),t4 into b from a group by t1,t2,t4 order by t4create trigger tr1 on afor INSERT, UPDATE, DELETE
asupdate b
set t3=t3-isnull((select sum(case t5 when 0 then t3 when 1 then -t3 else 0 end) from deleted where t1=b.t1 and t2=b.t2 and t4=b.t4),0)insert b
select t1,t2,t3,t4
from inserted i where not exists(select *from b where t1=i.t1 and t2=i.t2 and t4=i.t4)
update b
set t3=(select sum(case t5 when 0 then t3 when 1 then -t3 else 0 end) from a where t1=i.t1 and t2=i.t2 and t4=i.t4)
from inserted i where b.t1=i.t1 and b.t2=i.t2 and b.t4=i.t4--drop trigger tr1
select * from a
select * from b
--drop table a
--drop table b
update a
set t5=0
where id=2insert a select 6, '023', '02', 6.00 , '200601', 0
on table1
for insert,delete,update
as
select t1,t2,t3,t4 into #r from inserted where t5=1
union all
select t1,t2,-t3,t4 from deleted where t5=1select t1,t2,sum(t3) as t3,t4 into #s from #r
group by t1,t2,t4update table2
set t3=a.t3+t.t3
from #s as t,table2 a
where t.t1=a.t1
and t.t2=a.t2
and t.t4=a.t4insert table2(t1,t2,t3,t4)
select t1,t2,t3,t4 from #s as t
where not exists (
select 1 from table2 a
where t.t1=a.t1
and t.t2=a.t2
and t.t4=a.t4
)go
set msrate=1
from Inserted a,[order]..orderdetail b
where b.orderno=a.sonocreate trigger InserDataon table1
FOR UPDATE
as
if(update(T5))
begin
insert into table2(T1,T2,T3,T4)
select T1,T2,sum(T3) T3,max(T4)
from TABLE1
group by T1,T2
end
go
FOR UPDATE
as
if(update(T5))
begin
insert into table2(T1,T2,T3,T4)
select T1,T2,sum(T3) T3,max(T4)
from TABLE1
group by T1,T2
end
go