我想建立个触发器,,实现下面的功能:有一个成绩表1(SNO(主键),CNO(主键),SCORE)还有成绩表2(SNO(主键),CNO(主键),SCORE……),现在我修改成绩表1的成绩SCORE,如果成绩SCORE小于60分,自动把(SNO,CNO,SCORE)添加到成绩表2中,如果我再更新成绩表1的成绩SCORE,把以前小于60分的成绩改为大于60分的成绩,自动删除成绩表2对应的信息。如果把表1原来小于60分的成绩继续改为小于60分的成绩,它只改表2的成绩SCORE,而是不是再插入(SNO,CNO,SCORE)整条记录(因为设置了主键,不允许插入).
搞了一个晚上,搞不出来,求个高手帮帮忙。
搞了一个晚上,搞不出来,求个高手帮帮忙。
create table score1(sno int,cno int,score int)insert into score1 select 1,101,61create table score2(sno int,cno int,score int)-- 建触发器
create trigger tr_t1 on score1
for update
as
begin
declare @s1 int,@s2 int
if update(score)
begin
select @s1=score from deleted
select @s2=score from inserted
if @s1>=60 and @s2<60
insert into score2 select * from inserted
if @s1<60 and @s2>=60
delete a from score2 a inner join inserted b on a.sno=b.sno and a.cno=b.cno
if @s1<60 and @s2<60
update a set a.score=b.score from score2 a inner join inserted b on a.sno=b.sno and a.cno=b.cno
end
end-- 更新前的数据
select * from score1sno cno score
----------- ----------- -----------
1 101 61select * from score2sno cno score
----------- ----------- -----------
-- 更新表1的成绩SCORE,如果成绩SCORE小于60分,自动把(SNO,CNO,SCORE)添加到成绩表2中
update score1 set score=55 where sno=1select * from score1sno cno score
----------- ----------- -----------
1 101 55select * from score2sno cno score
----------- ----------- -----------
1 101 55
-- 更新成绩表1的成绩SCORE,把以前小于60分的成绩改为大于60分的成绩,自动删除成绩表2对应的信息。
update score1 set score=65 where sno=1select * from score1sno cno score
----------- ----------- -----------
1 101 65select * from score2sno cno score
----------- ----------- -----------
-- 把表1原来小于60分的成绩继续改为小于60分的成绩,它只改表2的成绩SCORE
update score1 set score=55 where sno=1update score1 set score=50 where sno=1select * from score1sno cno score
----------- ----------- -----------
1 101 50select * from score2sno cno score
----------- ----------- -----------
1 101 50
as
if not exists(select 1 from inserted)
delete 成绩表2 from 成绩表2 where sno = (select 成绩表2 from deleted)
else if not exists(select 1 from deleted)
insert into 成绩表2(SNO,CNO,SCORE) select SNO,CNO,SCORE from inserted where SCORE < 60
else
update 成绩表2 set SCORE = isnull((select t.SCORE from inserted t where m.SNO = t.SNO and t.SCORE < 60),m.score) from 成绩表2 m
go