第 7 章 触发器

1. 第7章 触发器

2. 主要内容 • 明确了触发器的基本概念、用途，类型以及工作原理 • 了解DML触发器 • DDL触发器的使用

3. 7.1 触发器简介 7.1.1 触发器概念 • 触发器是在对表进行插入（INSERT）、更新（UPDATE）或删除（DELETE）操作时自动执行的存储过程，它是一种特殊类型的存储过程。 • 触发器这种特殊类型的存储过程，是在基表被修改时自动执行的内嵌过程。 • 触发器是独立存储在数据库中的独立对象。触发器是现代数据库管理系统用于响应数据变化的一种机制。触发器是实现数据维护规则的有效机制。

4. 7.1.2 触发器用途 触发器的用途有： 比较不同版本的数据。 读取其他数据库的表中的数据。 在数据库中所有的相关表中级联所作的修改或删除。 回滚无效的修改。 强制实现比由CHECK约束提供的限制更为复杂的限制。 执行本地和远程存储过程。

5. 7.1.3 触发器类型 在SQL SERVER 2005中，触发器分为两大类： • DML触发器：DML触发器是当数据库服务器中发生数据操作语言事件时执行的存储过程。DML触发器又分为两大类：AFTER 触发器和INSTEAD OF触发器。 • DDL触发器：DDL触发器是在响应数据定义语言事件时执行的存储过程。

6. 7.1.4 触发器工作原理 在SQL SERVER 2005中三种类型的“动作”可以激活DML触发器，即INSERT、 DELETE以及UPDATE。 • After触发器在操作完成后再被激活执行触发器里的SQL语句 • Instead Of触发器是在对记录的操作进行之前就被激活，执行触发器中的SQL语句，而不再执行原来的SQL操作 • DDL触发器是在响应数据定义语言（DDL）语句时触发，一般用于在数据库中执行管理任务。

7. 7.2 DML触发器的使用 • DML触发器是由DML语句触发的。 • DML触发器的基本要点： • 触发时机：指定触发器的出发时间。 • 触发事件：引起触发器被触发的事件。 • 条件谓词：当触发器中包含多个触发事件的组合时，为了分别针对不同的事件进行不同的处理，需要使用Oracle提供的条件谓词。 • INSERTING 当触发事件是INSERT时，为真。 • UPDATING[(COLUMN—X)]：当触发事件是UPDATE时，如果修改了column_x列，为真。 • DELETING：当触发时间是DELETE时，取值为真。

8. 7.2.1 After触发器工作原理 • AFTER 触发器是这记录改变完之后，才会被激活执行，且只能在表上定义。它主要用于记录变更后的处理或检查，一旦发现错误可用Rollback Transaction语句回滚本次操作。 • 当使用After Table修改一个表的结构以后，会使该表上的触发器变得无效。要使触发器重新发挥作用，就需要重新编译各个触发器。After触发器触发器只能建在数据表上。

9. 7.2.2 Instead Of触发器工作原理 Instead Of触发器用于取代原来的操作，在记录变更之前发生，它并不去执行原来的插入、更新、删除操作，而去执行触发器本身所定义的操作。Instead Of触发器是定义在复杂视图上的触发器。

10. 7.2.3 DML触发器注意事项 注意事项 如下： • After触发器只能用于数据表中，Instead Of触发器可以用于数据表中和视图中，但两种触发器都不可以建立在临时表上。 • 一个数据表可以有多个触发器，但一个触发器只能对应一个表。 • 在同一个数据表中，对每个操作而言可以建立许多个After触发器，但Instead Of触发器针对每个操作只能建立一个。 • 如果针对某个操作既设置了After触发器又设置了Instead Of触发器，则Instead Of触发器一定会被激活，After触发器就不一定了。

