《《数据库原理与应用》10.触发器的创建和使用.ppt》由会员分享，可在线阅读，更多相关《《数据库原理与应用》10.触发器的创建和使用.ppt（34页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、孙孙孙孙 发发发发 勤勤勤勤 扬州大学新闻与传媒学院扬州大学新闻与传媒学院扬州大学新闻与传媒学院扬州大学新闻与传媒学院YY数据库原理与应用数据库原理与应用第十讲第十讲 触发器创建和使用触发器创建和使用1、创建触发器、创建触发器2、查看、修改和删除触发器、查看、修改和删除触发器3、触发器的应用、触发器的应用 本讲主要内容触发器触发器是一种特殊类型的存储过程，它不同于前触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而发而被执行的，而存储过程可以通过存储过程名称而被直接
2、调用。触发器是一个功能强大的工具，它使每被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于触发器可以用于SQLServer约束、默认值和规则的约束、默认值和规则的完整性检查。完整性检查。SQLSERVER可以检查数据完整性的还有什么情况可以检查数据完整性的还有什么情况?比如前面讲过的比如前面讲过的CHECK约束。但是，与约束。但是，与CHECK约束相比，约束相比，触发器可以强制实现更加复杂的数据完整性，而且可以参考其他触发器可以强制实现更加复杂的数据完整性，而且可以参考其他表的字段。表的字
3、段。注意注意:触发器可以扩展触发器可以扩展SQLServer约束、默认值和规则的约束、默认值和规则的完整性检查逻辑，但只要约束和默认值提供了全部所需的功能，完整性检查逻辑，但只要约束和默认值提供了全部所需的功能，就应使用约束和默认值。就应使用约束和默认值。触发器可以使用触发器可以使用T一一SQL语句进行复杂的逻辑处理，语句进行复杂的逻辑处理，它基于一个表创建，但是可以对多个表进行操作，因此常它基于一个表创建，但是可以对多个表进行操作，因此常常用于复杂的业务规则。工般可以使用触发器完成如下功常用于复杂的业务规则。工般可以使用触发器完成如下功能能触发器的作用1、级联修改数据库中相关的表。、级联修改
4、数据库中相关的表。例如例如:在数据库在数据库company中有两个表中有两个表project和和customer,表表project中包含项目信息和项目的合作客户名，而中包含项目信息和项目的合作客户名，而customer表中则表中则存储所有客户的信息。如果删除了存储所有客户的信息。如果删除了customer表中的某一客户，如表中的某一客户，如张三，那么在张三，那么在project表中所有和该客户有关的项目记录都应该做表中所有和该客户有关的项目记录都应该做相应的调整。如在相应的调整。如在project表中将所有合作客户为张三的字段改为表中将所有合作客户为张三的字段改为null或者其它值，或者删除
5、整条和张三有关的记录。而使用触发器或者其它值，或者删除整条和张三有关的记录。而使用触发器就可以实现这样的功能。就可以实现这样的功能。2、执行比核查约束更为复杂的约束操作。、执行比核查约束更为复杂的约束操作。在触发器中可以书写更复杂的在触发器中可以书写更复杂的T一一SQL语句，例如可以引用语句，例如可以引用多个表，并使用多个表，并使用if.else等语句做更复杂的检查。等语句做更复杂的检查。3、拒绝或回滚违反引用完整性的操作。、拒绝或回滚违反引用完整性的操作。检查对数据表的操作是否违反引用完整性，并选择相应的操检查对数据表的操作是否违反引用完整性，并选择相应的操作。作。4、比较表修改前后数据之间
6、的差别，并根据差别采取相应、比较表修改前后数据之间的差别，并根据差别采取相应的操作。例如的操作。例如:若想规定每次工资的变动幅度不能超过若想规定每次工资的变动幅度不能超过40%，使用，使用触发器可以将修改后的表数据和修改前的表数据进行比较，若超触发器可以将修改后的表数据和修改前的表数据进行比较，若超出出40%，可以回滚该修改操作。，可以回滚该修改操作。触发器的类型触发器可以分为五种类型触发器可以分为五种类型:Update触发器触发器:在表上进行更新操作时触发。在表上进行更新操作时触发。Insert触发器触发器:在表上进行更新操作时触发在表上进行更新操作时触发Delete触发器触发器:在表上进行
7、更新操作时触发在表上进行更新操作时触发Insteadof触发器触发器:不执行插入、更新或删除操作时，将触发不执行插入、更新或删除操作时，将触发Insteadof触发器。触发器。After触发器触发器:在一个触发动作发生之后激发，并提供一种机制以便在一个触发动作发生之后激发，并提供一种机制以便控制多个触发器的执行顺序。控制多个触发器的执行顺序。AFTER要求只有执行某一操作要求只有执行某一操作(INSERT,UPDATE、DELETE)之后触发之后触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于器。对于A
8、FTER触发器，可以定义哪一个触发器被最先触发，哪一个被最触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程后触发，通常使用系统过程spsettriggerorder来完成此任务。来完成此任务。INSTEADOF触发器表示并不执行其所定义的操作触发器表示并不执行其所定义的操作(INSERT、UPDATE、DELETE)，而仅是执行触发器本身。既可，而仅是执行触发器本身。既可在表上定义在表上定义INSTEADOF触发器，也可以在视图上定义触发器，也可以在视图上定义INSTEADOF触发器，但对同一操作只能定义一个触发器，但对同一操作只能定义一个INSTEADOF触发器。触
9、发器。触发器主要优点如下：触发器是自动的：当对表中的数据作了任何修改（比如手触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。工输入或者应用程序采取的操作）之后立即被激活。触发器可以通过数据库中的相关表进行层叠更改。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制，这些限制比用触发器可以强制限制，这些限制比用CHECK约束所定义约束所定义的更复杂。的更复杂。创建触发器 创建触发器应该考虑以下几个问题：创建触发器应该考虑以下几个问题：CREATETRIGGER语句必须是批处理中的第一个语句。语句必须是批处理中的第一个语句。创建触发器的权
10、限默认分配给表的所有者，且不能将该权限转给其创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。他用户。触发器为数据库对象，其名称必须遵循标识符的命名规则。触发器为数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。中创建触发器。虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。时表。在在含含有有用用DELETE或或UPDATE操操作作定定义义的的外外键键的的表表中中，不不能能定定义义IN
11、STEADOF和和INSTEADOFUPDATE触发器。触发器。虽虽然然TRUNCATETABLE语语句句类类似似于于没没有有WHERE子子句句（用用于于删删除除行行）的的DELETE语语句句，但但它它并并不不会会引引发发DELETE触触发发器器，因因为为TRUNCATETABLE语句没有记录。语句没有记录。WRITETEXT语句不会引发语句不会引发INSERT或或UPDATE触发器。触发器。当当创创建建一一个个触触发发器器时时必必须须指指定定：名名称称；在在其其上上定定义义触触发发器器的的表表；触发器将何时激发；触发器将何时激发；激活触发器的数据修改语句。激活触发器的数据修改语句。下面的语句
12、不可以用于创建触发器下面的语句不可以用于创建触发器:AlterDatabase、CreateDatabase、DiskInit、DiskResize、DropDatabase、LoadDatabase、LoadLog、Reconfigure、RestoreDatabase、RestoreLog.当创建一个触发器时必须指定当创建一个触发器时必须指定:(1)名称名称;(2)在其上定义触在其上定义触发器的表发器的表;(3)触发器将何时激发触发器将何时激发;(4)激活触发器的数据修改语句。激活触发器的数据修改语句。1.使用企业管理器创建触发器在企业管理器中，展开指定的服务器和数据库项，然后展在企业管理
13、器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，用右键单击该表，从开要在其上创建触发器的表所在的数据库，用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在该对话框中，在名称文本框中选择会出现触发器属性对话框。在该对话框中，在名称文本框中选择新建，然后在文本框中输入触发器文本。单击新建，然后在文本框中输入触发器文本。单击“检查语法检查语法”按钮，按钮，则可以检查语法是否正确。单击则可以检查语法是否正确。单击“应用应用”按钮，则在名称下拉列按钮，则在名称下拉列表中
14、会有新创建的触发器名称。最后，单击表中会有新创建的触发器名称。最后，单击“确定确定”按钮，即可按钮，即可关闭该对话框，成功创建触发器。关闭该对话框，成功创建触发器。2.使用CREATE TRIGGER命令创建触发器其语法形式如下：其语法形式如下：CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEWITHAPPENDNOTFORREPLICATIONASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED(
15、)bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n各参数的说明如下:Trigger_ame:是用户要创建的触发器的名字。触发器的名字必须符合是用户要创建的触发器的名字。触发器的名字必须符合MSSQLServer的命名规则，且其名字在当前数据库中必须是惟一的的命名规则，且其名字在当前数据库中必须是惟一的.Table:是与用户创建的触发器相关联的表的名字，并且该表己经存在。是与用户创建的触发器相关联的表的名字，并且该表己经存在。WITHENCRYPTION:表示对包含有表示对包含有C
16、REATETRIGGER文本的文本的syscomments表进行加密表进行加密DELETE，INSERT，UPDATE:关键字用来指明哪种数据操作将激关键字用来指明哪种数据操作将激活触发器，至少要指明一个选项，在触发器的定义中三者的顺序不受限制，且活触发器，至少要指明一个选项，在触发器的定义中三者的顺序不受限制，且各选项要用逗号隔开。各选项要用逗号隔开。AFTER:表示只有在执行了指定的操作表示只有在执行了指定的操作INSERT,DELETE,UPDATE之后触发器才被激活，执行触发器中的之后触发器才被激活，执行触发器中的SQL语句。若使用关键语句。若使用关键字字FOR，则表示为，则表示为AF
17、TER触发器，且该类型触发器仅能在表上创建。触发器，且该类型触发器仅能在表上创建。INSTEADOF:当为表或视图定义了针对某一操作当为表或视图定义了针对某一操作INSERT、DELETE、UPDATE的的INSTEADOF类型触发器，且执行了相应的操作类型触发器，且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发器器SQL语句本身。语句本身。例子例子UsecompanyGo-创建触发器创建触发器Createtrigger触发器触发器-欢迎新员工欢迎新员工OnemployeeAfterinsertAsP
18、rint新员工加入本公司，欢迎欢迎新员工加入本公司，欢迎欢迎!go例：创创建建了了一一个个触触发发器器，在在titles表表上上创创建建一一个个插插入入、更更新新类类型型的的触发器，其程序清单如下：触发器，其程序清单如下：USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=reminderANDtype=TR)DROPTRIGGERreminderGOCREATETRIGGERreminderONtitlesFORINSERT,UPDATEASsql_statementsGO查看、修改和删除触发器查看、修改和删除触发器1.查看触发器查看触发器（）
19、使用企业管理器查看触发器信息（）使用企业管理器查看触发器信息（）使用系统存储过程查看触发器（）使用系统存储过程查看触发器sp_help、sp_helptext和sp_depends具体用途和语法形式如下。sp_help：用用于于查查看看触触发发器器的的一一般般信信息息，如如触触发发器的名称、属性、类型和创建时间。器的名称、属性、类型和创建时间。sp_help触发器名称触发器名称sp_helptext：用于查看触发器的正文信息：用于查看触发器的正文信息sp_helptext触发器名称触发器名称sp_depends：用用于于查查看看指指定定触触发发器器所所引引用用的的表表或者指定的表涉及到的所有触
20、发器。或者指定的表涉及到的所有触发器。sp_depends触发器名称触发器名称sp_depends表名表名修改触发器（）使用企业管理器修改触发器正文（）使用企业管理器修改触发器正文在企业管理器中，展开指定的服务器和数在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，用右键单击要修据库，选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择所有任务子菜改的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要修改的触发器的名话框。在名称选项框中选择要修改的触发器的名称，然后在文
21、本框中修改触发器的称，然后在文本框中修改触发器的SQL语句，单语句，单击击“检查语法检查语法”按钮，可以检查语法是否正确。按钮，可以检查语法是否正确。（）使用sp_rename命令修改触发器的名称 sp_rename命令的语法形式如下：命令的语法形式如下：sp_renameoldname,newname（）使用alter trigger命令修改触发器正文altertrigger命令的语法形式如下：命令的语法形式如下：ALTERTRIGGERtrigger_nameON(table|view)WITHENCRYPTION(FOR|AFTER|INSTEADOF)DELETE,INSERT,UPD
22、ATENOTFORREPLICATIONASsql_statement.n|(FOR|AFTER|INSTEADOF)INSERT,UPDATENOTFORREPLICATIONASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n删除触发器（）使使用用系系统统命命令令DROPTRIGGER删删除除指指定定的的触触发器，其语法形式如下：发器，其语法形式如下：DROP
23、TRIGGERtrigger,.n（）删删除除触触发发器器所所在在的的表表时时，SQLServer将将会会自自动动删除与该表相关的触发器。删除与该表相关的触发器。（）在在企企业业管管理理器器中中，用用右右键键单单击击要要删删除除的的触触发发器器所所在在的的表表，从从弹弹出出的的快快捷捷菜菜单单中中选选择择所所有有任任务务子子菜菜单单下下的的管管理理触触发发器器选选项项，则则会会出出现现触触发发器器属属性性对对话话框框。在在名名称称选选项项框框中中选选择择要要删删除除的的触触发发器器，单单击击“删删除除”按按钮钮，即即可可删删除该触发器。除该触发器。触发器的应用 1.使用使用INSERT触发器触
24、发器INSERT触发器通常被用来更新时间标记字段，或者触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中的数据满足要求的标准，以验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。确保数据完整性。2.使用使用UPDATE触发器触发器修改触发器和插入触发器的工作过程基本上一致，修修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的改一条记录等于插入了一条新的记录并且删除一条旧的记录。记录。3.使用DELETE触发器DELETE触触发发器器通通常常用用于于两两种种情情况况，第第一一种种情情况况是是为为了了防防止止那那些些确确实实需需
25、要要删删除除但但会会引引起起数数据据一一致致性问题的记录的删除。性问题的记录的删除。第第二二种种情情况况是是执执行行可可删删除除主主记记录录的的子子记记录录的的级级联联删删除除操操作作。可可以以使使用用这这样样的的触触发发器器从从主主销销售售记记录录中删除所有的定单项。中删除所有的定单项。4.使用嵌套的触发器 如如果果一一个个触触发发器器在在执执行行操操作作时时引引发发了了另另一一个个触触发发器器，而而这这个个触触发发器器又又接接着着引引发发下下一一个个触触发发器器这这些些触触发发器器就就是是嵌嵌套套触触发发器器。触触发发器器可可嵌嵌套套至至32层层，并并且且可可以以控控制制是是否否可可以以通
26、通过过嵌嵌套套触触发发器器服服务务器器配配置置选选项项进进行行触触发发器器嵌嵌套套。如如果果允允许许使使用用嵌嵌套套触触发发器器，且且链链中中的的一一个个触触发发器器开开始始一一个个无无限限循循环环，则则超超出出嵌嵌套套级级，而而且且触触发发器器将将终终止止。在在执执行行过过程程中中，如如果果一一个个触触发发器器修修改改某某个个表表，而而这这个个表表已经有其它触发器，这是就要使用嵌套触发器。已经有其它触发器，这是就要使用嵌套触发器。Select*intotblstoresfonmpubs.storesSelect*intotblsalesfrompups.salesgoselectsa.sto
27、r_id,st.stor_namefromtblstoresst,tblsalessawherest.stor_id=sa.stor_idgocreatetriggertrDelAuthorsOntblsalesfordeleteasraiserror(%drowsaregoingtobedeletedfromthesalestable!,0,1,rowcount)CreatetriggertrDelSalesOntblStoresForDeleteAsDeletetblsalesfromdeletedwheredeleted.stor_id=tbISales.stor_idDeletefromtblstoresWheretblstores.storid=7067封装事务规则CreateTriggertrDelSalesantblSalesForDeleteAsIf(SelectCount(*)fromDeletedWhereDeleted.qty=213)13BeginPrintyoucannotdeleteanyofthesestoresPrintsomestoreshavemorethan213sales!PrintRollingbackyourtransaction!RollbackTransactionend