SQL 触发器的创建和使用(SQL2005)

2013年8月4日 第9页
10.1.3 触发器的类型 按照触发事件的不同，可以把Microsoft SQL Server 2005系统提供的触发器分成两大类型， 即DML触发器和DDL触发器。

DML触发器可以在数据库中数据修改时被执行。 DML事件包括在指定表或视图中修改数据的 INSERT语句、UPDATE语句或DELETE语句。 DML触发器可以查询其他表，还可以包含复 杂的T-SQL语句。系统将触发器和触发它的语 句作为可在触发器内回滚的单个事务对待，如 果检测到错误（例如，磁盘空间不足），则整 个事务自动回滚。
第18页
2013年8月4日
理解触发器里面的两个临时的表：
Deleted , Inserted 注意Deleted 与Inserted分别表示触发事件的表 “旧的一条记录”和“新的一条记录”。 一个数据库系统中有两个虚拟表用于存储在表中记 录改动的信息，分别是：
表中记录的操作
虚拟表Inserted
存放新增的记录
2013年8月4日 第6页
10.1.2 触发器的优点 使用触发器有以下优点： 触发器是自动执行的。 触发器可以通过数据库中的相关表进行层 叠更改。 触发器可以强制限制。这些限制比用 CHECK约束所定义的更复杂。
பைடு நூலகம்
2013年8月4日
第7页
触发器概述
1. 触发器的作用 触发器的主要作用是能实现由主键和外键所不能保 证的、复杂的参照完整性和数据的一致性，除此之 外，触发器还有其他许多不同的功能。 可以调用存储过程 为了响应数据库更新，触发器的操作可以通过调用 一个或多个存储过程，甚至可以通过调用外部过程 完成相应操作。 跟踪变化 触发器可以侦测数据库内的操作，从而禁止了数据 库未经许可的更新和变化，使数据库的修改、更新 操作更安全，数据库运行更稳定。
2013年8月4日 第8页
触发器概述
可以强化数据条件约束 触发器能够实现比CHECK语句更为复杂的约束，更 适合在大型数据库管理系统中用来约束数据的完整性 级联和并行运行 触发器可以侦测数据库内的操作，并自动地级联影响 整个数据库的各项内容。例如，某个表的触发器中包 含有对另外一个表的数据操作，如删除、更新、插入， 而该操作又导致该表上的触发器被触发。 由此可见，触发器可以实现高级形式的业务规则、复杂 行为限制和定制记录等功能。
2013年8月4日 第12页
10.2 DML触发器的创建和应用
按照触发器和触发事件的操作时间划分，可以把DML触 发器分为AFTER触发器和INSTEAD OF触发器。 当在INSERT、UPDATE、DELETE语句执行之后才执 行DML触发器的操作时，这种触发器的类型就是 AFTER触发器。AFTER触发器只能在表上定义。 如果希望使用触发器操作代替触发事件操作，可以使用 INSTEAD OF类型的触发器。也就是说，INSTEAD OF触发器可以替代INSERT、UPDATE和DELETE触 发事件的操作。INSTEAD OF触发器既可以建在表上， 也可以建在视图上。通过在视图上建立触发器，可以 大大增强通过视图修改表中数据的功能。
2013年8月4日 第14页
10.2 DML触发器的创建和应用
创建DML触发器应该考虑以下几个问题： ♦ CREATE TRIGGER 必须是批处理中的第一条语句， 并且只能应用于一个表。 ♦ 触发器只能在当前的数据库中创建，但是可以引用 当前数据库的外部对象。 ♦ 创建触发器的权限默认分配给表的所有者，且不能 将该权限转给其他用户。 ♦ 触发器是一种数据库对象，其名称必须遵循标识符 的命名规则。 ♦ 虽然不能在临时表或系统表上创建触发器，但是触 发器可以引用临时表。 2013年8月4日 第15页
第10页
2013年8月4日

