数据库原理与应用-10.触发器的创建和使用ppt课件

1、 第十讲第十讲 触发器创建和运用触发器创建和运用1、创建触发器、创建触发器2 、查看、修正和删除触发器、查看、修正和删除触发器3 、触发器的运用、触发器的运用 本讲主要内容触发器触发器是一种特殊类型的存储过程，它不同于前触发器是一种特殊类型的存储过程，它不同于前面引见过的存储过程。触发器主要是经过事件进展触面引见过的存储过程。触发器主要是经过事件进展触发而被执行的，而存储过程可以经过存储过程称号而发而被执行的，而存储过程可以经过存储过程称号而被直接调用。触发器是一个功能强大的工具，它使每被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修正时自动强迫执行其业务规那个站点可以在有数

2、据修正时自动强迫执行其业务规那么。触发器可以用于么。触发器可以用于 SQL Server 约束、默许值和规约束、默许值和规那么的完好性检查。那么的完好性检查。SQL SERVER可以检查数据完好性的还有什么情况可以检查数据完好性的还有什么情况?比如前面讲过的比如前面讲过的CHECK约束。但是，与约束。但是，与CHECK约束相比，约束相比，触发器可以强迫实现更加复杂的数据完好性，而且可以参考其他触发器可以强迫实现更加复杂的数据完好性，而且可以参考其他表的字段。表的字段。留意留意:触发器可以扩展触发器可以扩展SQL Server约束、默许值和规那么约束、默许值和规那么的完好性检查逻辑，但只需约束和

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

4、oject中包含工程信息和工程的协作客户名，而中包含工程信息和工程的协作客户名，而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 UPDA

11、TE 触发器。触发器。虽然虽然 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|ORUPD

15、ATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_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-创建触发器创建触发器Creat

18、e 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 remin

19、derON 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命令的语法方式如下：命令的语法方式如下：ALTE

22、RTRIGGERtrigger_name ON(table|view) WITHENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOTFORREPLICATION AS sql_statement.n | (FOR|AFTER|INSTEADOF)INSERT,UPDATENOTFORREPLICATIONASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_o

23、peratorcolumn_bitmask.nsql_statement.n删除触发器运用系统命令运用系统命令DROP TRIGGER删除指定的触删除指定的触发器，其语法方式如下：发器，其语法方式如下：DROP TRIGGER trigger ,.n 删除触发器所在的表时，删除触发器所在的表时，SQL Server将会自动将会自动删除与该表相关的触发器。删除与该表相关的触发器。在企业管理器中，用右键单击要删除的触发器在企业管理器中，用右键单击要删除的触发器所在的表，从弹出的快捷菜单中选择一切义务子菜单下的所在的表，从弹出的快捷菜单中选择一切义务子菜单下的管理触发器选项，那么会出现触发器属性对话

24、框。在称号管理触发器选项，那么会出现触发器属性对话框。在称号选项框中选择要删除的触发器，单击选项框中选择要删除的触发器，单击“删除按钮，即可删除按钮，即可删除该触发器。删除该触发器。触发器的运用 1. 运用运用INSERT触发器触发器INSERT触发器通常被用来更新时间标志字段，或者触发器通常被用来更新时间标志字段，或者验证被触发器监控的字段中的数据满足要求的规范，以验证被触发器监控的字段中的数据满足要求的规范，以确保数据完好性。确保数据完好性。2. 运用运用UPDATE触发器触发器修正触发器和插入触发器的任务过程根本上一致，修正触发器和插入触发器的任务过程根本上一致，修正一条记录等于插入了一

25、条新的记录并且删除一条旧修正一条记录等于插入了一条新的记录并且删除一条旧的记录。的记录。3.运用DELETE触发器DELETE触发器通常用于两种情况，第一种情况触发器通常用于两种情况，第一种情况是为了防止那些确实需求删除但会引起数据一致是为了防止那些确实需求删除但会引起数据一致性问题的记录的删除。性问题的记录的删除。第二种情况是执行可删除主记录的子记录的级联第二种情况是执行可删除主记录的子记录的级联删除操作。可以运用这样的触发器从主销售记录删除操作。可以运用这样的触发器从主销售记录中删除一切的定单项。中删除一切的定单项。4. 运用嵌套的触发器 假设一个触发器在执行操作时引发了另一个触发假设一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器器，而这个触发器又接着引发下一个触发器这些触发器就是嵌套触发器。触发器可嵌套至这些触发器就是嵌套触发器。触发器可嵌套至 32 层，并且可以控制能否可以经过层，并且可以控制能否可以经过嵌套触发器嵌套触发器效力器配置选项进展触发器嵌套。假设允许运效力器配置选项进展触发器嵌套。假设允许运用嵌套触发器，且链中的一个触发器开场一个无用嵌套触发器，且链中的一个触发器开场一个无限循环，那