11. 7.2.4 设计After触发器 CreateTRIGGERtrigger_name ON{table|view} [WITHENCRYPTION] {{{FOR|AFTER|INSTEADOF}{[Delete][,][Insert][,][Update]} [WITHAPPEND] [NOTFORREPLICATION] AS [{IFUpdate(column) [{AND|or}Update(column)] [...n] |IF(COLUMNS_UpdateD(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}] sql_statement[...n] } }

12. 7.2.4 设计After触发器 • 参数trigger_name：是触发器的名称。 • Table|view：是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。 • WITHENCRYPTION加密：syscomments表中包含CreateTRIGGER语句文本的条目。 AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。 • INSTEADOF：指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。

13. 7.2.4 设计After触发器 • {[Delete][,][Insert][,][Update]}：是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。 • WITHAPPEND：指定应该添加现有类型的其它触发器。 • NOTFORREPLICATION:表示当复制进程更改触发器所涉及的表时，不应执行该触发器。 • AS：是触发器要执行的操作。 • sql_statement:是触发器的条件和操作。触发器条件指定其它准则，以确定Delete、Insert或Update语句是否导致执行触发器操作。

14. 7.2.5 设计Instead Of 触发器 Instead Of 触发器在工作时，SQL Server服务器接到执行SQL语句请求以后，先建立临时的INSERT表和DELETE表,然后就激发Instead Of触发器，至于那个SQL语句是如何执行就不管了。把执行权交给了Instead Of 触发器，由它完成之后的操作。

15. 7.2.6 修改DML触发器 • 使用SQL Server Management Studio修改触发器内容 2. 使用Transact-SQL语句修改触发器内容 • 可以使用系统存储过程sp_rename 命令修改触发器的名字。 • 其语法格式为： • Sp_rename oldname, ,newname • 例如修改前面创建的学生_INSERT触发器的名称为学生2_INSERT的语句为： • Sp_rename学生_INSERT, 学生2_INSERT

16. 7.2.7 删除DML触发器 • 使用Server Management Studio删除触发器 • 1) 在Server Management Studio中展开指定的服务器和数据库，这里选中【教务管理】数据库。 • 2) 选择要创建触发器的表（如选择“学生”表），展开数据表，双击数据表结点下的【触发器】项，可以查看到已经存在的触发器 • 3) 在要进行删除的触发器上右击，在弹出的快捷菜单中选择【删除】命令。 • 4) 在弹出的删除对象对话框中显示了当前要删除的触发器相关信息，如果确认删除，则单击确定按钮，系统将删除触发器。

17. 7.2.7 删除DML触发器 2. 使用系统存储命令删除触发器 用系统命令DROP TRIGGER 删除指定的触发器，其语法形式如下： DROP TRIGGER Trigger name 例如删除触发器学生_INSERT的命令为： DROP TRIGGER 学生_INSERT

18. 7.2.8 禁用与启动DML触发器 禁用触发器与删除触发器不同，禁用触发器时，仍会为数据表定义该触发器，只是在执行Delete语句、Insert语句或Update语句，除非重新启动触发器，否则不会执行触发器中的操作。 在Server Management Studio中禁用与启动触发器，也不需要先查到触发器列表。在触发器列表里，右击其中一个触发器，在弹出的快捷菜单中选择【禁用】选项，即可禁用该触发器。

19. 7.3 DDL触发器 7.3.1 DDL触发器简介 DDL 触发器是SQL Server 2005新增的一种特殊的触发器,它在响应数据定义语言 (DDL) 语句时触发。一般有以下几种情况可以使用DDL 触发器： • 数据库里的库架构或数据表结构很重要，不允许被修改。 • 防止数据库或数据表被误操作删除。 • 在修改某个数据表结构的同时在修改另一个数据表的结构。 • 要记录对数据库结构操作的事件。

20. 7.3.2 设计DDL触发器 建立DDL触发器的语法代码如下： CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] } <ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] <method_specifier> ::= assembly_name.class_name.method_name

21. 7.3.2 设计DDL触发器 • trigger_name ：触发器的名称。每个 trigger_name 必须遵循标识符规则，但 trigger_name 不能以 # 或 ## 开头。 • ALL SERVER ：将 DDL 触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现 event_type 或 event_group，就会激发该触发器。 • DATABASE ：将 DDL 触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现 event_type 或 event_group，就会激发该触发器 • WITH ENCRYPTION ：对 CREATE TRIGGER 语句的文本进行加密。使用 WITH ENCRYPTION 可以防止将触发器作为 SQL Server 复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。

22. 7.3.2 设计DDL触发器 • EXECUTE AS ：指定用于执行该触发器的安全上下文。允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。 • event_type ：执行之后将导致激发 DDL 触发器的 Transact-SQL 语言事件的名称。用于激发 DDL 触发器的 DDL 事件中列出了在 DDL 触发器中可用的事件。 • event_group ：预定义的 Transact-SQL 语言事件分组的名称。执行任何属于 event_group 的 Transact-SQL 语言事件之后，都将激发 DDL 触发器。用于激发 DDL 触发器的事件组中列出了在 DDL 触发器中可用的事件组。 • sql_statement ：触发条件和操作。触发器条件指定其他标准，用于确定尝试的 DML 或 DDL 语句是否导致执行触发器操作。

23. 7.3.3 查看与修改DDL触发器 在【查询编辑器】里可以输入SQL代码对DDL触发器进行修改。 • 创建DDL触发器 CREATE TRIGGER （Transact-SQL） • 删除DDL触发器 DROP TRIGGER （Transact-SQL） • 修改DDL触发器 ALTER TRIGGER （Transact-SQL） • 重命名DDL触发器 SP_RENAME TRIGGER（Transact-SQL）

24. 7.3.3 查看与修改DDL触发器 • 禁用DDL触发器 DISABLE TRIGGER （Transact-SQL） • 启用DDL触发器 ENABLE TRIGGER （Transact-SQL） • 删除DDL触发器 DROP TRIGGER （Transact-SQL）