数据库原理与应用：第10章 触发器的创建和使用

1、数据库原理与应用数据库原理与应用第十讲第十讲 触发器创建和使用触发器创建和使用1、创建触发器、创建触发器2 、查看、修改和删除触发器、查看、修改和删除触发器3 、触发器的应用、触发器的应用 本讲主要内容触发器触发器是一种特殊类型的存储过程，它不同于前触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而发而被执行的，而存储过程可以通过存储过程名称而被直接调用。触发器是一个功能强大的工具，它使每被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制

2、执行其业务规则。个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于触发器可以用于 SQL Server 约束、默认值和规则的约束、默认值和规则的完整性检查。完整性检查。SQL SERVER可以检查数据完整性的还有什么情况可以检查数据完整性的还有什么情况?比如前面讲过的比如前面讲过的CHECK约束。但是，与约束。但是，与CHECK约束相比，约束相比，触发器可以强制实现更加复杂的数据完整性，而且可以参考其他触发器可以强制实现更加复杂的数据完整性，而且可以参考其他表的字段。表的字段。注意注意:触发器可以扩展触发器可以扩展SQL Server约束、默认值和规则的约束、默认值和规则的完整性检

3、查逻辑，但只要约束和默认值提供了全部所需的功能，完整性检查逻辑，但只要约束和默认值提供了全部所需的功能，就应使用约束和默认值。就应使用约束和默认值。触发器可以使用触发器可以使用T一一SQL语句进行复杂的逻辑处理，语句进行复杂的逻辑处理，它基于一个表创建，但是可以对多个表进行操作，因此常它基于一个表创建，但是可以对多个表进行操作，因此常常用于复杂的业务规则。工般可以使用触发器完成如下功常用于复杂的业务规则。工般可以使用触发器完成如下功能能触发器的作用1、级联修改数据库中相关的表。、级联修改数据库中相关的表。例如例如:在数据库在数据库company中有两个表中有两个表project和和custom

4、er,表表project中包含项目信息和项目的合作客户名，而中包含项目信息和项目的合作客户名，而customer表中则表中则存储所有客户的信息。如果删除了存储所有客户的信息。如果删除了customer表中的某一客户，如表中的某一客户，如张三，那么在张三，那么在project表中所有和该客户有关的项目记录都应该做表中所有和该客户有关的项目记录都应该做相应的调整。如在相应的调整。如在project表中将所有合作客户为张三的字段改为表中将所有合作客户为张三的字段改为null或者其它值，或者删除整条和张三有关的记录。而使用触发器或者其它值，或者删除整条和张三有关的记录。而使用触发器就可以实现这样的功能

5、。就可以实现这样的功能。2、执行比核查约束更为复杂的约束操作。、执行比核查约束更为复杂的约束操作。 在触发器中可以书写更复杂的在触发器中可以书写更复杂的T一一SQL语句，例如可以引语句，例如可以引用多个表，并使用用多个表，并使用if . else等语句做更复杂的检查。等语句做更复杂的检查。3、拒绝或回滚违反引用完整性的操作。、拒绝或回滚违反引用完整性的操作。 检查对数据表的操作是否违反引用完整性，并选择相应的操检查对数据表的操作是否违反引用完整性，并选择相应的操作。作。4、比较表修改前后数据之间的差别，并根据差别采取相应、比较表修改前后数据之间的差别，并根据差别采取相应的操作。例如的操作。例如

6、:若想规定每次工资的变动幅度不能超过若想规定每次工资的变动幅度不能超过40%，使用，使用触发器可以将修改后的表数据和修改前的表数据进行比较，若超触发器可以将修改后的表数据和修改前的表数据进行比较，若超出出40%，可以回滚该修改操作。，可以回滚该修改操作。 触发器的类型触发器可以分为五种类型触发器可以分为五种类型:Update触发器触发器:在表上进行更新操作时触发。在表上进行更新操作时触发。Insert触发器触发器:在表上进行更新操作时触发在表上进行更新操作时触发Delete触发器触发器:在表上进行更新操作时触发在表上进行更新操作时触发Instead of触发器触发器:不执行插入、更新或删除操作

7、时，将触发不执行插入、更新或删除操作时，将触发Instead of触发器。触发器。After触发器触发器:在一个触发动作发生之后激发，并提供一种机制以便在一个触发动作发生之后激发，并提供一种机制以便控制多个触发器的执行顺序。控制多个触发器的执行顺序。AFTER要求只有执行某一操作要求只有执行某一操作(INSERT,UPDATE、DELETE)之后触之后触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于发器。对于AFTER触发器，可以定义哪一个触发器被最先触发，哪一个被触发器，可以定义哪一个触发器被最先触发

