第13章触发器

1、触发器触发器 2022年3月23日星期三1在前面介绍了编写批处理的方法，但要让这些批处理保持永久有效，就要利用到存储过程和触发器了。存储过程的存在独立于表，触发器的利用则与表紧密结合。存储过程的存在独立于表，触发器的利用则与表紧密结合。用户可以使用存储过程来完善应用程序，使应用程序更有效用户可以使用存储过程来完善应用程序，使应用程序更有效率；可以利用触发器来实现复杂的业务规则，更有效地实施数率；可以利用触发器来实现复杂的业务规则，更有效地实施数据完整性。据完整性。触发器触发器 2022年3月23日星期三2第第1313章章 触发器触发器 q 概述概述 q 管理触发器管理触发器 q 触发器的工作原

2、理触发器的工作原理q 小结小结q 练习练习触发器触发器 2022年3月23日星期三3q 典型的应用就是银行的取款机系统 为什么需要触发器为什么需要触发器帐户信息表bank 交易信息表transInfo 张三取钱200 问题：没有自动修改张三的余额张三开户1000元,李四开户1元 T-SQL语句：触发器问题描述（银行取款）触发器触发器 2022年3月23日星期三4最优的解决方案就是采用触发器触发器： 它是一种特殊的存储过程 即：触发器是在对表进行插入、更新或删除操作时自动执行的存储过程。 也具备事务的功能 它能在多表之间执行特殊的业务规则即：触发器通常用于强制业务规则。 触发器是一种高级约束，可

3、以定义比用CHECK 约束更为复杂的约束 即：触发器可执行复杂的SQL语句（if/while/case）；可引用其它表中的列。触发器触发器 2022年3月23日星期三5张三张三李四李四王五王五赵二赵二王三王三宋二宋二刘五刘五插入什么是触发器什么是触发器删除触发器触发赵二退休 赵二赵二员工表员工表退休员工表退休员工表触发器触发器 2022年3月23日星期三6概概 述述Microsoft SQL Server 2000 触发器是一类特殊的存储过程，被定义为在对表或视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行。触发器是功能强大的工具，使每个站点可以在有数据修改时自动强制执行

4、其业务规则。触发器可以扩展 SQL Server 约束、默认值和规则的完整性检查逻辑，但只要约束和默认值提供了全部所需的功能，就应使用约束和默认值。当有操作影响到触发器保护的数据时，触发器就自动发生。因此，触发器是在特定表上进行定义的，该表也称为触发器表。当有操作针对触发器表时，例如在表中插入、删除、更新数据时，如果该表有相应操作类型的触发器，那么触发器就自动触发执行。因为触发器在操作有效之后才能执行，所以在修改中他们代表“最后的动作”。假如导致触发器的一个请求失败，SQL Server系统将拒绝更新数据，并且对那些应用程序返回一个错误消息。表可以有多个触发器。CREATE TRIGGER 语

5、句可以与 FOR UPDATE、FOR INSERT 或 FOR DELETE 子句一起使用，指定触发器专门用于特定类型的数据修改操作。当指定 FOR UPDATE 时，可以使用 IF UPDATE (column_name) 子句，指定触发器专门用于具体某列的更新。触发器触发器 2022年3月23日星期三7触发器定义在特定的表上，与表相关自动触发执行不能直接调用是一个事务（可回滚）触发器触发器 2022年3月23日星期三8【存储过程】存储过程是一组 Transact-SQL 语句，在一次编译后可以执行多次。因为不必重新编译 Transact-SQL 语句，所以执行存储过程可以提高性能。【触发

6、器】触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。CREATE PROCEDURE 或 CREATE TRIGGER 语句不能跨越批处理。即存储过程或触发器始终只能在一个批处理中创建并编译到一个执行计划中。触发器既然是一种特殊类型的存储过程，那么它与存储过程也有一些区别。例如，存储过程可以由用户直接调用执行，但是触发器不能被直接调用执行。触发器触发器 2022年3月23日星期三9在SQL Server 中一张表可以有多个触发器。用户可以针对INSERT、UPDATE或DELETE语句分别设置触发器，也可以针对一张表上

7、的特定操作设置多个触发器。触发器里可以容纳非常复杂的Transact-SQL 语句。但是不但是不管触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，管触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看做是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将被看做是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。会自动回滚。触发器在服务器将特定的操作（触发器在服务器将特定的操作（INSERT、UPDATE或或DELETE ）执行）执行结束后才执行。结束后才执行。触发器触发器 2022年3月23日星期三10使用触发器的最终目的是更好地维护

