数据库原理与SQL Server教程第13章 触发器.ppt

1、数据库原理与SQL Server教程,第十三章 触发器,2020年12月19日星期六,数据库原理与SQL Server教程,第十三章 触发器,13.1 触发器简介 13.2 创建和管理触发器 13.3 触发器工作原理及应用 实训13 创建和管理触发器,2020年12月19日星期六,数据库原理与SQL Server教程,13.1 触发器简介,触发器是一种特殊类型的存储过程，它在试图更改触发器所保护的数据时自动执行。触发器与特定的表相关联。 触发器的主要作用是能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。当使用UPDATE、INSERT或DELETE中的一种或多种数据修改操作在指

2、定表中对数据进行修改时，触发器会生效并自动执行。触发器可以查询其他表，并可以包含复杂的Transact-SQL语句。一个表可以有多个触发器。,2020年12月19日星期六,数据库原理与SQL Server教程,13.1 触发器简介,触发器具有如下优点： （1）实现数据库中跨越相关表的级联修改。 （2）实现比CHECK约束更复杂的数据完整性。 （3）实现自定义的错误信息。 （4）维护非规范化数据。 （5）比较修改前后数据的状态。,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.1 创建触发器 13.2.2 修改触发器 13.2.3 删除触发器,13.2 创建和管理触

3、发器,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.1 创建触发器,触发器可以由Transact-SQL语句创建，也可以通过企业管理器来创建： 创建触发器的方法： 1使用Transact-SQL语句创建触发器 2使用企业管理器创建触发器 3创建触发器的注意事项,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.1 创建触发器,1使用Transact-SQL语句创建触发器 创建触发器可以使用CREATE TRIGGER语句，其语法格式如下： CREATE TRIGGER trigger_name ON table | view WITH

4、 ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) n | IF (COLUMNS_UPDATED() bitwise_operator updated_bitmask ) comparison_operator column_bitmask n sql_statement n ,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.1 创建触发器,2使

5、用企业管理器创建触发器 实例演示： 使用企业管理器创建触发器,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.1 创建触发器,3创建触发器的注意事项 在创建触发器时，应该考虑到下列事实和原则： （1）大多数触发器是后反应的，约束和INSTEAD OF触发器是前反应的。 （2）在触发器定义的表中执行插入、删除或者更新语句后触发器自动执行。 （3）创建触发器的用户必须有相关的权限。 （4）只有表或视图的所有者、sysadmin角色的成员、db_owner和 db_ddladmin固定数据库角色的成员能够创建和删除触发器，这个权限不能被传递。 （5）表的所有者不能在视图

6、或者临时表上创建AFTER触发器，但是，触发器可以引用视图和临时表。 （6）表的所有者可以在视图和临时表上创建INSTEAD OF触发器，INSTEAD OF触发器大大的扩展了视图能够支持的更新类型。 （7）触发器不应该返回结果集,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.1 创建触发器,3创建触发器的注意事项 在创建触发器时，应该考虑到下列事实和原则： （8）触发器包含了T-SQL语句，这与存储过程完全相同。 （9）触发器为数据库对象，其名称必须遵循标识符的命名规则。 （10）SQL Server 2000允许在一个表上嵌套几个触发器。一个表可以定义多个触

7、发器，每个触发器可以为一个或几个动作定义。 （11）虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。 （12）在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。 （13）不能包含某些语句。,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.2 修改触发器,对已有的触发器，可以直接进行修改而无需删除后再重建，修改触发器时，可以使用ALTER TRIGGER语句也可以通过企业管理器。 修改触发器的方法： 1ALTER TRIGGER修改触发器 2通过企业管理器修改触发器

8、,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.2 修改触发器,1ALTER TRIGGER修改触发器 ALTER TRIGGER语句语法格式如下： ALTER TRIGGER trigger_name ON ( table | view ) WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOT FOR REPLICATION AS sql_statement .n | ( FOR | AFTER | INSTEAD OF ) INSERT , UPDATE NOT F

9、OR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n ,2020年12月19日星期六,数据库原理与SQL Server教程,13.2.2 修改触发器,2通过企业管理器修改触发器 实例演示： 通过企业管理器修改触发器,2020年12月19日星期六,数据库原理与SQL Server教程

