SQL触发器作业

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 TransactionINSERT INTO SC VALUES(S1,C5,190) GO INSERT INTO SC VALUES(S1,C5,100)GOUPDATE sc SET Score=130 WHERE sno=S2AND cno=C5GOUPDATE sc SET Score=60 WHERE sno=S2AND cno=C5GO2.为T创建一个触发器,男职工年龄不超过60周岁,女职工是教授的年龄不超过60,其他女职工不超过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_new0 begin print 操作完成 return end else if sex=女and prof=教授 and Age_new0 begin print 操作完成 return endelse if sex=女and prof教授 and Age_new0 begin print 操作完成 return endprint 记录数据错误！请重新输入。rollback TransactionINSERT INTO Teacher VALUES(S1,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 INSERTEDSELECT 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 CHAR(5),cno_CNT INTSELECT cno_OLD=cno FROM DELETEDSELECT cno_CNT=COUNT(*) FROM course WHERE cno=cno_OLDIF cno_CNT0 ROLLBACK TRANSACTION END为表TC创建一限制更新触发器TRIGGER_TC要求：若修改TC表中一记录的课号，则要检查表course中是否存在与该课号相同的记录，若有则不许修改，若没有则可修改。CREATE TRIGGER TRIGGER_TC ON TC FOR UPDATE AS IF UPDATE(cno) BEGINDECLARE 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_CNT0 ROLLBACK TRANSACTION END用SQL为表C创建一个级联删除触发器TRIGGER_DC：通过课程名从C表中删除某课程信息，同时删除SC,TC表中与此课程相关的选课记录USE jiaoxuedbGOCreate trigger TRIGGER_DCourse On Course For 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 INT