8、企业的业务规则。在实际运用中，触发器主要提供以下功能： 级联修改数据库中的所有相关表。如：publishers表中的内容被删除后，将自动对titles、titleauthor等表的修改。 撤销或回滚违反引用完整性的操作，防止非法修改数据。 执行比检查约束CHECK更复杂的约束操作。 查找在数据修改前后，表状态之间的差别，并根据差别来分别采取相应的措施。 在一张表同一类型的操作（INSERT、UPDATE或DELETE ）上设置多个触发器，从而可以针对同样的修改语句执行不同的多种操作。触发器触发器 2022年3月23日星期三11创建触发器创建触发器创建触发器，触发器是一种特殊的存储过程，在用户试

9、图对指定的表执行指定的数据修改语句时自动执行。Microsoft SQL Server 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。触发器的类型：DELETE 触发器INSERT 触发器UPDATE 触发器触发器触发器 2022年3月23日星期三12创建触发器时需指定： 名称。在其上定义触发器的表。触发器将何时激发。激活触发器的数据修改语句。有效选项为 INSERT、UPDATE 或 DELETE。多个数据修改语句可激活同一个触发器。执行触发操作的编程语句。 触发器触发器 2022年3月23日星期三13创建触发器前应考虑下列问题： CREATE TRIG

10、GER 语句必须是批处理中的第一个语句。将该批处理中随后的其它所有语句解释为 CREATE TRIGGER 语句定义的一部分。创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。触发器为数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF 和 INSTEAD OF UPDATE 触发器。虽然 TRUNCATE TABLE 语

11、句类似于没有 WHERE 子句（用于删除行）的 DELETE 语句，但它并不会引发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。WRITETEXT 语句不会引发 INSERT 或 UPDATE 触发器。 触发器触发器 2022年3月23日星期三141使用T-SQL语句创建触发器创建触发器使用CREATE TRIGGER语句。CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员以及 db_owner 和 db_ddladmin 固定数据库角色成员，并且不可转让。触发器触发器 2022年3月23日星期三15【语法格式语法格

12、式】CREATE TRIGGER trigger_name ON table | view WITH 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_stateme

13、nt .n 触发器触发器 2022年3月23日星期三16q创建触发器的语法：CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE, INSERT, UPDATE AS T-SQL语句GO WITH ENCRYPTION表示加密触发器定义的SQL文本 DELETE, INSERT, UPDATE指定触发器的类型触发器触发器 2022年3月23日星期三17【示例】 使用带有提醒消息的触发器当有人试图在 titles 表中添加或更改数据时，下例将向客户端显示一条消息。USE pubsIF EXISTS (SELECT

14、 name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)GO触发器触发器 2022年3月23日星期三18【示例】使用带有提醒电子邮件的触发器当 titles 表更改时，下例将电子邮件发送给指定的人员 (MaryM)。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name =

15、 reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETE AS EXEC master.xp_sendmail MaryM, Dont forget to print a report for the distributors.GO触发器触发器 2022年3月23日星期三19【示例】在 employee 和 jobs 表之间使用触发器业务规则由于 CHECK 约束只能引用定义了列级或表级约束的列，表间的任何约束（在下例中是指业务规则）都必须

16、定义为触发器。下例创建一个触发器，当插入或更新雇员工作级别 (job_lvls) 时，该触发器检查指定雇员的工作级别（由此决定薪水）是否处于为该工作定义的范围内。若要获得适当的范围，必须引用 jobs 表。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = employee_insupd AND type = TR) DROP TRIGGER employee_insupdGO触发器触发器 2022年3月23日星期三20CREATE TRIGGER employee_insupdON employeeFOR INSERT, U

17、PDATEAS/* Get the range of level for this job type from the jobs table. */DECLARE min_lvl tinyint, max_lvl tinyint, emp_lvl tinyint, job_id smallintSELECT min_lvl = min_lvl, max_lvl = max_lvl, emp_lvl = i.job_lvl, job_id = i.job_idFROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id JOIN job

