第八章 触发器1
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第八章 触发器
自强不息 厚德载物
课程目标
触发器的类型 DML触发器 DDL触发器
触发器的类型
• SQL Server 2005包括两大类触发器：DML 触发器和DDL触发器。 • DML触发器在数据库中发生数据操作语言 （DML）事件时将启用。 • DDL触发器是SQL Server 2005的新增功能。 当服务器或数据库中发生数据定义语言 （DDL）事件时将调用这些触发器。
1. 1 DML触发器创建
• 使用SQL Server管理 平台创建触发器的过 程如下。 • 在SQL Server管理平 台中，展开指定的服 务器和数据库项，然 后展开表，选择并展 开要在其上创建触发 器的表。右键单击触 发器选项，从弹出的 快捷菜单中选择‚新 建触发器‛选项。
1. 1 DML触发器创建
• END
1. 2 DML触发器的应用
• 创建一个触发器，当插入或更新成绩列时，该触发器检查插入的数 据是否处于设定的范围内。 • CREATE TRIGGER sc_insupd • ON sc • FOR INSERT, UPDATE • AS • DECLARE @cj int, • SELECT @cj=inserted.score • from inserted • IF （@cj<0 or @cj > 100） • BEGIN • RAISERROR （‘成绩的取值必须在0到100之间’, 16, 1） • ROLLBACK TRANSACTION • END
1. 1 DML触发器创建
INSTEAD OF：用于规定执行的是触发器 而不是执行触发SQL语句，从而用触发器 替代触发语句的操作。在表或视图上，每 个 INSERT、 UPDATE或 DELETE语 句最 多可以定义一个INSTEAD OF触发器。然 而，可以在每个具有INSTEAD OF触发器 的视图上定义视图。INSTEAD OF触发器 不能在WITH CHECK OPTION的可更新 视图上定义。
1. 1 DML触发器创建
• EXECUTE AS：指定用于执行该触发器的安全 上下文。允许控制SQL Server实例用于验证被触 发器引用的任意数据库对象的权限的用户账户。 • AFTER：用于规定触发器只有在触发SQL语句 中指定的所有操作都已成功执行后才激发。所有 的引用级联操作和约束检查也必须成功完成后， 才能执行此触发器。如果仅指定FOR关键字，则 AFTER是默认设臵。注意，该类型触发器仅能 在表上创建，而不能在视图上定义该触发器。
1. 1 DML触发器创建
• 其中，各参数的说明如下。 • schema_name：DML触发器所属架构的名 称。DML触发器的作用域是为其创建该触 发器的表或视图的架构。 • trigger_name：用于指定触发器的名称。 触发器的名称必须符合SQL Server标识符 规则，并且其名称在当前数据库中必须是 唯一的。另外，还可以选择是否指定触发 器所有者的名称。
1. DML触发器
• 当数据库中发生数据操作语言（DML）事 件时将调用DML触发器。 • DML事件包括在指定表或视图中修改数据 的INSERT语句、UPDATE语句或DELETE 语句。 • 当往某一个表格中插入记录、修改记录或删 除记录时，SQL Server就会自动执行触发器 发所定义的SQL语句，从而确保对数据的处 理必须符合由这些SQL语句所定义的规则。
1. 1 DML触发器创建
• Table|view：用于指定在其上执行触发器的 表或视图，有时称为触发器表或触发器视 图。可以选择是否指定表或视图的所有者 名称。 • WITH ENCRYPTION：用于加密 syscomments表中包含CREATE TRIGGER语句文本的条目。使用WITH ENCRYPTION可防止将触发器作为SQL Server复制的一部分发布。
1. 2 DML触发器的应用
• 2．使用UPDATE触发器 • 修改触发器和插入触发器的工作过程基本 上一致，修改一条记录等于插入了一条新 的记录并且删除一条旧的记录。当在一个 有UPDATE触发器的表中修改记录时，表 中原来的记录被移动到删除表中，修改过 的记录插入到了插入表中，触发器可以参 考删除表和插入表以及被修改的表，以确 保如何完成数据库操作。
1. 1 DML触发器创建
• （5）虽然不能在临时表或系统表上创建触发器，但是 触发器可以引用临时表。 • （6）在含有用DELETE或UPDATE操作定义的外键的 表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。 • （7）虽然TRUNCATE TABLE语句类似于没有 WHERE子句（用于删除行）的DELETE语句，但它并 不会引发DELETE触发器，因为TRUNCATE TABLE语 句没有记录。 • （8）WRITETEXT语句不会引发INSERT或UPDATE 触发器。
1. 2 DML触发器的应用
• 建立一个触发器，当向sc表中添加数据时，如果添加的数据与s表中 的数据不匹配（没有对应的学号），则将此数据删除。 • CREATE TRIGGER sc_ins • ON sc • FOR INSERT • AS • BEGIN • DECLARE @bh char(5) • Select @bh=Inserted.sno from Inserted • If not exists • (select sno from s where s.sno=@bh) • Delete sc where sno=@bh
1. 1 DML触发器创建
DML触发器使用deleted和inserted逻 辑表。它们在结构上和触发器所在的表的 结构相同，SQL Server会自动创建和管理 这些表。可以使用这两个临时的驻留内存 的表测试某些数据修改的效果及设臵触发 器操作的条件。
1. 1 DML触发器创建
Deleted表用于存储delete、update语句所影 响的行的副本。在执行deleted和update语 句时，行从触发器表中删除，并传输到 deleted表中。 Inserted表用于存储Insert 或update语句所 影响的行的副本，在一个插入或更新事务 处理中，新建的行被同时添加到Inserted 表和触发器表中。Inserted表中的行是触 发器表中新行的副本。
1. 1 DML触发器创建
当创建一个触发器时，必须指定如下选项。 （1）名称； （2）在其上定义触发器的表； （3）触发器将何时激发； （4）激活触发器的数据修改语句，有效选项为INSERT、 UPDATE或DELETE，多个数据修改语句可激活同一个 触发器，例如，触发器可由INSERT或UPDATE语句激 活； （5）执行触发操作的编程语句。
1. DML触发器
• 触发器和引起触发器执行的SQL语句被当作 一次事务处理，如果这次事务未获得成功， SQL Server会自动返回该事务执行前的状态。 和CHECK约束相比较，触发器可以强制实 现更加复杂的数据完整性，而且可以参考其 他表的字段。
1. DML触发器
DML触发器的主要优点如下： （1）DML触发器可通过数据库中的相关表实现级联更改。 （2）DML触发器可以防止恶意或错误的INSERT、 UPDATE和DELETE操作，并强制执行比CHECK约束 定义的限制更为复杂的其他限制。与CHECK约束不同， DML触发器可以引用其他表中的列。 （3）DML触发器可以评估数据修改前后表的状态，并根 据该差异采取措施。
1. 1 DML触发器创建
{[DELETE][，][INSERT][，][UPDATE]}： 用于指定在表或视图上执行哪些数据修改语句时 将激活触发器的关键字。必须至少指定一个选项。 在触发器定义中允许使用以任意的顺序组合这些 关键字。如果指定的选项多于一个，需用逗号分 隔这些选项。
1. 1 DML触发器创建
1. 1 DML触发器创建
• AS：触发器要执行的操作。
• sql_statement：触发器的条件和操作。触 发器条件指定其他准则，以确定DELETE、 INSERT或UPDATE语句是否导致执行触 发器操作。
1. 1 DML触发器创建
• • • • • • • • • • • • • • • • create table sc ( sno char(10), cno char(2), score real ) Go CREATE TRIGGER tr1 ON sc FOR INSERT, UPDATE, DELETE AS PRINT ‘inserted表：’ Select * from inserted PRINT ‘deleted表：’ Select * from deleted Go
1. 1 DML触发器创建
1. 1 DML触发器创建
• 执行插入操作的结果如下：
1. 2 DML触发器的应用
• 1．使用INSERT触发器 • INSERT触发器通常被用来更新时间标记 字段，或者验证被触发器监控的字段中数 据满足要求的标准，以确保数据的完整性。 当向数据库中插入数据时，INSERT触发 器将被触发执行。INSERT触发器被触发 时，新的记录增加到触发器的对应表中， 并且同时也添加到一个插入表中。该插入 表是一个逻辑表，以确定该触发器的操作 是否应该执行，以及如何去执行。
1. 1 DML触发器创建
在对具有触发器的表进行操作时，其操作过程如下。
• 执行insert操作，插入到触发器表中的新行被插入到 inserted表中。
• 执行delete操作，从触发器表中删除的行被插入到 deleted表中。 • 执行update操作，先从触发器表中删除旧行，然后再插 入新行。其中，删除的旧行插入到deleted表中，插入的 新行同时添加到inserted表中。
1. 1 Dຫໍສະໝຸດ BaiduL触发器创建
在创建DML触发器之前应该考虑以下几个问题： （1）CREATE TRIGGER语句必须是批处理中的第一个 语句。将该批处理中随后的其他所有语句解释为 CREATE TRIGGER语句定义的一部分。 （2）创建触发器的权限默认分配给表的所有者，且不能 将该权限转给其他用户。 （3）触发器为数据库对象，其名称必须遵循标识符的命 名规则。 （4）虽然触发器可以引用当前数据库以外的对象，但只 能在当前数据库中创建触发器。
• 会出现触发器 创建窗口，编 写 相 应 SQL 语 句 后 ， 单 击 ‚执行‛按钮， 即可成功创建 触发器。
1. 1 DML触发器创建
• • • • • • • • • • • • • • • 使用Create Trigger命令创建DML触发器的语法形式如下： CREATE TRIGGER [架构名.]触发器名 ON {表名|视图名} [WITH [ENCRYPTION] EXECUTE AS Clause][,...n]] {FOR|AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]} [WITH APPEND] [NOT FOR REPLICATION] AS { SQL语句1 [;] [...n]|EXTERNAL NAME <method specifier [;]> } <method_specifier> ::= assembly_name.class_name.method_name
• WITH APPEND：用于指定应该添加现有 类型的其他触发器。只有当兼容级别（指 某一数据库行为与以前版本的SQL Server 兼容程度）是65或更低时，才需要使用该 可选子句。如果兼容级别是70或更高，则 不必使用该子句。 • NOT FOR REPLICATION：表示当复制 进程更改触发器所涉及的表时，不应执行 该触发器。
• UPDATE函数:只在触发器的作用域中适 用，提供一个布尔值判断特殊的列是否 已更新 IF UPDATE（OrderName） BEGIN …… END
自强不息 厚德载物
课程目标
触发器的类型 DML触发器 DDL触发器
触发器的类型
• SQL Server 2005包括两大类触发器：DML 触发器和DDL触发器。 • DML触发器在数据库中发生数据操作语言 （DML）事件时将启用。 • DDL触发器是SQL Server 2005的新增功能。 当服务器或数据库中发生数据定义语言 （DDL）事件时将调用这些触发器。
1. 1 DML触发器创建
• 使用SQL Server管理 平台创建触发器的过 程如下。 • 在SQL Server管理平 台中，展开指定的服 务器和数据库项，然 后展开表，选择并展 开要在其上创建触发 器的表。右键单击触 发器选项，从弹出的 快捷菜单中选择‚新 建触发器‛选项。
1. 1 DML触发器创建
• END
1. 2 DML触发器的应用
• 创建一个触发器，当插入或更新成绩列时，该触发器检查插入的数 据是否处于设定的范围内。 • CREATE TRIGGER sc_insupd • ON sc • FOR INSERT, UPDATE • AS • DECLARE @cj int, • SELECT @cj=inserted.score • from inserted • IF （@cj<0 or @cj > 100） • BEGIN • RAISERROR （‘成绩的取值必须在0到100之间’, 16, 1） • ROLLBACK TRANSACTION • END
1. 1 DML触发器创建
INSTEAD OF：用于规定执行的是触发器 而不是执行触发SQL语句，从而用触发器 替代触发语句的操作。在表或视图上，每 个 INSERT、 UPDATE或 DELETE语 句最 多可以定义一个INSTEAD OF触发器。然 而，可以在每个具有INSTEAD OF触发器 的视图上定义视图。INSTEAD OF触发器 不能在WITH CHECK OPTION的可更新 视图上定义。
1. 1 DML触发器创建
• EXECUTE AS：指定用于执行该触发器的安全 上下文。允许控制SQL Server实例用于验证被触 发器引用的任意数据库对象的权限的用户账户。 • AFTER：用于规定触发器只有在触发SQL语句 中指定的所有操作都已成功执行后才激发。所有 的引用级联操作和约束检查也必须成功完成后， 才能执行此触发器。如果仅指定FOR关键字，则 AFTER是默认设臵。注意，该类型触发器仅能 在表上创建，而不能在视图上定义该触发器。
1. 1 DML触发器创建
• 其中，各参数的说明如下。 • schema_name：DML触发器所属架构的名 称。DML触发器的作用域是为其创建该触 发器的表或视图的架构。 • trigger_name：用于指定触发器的名称。 触发器的名称必须符合SQL Server标识符 规则，并且其名称在当前数据库中必须是 唯一的。另外，还可以选择是否指定触发 器所有者的名称。
1. DML触发器
• 当数据库中发生数据操作语言（DML）事 件时将调用DML触发器。 • DML事件包括在指定表或视图中修改数据 的INSERT语句、UPDATE语句或DELETE 语句。 • 当往某一个表格中插入记录、修改记录或删 除记录时，SQL Server就会自动执行触发器 发所定义的SQL语句，从而确保对数据的处 理必须符合由这些SQL语句所定义的规则。
1. 1 DML触发器创建
• Table|view：用于指定在其上执行触发器的 表或视图，有时称为触发器表或触发器视 图。可以选择是否指定表或视图的所有者 名称。 • WITH ENCRYPTION：用于加密 syscomments表中包含CREATE TRIGGER语句文本的条目。使用WITH ENCRYPTION可防止将触发器作为SQL Server复制的一部分发布。
1. 2 DML触发器的应用
• 2．使用UPDATE触发器 • 修改触发器和插入触发器的工作过程基本 上一致，修改一条记录等于插入了一条新 的记录并且删除一条旧的记录。当在一个 有UPDATE触发器的表中修改记录时，表 中原来的记录被移动到删除表中，修改过 的记录插入到了插入表中，触发器可以参 考删除表和插入表以及被修改的表，以确 保如何完成数据库操作。
1. 1 DML触发器创建
• （5）虽然不能在临时表或系统表上创建触发器，但是 触发器可以引用临时表。 • （6）在含有用DELETE或UPDATE操作定义的外键的 表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。 • （7）虽然TRUNCATE TABLE语句类似于没有 WHERE子句（用于删除行）的DELETE语句，但它并 不会引发DELETE触发器，因为TRUNCATE TABLE语 句没有记录。 • （8）WRITETEXT语句不会引发INSERT或UPDATE 触发器。
1. 2 DML触发器的应用
• 建立一个触发器，当向sc表中添加数据时，如果添加的数据与s表中 的数据不匹配（没有对应的学号），则将此数据删除。 • CREATE TRIGGER sc_ins • ON sc • FOR INSERT • AS • BEGIN • DECLARE @bh char(5) • Select @bh=Inserted.sno from Inserted • If not exists • (select sno from s where s.sno=@bh) • Delete sc where sno=@bh
1. 1 DML触发器创建
DML触发器使用deleted和inserted逻 辑表。它们在结构上和触发器所在的表的 结构相同，SQL Server会自动创建和管理 这些表。可以使用这两个临时的驻留内存 的表测试某些数据修改的效果及设臵触发 器操作的条件。
1. 1 DML触发器创建
Deleted表用于存储delete、update语句所影 响的行的副本。在执行deleted和update语 句时，行从触发器表中删除，并传输到 deleted表中。 Inserted表用于存储Insert 或update语句所 影响的行的副本，在一个插入或更新事务 处理中，新建的行被同时添加到Inserted 表和触发器表中。Inserted表中的行是触 发器表中新行的副本。
1. 1 DML触发器创建
当创建一个触发器时，必须指定如下选项。 （1）名称； （2）在其上定义触发器的表； （3）触发器将何时激发； （4）激活触发器的数据修改语句，有效选项为INSERT、 UPDATE或DELETE，多个数据修改语句可激活同一个 触发器，例如，触发器可由INSERT或UPDATE语句激 活； （5）执行触发操作的编程语句。
1. DML触发器
• 触发器和引起触发器执行的SQL语句被当作 一次事务处理，如果这次事务未获得成功， SQL Server会自动返回该事务执行前的状态。 和CHECK约束相比较，触发器可以强制实 现更加复杂的数据完整性，而且可以参考其 他表的字段。
1. DML触发器
DML触发器的主要优点如下： （1）DML触发器可通过数据库中的相关表实现级联更改。 （2）DML触发器可以防止恶意或错误的INSERT、 UPDATE和DELETE操作，并强制执行比CHECK约束 定义的限制更为复杂的其他限制。与CHECK约束不同， DML触发器可以引用其他表中的列。 （3）DML触发器可以评估数据修改前后表的状态，并根 据该差异采取措施。
1. 1 DML触发器创建
{[DELETE][，][INSERT][，][UPDATE]}： 用于指定在表或视图上执行哪些数据修改语句时 将激活触发器的关键字。必须至少指定一个选项。 在触发器定义中允许使用以任意的顺序组合这些 关键字。如果指定的选项多于一个，需用逗号分 隔这些选项。
1. 1 DML触发器创建
1. 1 DML触发器创建
• AS：触发器要执行的操作。
• sql_statement：触发器的条件和操作。触 发器条件指定其他准则，以确定DELETE、 INSERT或UPDATE语句是否导致执行触 发器操作。
1. 1 DML触发器创建
• • • • • • • • • • • • • • • • create table sc ( sno char(10), cno char(2), score real ) Go CREATE TRIGGER tr1 ON sc FOR INSERT, UPDATE, DELETE AS PRINT ‘inserted表：’ Select * from inserted PRINT ‘deleted表：’ Select * from deleted Go
1. 1 DML触发器创建
1. 1 DML触发器创建
• 执行插入操作的结果如下：
1. 2 DML触发器的应用
• 1．使用INSERT触发器 • INSERT触发器通常被用来更新时间标记 字段，或者验证被触发器监控的字段中数 据满足要求的标准，以确保数据的完整性。 当向数据库中插入数据时，INSERT触发 器将被触发执行。INSERT触发器被触发 时，新的记录增加到触发器的对应表中， 并且同时也添加到一个插入表中。该插入 表是一个逻辑表，以确定该触发器的操作 是否应该执行，以及如何去执行。
1. 1 DML触发器创建
在对具有触发器的表进行操作时，其操作过程如下。
• 执行insert操作，插入到触发器表中的新行被插入到 inserted表中。
• 执行delete操作，从触发器表中删除的行被插入到 deleted表中。 • 执行update操作，先从触发器表中删除旧行，然后再插 入新行。其中，删除的旧行插入到deleted表中，插入的 新行同时添加到inserted表中。
1. 1 Dຫໍສະໝຸດ BaiduL触发器创建
在创建DML触发器之前应该考虑以下几个问题： （1）CREATE TRIGGER语句必须是批处理中的第一个 语句。将该批处理中随后的其他所有语句解释为 CREATE TRIGGER语句定义的一部分。 （2）创建触发器的权限默认分配给表的所有者，且不能 将该权限转给其他用户。 （3）触发器为数据库对象，其名称必须遵循标识符的命 名规则。 （4）虽然触发器可以引用当前数据库以外的对象，但只 能在当前数据库中创建触发器。
• 会出现触发器 创建窗口，编 写 相 应 SQL 语 句 后 ， 单 击 ‚执行‛按钮， 即可成功创建 触发器。
1. 1 DML触发器创建
• • • • • • • • • • • • • • • 使用Create Trigger命令创建DML触发器的语法形式如下： CREATE TRIGGER [架构名.]触发器名 ON {表名|视图名} [WITH [ENCRYPTION] EXECUTE AS Clause][,...n]] {FOR|AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]} [WITH APPEND] [NOT FOR REPLICATION] AS { SQL语句1 [;] [...n]|EXTERNAL NAME <method specifier [;]> } <method_specifier> ::= assembly_name.class_name.method_name
• WITH APPEND：用于指定应该添加现有 类型的其他触发器。只有当兼容级别（指 某一数据库行为与以前版本的SQL Server 兼容程度）是65或更低时，才需要使用该 可选子句。如果兼容级别是70或更高，则 不必使用该子句。 • NOT FOR REPLICATION：表示当复制 进程更改触发器所涉及的表时，不应执行 该触发器。
• UPDATE函数:只在触发器的作用域中适 用，提供一个布尔值判断特殊的列是否 已更新 IF UPDATE（OrderName） BEGIN …… END