8、，哪一个被最后触发，通常使用系统过程最后触发，通常使用系统过程sp settriggerorder来完成此任务。来完成此任务。INSTEAD OF触发器表示并不执行其所定义的操作触发器表示并不执行其所定义的操作(INSERT、UPDATE、DELETE)，而仅是执行触发器本身。既，而仅是执行触发器本身。既可在表上定义可在表上定义INSTEAD OF触发器，也可以在视图上定义触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个触发器，但对同一操作只能定义一个INSTEAD OF触发器。触发器。 触发器主要优点如下：触发器是自动的：当对表中的数据作了任何修改（比如手触发器

9、是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。工输入或者应用程序采取的操作）之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。触发器可以通过数据库中的相关表进行层叠更改。 触发器可以强制限制，这些限制比用触发器可以强制限制，这些限制比用 CHECK 约束所定义约束所定义的更复杂。的更复杂。 创建触发器 创建触发器应该考虑以下几个问题：创建触发器应该考虑以下几个问题：CREATE TRIGGER 语句必须是批处理中的第一个语句。语句必须是批处理中的第一个语句。创建触发器的权限默认分配给表的所有者，且不能将该权限转给其创建触发器的权限默认分配

10、给表的所有者，且不能将该权限转给其他用户。他用户。 触发器为数据库对象，其名称必须遵循标识符的命名规则。触发器为数据库对象，其名称必须遵循标识符的命名规则。 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。中创建触发器。 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。时表。 在含有用在含有用 DELETE 或或 UPDATE 操作定义的外键的表中，不能定义操作定义的外键的表中，不能定义 INSTEAD OF 和和 INSTEAD OF U

11、PDATE 触发器。触发器。虽然虽然 TRUNCATE TABLE 语句类似于没有语句类似于没有 WHERE 子句（用于删子句（用于删除行）的除行）的 DELETE 语句，但它并不会引发语句，但它并不会引发 DELETE 触发器，因为触发器，因为 TRUNCATE TABLE 语句没有记录。语句没有记录。WRITETEXT 语句不会引发语句不会引发 INSERT 或或 UPDATE 触发器。触发器。 当创建一个触发器时必须指定：当创建一个触发器时必须指定： 名称；在其上定义触发器的表；名称；在其上定义触发器的表；触发器将何时激发；激活触发器的数据修改语句。触发器将何时激发；激活触发器的数据修改

12、语句。 下面的语句不可以用于创建触发器下面的语句不可以用于创建触发器:Alter Database、Create Database、Disk Init、Disk Resize、Drop Database、Load Database、Load Log、Reconfigure、RestoreDatabase、Restore Log.当创建一个触发器时必须指定当创建一个触发器时必须指定:(1)名称名称;(2)在其上定义触发在其上定义触发器的表器的表;(3)触发器将何时激发触发器将何时激发;(4)激活触发器的数据修改语句。激活触发器的数据修改语句。1. 使用企业管理器创建触发器在企业管理器中，展开指定

13、的服务器和数据库项，然后展在企业管理器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，用右键单击该表，从开要在其上创建触发器的表所在的数据库，用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在该对话框中，在名称文本框中选择会出现触发器属性对话框。在该对话框中，在名称文本框中选择新建，然后在文本框中输入触发器文本。单击新建，然后在文本框中输入触发器文本。单击“检查语法检查语法”按钮，按钮，则可以检查语法是否正确。单击则可以检查语法是否正确。单击“应用应用”按钮，则

14、在名称下拉列按钮，则在名称下拉列表中会有新创建的触发器名称。最后，单击表中会有新创建的触发器名称。最后，单击“确定确定”按钮，即可按钮，即可关闭该对话框，成功创建触发器。关闭该对话框，成功创建触发器。2. 使用CREATE TRIGGER命令创建触发器其语法形式如下：其语法形式如下：CREATE TRIGGER trigger_name ONtable|view WITHENCRYPTION FOR | AFTER | INSTEAD OF DELETE, INSERT , UPDATE WITHAPPEND NOTFORREPLICATION AS IFUPDATE(column) AND|

15、ORUPDATE(column) .n |IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask) comparison_operatorcolumn_bitmask.n sql_statement.n 各参数的说明如下:Trigger_ame:是用户要创建的触发器的名字。触发器的名字必须符合是用户要创建的触发器的名字。触发器的名字必须符合MS SQL Server的命名规则，且其名字在当前数据库中必须是惟一的的命名规则，且其名字在当前数据库中必须是惟一的.Table:是与用户创建的触发器相关联的表的名字，并且该表己经存在。是与用户创建的触发器相

16、关联的表的名字，并且该表己经存在。WITH ENCRYPTION:表示对包含有表示对包含有CREATE TRIGGER文本的文本的syscomments表进行加密表进行加密DELETE，INSERT，UPDATE:关键字用来指明哪种数据操作将关键字用来指明哪种数据操作将激活触发器，至少要指明一个选项，在触发器的定义中三者的顺序不受限制，激活触发器，至少要指明一个选项，在触发器的定义中三者的顺序不受限制，且各选项要用逗号隔开。且各选项要用逗号隔开。AFTER:表示只有在执行了指定的操作表示只有在执行了指定的操作INSERT, DELETE, UPDATE之后触发器才被激活，执行触发器中的之后触发