DDL触发器是Microsoft SQL Server 2005的 新增功能。当服务器或数据库中发生数据定 义语言（DDL）事件时将调用这些触发器。
DDL触发器与DML触发器的相同之处在于都 需要触发事件进行触发，但是，它与DML触 发器不同的是，它不会为响应针对表或视图 的UPDATE、INSERT或DELETE语句而触发， 相反，它会为响应多种数据定义语言（DDL） 语句（如：CREATE、ALTER）而触发。规 范数据库操作，防止数据库表结构被修改等。
第5页
10.1.1 触发器的概念 当创建数据库对象或在数据表中插入记录、修改 记录或者删除记录时，SQL Server就会自动 执行触发器所定义的SQL语句，从而确保对数 据的处理必须符合由这些SQL语句所定义的规 则。触发器和引起触发器执行的SQL语句被当 作一次事务处理，如果这次事务未获得成功， SQL Server会自动返回该事务执行前的状态 例 如 ， 当 对 某 一 个 表 进 行 诸 如 UPDATE 、 INSERT、DELETE这些操作时，SQL Server 就会自动执行触发器所定义的T-SQL语句，从 而确保对数据的处理必须符合由这些T-SQL语 句所定义的规则。
2013年8月4日 第21页
10.2.2使用Transact-SQL语句创建DML触发器 CREATE TRIGGER 触发器名 ON {表|视图} [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} [NOT FOR REPLICATION] AS [{IF UPDATE(列名)[{AND|OR} UPDATE(列 名)][…n]}] SQL语句
2013年8月4日 第16页
10.2 DML触发器的创建和应用 1. DML 触发器的创建 当创建一个触发器时必须指定如下选项： ♦ 名称。 ♦ 在其上定义触发器的表。 ♦ 触发器将何时激发 ♦ 激活触发器的数据修改语句，有效选项为 INSERT, UPDATE或 DELETE，多个数据 修改语句可激活同一个触发器，例如，触 发器可由INSERT或 UPDATE语句激活。 ♦ 执行触发器操作的编程语句。
虚拟表Deleted
不存储记录
新增时
修改时 删除时
2013年8月4日
存放用来更新的新记录 存放更新前的记录 不存储记录 存放被删除的记录
第19页
10.2.1 使用SQL Server管理控制台 创建DML触发器

在SQL Server管理控制台中，展开指定的 服务器和数据库，单击要创建触发器的数 据表，右击其中的“触发器”文件夹。从 弹出的快捷菜单中选择“新建触发器”选 项，会出现新建触发器模板窗口。
2013年8月4日 第17页



DML 触发器使用 deleted 和 inserted 逻辑表。 它们在结构上和触发器所在的表的结构相同， SQL Server会自动创建和管理这些表。可以使用 这两个临时的驻留内存的表测试某些数据修改的 效果及设置触发器操作的条件。 Deleted表用于存储delete，update语句所影响 的行的副本。在执行delete或update语句时，行 从触发器表中删除，并传输到deleted表中。 Inserted表用于存储Insert或update语句所影响 的行的副本，在一个插入或更新事务处理中，新 建的行被同时添加到Inserted表和触发器表中。 Inserted表中的行是触发器表中新行的副本。
2013年8月4日
第20页
10.2.1 使用SQL Server管理控制台 创建DML触发器 【例】创建一个INSERT触发器 “TR_xs_Insert”，当在xs表中插入一条新 记录时，触发该触发器，并给出“你插入 了一条新记录！”的提示信息。 【例】创建了TR_xs_Insert触发器后，查看 向xs表中插入数据时，此触发器所完成的 功能。
2013年8月4日 第22页
（2）FOR|AFTER。FOR与AFTER同义，指定触发器 只有在触发器SQL语句中指定的所有操作都已成功后 才激发。所有的引用级联操作和约束检查也必须成功 完成后，才能执行此触发器，即为后触发。只能在表 上定义 （3）INSTEAD OF。指定执行触发器而不执行造成触 发的SQL语句，从而替代造成触发的语句。在表或视 图上，每个INSERT、UPDATE或DELETE语句只能 定义一个INSTEAD OF触发器，即替代触发。 （4）[INSERT][,][UPDATE][,][DELETE]是指定在表上 执行哪些数据修改语句时将激活触发器的关键字。必 须至少指定一个选项。在触发器定义中允许使用任意 顺序组合的这些关键字。当进行触发条件的操作时 （INSERT、UPDATE或DELETE），将执行SQL语 2013年8月4日 第23页 句中指定的触发器操作。