18、s j ON j.job_id = i.job_id触发器触发器 2022年3月23日星期三21IF (job_id = 1) and (emp_lvl 10) BEGIN RAISERROR (Job id 1 expects the default level of 10., 16, 1) ROLLBACK TRANSACTION ENDELSE IF NOT (emp_lvl BETWEEN min_lvl AND max_lvl) BEGIN RAISERROR (The level for job_id:%d should be between %d and %d., 16, 1,

19、job_id, min_lvl, max_lvl) ROLLBACK TRANSACTION END触发器触发器 2022年3月23日星期三22【例】创建一个针对products 表执行update操作时被触发的触发器，返回共修改了多少行数据。USE NorthwindIF EXISTS (SELECT name FROM sysobjects WHERE name=tr_product_update AND type=TR)DROP TRIGGER tr_product_updateGOUSE NorthwindGOCREATE TRIGGER tr_product_update ON pr

20、oductsFOR update AS DECLARE msg varchar(100) SELECT msg=str(rowcount)+employees updated by this statement PRINT msg RETURNGO触发器触发器 2022年3月23日星期三23在创建触发器的语句中，禁止使用下列T-SQL语句：ALTER DATABASE、ALTER PROCEDURE、ALTER TABLE、ALTER TRIGGER 、ALTER VIEWCREATE DATABASE、CREATE DEFAULT、CREATE INDEX、 CREATE PROCEDURE

21、、 CREATE RULE、CREATE TABLE、 CREATE TRIGGERDROP DATABASE、 DROP DEFAULT、DROP INDEX、DROP PROCEDURE、DROP RULE、 DROP TABLE、DROP TRIGGER、DROP VIEW GRANT RESTORE DATABASE、RESTORE LOG REVOKETRUNCATE TABLE触发器触发器 2022年3月23日星期三24注：在CREATE TRIGGER语句中不能使用SELECT语句返回对表格查询的数据，因为触发器不接受用户应用程序传递的参数，从而也无法向用户应用程序返回查询表格数

22、据所得到的结果。由于系统表具有存储数据的特殊性和重要性，所以建议用户不要自己在系统表上建立触发器。在创建触发器时，不允许RETURN返回体现运行状态的数据。触发器触发器 2022年3月23日星期三25 【例】 创建一个当products表执行INSERT、UPDATE操作时触发的触发器。 create trigger tr_products_insupd on products for insert,update as 由于SQL Server支持在同一个表的同一种操作类型上建立多个触发器，所以当建立完tr_products_insupd触发器后， 在products表执行INSERT操作时，

23、将触发tr_products_insupd触发器，在执行update操作时，将触发tr_product_update和tr_products_insupd触发器，他们都是有效的触发器。触发器触发器 2022年3月23日星期三26 2使用企业管理器来创建触发器可以使用Enterprise Manager 来创建触发器，步骤是：（1）选中要创建触发器的表所在的数据库，展开该节点。（2）展开该数据库节点的表节点。（3）选中指定的表，右击鼠标，从弹出的快捷菜单中展开“所有任务”子菜单，选择“管理触发器”。（4）弹出对话框。（5）在“文本”文本框中输入触发器的语句。（6）单击“检查语法”按钮，检查语法错

24、误。（7）单击“确定”按钮完成触发器的创建。触发器触发器 2022年3月23日星期三27修改触发器修改触发器修改触发器使用ALTER TIGGER语句。语法格式为：ALTER TIGGER trigger_nameON TABLE WITH ENCRYPTIONFOR DELETE , UPDATE , INSERT NOT FOR REPLICATION AS SQL_statement .n用户也可以用Enterprise Manager 来修改触发器，步骤与创建触发器的步骤类似。触发器触发器 2022年3月23日星期三28嵌套触发器嵌套触发器如果一个触发器在执行操作时引发了另一个触发器，

25、而这个触发器又接着引发下一个触发器.，这些触发器就是嵌套触发器。触发器可嵌套至32层，并且可以控制是否可以通过“嵌套触发器”服务器配置选项进行触发器嵌套。触发器触发器 2022年3月23日星期三29查看触发器查看触发器 触发器是特殊的存储过程，适用于存储过程的管理方式都适用于触发器，所以用户完全可以使用sp_helptText、sp_help、sp_depends等系统存储过程以及使用企业管理器来浏览触发器的有关信息。也可以使用sp_rename 系统存储过程来为触发器更名。(1)使用sp_helptrigger系统存储过程来显示指定表上指定类型的触发器的信息【语法格式】 sp_helptri

