实验11 触发器.docx

1、实验11 触发器实验 触发器一、实验目的（1）理解触发器的用途、类型和工作原理（2）掌握利用T-SQL语句创建和维护触发器的方法（3）掌握利用企业管理器创建、维护触发器的方法二、实验内容1.创建after触发器(1）创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时，须确保插入的学号已在Student表中存在，并且还须确保插入的课程号在Course表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件（注：Student表与sc表的外键约束要先取消）。create trigger sc_inserton scaf

2、ter insertasif not exists(select * from student,inserted where student.sno=inserted.sno) begin print 插入信息的学号不在学生表中！ if not exists(select * from course,inserted where o=o) print 插入信息的课程号不在课程表中！ rollback endelse begin if not exists(select * from course,inserted where o=o) begin print 插入信息的课程号不在课程表中！ r

3、ollback end end执行：、insert into SCvalues(20110112,001,78)删除外键约束：alter table SCdrop constraint FK_SC_Sno_182C9B23、insert into SCvalues(20110002,001,78)、insert into SCvalues(20110002,006,78)(2）为Course表创建一个触发器Course_del，当删除了Course表中的一条课程信息时，同时将表sc表中相应的学生选课记录删除掉。create trigger course_delon courseafter de

4、leteas if exists(select * from sc,deleted where o=o) begin delete from sc where o in(select cno from deleted) enddelete from Coursewhere Cno=003select * from SC(3）在Course表中添加一个平均成绩avg_Grade字段（记录每门课程的平均成绩），创建一个触发器Grade_modify，当SC表中的某学生的成绩发生变化时，则Course表中的平均成绩也能及时相应的发生改变。alter table Courseadd avg_Grade

5、 smallintupdate Courseset avg_Grade=(select AVG(Grade) from SC where SC.Cno=Course.Cno)select * from Coursecreate trigger Grade_modifyon scafter updateasif update(grade) begin update course set avg_grade=(select avg(grade) from sc where o=o group by cno)end update SCset Grade=90where Sno=20050001 an

6、d Cno=001（4）测试上述三个触发器。测试过程在（1）、（2）、（3）中均给出2.创建instead of 触发器(1）创建一视图Student_view,包含学号、姓名、课程号、课程名、成绩等属性，在Student_view上创建一个触发器Grade_moidfy，当对Student_view中的学生的成绩进行修改时，实际修改的是sc中的相应记录。create view Student_viewasselect s.Sno,Sname,c.Cno,Cname,Gradefrom Student s,Course c,SCwhere s.Sno=SC.Sno and c.Cno=SC.C

7、noselect * from Student_viewcreate trigger Grade_moidfyon Student_viewinstead of updateasif UPDATE(Grade)beginupdate SC set Grade=(select Grade from inserted) where Sno=(select Sno from inserted) and Cno=(select Cno from inserted)endupdate Student_viewset Grade=40where Sno=20110001 and Cno=002select

8、 * from Student_viewselect * from SC(2）在SC表中插入一个getcredit字段（记录某学生，所选课程所获学分的情况），创建一个触发器ins_credit，当更改（注：含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与Course表中的值一致；如果新成绩小于60分，则该生未能获得学分，修改值为0。 alter table SCadd getcredit smallintselect * from SCupdate SCset getcredit=(select credit from Course where

9、SC.Cno=Course.Cno)where Grade=60update SCset getcredit=0where Grade=60) begin delete from SC where sno=s_no and cno=c_no insert into SC values(s_no,c_no,new_grade,cred) end else begin delete from SC where sno=s_no and cno=c_no insert into SC values(s_no,c_no,new_grade,0) endendinsert into SC(sno,cno

10、,grade) values(20081800,002,85)（3）测试上述两个触发器。测试结果在（1）、（2）中均已给出3. 使用T-SQL语句管理和维护(1）用系统存储过程sp_helptrigger查看触发器Grade_modify的相关信息sp_helptrigger Student_view(2）使用系统存储过程sp_helptext查看触发器Grade_modify中的定义内容。sp_helptext Grade_moidfy(3) 使用select语句查看触发器Grade_modify的定义内容。select o.id,c.textfrom sysobjects o inner

11、join syscomments con o.id=c.idwhere o.type=TR and o.name=Grade_modify（4）用系统存储过程sp_depends查看触发器Grade_modify的相关性。sp_depends Grade_modify（5）将sc_insert触发器改为instead of 触发器，实现的功能不变。drop trigger sc_insertcreate trigger sc_inserton scinstead of insertasif not exists(select * from student,inserted where stud

12、ent.sno=inserted.sno) begin print 插入信息的学号不在学生表中！ if not exists(select * from course,inserted where o=o) print 插入信息的课程号不在课程表中！ rollback endelse begin if not exists(select * from course,inserted where o=o) begin print 插入信息的课程号不在课程表中！ rollback end endinsert into SCvalues(20110005,001,78,6)（6）将触发器sc_ins

13、ert删除。drop trigger sc_insert4.使用SQL Server Management Studio管理存储过程（1）在SQL Server Management Studio中重新创建刚删除的触发器sc_insert选中SC表，展开右击“触发器”新建触发器出现如下界面：编写余下的SQL语句：CREATE TRIGGER sc_insert ON SC INSTEAD OF INSERTAS BEGIN if not exists(select * from student,inserted where student.sno=inserted.sno) begin pri

14、nt 插入信息的学号不在学生表中！ if not exists(select * from course,inserted where o=o) print 插入信息的课程号不在课程表中！ rollback endelse begin if not exists(select * from course,inserted where o=o) begin print 插入信息的课程号不在课程表中！ rollback end endEND（2）查看触发器sc_insert 的内容。展开表SC触发器选择sc_insert触发器查看依赖关系出现如下界面：（3）删除触发器sc_insert选中sc_insert触发器，右击删除