实验六 触发器(2012new)

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

2、ourse表与sc表的外键约束要先取消）。触发器的创建语句：if exists (select name from sysobjects where name='sc_insert' and type='tr')drop trigger sc_insertgocreate trigger sc_insert on sc for insertAs declare sno char(8),cno char(3)Select sno=sno,cno=cno from insertedIf not exists (select * from student where

3、sno=sno)Beginprint 'not exist the student'If not exists (select * from course where cno=cno)print 'not exist the course'Rollback tranEndelse if not exists (select * from course where cno=cno)beginRollback tranprint 'not exist the course'endgo触发器的触发执行测试结果：(2）为course表创建一个触发器cou

4、rse_del，当删除了course表中的一条课程信息时，同时将表sc表中相应的学生选课记录也一并删除。触发器的创建语句:create trigger course_del on course for deleteAs declare cno char(3)Select cno=cno from deletedDelete from sc where cno=cno触发器的触发执行测试结果： (3）在course表中添加一个平均成绩avg_grade字段（记录每门课程的平均成绩），创建一个触发器grade_modify，当SC表中的某学生的成绩发生变化时，则course表中的平均成绩也能及时相

5、应发生改变。触发器的创建语句：create trigger grade_modify on sc for update as if update(grade)beginupdate courseset avg_grade=(select avg(grade) from scwhere o=ogroup by cno)end触发器的触发执行测试结果：2.创建INSTEAD OF触发器(1）创建一视图student_view,包含学号、姓名、课程号、课程名、成绩等属性，在student_view上创建一个触发器grade_moidfy，当对student_view中的学生的成绩进行修改时，实际修改

6、的是sc中的相应记录。触发器的创建语句：create trigger grade_moidfyon student_viewinstead of insertas declare grade tinyint,sno char(8),cno char(3)if update(grade)beginselect grade=grade,sno=sno,cno=cno from insertedupdate sc set grade=gradewhere sno=sno and cno=cnoend触发器的触发执行测试结果：(2）在Student表中插入一个getcredit字段（记录学生所获学分的

7、情况），创建一个触发器ins_credit，当更改（注：含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与course表中的值一致；如果新成绩小于60分，则该生未能获得学分，修改值为0。触发器的创建语句：create trigger ins_crediton sc for update,insertas declare sno char(8),cno char(3),grade tinyint,credit tinyintif update(grade)beginselect grade=grade,cno=cno,sno=snofrom inse

8、rtedselect if grade>=60beginselect credit=credit from course where cno=cnoupdate studentset getcredit=credit where sno=snoendelse update studentset getcredit=credit where sno=snoend触发器的触发执行测试结果：3. 使用T-SQL语句管理和维护(1）用系统存储过程sp_helptrigger查看触发器grade_modify的相关信息。结果：EXECUTE sp_helptrigger grade_modify

9、(2）使用sp_helptext查看触发器grade_modify中的定义内容。结果：EXECUTE sp_helptext grade_modify(3) 使用select语句查看触发器grade_modify的定义内容。（提示：通过查询表sysobjects和表syscomments，触发器的名称保存在表sysobjects的name列，定义脚本保存在表syscomments的text列）(select text from sysobjects, syscomments where name=grade_modify and sysobjects.id=syscomments.id)结果：

10、select text from sysobjects, syscomments where name='grade_modify' and sysobjects.id=syscomments.id（4）用系统存储过程sp_depends查看触发器grade_modify的相关性（即该触发器涉及到哪些基本表）。（提示：sp_depends grade_modify 查看某个对象被其他调用（或引用）的关系）结果：EXECUTE sp_depends 'grade_modify'（5）将sc_insert触发器改为instead of触发器，实现的功能不变。（提示：

11、使用alter trigger 语句修改）结果：alter trigger sc_insertON SC INSTEAD OF INSERTASdeclare Sno char(8),Cno char(3)SELECT Sno=Sno,Cno=CnoFROM INSERTEDIF Sno NOT IN(SELECT Sno FROM student WHERE Sno=Sno) BEGIN ROLLBACK TRAN RAISERROR ('Sno is not in student',16,10)ENDIF Cno NOT IN(SELECT Cno FROM Course WHERE Cno=Cno) BEGIN ROLLBACK TRAN RAISERROR ('Cno is not in Course',16,10)ENDGO（6）将触发器sc_insert删除。（提示：使用drop trigger 语句删除）结果：drop trigger sc_insert4.使用SQL Server Management Studio管理存储过程（1）在SQL Server Management Studio中重新创建先前删除的触发器