17、器才被激活，执行触发器中的SQL语句。若使用关键语句。若使用关键字字FOR，则表示为，则表示为AFTER触发器，且该类型触发器仅能在表上创建。触发器，且该类型触发器仅能在表上创建。INSTEAD OF:当为表或视图定义了针对某一操作当为表或视图定义了针对某一操作INSERT、DELETE、UPDATE的的INSTEAD OF类型触发器，且执行了相应的操作类型触发器，且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发器器SQL语句本身。语句本身。例子例子Use company Go-创建触发器创建触发器C

18、reate trigger 触发器触发器-欢迎新员工欢迎新员工On employee After insertAs Print 新员工加入本公司，欢迎欢迎新员工加入本公司，欢迎欢迎! go例： 创建了一个触发器，在创建了一个触发器，在 titles 表上创建一个插入、更新类型的表上创建一个插入、更新类型的触发器，其程序清单如下：触发器，其程序清单如下：USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER r

19、eminderON titlesFOR INSERT, UPDATE AS sql_statementsGO查看、修改和删除触发器查看、修改和删除触发器1. 查看触发器查看触发器（）使用企业管理器查看触发器信息（）使用企业管理器查看触发器信息（）使用系统存储过程查看触发器（）使用系统存储过程查看触发器sp_help、sp_helptext和sp_depends具体用途和语法形式如下。 sp_help：用于查看触发器的一般信息，如触发：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。器的名称、属性、类型和创建时间。sp_help 触发器名称触发器名称 sp_helptext：用于

20、查看触发器的正文信息：用于查看触发器的正文信息sp_helptext 触发器名称触发器名称 sp_depends：用于查看指定触发器所引用的表：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。或者指定的表涉及到的所有触发器。sp_depends 触发器名称触发器名称 sp_depends 表名表名 修改触发器 （）使用企业管理器修改触发器正文（）使用企业管理器修改触发器正文在企业管理器中，展开指定的服务器和数在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，用右键单击要修据库，选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择所有任务子菜改的表，从弹出

21、的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要修改的触发器的名话框。在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的称，然后在文本框中修改触发器的SQL语句，单语句，单击击“检查语法检查语法”按钮，可以检查语法是否正确。按钮，可以检查语法是否正确。 （）使用sp_rename命令修改触发器的名称 sp_rename命令的语法形式如下：命令的语法形式如下：sp_rename oldname,newname（）使用alter trigger命令修改触发器正文alter trigger命令的

22、语法形式如下：命令的语法形式如下：ALTERTRIGGERtrigger_name ON(table|view) WITHENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOTFORREPLICATION AS sql_statement.n | (FOR|AFTER|INSTEADOF)INSERT,UPDATE NOTFORREPLICATION AS IFUPDATE(column) AND|ORUPDATE(column) .n | IF ( COLUMNS_UPDATED ( ) bitwise_ope

23、rator updated_bitmask ) comparison_operatorcolumn_bitmask.n sql_statement.n 删除触发器（）使用系统命令（）使用系统命令DROP TRIGGER删除指定的触删除指定的触发器，其语法形式如下：发器，其语法形式如下：DROP TRIGGER trigger ,.n （）删除触发器所在的表时，（）删除触发器所在的表时，SQL Server将会自动将会自动删除与该表相关的触发器。删除与该表相关的触发器。（）在企业管理器中，用右键单击要删除的触发器（）在企业管理器中，用右键单击要删除的触发器所在的表，从弹出的快捷菜单中选择所有任务

24、子菜单下的所在的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要删除的触发器，单击项框中选择要删除的触发器，单击“删除删除”按钮，即可删按钮，即可删除该触发器。除该触发器。触发器的应用 1. 使用使用INSERT触发器触发器INSERT触发器通常被用来更新时间标记字段，或者触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中的数据满足要求的标准，以验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。确保数据完整性。2. 使用使用UPDATE触发器触发器修改触发器和插

25、入触发器的工作过程基本上一致，修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧修改一条记录等于插入了一条新的记录并且删除一条旧的记录。的记录。3.使用DELETE触发器DELETE触发器通常用于两种情况，第一种情况触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。性问题的记录的删除。第二种情况是执行可删除主记录的子记录的级联第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。中删除所有的定单项。4. 使用嵌套的触发器 如果一个触发器在执行操作时引发了另一个触发如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器器，而这个触发器又接着引发下一个触发器这些触发器就是嵌套触发器。触发器可嵌套至这些触发器就是嵌套触发器。触发器可嵌套至 32 层，并且可以控制是否可以通过层，并且可以控制是否可以通过嵌套触发器嵌套触发器服务器配置选项进行触发器嵌套。如果允许使服务器配置选项进行触发器嵌套。如果允许使用嵌套触发器，且链中的一个触发器开始一个无用嵌套触发器，且链中的一个触发器开始一