1、第12章 触发器,第12章 触发器,案例1,李四晋级后，薪水也需调整,触发器可以完成自动调整,第12章 触发器,案例2,删除学生时，一并删除学生的成绩信息；,触发器可以完成自动判断,12.1触发器概述,触发器就像一枚地雷，被特定事件触发时，就会发生一系列动作。 触发器实际上就是一种特殊类型的存储过程只有在系统执行某些特定的T-SQL语句或操作时，触发器才会自动执行。,12.1触发器概述功能,触发器的常用功能如下： 1完成更复杂的数据约束 2检查所做的SQL所作的操作是否允许 3修改其它数据表里的数据 4调用更多的存储过程 5返回自定义的错误信息 6更改原本要操作的SQL语句 7防止数据表结构被
2、更改或数据表被删除 8审核和控制服务器会话,12.1触发器概述,触发器的类型 1DML触发器（ select、insert、update、delete触发 ）DML触发器是当数据库服务器中发生数据操作语言事件时执行的存储过程。 2DDL触发器（ create 、 drop、alter 触发）DDL触发器是在响应数据定义语言事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。 3登录触发器登录触发器将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。,12.2 DML触发器,DML触发器
3、的类型 1After触发器：这类触发器是在记录已经改变之后,才会被激活执行，它主要是用于记录变更后的处理或检查。 2Instead Of触发器：这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作，而去执行触发器本身所定义的操作。,12.2 DML触发器两个特殊的系统表,为了保存DML操作之前的数据和操作之后的数据，系统提供了两个特殊的表。 SQL Server为每个DML触发器都定义了两个特殊的表，一个是INSERTED表，一个是DELETED表。 这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于
4、这两个表，用户只有读取的权限，没有修改的权限。 这两个表的结构与触发器所在数据表的结构完全一致，当触发器的工作完成之后，这两个表也将会从内存中删除。,12.2 DML触发器两个特殊的系统表,对于插入记录操作来说， INSERTED表里存放的是要插入的数据； 对于更新记录操作来说， INSERTED表里存放的是更新后的记录。 对于更新记录操作来说， DELETED表里存放的是更新前的记录； 对于删除记录操作来说， DELETED表里存入的是被删除的旧记录。,12.2 DML触发器用T-SQL语言创建触发器,1创建AFTER触发器的语法如下： CREATE TRIGGER schema_name.
5、trigger_name ON table | view WITH ENCRYPTION|EXECUTE AS FOR|AFTER INSERT,UPDATE,|EXTERNAL NAME ,12.2 DML触发器用T-SQL语言创建触发器,【例12.1】修改Teacher（教师）表中的数据时，通过触发器向客户端显示一条消息。CREATE TRIGGER teacher_update_message ON Teacher AFTER UPDATE AS BEGIN RAISERROR (注意：有人修改教师表中的数据！,16,10) END GO,12.2 DML触发器用T-SQL语言创建触发器
6、,【例12.2】删除Teacher（教师）表中的记录时，通过触发器删除CourseTeacher（课程教师）表中和该教师相关的记录。 CREATE TRIGGER teacher_delete_course ON Teacher AFTER DELETE AS BEGINDELETE FROM CourseTeacherWHERE teacherID in (SELECT teacherID from deleted) END GO,12.2 DML触发器用T-SQL语言创建触发器,创建INSTEAD OF触发器 Instead Of触发器是在SQL Server服务器接到执行SQL语句请求后
7、，激活Instead Of触发器，至于该SQL语句本身的操作，系统直接忽略。,12.2 DML触发器用T-SQL语言创建触发器,创建INSTEAD OF触发器的语法代码如下： CREATE TRIGGER schema_name.trigger_name ON table | view WITH ENCRYPTION|EXECUTE AS INSTEAD OF INSERT,UPDATE,|EXTERNAL NAME ,和After触发器相比，仅将此处改为Instead Of,12.2 DML触发器用T-SQL语言创建触发器,【例12.3】当有人试图修改课程表中的数据时，利用触发器跳过修改数据
8、的SQL语句（防止数据被修改），并向客户端显示一条消息。 CREATE TRIGGER course_update ON course INSTEAD OF UPDATE AS BEGINRAISERROR (警告：你无权修改课程表中的数据！,16,10) END GO,12.2 DML触发器用T-SQL语言创建触发器,对触发器进行测试： SELECT * FROM Course WHERE courseID = 2 UPDATE Course SET totalperiod = 80 WHERE courseID = 2 SELECT * FROM Course WHERE courseID
9、 = 2 在“结果”对话框里显示了两个完全一样的记录 说明UPDATE语句没有发生作用。,12.2 DML触发器查看触发器,查看DML触发器有两种方式： 通用Management Studio来查看选中某一触发器，然后双击即可查看该触发器的信息。 利用系统存储过程来查看（1）系统存储过程sp_help可以了解如触发器名称、类型、创建时间等基本信息，其语法格式为：sp_help 触发器名例如：sp_help teacher_insert,12.2 DML触发器查看触发器,（2）sp_helptext系统存储过程sp_helptext可以查看触发器的文本信息，其语 法格式为：sp_helptext
10、 触发器名例如：sp_helptext teacher_insert,12.2 DML触发器修改触发器,修改触发器的语法代码如下： ALTER TRIGGER ON table|view WITH ENCRYPTION|EXECUTE AS FOR|AFTER|INSTEAD OF INSERT,UPDATE,|EXTERNAL NAME 分析上述语法代码可以发现，修改触发器语法中所涉及到主要参数和创建 触发器的主要参数几乎一样，在此不再赘述。,12.2 DML触发器修改触发器,1. 禁用DML触发器 使用Alter Table语句禁用DML触发器的语法如下： Alter table 数据表名
11、Disable trigger 触发器名或ALL如果要禁用所有触发器，用“ALL”来代替触发器名。 2启用DML触发器使用Alter Table语句也可以启用触发器，其语法如下：Alter table 数据表名Enable trigger 触发器名或ALL如果要启用所有触发器，用“ALL”来代替触发器名。,12.2 DML触发器修改触发器,1. 删除DML触发器 用SQL语句也可删除触发器，删除触发器的语法代码如下所示： Drop Trigger 触发器名 例子 删除触发器teacher_update Drop trigger teacher_update,22,表1 Student表结构,表
12、2 Course表结构,小测验,表3 SC表结构,23,1.创建一个视图vw_stu_avg：查询每个学生的平均成绩，要求列出学生的姓名、学号、平均成绩。 2.创建一个唯一索引，该索引依赖于数据表Student的Sname字段。 3.创建一个名为pro_get_avg的存储过程，该存储过程能够根据用户指定的 Cname（课程名）求出该课程的平均成绩。执行该存储过程。,24,回顾,1.触发器的定义，触发器和存储过程的区别 。 2.触发器的功能。 3.触发器的分类。 4.触发器有哪几种临时表，里面存什么内容。 5.DML触发器的分类。 6.DML触发器中After触发器的创建。 7.DML触发器中
13、Instead of触发器的创建。,12.3 DDL触发器,DDL触发器像常规触发器一样，DDL 触发器将激发存储过程以响应事件。 但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。而是为响应以 CREATE、ALTER和DROP开头的语句。,12.3 DDL触发器,一般来说，在以下几种情况下可以使用DDL触发器： （1）防止数据库架构进行某些修改。 （2）防止数据库或数据表被误操作删除。 （3）要记录数据库架构中的更改或事件。仅在运行触发DDL触发器的DDL语句后，DDL触发器才会激发。 DDL触发器只能作为AFTER触发器使用。,12
14、.3 DDL触发器,创建DDL触发器的语法代码如下： CREATE TRIGGER ON ALL SERVER|DATABASE WITH ,.n FOR|AFTERevent_type|event_group,.n AS sql_statement;.n|EXTERNAL NAME; := ENCRYPTION EXECUTE AS Clause := assembly_name.class_name.method_name,12.3 DDL触发器,【例12.4】建立用于保护CJGL数据库中的数据表不被删除的触发器。 具体操作步骤如下： （1）启动“SQL Server Management
15、 Studio”，在“对象资源管理器”下选择“数据库”，定位到“CJGL”数据库。 （2）单击“新建查询”按钮，在弹出的“查询编辑器”的编辑区里输入以下代码： CREATE TRIGGER disable_drop_table ON DATABASE FOR DROP_TABLE AS BEGIN RAISERROR (对不起，不能删除CJGL数据库中的数据表,16,10) ROLLBACK END GO （3）单击“执行”按钮，生成触发器。,12.3 DDL触发器,测试DDL触发器的功能，具体操作步骤如下： （1）启动“SQL Server Management Studio”，在“对象资源
16、管理器”下选择“数据库”，定位到“CJGL”数据库。 （2）单击“新建查询”按钮，在弹出的“查询编辑器”的编辑区里输入以下代码：DROP TABLE Teacher （3）单击“执行”按钮，运行结果如图所示。,12.3 DDL触发器,查看DDL触发器 1作用在当前SQL Server服务器上的DDL触发器所在位置 选择所在SQL Server服务器，定位到“服务器对象”，“触发器”，在“摘要”对话框里就可以看到所有的作用在当前SQL Server服务器上的DDL触发器 2作用在当前数据库中的DDL触发器所在位置 选择所在SQL Server服务器，找到特定的“数据库”，定位到“可编程性”，然后
17、在“数据库触发器”树形目录中可以看到所有的当前数据库中的DDL触发器,12.4 登录触发器,登录触发器将为响应 LOGON 事件而激发存储过程。 登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。如果身份验证失败，将不激发登录触发器。 可以使用登录触发器来审核和控制服务器会话，例如通过跟踪登录活动、限制 SQL Server 的登录名或限制特定登录名的会话数。,本章小结,本章首先介绍了触发器的特点、作用和类型，然后介绍了创建DML、DDL和登录触发器的方法和步骤。通过本章的学习，可以了解触发器的基本概念，掌握使用图形界面和代码创建、修改和删除触发器的方法。,Thank You !,
