实验七触发器

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

2、asif not exists(select * from student,insertedwhere =beginprint，插入信息的学号不在学生表中！，if not exists(select * from course,inserted where = print，插入信息的课程号不在课程表中！， rollbackend elsebeginif not exists(select * from course,inserted where = beginprint，插入信息的课程号不在课程表中！，rollbackendend执行:、insert into SCvalues(,001,78

3、)删除外键约束:alter table SCdrop constraintFK_SC_Sno_182C9B23、insert intoSCvalues (, 001,78)、insert into SCvalues(,006,78)(2)为Course表创建一个触发器Course_del，当删除了 Course表中的一条课程信息时，同时将表sc表中相应的学生选课记录删除掉。create trigger course_delon courseafter deleteasif exists(select * from sc,deletedwhere =begindelete from scwher

4、e 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 smallintupdate Courseset avg_Grade=(select AVG(Grade) from SCwhere =select * from

5、 Coursecreate trigger Grade_modifyon scafter updateasif update(grade) beginupdate courseset avg_grade=(select avg(grade)from sc where =group by cno)endupdate SC set Grade=90where Sno= and Cno=001测试上述三个触发器。测试过程在(1)、(2)、(3 )中均给出2.创建instead of触发器(1)创建一视图Student_view，包含学号、姓名、课程号、课程名、成绩等属性，在Student_view上

6、创建一个触发器Grade_moidfy，当对Student_view中的学生的成绩进行修改时，实际修改的是sc中的相应记录。create view Student_view asselect , Sname,Cname, Grade from Student s,Course c,SC where = and =select * from Student_viewcreate trigger Grade_moidfy on Student_view instead of update asif UPDATE(Grade)beginupdate SCset Grade=(select Grade

7、 from inserted) whereSno=(select Sno from inserted) andCno=(select Cno from inserted)endupdate Student_viewset Grade=40where Sno= and Cno=002select * from Student_view select * from SC在SC表中插入一个getcredit字段(记录某学生，所选课程所获学分的情况)，创建一个触发器ins_credit，当更改(注：含插入时)SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与Cour

8、se表中的值一致；如果新成绩小于60分，则该生未能获得学分，修改值为0。alter table SCadd getcredit smallint select * from SC update SCset getcredit=(select credit from Coursewhere =where Grade=60update SCset getcredit=0where Grade=60 beginenddeleteinsertfrom SC where sno=s_no andcno=c_nonew_grade,cred)into SC values(s_no,c_no,elsebeg

9、indeletefrom SC where sno=s_no andcno=c_noinsertinto SC values(s_no,c_no,new_grade,0endendinsert into SC(sno,cno,grade) values(,002,85测试上述两个触发器。测试结果在(1)、(2)中均已给出3. 使用T-SQL语句管理和维护用系统存储过程sp_helptrigger查看触发器Grade_modify的相关信息sp_helptrigger Student_view使用系统存储过程sp_helptext查看触发器Grade_modify中的定义内容。sp_helpte

10、xt Grade_moidfy使用select语句查看触发器Grade_modify的定义内容。select , from sysobjects o inner join syscomments c on =where =TR and =Grade_modify用系统存储过程sp_depends查看触发器Grade_modify的相关性。sp_depends Grade_modify将sc_insert触发器改为instead of触发器，实现的功能不变。drop trigger sc_insert create trigger sc_insert on sc instead of inser

11、tasif not exists(select * from student,inserted where = beginprint，插入信息的学号不在学生表中！， if not exists(select * from course,inserted where =print，插入信息的课程号不在课程表中！， rollbackend elsebegin if not exists(select * from course,inserted where = beginprint，插入信息的课程号不在课程表中！， rollbackend endinsert into SC(6)将触发器sc_in

12、sert删除。drop trigger sc_insert4. 使用SQL Server Management Studio管理存程在SQL Server Management Studio中重新创建刚删除的触发器sc_ insert选中SC表，展开T右击“触发器”T新建触发器出现如下界面：编写余下的SQL语句：CREATE TRIGGER sc_insert ON SC INSTEAD OF INSERTASBEGIN if not exists(select * from student,inserted where = begin print，插入信息的学号不在学生表中！， if not exists(select * from course,inserted where = print，插入信息的课程号不在课程表中！， rollback endelse