10、,13.2.3 删除触发器,当触发器没有必要存在时，需即时删除，删除触发器可以通过DROP TRIGGER语句也可以通过企业管理器完成。 通过DROP TRIGGER语句删除触发器时，语法格式如下： DROP TRIGGER trigger ,.n 通过企业管理器删除触发器和修改触发器的方法相似，在选择将删除的触发器后，点击“触发器属性”对话框中的“删除”按钮，在弹出的确认对话框中单击“确定”按钮即可删除触发器。 注意：删除触发器所在的表时，触发器将被一同自动地删除。,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.1 INSERT触发器 13.3.2 UPDAT

11、E触发器 13.3.3 DELETE触发器 13.3.4 INSTEAD OF触发器 13.3.5 嵌套触发器 13.3.6 递归触发器,13.3 触发器工作原理及应用,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.1 INSERT触发器,当试图向一个触发器保护的表中插入一行数据时，INSERT触发器将被激活。在插入操作过程中，插入的数据记录在inserted表中，执行插入的过程为： （1）向定义了INSERT触发器的表发送INSERT语句。 （2）INSERT语句记录到日志中。 （3）执行触发器动作。 当INSERT触发器被激发时，新的数据行被添加到表中，同时

12、被插入数据行的副本也被添加到临时表inserted表中，然后，INSERT触发器才被激发，触发器中定义的语句接着被执行。触发器也可以检查inserted表，确定是否执行触发器动作和如何执行触发器动作。 实例演示： INSERT触发器,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.2 UPDATE触发器,当更新定义有UPDATE触发器的表中的数据时，UPDATE操作将激发对应的触发器，执行更新时，UDPATE语句向deleted表中添加将被更新的行在更新前的原始数据行副本，把更新后的数据行副本添加到inserted表中，然后触发器被激发，执行解发器中定义的语句，执

13、行过程为： （1）向表发送UPDATE语句。 （2）在日志中以INSERT和DELETE语句方式记录UPDATE语句。 （3）触发器可以检查deleted表和inserted表以及被更新的库表，确定是否更新多行以及如何执行触发器动作。 在触发器中，可以通过IF UPDATE语句监控特定列数据是否被更新，当被监测列被更新时，触发器可以采取相应的动作。 实例演示： UPDATE触发器,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.3 DELETE触发器,当试图删除定义了DELETE触发器的表中数据行时，DELETE触发器被激发。DELETE操作在删除数据行时，将被删

14、除行的一个副本插入到deleted表中，执行过程为： （1）向表发送DELETE语句。 （2）在日志中记录DELETE语句。 （3）执行触发器动作。 在触发器中，可以通过IF UPDATE语句监控特定列数据是否被更新，当被监测列被更新时，触发器可以采取相应的动作。 在DELETE语句的执行过程中被插入到deleted表中的数据行，不再存在于原数据表中，所以原数据表和deleted表中不会有共同的数据行。 实例演示： UPDATE触发器,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.4 INSTEAD OF触发器,INSTEAD OF触发器取消所有的触发动作，原来

15、的触发动作(插入、更新或删除)不再发生，并执行相应的替代功能。INSTEAD OF触发器增加了可用于表和视图的各种类型的更新操作，当需要使用通常不可更新的视图支持数据修改时，INSTEAD OF触发器可以通过内含SQL语句完成数据的更新操作。 对每一种触发动作，每个表或视图只能有一个INSTEAD OF触发器，但在带有WITH CHECK OPTION定义的视图中不能创建INSTEAD OF触发器。 注意：INSTEAD OF触发器与AFTER触发器(不指定INSTREAD OF)之间的不同。 实例演示： INSTEAD OF触发器,2020年12月19日星期六,数据库原理与SQL Serve

16、r教程,13.3.5 嵌套触发器,触发器可以进行嵌套，最多可嵌套到32级。 在嵌套的触发器中，第一触发器可以激活第二个触发器，第二个触发器又激活第三个触发器，依次类推 。 由于触发器可以嵌套到32级，如果嵌套链中建立了无穷循环，超过最大嵌套级的触发器将被终止，并且整个事务将被回滚。 嵌套触发器可以用于备份等多种功能，使用嵌套触发器时应注意： （1）缺省情况下，嵌套触发器配置选项处于打开状态。 （2）在同一个触发器事务中，一个嵌套触发器不能激活两次；触发器不调用自己来响应触发器中对同一表的第二次更新。 （3）触发器是一个事务，在嵌套触发器中的任何一级中的失败都会取消整个事务，所以数据修改被回滚。