2013年8月4日
第4页
触发器概述
（1/4）
触发器是一种特殊类型的存储过程。一般的存储 过程通过存储过程名称被直接调用，而触发器 主要是通过当某个事件发生时自动被触发执行 的。触发器可以用于SQL Server约束、默认 值和规则的完整性检查，还可以完成难以用普 通约束实现的复杂功能。
2013年8月4日
2013年8月4日 第11页
10.2 DML触发器的创建和应用
在Microsoft SQL Server 2005系统中，按照触发器事 件类型的不同，可将DML触发器分成3种类型： INSERT类型、UPDATE类型和DELETE类型 如果该表有INSERT类型的DML触发器,则当向一个表中 插入数据时，则该INSERT类型的触发器触发执行； 如果该表有UPDATE类型的DML触发器，则当对该触发 器表中的数据执行更新操作时，该触发器就执行； 如果该表有DELETE类型的DML触发器，当对该触发器 表中的数据执行删除操作时，该DELETE类型的DML 触发器就触发执行。 也可以将这三种触发器组合起来使用。
第10章 触发器的创建和使用
教学目标 教学重点 教学过程
2013年8月4日
第1页
教学目标
了解触发器的概念和类型

理解触发器的优点 掌握触发器的创建、执行和删除等操作
2013年8月4日
第2页
教学重点
理解触发器的优点 掌握触发器的创建、执行和删除等操作
2013年8月4日
第3页
10.1 触发器(TRIGGER ['trigə] ) 触发器概述 DML触发器的创建和应用 DDL触发器的创建和应用 查看、修改和删除触发器
10.2 DML触发器的创建和应用
♦ 如果一个表的外键包含对定义的 DELETE或UPDATE 操作 的级联，则不能定义INSTEAD OF 和INSTEAD OF UPDATE触发器 ♦ 虽然TRUNCATE TABLE 语句类似于没有WHERE子句（用 于删除行）的DELETE语句，但它并不会引发DELETE触发 器，因为TRUNCATE TABLE 语句没有记录。 ♦ 如果指定了触发器架构名称来限定触发器，则将以相同的方 式限定表名称。 ♦ 在触发器内可以指定任意的 SET 语句。选择的 SET 选项在 触发器执行期间保持有效，然后恢复为原来的设置。 ♦ 在 DML 触发器中不允许使用下列 Transact-SQL 语句： ALTER DATABASE、CREATE DATABASE、DROP DATABASE、RECONFIGURE、LOAD LOG、LOAD DATABASE、RESTORE LOG、RESTORE DATABASE
2013年8月4日 第13页
10.2 DML触发器的创建和应用
DML触发器的主要优点如下： ♦ DML触发器可以防止恶意或错误的插入、修改及删除 操作，并强行比较检查约束定义的限制更为复杂的其 他限制。与检查约束不同，DML触发器可以引用其 他表中的列。例如，触发器可以使用另一个表中的 SELECT比较插入或更新的数据，以及执行其他操作， 如修改数据或显示用户定义错误信息。 ♦ DML触发器可以评估数据修改前后表的状态，并根据 该差异采取措施。 ♦ 一个表中的多个同类DML触发器（INSERT、 UPDATE或DELETE）允许采取多个不同的操作来响 应同一个修改语句。