试验七试验报告触发器

实验七 触发器实验目的(1)理解触发器的用途(2)掌握利用 T-SQL语句创建和维护触发器的办法(3)掌握利用 SQL 创建和维护触发器的办法实验内容1. 创建 AFTER触发器（1）ifexists( selectname fromsysobjectswherename ='sc_insert'andtype='tr') droptriggersc_insertgo createtriggersc_insertonSC afterinsertas ifnotexists(select*fromstudent, inserted wherestudent. sno =inserted. sno ) begin print' 该学号不存在，无法插入！' ifnotexists(select*fromcourse, inserted wherecourse. cno =inserted. cno) print' 该课程号不存在！' end go insert intosc values ( '2011001', '001', '88'), ( '2011009', '001', '99'), ( '2011002', '005', '55'); 插入后的结果如图：(2). createtriggercourse_del oncourse afterdelete as begintransaction declare@cnochar( 8) select@cno=cno fromdeleted delete fromsc wherecno =@cno committransactiongo deletefromcourse wherecno ='002' 执行之后的course 表(3) altertablecourse addavg_gradesmallint updatecourse setavg_grade=( selectavg ( grade)fromSC wheresc . cno =course. cno ) createtriggergrade_modify onscafterupdate as ifUPDATE( grade) beginupdatecourse setavg_grade=( selectavg ( grade)fromscwheresc . cno =course. cno groupbycno ) end updateSC setgrade='99' wheresno ='20110001'andcno ='001' 2. 创建 INSTEAD OF 触发器(1) createviewstudent_view as selectstudent. sno , sname , course. cno , cname , grade fromStudent, course, sc wherestudent. sno =sc . snoandcourse. cno =sc . cno select*fromstudent_view ifexists( selectname fromsysobjectswherename ='GRADE_MODIFY'and type='tr') droptriggerGRADE_MODIFY go createtriggerGRADE_MODIFY onstudent_view insteadofinsert as IFUPDATE( GRADE) BEGINupdateSC setgrade=( selectgradefrominsertedwheresno =( selectsnofrominserted) andcno =( selectcnofrominserted)) endUPDATEstudent_view setgrade=88 wheresno ='20110001'andcno ='001' （2）altertablestudentaddgetcreditint select*fromStudent updatesc se...