触发器的创建和使用优秀课件.ppt

触发器的创建和使用第1页，本讲稿共30页本章学习目标了解触发器和一般存储过程的主要区别了解触发器和一般存储过程的主要区别了解使用触发器的优点了解使用触发器的优点掌握创建触发器的方法掌握创建触发器的方法掌握查看触发器信息的方法掌握查看触发器信息的方法掌握修改和删除触发器的方法掌握修改和删除触发器的方法第2页，本讲稿共30页10.1 概述第3页，本讲稿共30页10.1.1 触发器的概念触触发发器器是是一一种种特特殊殊类类型型的的存存储储过过程程。一一般般的的存存储储过过程程通通过过存存储储过过程程名名称称被被直直接接调调用用，而而触触发发器器主主要要是是通通过过当当某某个个事事件件发发生生时时自自动动被被触触发发执执行行的的。触触发发器器可可以以用用于于SQL Server约约束束、默默认认值值和和规规则则的的完完整整性性检检查查，还还可可以以完完成难以用普通约束实现的复杂功能。成难以用普通约束实现的复杂功能。第4页，本讲稿共30页10.1.1 触发器的概念当当创创建建数数据据库库对对象象或或在在数数据据表表中中插插入入记记录录、修修改改记记录录或或者者删删除除记记录录时时，SQL Server就就会会自自动动执执行行触触发发器器所所定定义义的的SQL语语句句，从从而而确确保保对对数数据据的的处处理理必必须须符符合合由由这这些些SQL语语句句所所定定义义的的规规则则。触触发发器器和和引引起起触触发发器器执执行行的的SQL语语句句被被当当作作一一次次事事务务处处理理，如如果果这这次次事事务务未未获获得得成成功功，SQL Server会会自自动动返返回回该该事事务执行前的状态。务执行前的状态。第5页，本讲稿共30页10.1.2 触发器的优点使用触发器有以下优点：使用触发器有以下优点：触发器是自动的执行的。触发器是自动的执行的。触触发发器器可可以以通通过过数数据据库库中中的的相相关关表表进进行行层层叠叠更改。更改。触触发发器器可可以以强强制制限限制制。这这些些限限制制比比用用CHECK约束所定义的更复杂。约束所定义的更复杂。第6页，本讲稿共30页10.1.3 触发器的类型在在SQL Server 2005中中，触触发发器器可可以以分分为为DML触触发发器器和和DDL触触发发器器两两种种，它它们们的的主主要要区别如下：区别如下：DDL触触发发器器用用于于响响应应各各种种数数据据定定义义语语言言(DDL)事事件件。这这些些事事件件主主要要对对应应于于 Transact-SQL 中中的的CREATE、ALTER 和和 DROP 语语句句，以以及及执执行行类类似似 DDL 操操作作的的某某些些系系统统存存储储过过程程。它它们们用用于于执执行行管管理理任任务务，并并强强制制影影响响数数据据库库的业务规则。的业务规则。第7页，本讲稿共30页10.1.3 触发器的类型DML触触发发器器是是在在用用户户使使用用数数据据操操作作语语言言(DML)事事件件编编辑辑数数据据时时发发生生。DML 事事件件是是针针对对 表表 或或 视视 图图 的的 INSERT、UPDATE 或或 DELETE 语语句句。DML触触发发器器有有助助于于在在表表或或视视图图中中修修改改数数据据时时强强制制业业务务规规则则，扩扩展展数数据据完完整性。整性。第8页，本讲稿共30页10.1.3 触发器的类型DML触触发发器器又又分分为为AFTER触触发发器器和和INSTEAD OF触触发发器器两种：两种：AFTER触触 发发 器器：这这 种种 类类 型型 的的 触触 发发 器器 将将 在在 数数 据据 变变 动动（INSERT、UPDATE和和DELETE操操作作）完完成成以以后后才才被被触触发。发。AFTER触发器只能在表上定义。触发器只能在表上定义。INSTEAD OF触触发发器器：INSTEAD OF触触发发器器将将在在数数据据变变动动以以前前被被触触发发，并并取取代代变变动动数数据据的的操操作作，而而去去执执行行触触发发器器定定义义的的操操作作。INSTEAD OF触触发发器器可可以以在在表表或或视视图图上上定定义义。每每个个INSERT、UPDATE和和DELETE语语句句最最多多定定义义一一个个INSTEAD OF触发器。触发器。第9页，本讲稿共30页10.2 创建触发器在创建触发器之前应该考虑以下几个问题：在创建触发器之前应该考虑以下几个问题：CREATE TRIGGER 必必须须是是批批处处理理中中的的第第一一条语句。条语句。触发器只能在当前的数据库中创建。触发器只能在当前的数据库中创建。TRUNCATE TABLE语语句句不不会会引引发发DELETE触发器。触发器。WRITETEXT语语 句句 不不 会会 引引 发发 INSERT或或UPDATE触发器。触发器。第10页，本讲稿共30页10.2.1 使用SQL Server管理控制台创建DML触发器在在SQL Server管管理理控控制制台台中中，展展开开指指定定的的服服务务器器和和数数据据库库，单单击击要要创创建建触触发发器器的的数数据据表表，右右击击其其中中的的“触触发发器器”文文件件夹夹。从从弹弹出出的的快快捷捷菜菜单单中中选选择择“新新建建触触发发器器”选选项项，会会出出现现新建触发器模板窗口。新建触发器模板窗口。第11页，本讲稿共30页10.2.1 使用SQL Server管理控制台创建DML触发器当创建一个触发器时必须指定以下几项内容：当创建一个触发器时必须指定以下几项内容：触发器的名称；触发器的名称；在其上定义触发器的表；在其上定义触发器的表；触发器将何时激发；触发器将何时激发；执行触发操作的编程语句。执行触发操作的编程语句。第12页，本讲稿共30页10.2.1 使用SQL Server管理控制台创建DML触发器例例10-2-1：创建一个：创建一个INSERT触发器触发器“TR_Stu_Insert”，当在当在T_STUDENT表中表中插入一条新记录时，触发该触发器，并给出插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！你插入了一条新记录！”的提示信息。的提示信息。例例10-2-2：创建了：创建了Stu_Insert触发器后，查触发器后，查看向看向T_STUDENT表中插入数据时，此触发表中插入数据时，此触发器所完成的功能。器所完成的功能。第13页，本讲稿共30页10.2.2 使用Transact-SQL语句创建DML触发器使使用用Transact-SQL语语言言中中的的CREATE TRIGGER命命令令也也可以创建可以创建DML触发器：触发器：CREATE TRIGGER schema_name.trigger_name ON table|view WITH ,.n FOR|AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE AS sql_statement ;,.n :=ENCRYPTION EXECUTE AS Clause 第14页，本讲稿共30页10.2.2 使用Transact-SQL语句创建DML触发器其中，各参数的说明如下：其中，各参数的说明如下：schema_name：DML触发器所属架构的名称。触发器所属架构的名称。trigger_name：触发器的名称。触发器的名称。table|view：对其执行对其执行DML触发器的表或视图。触发器的表或视图。WITH ENCRYPTION：对对CREATE TRIGGER语语句句的的文本进行加密处理。文本进行加密处理。EXECUTE AS：指定用于执行该触发器的安全上下文。指定用于执行该触发器的安全上下文。第15页，本讲稿共30页10.2.2 使用Transact-SQL语句创建DML触发器FOR|AFTER：AFTER指指定定触触发发器器仅仅在在触触发发SQL语语句句中中指指定定的的所所有有操操作作都都已已成成功功执执行行时时才被触发。不能对视图定义才被触发。不能对视图定义AFTER触发器。触发器。INSTEAD OF：指指定定执执行行DML触触发发器器而而不不是是触发触发SQL语句。语句。DELETE,INSERT,UPDATE：指指定数据修改语句。必须至少指定一个选项。定数据修改语句。必须至少指定一个选项。sql_statement：触发条件和操作。触发条件和操作。第16页，本讲稿共30页10.2.2 使用Transact-SQL语句创建DML触发器DML触触发发器器可可以以使使用用两两个个特特殊殊的的临临时时表表，它它们们分分别别是是inserted表表和和deleted表表。这这两两个个表表都都存存在在于于内内存存中中。它它们们在在结结构构上上类类似似于于定定义义了触发器的表。了触发器的表。在在inserted表表中中存存储储着着被被INSERT和和UPDATE语语句句影影响响的的新新的的数数据据行行。在在执执行行INSERT或或UPDATE语语句句时时，新新的的数数据据行行被被添添加加到到基基本本表表中中，同同时时这这些些数数据据行行的的备备份份被被复复制制到到inserted临时表中。临时表中。第17页，本讲稿共30页10.2.2 使用Transact-SQL语句创建DML触发器在在deleted表表中中存存储储着着被被DELETE和和UPDATE语语句句影影响响的的旧旧数数据据行行。在在执执行行DELETE或或UPDATE语语句句时时，指指定定的的数数据据行行从从基基本本表表中中删删除除，然然后后被被转转移移到到了了deleted表表中中。在基本表和在基本表和deleted表中一般不会存在相同的数据行。表中一般不会存在相同的数据行。一一个个UPDATE操操作作实实际际上上是是由由一一个个DELETE操操作作和和一一个个INSERT操操作作组组成成的的。在在执执行行UPDATE操操作作时时，旧旧的的数数据据行行从从基基本本表表中中转转移移到到deleted表表中中，然然后后将将新新的的数数据据行行同时插入基本表和同时插入基本表和inserted表中。表中。第18页，本讲稿共30页10.2.2 使用Transact-SQL语句创建DML触发器例例10-2-3：创创建建一一个个AFTER触触发发器器，要要求求实实现现以以下下功功能能：在在T_SCORE表表上上创创建建一一个个插插入入、更更新新类类型型的的触触发发器器TR_ScoreCheck，当当在在SCORE字字段段中中插插入入或或修修改改考考试分数后，触发该触发器，检查分数是否在试分数后，触发该触发器，检查分数是否在0-100之间。之间。例例10-2-4：创创建建一一个个AFTER触触发发器器，要要求求如如下下：在在T_STUDENT表表 上上 创创 建建 一一 个个 删删 除除 类类 型型 的的 触触 发发 器器TR_Stu_Delete，当当在在T_STUDENT表表中中删删除除某某一一条条记记录录后后，触触发发该该触触发发器器，在在T_SCORE表表中中删删除除与与此此学学号号对对应的记录。应的记录。第19页，本讲稿共30页10.2.2 使用Transact-SQL语句创建DML触发器例例10-2-5：创创建建一一个个INSTEAD OF触触发发器器，要要求求实实现现以以下下功功能能：在在T_COURSE表表上上创创建建一一个个删删除除类类型型的的触触发发器器TR_NotAllowDelete，当当在在T_COURSE表表中中删删除除记记录录时时，触触发发该该触触发发器器，显显示示不不允允许许删删除除表表中中数数据据的的提提示示信信息。息。第20页，本讲稿共30页10.3 查看、修改和删除触发器第21页，本讲稿共30页10.3.1 查看并修改触发器1使用使用SQL Server管理控制台查看并修改触发器管理控制台查看并修改触发器（1）查看并修改触发器定义信息）查看并修改触发器定义信息在在SQL Server管管理理控控制制台台中中，展展开开指指定定的的服服务务器器和和数数据据库库，选选择择指指定定的的数数据据库库和和表表，单单击击要要查查看看的的表表，单单击击其其中中的的“触触发发器器”文文件件夹夹，此此时时会会在在右右侧侧的的“摘摘要要”窗窗口口中中看看到到此此表表中中的的所所有有触触发发器器，右右击击某某个个触触发发器器名名称称，从从弹弹出出的的快快捷捷菜菜单单中中选选择择“修修改改”选选项项，在在打打开开的的窗窗口口中中可可以以查查看看到到定定义义触触发发器器的的语语句句，在在窗窗口口中中也也可可以以直直接接修改触发器的定义。修改触发器的定义。第22页，本讲稿共30页10.3.1 查看并修改触发器（2）查查看看与与触触发发器器有有依依赖赖关关系系的的其其他他数数据据库库对象对象右右击击某某个个触触发发器器名名称称，从从弹弹出出的的快快捷捷菜菜单单中中选选择择“查查看看依依赖赖关关系系”选选项项，在在出出现现的的“对对象象依依赖赖关关系系”对对话话框框中中可可以以查查看看到到依依赖赖于于此此触发器的对象和此触发器依赖的对象。触发器的对象和此触发器依赖的对象。第23页，本讲稿共30页10.3.1 查看并修改触发器2使用系统存储过程查看触发器使用系统存储过程查看触发器可可 以以 使使 用用 系系 统统 存存 储储 过过 程程 sp_help、sp_helptext和和sp_depends分分别别查查看看触触发发器器的的不不同同信信息息。它它们们的的具具体体用用途和语法形式如下：途和语法形式如下：sp_help：用用于于查查看看触触发发器器的的一一般般信信息息，如如触触发发器器的的名称、属性、类型和创建时间。名称、属性、类型和创建时间。sp_help 触发器名称触发器名称sp_helptext：用于查看触发器的正文信息。用于查看触发器的正文信息。sp_helptext 触发器名称触发器名称第24页，本讲稿共30页10.3.1 查看并修改触发器sp_depends：用用于于查查看看指指定定触触发发器器所所引引用用的表。的表。sp_depends 触发器名称触发器名称 例例10-3-1：使用系统存储过程查看：使用系统存储过程查看TR_Stu_Insert触发器的一般信息。触发器的一般信息。例例10-3-2：使用系统存储过程查看：使用系统存储过程查看T_STUDENT表涉及到的所有触发器。表涉及到的所有触发器。第25页，本讲稿共30页10.3.2 删除触发器1使用使用SQL Server管理控制台删除触发器管理控制台删除触发器在在SQL Server管管理理控控制制台台中中，展展开开指指定定的的服服务务器器和和数数据据库库，选选择择指指定定的的数数据据库库和和表表，单单击击要要查查看看的的表表，单单击击其其中中的的“触触发发器器”文文件件夹夹，此此时时会会在在右右侧侧的的“摘摘要要”窗窗口口中中看看到到此此表表中中的的所所有有触触发发器器，右右击击某某个个触触发发器器名名称称，从从弹弹出出的的快快捷捷菜菜单单中中选选择择“删删除除”选选项项，会会弹弹出出“删删除除对对象象”对对话话框框，单单击击“确确定定”按按钮，即可删除该触发器。钮，即可删除该触发器。第26页，本讲稿共30页10.3.2 删除触发器2使用使用Transact-SQL语句删除触发器语句删除触发器使使用用系系统统命命令令DROP TRIGGER删删除除指指定定的的触触发器。其语法形式如下：发器。其语法形式如下：DROP TRIGGER trigger ,.n 例例10-3-3：使使用用系系统统命命令令删删除除TR_Stu_Insert触发器。触发器。第27页，本讲稿共30页10.3.2 删除触发器3直接删除触发器所在的表直接删除触发器所在的表删删除除触触发发器器的的另另一一个个方方法法是是直直接接删删除除触触发发器器所所在在的的表表。删删除除表表时时，SQL Server将将会会自自动动删除与该表相关的所有触发器。删除与该表相关的所有触发器。第28页，本讲稿共30页10.4 思考与练习1触触发发器器与与一一般般的的存存储储过过程程的的主主要要区区别别是是什什么？么？2简述触发器的分类及各自的特点。简述触发器的分类及各自的特点。3使用触发器有哪些优点？使用触发器有哪些优点？第29页，本讲稿共30页10.4 思考与练习4使使用用Transact-SQL语语句句创创建建一一个个INSERT触触发发器器，完完成成的的功功能能是是当当在在T_SCORE表表中中插插入入或或修修改改S_NUMBER列列的的数数据据时时，检检测测在在T_STUDENT表表 中中 是是 否否 存存 在在 相相 应应 的的S_NUMBER值值，如如果果不不存存在在，则则给给出出相相应应的的提示信息。提示信息。5删删除除触触发发器器的的方方法法有有哪哪几几种种？并并分分别别举举例例说明。说明。第30页，本讲稿共30页