实验七 触发器.doc

实验七 触发器班级：2010251 学号：20103269 姓名：邢凯一、实验目的（1）理解触发器的用途、类型和工作原理（2）掌握利用T-SQL语句创建和维护触发器的方法（3）掌握利用企业管理器创建、维护触发器的方法二、实验内容1.创建after触发器(1）创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时，须确保插入的学号已在studentStudent表中存在，并且还须确保保插入的课程号在courseCourse表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件。（注：studentStudent表与sc表的外键约束要先取消）。create trigger sc_insert_20103269 on SC_20103269for insertasif exists(select Sno from insertedwhere Sno not in(select Snofrom Student_20103269)beginrollback transactionprint插入的信息学号不满足条件endif exists(select Cnofrom insertedwhere Cno not in(select Cnofrom Course_20103269)beginrollback transactionprint插入的信息课程号不满足条件End 触发器的触发执行测试语句（T-SQL）：insert into SC_20103269values (20103269,001,100)(2）为courseCourse表创建一个触发器courseCourse_del，当删除了courseCourse表中的一条课程信息时，同时将表sc表中相应的学生选课记录删除掉。 触发器的触发执行测试语句（T-SQL）：create trigger course_del_20103269on Course_20103269after deleteasbeginbegin transactiondeclare course_cno char(3)select course_cno=deleted.Cno from deleteddelete from sc where SC_20103269.Cno=course_cnocommit tranend 触发器的触发执行测试语句（T-SQL）：delete from Course_20103269where Cno=002(3） 在courseCourse表中添加一个平均成绩avg_grade字段（记录每门课程的平均成绩），创建一个触发器gradeGrade_modify，当SC表中的某学生的成绩发生变化时，则courseCourse表中的平均成绩也能及时相应的发生改变。Alter table course_20103269Add avg_grade tinyintupdate Course_20103269setavg_grade=(select avg(grade)from SC_20103269where SC_20103269.Cno=Course_20103269.Cno)create trigger grade_modifyon Course_20103269for insert,delete,updateasbegindeclare avg_grade tinyint,sno char(8),cno char(3)select cno=Cno,sno=Snofrom updatedselect avg_grade=avg(Grade)from SC_20103269where Cno=cnoupdate Course_20103269set avg_grade=avg_gradewhere Cno=cnoEnd触发器的触发执行测试语句（T-SQL）：Update SC_20103269Set grade=99Where sno=20110001 and cno=001SELECT Cno,avg_gradefrom Course_201032692.创建instead of 触发器(1）创建一视图studentStudent_view,包含学号、姓名、课程号、课程名、成绩等属性，在studentStudent_view上创建一个触发器gradeGrade_moidfy，当对studentStudent_view中的学生的成绩进行修改时，实际修改的是sc中的相应记录。create view student_view_20103269(Sno,Sname,Cno,Cname,Grade)as(select Student_20103269.Sno,Sname,SC_20103269.Cno,Cname,Grade from Student_20103269,SC_20103269,Course_20103269 where Student_20103269.Sno=SC_20103269.Sno and Course_20103269.Cno=SC_20103269.Cno)gocreate trigger grade_modiy_20103269on student_view_20103269instead of updateasbeginif update(Grade)update SC_20103269 set Grade=(select Grade FROM inserted)where Sno=(select Sno from inserted) and Cno=(SELECT Cno from inserted)End(2） 在SC表中插入一个getcredit字段（记录某学生，所选课程所获学分的情况），创建一个触发器ins_credit，当更改（注：含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与course表中的值一致；如果新成绩小于60分，则该生未能获得学分，修改值为0。Alter table sc_20103269Add getcredit tinyintcreate trigger ins_crediton SC_20103269instead of updateasif update(Grade)if(select Grade FROM inserted)=60)beginupdate SC_20103269 set getcredit=(select credit from Course_20103269 where Cno=Course_20103269.Cno)where Sno=(select Sno from inserted) and Cno=(select Cno from inserted)endelsebeginupdate SC_20103269 set getcredit=0where Sno=(select Sno from inserted)and Cno=(select Cno from inserted)End(3） 触发器的触发执行测试语句 select *from SC_201032693. 使用T-SQL语句管理和维护(1）用系统存储过程sp_helptrigger查看触发器gradeGrade_modify的相关信息sp_helptrigger student_view_20103269(2） 使用系统存储过程sp_helptext查看触发器gradeGrade_modify中的定义内容。sp_helptext grade_modify_20103269(3) 使用select语句查看触发器Grade_modifystudent_sc存储过程的定义内容。脚本select s2.TEXTfrom sysobjects s1,syscomments s2where =grade_modify and s1.id=s2.id（4） 用系统存储过程sp_depends查看触发器gradeGrade_modify的相关性。sp_depends grade_modify（5）修改触发器将sc_insert触发器改为instead of 触发器，实现的功能不变。alter trigger sc_insert_20103269on SC_20103269instead of insertasif(not exists (select* from Student_20103269 where Sno=(select Sno from inserted)beginrollback transactionprint 学号不存在，不能插入endif(not exists (select * from Course_20103269 where Cno=(select Cno from inserted)beginrollback transactionprint课程不存在，不能插入End（6）删除触发器 将触发器sc