26、gger table_name,type其中，type是触发器类型的取值范围，包括INSERT、UPDATE和DELETE。如果不设置type的值，则返回定义在该表上所有触发器的信息。【例】 sp_helptrigger products,update 触发器触发器 2022年3月23日星期三30(2) 使用sp_helptext系统存储过程显示一个指定触发器的代码。【例】 sp_helptext tr_products_insupd与存储过程一样，在定义或修改触发器的语句中使用WITH ENCRYPTION子句，触发器的代码也可以被加密。被加密的触发器的代码将不能使用sp_helptext系

27、统存储过程来查看。(3) 使用sp_depends系统存储过程来显示一个表上触发器的依赖关系。【例】 sp_depends tr_products_insupd触发器触发器 2022年3月23日星期三31删除触发器删除触发器1使用T-SQL语句删除触发器删除触发器使用DROP TRIGGER语句。 【语法格式】DROP TRIGGER 触发器名,n用户删除某个表格时，所有建立在该表上的触发器都将被删除。2使用企业管理器删除触发器触发器触发器 2022年3月23日星期三32触发器的工作原理触发器的工作原理Microsoft SQL Server 2000系统提供了3种类型的触发器，即INSERT

28、类型、UPDATE类型和DELETE类型。当向表中插入数据时，INSERT触发器触发执行。当INSERT触发器触发时，新的记录增加到触发器表中和inserted表中。该inserted表是一个逻辑表，保存了所插入记录的拷贝，允许用户参考INSERT语句中数据。触发器可以检查inserted表，来确定该触发器的操作是否应该执行和如何执行。在inserted表中的那些记录，总是触发器表中一行或多行记录的冗余。当触发一个DELETE触发器时，被删除的记录放在一个特殊的deleted表中。deleted表是一个逻辑表，用来保存已经从表中删除的记录。该deleted表允许参考原来的DELETE语句删除的

29、已经记录在日志中的数据。修改一条记录就等于插入一条新记录和删除一条旧记录。同样，UPDATE语句也可以看成是由删除一条记录的DELETE语句和增加一条记录的INSERT语句组成。当在某一个有UPDATE触发器表的上面修改一条记录时，表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。触发器可以检查deleted表和inserted表以及被修改的表，以便确定是否修改了多个行和应该如何执行触发器的操作。触发器触发器 2022年3月23日星期三33执行触发器时，系统创建了两个特殊的逻辑表：inserted表表和deleted表表。它们由系统来维护，用户不能对其进行修改。

30、它们存在于内存中而不是数据库中。这两个表的结构总是与被触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也会被删除。inserted逻辑表：存放由于执行INSERT或UPDATE语句而要从表中插入的所有行。在执行INSERT或UPDATE操作时，新的行同时添加到激活触发器的表中和inserted表中，inserted表的内容是激活触发器的表中新行的拷贝。deleted逻辑表：存放由于执行DELETE或UPDATE语句而要从表中删除的所有行。在执行DELETE或UPDATE操作时，被删除的行从激活触发器的表中被移动到deleted表中，这两个表（DELETE 表和触发器表不会有

31、共同的行。触发器中使用的特殊表触发器中使用的特殊表触发器触发器 2022年3月23日星期三34对具有触发器的表（触发器表）进行操作时，其操作过程为：执行INSERT操作插入到触发器表中的新行被插入到inserted表中。执行DELETE操作从触发器表中删除的行被插入到deleted表中。执行UPDATE操作先从触发器表中删除旧行，然后再插入新行。其中，被删除的旧行被插入到deleted表中，插入的新行被插入到inserted表中。deleted表和inserted表都是针对当前触发器的局部临时表，这些表只对应于当前触发器的基本表。如果在触发器中使用了存储过程，或是产生了嵌套触发器的情况，则不同的触发器将会使用属于自己基本表的deleted和inserted临时表。触发器触发器 2022年3月23日星期三35q 触发器触发时q系统自动在内存中创建deleted表或inserted表q只读，不允许修改；触发器执行完成后，自动删除q inserted 表 q临时保存了插入或更新后的记录行 q可以从inserted表中检查插入的数据是否满足业务需求q 如果不满足，则向用户报告错误消息，并回滚插入操作q deleted 表q临时保存了删除或更新前的记录行 q可以从deleted表中检查被删除的数据是否满足业务