17、,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.5 嵌套触发器,嵌套可以用来保持整个数据库的数据完整性的重要特性，但有时要禁止嵌套功能。 以下语句可用于禁止嵌套： EXEC sp_configure nested TRIGGERS, 0 以下语句可用于启用触发器嵌套： EXEC sp_configure nested TRIGGERS, 1 注意：嵌套启用与禁止操作，不仅适用于当前数据库，而且会应用到服务器上的其他数据库。 在使用时，不推荐按依赖于顺序的序列使用嵌套触发器，应使用单独的触发器层叠数据修改。 实例演示： 在表上创建存储过程，以保存删除触发器所删除的

18、行的备份,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.6 递归触发器,任何触发器都可以包含影响同一个表或另一个表的UPDATE、INSERT或DELETE语句。 启动递归触发器选项，修改一个表中数据的触发器可以激活其自身，从而递归执行。 在数据库创建时，递归触发器选项是禁止的，通过使用ALTER DATABASE语句或企业管理器可以启用和禁止递归触发器。 开启pubs数据库的递归触发器： ALTER DATABASE pubs SET RECURSIVE_TRIGGERS ON 注意：如果嵌套触发器选项被关闭，递归触发器也被禁止，不管数据库的递归触发器设置为开

19、启或关闭。,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.6 递归触发器,实例演示： 在企业管理器中设置递归触发器,2020年12月19日星期六,数据库原理与SQL Server教程,13.3.6 递归触发器,递归触发器的递归可以达到32级，如果触发器中包含无穷循环，则触发器被终止，并回滚整个事务。 递归触发器分为直接递归和间接递归两种： 1直接递归触发器 直接递归触发器，在一个触发器的执行中，导致同一个触发器再次触发。 2间接递归触发器 间接递归触发器，触发器激发并执行一个操作，而该操作又使另一个表中的某个触发器激发。第二个触发器使原始表得到更新，从而再次引发

20、第一个触发器。,2020年12月19日星期六,数据库原理与SQL Server教程,本章小结,触发器是一种特殊类型的存储过程，它在试图更改触发器所保护的数据时自动执行。触发器与特定的表相关联。 触发器的主要作用是能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。当使用UPDATE、INSERT或DELETE中的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效并自动执行。触发器可以查询其他表，并可以包含复杂的Transact-SQL语句。一个表可以有多个触发器。 创建触发器使用CREATE TRIGGER语句，修改触发器使用ALTER TRIGGER语句，删除触发器使

21、用DROP TRIGGER语句。 触发器可以检查deleted表和inserted表以及被更新的库表，确定是否更新多行以及如何执行触发器动作。在触发器中，可以通过IF UPDATE语句监控特定列数据是否被更新，当被监测列被更新时，触发器可以采取相应的动作。,2020年12月19日星期六,数据库原理与SQL Server教程,实训13 创建和管理触发器,目标 准备工作 场景 练习1 创建触发器 练习2 修改触发器 练习3 删除触发器,2020年12月19日星期六,数据库原理与SQL Server教程,实训13 创建和管理触发器,目标 完成本实验后，将掌握以下内容： （1）创建触发器 （2）修改触

22、发器 （3）删除触发器,2020年12月19日星期六,数据库原理与SQL Server教程,准备工作 建立HongWenSoft数据库。 在进行本实验前，必须先建立HongWenSoft数据库。如果还没有创建这个数据库，请先通过练习前创建数据库的脚本创建数据库到数据库管理系统中。,实训13 创建和管理触发器,2020年12月19日星期六,数据库原理与SQL Server教程,场景 宏文软件股份有限公司的人事管理系统数据库，由于公司对于员工的请假需要员工所在部门的部门经理批准，在员工提交请假申请时，员工的请假时间已经变更到数据库中，当部门经理对请假申请否决后，需要把这部分的时间再还原到减少前的状态。为了方便应用程序的开发，减少代码的复杂性，通过触发器完成请假时间的还原功能。,实训13 创建和管理触发器,2020年12月19日星期六,数据库原理与SQL Server教程,练习1 创建触发器 本练习中，将创建触发器，当部门经理否决员工的请假申请时，把该申请中的请假时间还原到员工信息表中。 实验步骤： （1）打开“查询分析器”