一张表:id total (table1)
1 2.5
2 3.5
3 5.5
1 2.9
1 3.6
2 3.9
第二张表: id total (table2)
1 3.5
2 6.7
3 6.4
1 5.5
1 4.5
2 1.5
第三张表: id total1 total1 总total (table3)
当向第二张表 增加和修改数据时候,第三张表就会自动添加或修改对应的列的值,列 id total1 total2 总 total
1 (表1的id=1时,total的平均) (表2的id=1时,total的平均) (id=1时,total1*权重+total2*权重)
2 (表1的id=2时,total的平均) (表2的id=2时,total的平均) (id=2时,total1*权重+total2*权重)
table3的列id也能自动的加入进表table3
求问一个触发器能完成么? 应该怎么去写! 学习,学习!
1 2.5
2 3.5
3 5.5
1 2.9
1 3.6
2 3.9
第二张表: id total (table2)
1 3.5
2 6.7
3 6.4
1 5.5
1 4.5
2 1.5
第三张表: id total1 total1 总total (table3)
当向第二张表 增加和修改数据时候,第三张表就会自动添加或修改对应的列的值,列 id total1 total2 总 total
1 (表1的id=1时,total的平均) (表2的id=1时,total的平均) (id=1时,total1*权重+total2*权重)
2 (表1的id=2时,total的平均) (表2的id=2时,total的平均) (id=2时,total1*权重+total2*权重)
table3的列id也能自动的加入进表table3
求问一个触发器能完成么? 应该怎么去写! 学习,学习!
select a.id,a.total1,b.total2,total1*权重+total2*权重 from
(select id,avg(total) as total1 from TB1
group by id) a,
(select id,avg(total) as total2 from TB2
group by id) b
where a.id=b.id
create trigger trig_table2 on table2
for insert,update
as
if not exists(select 1 from table3 a,inserted b where a.id=b.id)
insert table3
select b.id ,isnull(b.total1,0),isnull(b.total2,0) ,isnull(b.total1,0)*权重+isnull(b.total2,0)*权重 from
(select a.id,(select avg(total) total1 from table1 where id=a.id),(select avg(total) total2 from table2 where id=a.id) from inserted a) b
else
update table3 set total1=isnull(b.total1,0),total2=isnull(b.total2,0),total=isnull(b.total1,0)*权重+isnull(b.total2,0)*权重
from
(select a.id,(select avg(total) total1 from table1 where id=a.id),(select avg(total) total2 from table2 where id=a.id) from inserted a) b
where table3.id=b.id
go
--把我上一个照搬过来就是,这样,内容基本没变。
create trigger trig_table1 on table1
for insert,update
as
if not exists(select 1 from table3 a,inserted b where a.id=b.id)
insert table3
select b.id ,isnull(b.total1,0),isnull(b.total2,0) ,isnull(b.total1,0)*权重+isnull(b.total2,0)*权重 from
(select a.id,(select avg(total) total1 from table1 where id=a.id),(select avg(total) total2 from table2 where id=a.id) from inserted a) b
else
update table3 set total1=isnull(b.total1,0),total2=isnull(b.total2,0),total=isnull(b.total1,0)*权重+isnull(b.total2,0)*权重
from
(select a.id,(select avg(total) total1 from table1 where id=a.id),(select avg(total) total2 from table2 where id=a.id) from inserted a) b
where table3.id=b.id
go
create trigger trig_table1 on table1
for insert,update
as
if not exists(select 1 from table3 a,inserted b where a.id=b.id)
insert table3
select b.id ,isnull(b.total1,0),isnull(b.total2,0) ,isnull(b.total1,0)*权重+isnull(b.total2,0)*权重 from
(select a.id,(select avg(total) total1 from table1 where id=a.id),(select avg(total) total2 from table2 where id=a.id) from inserted a) b
else
update table3 set total1=isnull(b.total1,0),total2=isnull(b.total2,0),total=isnull(b.total1,0)*权重+isnull(b.total2,0)*权重
from
(select a.id,(select avg(total) total1 from table1 where id=a.id),(select avg(total) total2 from table2 where id=a.id) from inserted a) b
where table3.id=b.id
go