SQL触发器作业(共3页)

1、精选优质文档-倾情为你奉上1.为SC创建一个触发器,当插入或者修改一个记录时候,确保此记录的成绩在0-100之间.Create trigger Score_sc_tri On SC For Insert,update As declare score_read tinyint Select score_read=score from inserted If score_read>=0 and score_read<=100 begin Print ' 操作完成!' Return End Print '成绩超出之间！请重新输入.' Rollback T

2、ransactionINSERT INTO SC VALUES('S1','C5',190) GO INSERT INTO SC VALUES('S1','C5',100)GOUPDATE sc SET Score=130 WHERE sno='S2'AND cno='C5'GOUPDATE sc SET Score=60 WHERE sno='S2'AND cno='C5'GO2.为T创建一个触发器,男职工年龄不超过60周岁,女职工是教授的年龄不超过60,其他女职工

3、不超过55.create trigger trigger_Teacher on Teacher for update ,insert as declare Age_new int,sex char(2),prof char(10)select Age_new=age from insertedselect sex=sex ,prof=prof from Teacherif sex='男'and Age_new<=60and Age_new>0 begin print '操作完成' return end else if sex='女'a

4、nd prof='教授' and Age_new<=60and Age_new>0 begin print '操作完成' return endelse if sex='女'and prof<>'教授' and Age_new<=55and Age_new>0 begin print '操作完成' return endprint '记录数据错误！请重新输入。'rollback TransactionINSERT INTO Teacher VALUES('S1

5、','C5','男','63','','','','') GO 3.为TCCSC创建参照完整性,级联删除和级联修改触发器.为表C创建一级联更新触发器TRIGGER_Course。CREATE TRIGGER TRIGGER_Course ON course FOR UPDATE AS IF UPDATE(cno) BEGIN DECLARE cno_NEW CHAR(2),cno_OLD CHAR(2)SELECT cno_NEW=cno FROM INSERTEDSELEC

6、T cno_OLD=cno FROM DELETEDUPDATE SC SET cno=cno_NEW WHERE cno=cno_OLDUPDATE TC SET cno=cno_NEW WHERE cno=cno_OLD END为表SC创建一限制更新触发器TRIGGER_SC要求：若修改SC表中一记录的课号，则要检查表course中是否存在与该课号相同的记录，若有则不许修改，若没有则可修改。CREATE TRIGGER TRIGGER_SC ON SC FOR UPDATE AS IF UPDATE(cno) BEGINDECLARE cno_NEW CHAR(5),cno_OLD CHA

7、R(5),cno_CNT INTSELECT cno_OLD=cno FROM DELETEDSELECT cno_CNT=COUNT(*) FROM course WHERE cno=cno_OLDIF cno_CNT<>0 ROLLBACK TRANSACTION END为表TC创建一限制更新触发器TRIGGER_TC要求：若修改TC表中一记录的课号，则要检查表course中是否存在与该课号相同的记录，若有则不许修改，若没有则可修改。CREATE TRIGGER TRIGGER_TC ON TC FOR UPDATE AS IF UPDATE(cno) BEGINDECLARE

8、 cno_NEW CHAR(5),cno_OLD CHAR(5),cno_CNT INTSELECT cno_OLD=cno FROM DELETEDSELECT cno_CNT=COUNT(*) FROM course WHERE cno=cno_OLDIF cno_CNT<>0 ROLLBACK TRANSACTION END用SQL为表C创建一个级联删除触发器TRIGGER_DC：通过课程名从C表中删除某课程信息，同时删除SC,TC表中与此课程相关的选课记录USE jiaoxuedbGOCreate trigger TRIGGER_DCourse On Course For

9、DELETE As declare CNO_DEL CHAR(5)Select CNO_DEL=CNO From DELETEDDELETE FROM SC WHERE SC.CNO=CNO_DEL DELETE FROM TC where TC.cno=CNO_DEL GO（）验证触发器的作用。Delete from Course where CNO= '01001' GOINSERT INTO Course VALUES('01001',' 信息与计算科学','90','4.0') GO INSERT INTO SC VALUES('S1','C5',100)G