第12章 触发器ppt课件.ppt
第12章 触发器,计算机系 软件教研室,第12章 触发器,目标：触发器的基本概念及工作原理如何创建触发器触发器如何实现数据完整性如何查看、修改、删除触发器,第12章 触发器,12.1 概述12.2 触发器的创建12.3 触发器实施数据完整性实例12.4 查看修改和删除触发器12.5 注意事项练习,12.1 概述,触发器(Trigger)是一种实施复杂数据完整性的特殊存储过程，在对表或视图执行UPDATE、INSERT或DELETE语句时自动触发执行，以防止对数据进行不正确、未授权或不一致的修改。触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于 SQL Server 约束、默认值和规则的完整性检查。,分类：INSERT触发器UPDATE触发器DELETE触发器,触发器的优点,触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。 触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。,触发器的工作原理,相关概念触发器表Insert 表和delete 表：临时表，结构，内存分类及工作原理：Insert触发器的工作原理delete触发器的工作原理update触发器的工作原理,12.2 触发器的创建,创建触发器应该考虑以下几个问题：CREATE TRIGGER 语句必须是批处理中的第一个语句。创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。 触发器为数据库对象，其名称必须遵循标识符的命名规则。 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。 当创建一个触发器时必须指定： 名称；在其上定义触发器的表；触发器将何时激发；激活触发器的数据修改语句。,方法：企业管理器T-SQL语句,一、使用T-SQL语句创建触发器,其语法形式如下： CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR DELETE,INSERT, UPDATE ASsql_statement,1.创建insert触发器,例12-1:为student 表建立insert 触发器以自动更新class表的学生人数。use jwglGo/*如果存在同名的触发器，则删除之 */If exists(select name from sysobjectsWhere type=TR AND name=student_insert)Drop TRIGGER student_insertGo/*建立插入触发器*/Create TRIGGER student_insert on studentFor insertAs,Declare NumOfStuent TinyintSelect NumOfStuent=c.student_num from class c,inserted Iwhere c.class_id=I.class_idIf (NumOfStuent0)begin update class set student_num=student_num+1from class c, inserted I where c.class_id=I.class_idendElsebeginupdate class set student_num=( select count(s.student_id) from student s,inserted I where s.class_id=I.class_id) from class c,inserted I where c.class_id=I.class_idendgo,运行Select *from class where class_id=g99403Exec spAddStudent g9940306,程涛,男,1981-9-22,g99403,1999-9-1,南京市御道街2号Select *from class where class_id=g99403分析执行过程,2.创建删除触发器,例12-2:为student 表建立delete 触发器以自动更新class表的学生人数。use jwglGo/*如果存在同名的触发器，则删除之 */If exists(select name from sysobjectsWhere type=TR AND name=student_delete)Drop TRIGGER student_deleteGo/*建立删除触发器*/Create TRIGGER student_delete on studentFor deleteAs,Declare NumOfStuent TinyintSelect NumOfStuent=c.student_num from class c,deleted dwhere c.class_id=d.class_idIf (NumOfStuent0)begin update class set student_num=student_num-1from class c, deleted d where c.class_id=d.class_idendElsebeginupdate class set student_num=( select count(s.student_id) from student s,deleted d where s.class_id=d.class_id) from class c, deleted d where c.class_id=d.class_idendgo,运行Select *from class where class_id=g99403Delete from student where student_id= g9940306 Select *from class where class_id=g99403分析执行过程,3.创建更新触发器,例12-3:为teacher表创建update触发器，在教师数据变更时自动更新department表的教师人数。use jwglGo/*如果存在同名的触发器，则删除之 */If exits(select name from sysobjectsWhere type=TR AND name=student_update)Drop TRIGGER student_updateGo/*建立更新触发器*/Create TRIGGER student_update on teacherFor updateAs,update department set teacher_num=(select count(t.teacher_id) from teacher t,inserted Iwhere t.department_id=I.department_id)From department d,inserted Iwhere d.department_id=i. department_idupdate department set teacher_num=(select count(t.teacher_id) from teacher t,deleted ewhere t.department_id=e.department_id)From department d, deleted e where d.department_id=e.department_idgo,运行Select * from departmentUpdate teacher set department_id=dep_01 where teacher_name=潘惠Select * from department分析执行过程Deleted 表和inserted 表问题：只要对表中的数据进行更新，这种更新无论是对表中的一行或多行，还是一列或多列，都将执行触发器。实际应用中，可能只关心特定的列是否被更新。若特定的列被更新，则执行触发器，否则不执行触发器：If update ,例12-4：根据例12-3，使用if update 优化，只对特定的列更新执行触发器操作。use jwglGo/*如果存在同名的触发器，则删除之 */If exits(select name from sysobjectsWhere type=TR AND name=student_update)Drop TRIGGER student_updateGo/*建立更新触发器*/Create TRIGGER student_update on studentFor updateAs,/* 如果更新teacher表中的department_id 列,则执行对department表的teacher_num列的更新*/if update (department) begin update department set teacher_num=(select count(t.teacher_id) from teacher t,inserted Iwhere t.department_id=I.department_id)From department d,inserted Iwhere d.department_id=i. department_idupdate department set teacher_num=(select count(t.teacher_id) from teacher t,deleted ewhere t.department_id=e.department_id)From department d, deleted e where d.department_id=e.department_id end go,二、使用企业管理器创建触发器,在企业管理器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在该对话框中，在名称文本框中选择新建，然后在文本框中输入触发器文本。单击“检查语法”按钮，则可以检查语法是否正确。单击“应用”按钮，则在名称下拉列表中会有新创建的触发器名称。最后，单击“确定”按钮，即可关闭该对话框，成功创建触发器。 举例,12.3触发器实施数据完整性实例,实现参照完整性例12-5 向teacher_course_class 表中添加一行数据，检查所插入数据的有效性。确保教师（teacher_id）存在teacher表中，课程(course_id)存在于course表中,班级（class_id）存在于 class表中。Create trigger tcc_insert on teacher_course_classFor insertAsIf(not exists(select teacher_id from teacher where teacher_id in (select teacher_id from inserted ) or,(not exists(select course_id from course where course_id in (select course_id from inserted ) or(not exists(select class_id from class where class_id in (select class_id from inserted )Beginprint 添加记录不能完成！print 输入的教师编号、课程号或班级号有错误！rollback transactionend,12.3触发器实施数据完整性实例,实施特殊业务规则例12-5 定义teacher表的delete触发器，实施“正在授课的教师数据不能删除”这一业务规则。create trigger teacher_delete on teacherfor deleteasif (exists (select teacher_id from teacher_course_class where teacher_id in(select teacher_id from deleted)begin print 删除记录不能完成！该教师正为某个班级授课！rollback transactionend,12.4 查看、修改和删除触发器,查看T-sql 语言Sp_helptriggerSp_heplptextSp_depends企业管理器,修改删除，再重建T-sql语言的alter trigger或企业管理器删除T-sql语言Drop trigger 触发器名,n企业管理器,12.5 使用触发器时注意事项,触发器的嵌套和递归约束检查优先于触发器检查触发器中的T-SQL语句限制关于多行触发器,思考题：,什么是触发器？触发器有哪几种？触发器主要用于实施什么类型的数据完整性？,上机作业：,为teacher表建立插入和删除触发器，实现department 表教师人数的自动更新。注意，正在任课的教师的数据不能删除。为表student_course建立插入触发器，当向表student_course插入数据时，如果成绩grade=60，该生得到相应的学分，否则不得学分。为表student建立删除触发器，当删除表student中的一个学生资料时，删除其在student_course 表中的成绩数据。为表student_course建立更新触发器，当更改表student_course中的数据时，如果成绩grade由原来的=60，该生得到相应的学分，如由=60更新为60则学分为0。,
