触发器数据库精选文档.ppt

《触发器数据库精选文档.ppt》由会员分享，可在线阅读，更多相关《触发器数据库精选文档.ppt（56页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、触发器数据库1本讲稿第一页，共五十六页7.1.1触发器的基本概念触发器的基本概念7.1触发器的基本概念和优点触发器的基本概念和优点 数据库触发器是一种在基表被修数据库触发器是一种在基表被修改时自动执行的内嵌过程，当使用改时自动执行的内嵌过程，当使用UPDATEUPDATE、INSERTINSERT或或DELETEDELETE命令在指定命令在指定表中对数据进行修改时，用来防止对表中对数据进行修改时，用来防止对数据进行的不正确或不一致的修改数据进行的不正确或不一致的修改 2本讲稿第二页，共五十六页通过触发器可以把事务通过触发器可以把事务规则从应用程序代码移到数规则从应用程序代码移到数据库中从而确保

2、事务规则被据库中从而确保事务规则被遵守，并能显著提高性能遵守，并能显著提高性能3本讲稿第三页，共五十六页7.1.2使用触发器的优点使用触发器的优点1.强制比强制比CHCEKCHCEK约束更复杂的数据约束更复杂的数据完整性完整性在在CHECKCHECK约束中不允许引用其他约束中不允许引用其他表中的列来完成检查工作，而触发表中的列来完成检查工作，而触发器则可以引用其他表中的列来完成器则可以引用其他表中的列来完成数据完整性的约束数据完整性的约束进出货：存货量进出货：存货量定购量定购量4本讲稿第四页，共五十六页2.使用自定义的错误信息使用自定义的错误信息用户有时需要在数据完整用户有时需要在数据完整性遭

3、到破坏或其他情况下，发性遭到破坏或其他情况下，发出预先自定义好的错误信息或出预先自定义好的错误信息或动态自定义的错误信息动态自定义的错误信息5本讲稿第五页，共五十六页3.实现数据库中多张表的级联修改实现数据库中多张表的级联修改在在titleauthor、sales及及roysched表中对各匹配行进行定位删除表中对各匹配行进行定位删除title_idtitle_id列列titlestitles表表在title_id列上定义一个删除触发器titleauthortitleauthor表表salessales表表royschedroysched表表此三列均有title_id字段6本讲稿第六页，共五十

4、六页4.比较数据库修改前后数据的状态比较数据库修改前后数据的状态用户可在触发器用户可在触发器中引用由于修改中引用由于修改所影响的记录行所影响的记录行触触发发器器提供提供访问由访问由INSERTINSERT、UPDATEUPDATE或或DELETEDELETE语句引起的数据语句引起的数据变化的前后状态变化的前后状态7本讲稿第七页，共五十六页5.维护非规范化数据维护非规范化数据非规范数据通非规范数据通常是指在表中常是指在表中的派生的、冗的派生的、冗余的数据值余的数据值维护非规范化数据应该通过使用触发器来实现表的级联是指不表的级联是指不同表之间的主外同表之间的主外键关系，维护表键关系，维护表的级联可

5、通过设的级联可通过设置表的主键与外置表的主键与外键的关系来实现键的关系来实现注意区别8本讲稿第八页，共五十六页 触发器类型：触发器类型：分为两种：分为两种：AFTER触发器和触发器和INSTEADOF触发器触发器AFTER触发器：触发器：这种触发器将在数据变动这种触发器将在数据变动(insert,update,delete操作操作)完成完成以后才触发。对变动的数据进行检查，如果发现错误，则拒以后才触发。对变动的数据进行检查，如果发现错误，则拒绝或回滚变动的数据。绝或回滚变动的数据。INSTEADOF触发器：触发器：这种触发器将在数据变动以前被触发。并取代变动数据这种触发器将在数据变动以前被触发

6、。并取代变动数据的操作的操作(insert,update,delete操作操作)，转而去执行触发器定义，转而去执行触发器定义的操作。的操作。在建立触发器时，还必须指定触发操作：在建立触发器时，还必须指定触发操作：insert、update、delete操作，至少指定一种，也可指定多种。操作，至少指定一种，也可指定多种。9本讲稿第九页，共五十六页触发事件发生After条件触发器执行示意图条件触发器执行示意图执行触发事件执行触发器动作触发事件发生Insteadof条件触发器执行示意图条件触发器执行示意图不执行触发事件不执行触发事件执行触发器动作10本讲稿第十页，共五十六页 触发器结构：触发器结构：

7、事件UPDATEINSERTDELETE条件AFTERINSTEAD OF动作各种Transact-SQL语句有时，触发器也称主动规则，或称事件条件动作规则(ECA)11本讲稿第十一页，共五十六页7.2触发器的创建触发器的创建CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEWITHAPPENDNOTFORREPLICATIONAS12本讲稿第十二页，共五十六页IFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPD

8、ATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n SQL Server不支持在触发器中包含所有的create语句、DROP语句等13本讲稿第十三页，共五十六页创建触发器创建触发器在同一个数据表中可以创建多个在同一个数据表中可以创建多个after触发器，在表或视图上，触发器，在表或视图上，每个每个insert、update、delete语句语句最多可以定义一个最多可以定义一个insteadof触发触发器。器。14本讲稿第十四页，共五十六页创建触发器应该考虑以下几个问题：

9、创建触发器应该考虑以下几个问题：CREATETRIGGER语句必须是批处理中的语句必须是批处理中的第一个语句。第一个语句。创建触发器的权限默认分配给表的所有者，创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。且不能将该权限转给其他用户。触发器为数据库对象，其名称必须遵循标识触发器为数据库对象，其名称必须遵循标识符的命名规则。符的命名规则。虽然触发器可以引用当前数据库以外的对象，虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。但只能在当前数据库中创建触发器。15本讲稿第十五页，共五十六页创建触发器应该考虑以下几个问题：创建触发器应该考虑以下几个问题：虽然

10、不能在临时表或系统表上创建触发器，但虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。是触发器可以引用临时表。在为某数据表创建外键约束时在为某数据表创建外键约束时,选中选中级联删除级联删除相关字段相关字段，不能定义，不能定义INSTEADOFDELETE触发器；选中触发器；选中级联更新相关字段级联更新相关字段，不能定义，不能定义INSTEADOFUPDATE触发器。触发器。虽然虽然TRUNCATETABLE语句类似于没有语句类似于没有WHERE子句（用于删除行）的子句（用于删除行）的DELETE语语句，但它并不会引发句，但它并不会引发DELETE触发器，因为触发器，因为TRUNC

11、ATETABLE语句没有记录。语句没有记录。16本讲稿第十六页，共五十六页7.2.1INSERT触发器触发器例：例：在在pubs库的库的authors表上创建表上创建my_trigger1触发器，该触发器被操作触发器，该触发器被操作INSERT所触发所触发USEpubsgoCREATETRIGGERmy_trigger1ONauthorsFORINSERTASraiserror(unauthorized,10,1)17本讲稿第十七页，共五十六页当向表当向表authors插入数据时将触发触发插入数据时将触发触发器，但是数据仍能被插入表中器，但是数据仍能被插入表中INSERTINTOauthors

12、values(172-33-1234,White,John,408496-7223,10932BiggeRd.,MenloPark,CA,94025,1)定义触发器时指定了定义触发器时指定了FORFOR选项，选项，AFTERAFTER为为默默认值，触发器只在认值，触发器只在INSERTINSERT中指定的操作都中指定的操作都执行后才激发，因此仍能插入数据执行后才激发，因此仍能插入数据18本讲稿第十八页，共五十六页有没有什么办法能实现有没有什么办法能实现触发器被执行的触发器被执行的同时同时，取消触发触发器的取消触发触发器的SQL语句的操作呢？语句的操作呢？用INSTEAD OF关键字来实现19本

13、讲稿第十九页，共五十六页例例:在在pubspubs库的库的authors表上创建触发器表上创建触发器mymy_trigger2，它被操作，它被操作DELETEDELETE所触发，所触发，且要求触发触发器的且要求触发触发器的DELETEDELETE语句在执行语句在执行后被取消后被取消USEPubsGOCREATETRIGGERmy_trigger2ONauthorsINSTEADOFDELETEASraiserror(NoRightToDeleteIt,10,1)20本讲稿第二十页，共五十六页如果在表如果在表authors中删除前面例中删除前面例子中新增的记录，就会报错，子中新增的记录，就会报错

14、，如：如：DELETE*fromauthorsWhereau_id=172-33-123421本讲稿第二十一页，共五十六页7.2.2UPDATE触发器触发器通过定义通过定义IF UPDATE IF UPDATE 实现当特定列被更新时触发触发器实现当特定列被更新时触发触发器不论该更新影响表中的多少行不论该更新影响表中的多少行若用户需要实现多个特定列中的任意一若用户需要实现多个特定列中的任意一列被更新时触发触发器，可以通过在触列被更新时触发触发器，可以通过在触发器定义中使用多个发器定义中使用多个IFUPDATE语句来实现语句来实现22本讲稿第二十二页，共五十六页例例:在在pubspubs库的库的a

15、uthorsauthors表上建触发器表上建触发器member_trigger，它将被，它将被UPDATEUPDATE操作操作激活激活,且不允许修改且不允许修改au_lname字段字段USEpubsgoCREATETRIGGERmember_triggerONauthorsFORUPDATEAS23本讲稿第二十三页，共五十六页不使用INSTEAD OF而是通过rollback transaction子句恢复原来的数据的方法来实现字段不被修改IFUPDATE(au_lname)BEGINraiserror(Unauthorized!,10,1)rollbacktransactionEND24本讲

16、稿第二十四页，共五十六页建好触发器后试着执行建好触发器后试着执行UPDATEUPDATE操作操作USEpubsgoUPDATEauthorsSETau_lname=WangtiWHEREau_lname=White运行结果显示：运行结果显示：“Unauthorized!”25本讲稿第二十五页，共五十六页在在“查询分析器查询分析器”中运行如下命令：中运行如下命令：USEpubsgoSELECTau_lnameFROMauthorsWHEREau_lnameLIKEW%查询结果中只有两行的查询结果中只有两行的White的的内容，可见刚才的更新操作并不能实内容，可见刚才的更新操作并不能实现对表中现对

17、表中au_lname字段的更新字段的更新26本讲稿第二十六页，共五十六页UPDATEUPDATE可对未建立保护性触发的字段可对未建立保护性触发的字段进行更新而不激发触发器，例如：进行更新而不激发触发器，例如：USEpubsgoUPDATEauthorsSETau_fname=WangtiWHEREau_fname=Johnson检索表检索表authorsauthors可看到表内的信息被更可看到表内的信息被更新了，这是由于在新了，这是由于在au_fnameau_fname字段上未建字段上未建立立UPDATEUPDATE的触发的触发返回消息：“所影响的行数为 1 行”27本讲稿第二十七页，共五十六

18、页7.2.3DELETE触发器触发器 利用利用DELETEDELETE触发器，能在触发器，能在相应的表中实现当遇到删相应的表中实现当遇到删除动作时自动发出报警除动作时自动发出报警 28本讲稿第二十八页，共五十六页例：例：在在pubspubs库的库的sales表上建立名为表上建立名为delete_trigger的的DELETEDELETE触发器，实触发器，实现对删除现对删除sales表中销售记录的操作表中销售记录的操作给出报警，并取消当前的删除操作给出报警，并取消当前的删除操作USE pubsUSE pubsgogoCREATE TRIGGER delete_trigger ON salesCR

19、EATE TRIGGER delete_trigger ON salesFOR DELETEFOR DELETEAS BEGINAS BEGIN raiserror(Unauthorized!,10,1)raiserror(Unauthorized!,10,1)rollback transaction rollback transactionEND END 29本讲稿第二十九页，共五十六页7.2.4查看触发器信息查看触发器信息象存储过程一样，触发器在创象存储过程一样，触发器在创建后，建后，系统系统把触发器的名称保把触发器的名称保存在系统表存在系统表sysobjectssysobjects中，并

20、中，并把创建的源代码保存在系统表把创建的源代码保存在系统表syscommentssyscomments中中30本讲稿第三十页，共五十六页1.使用系统存储过程使用系统存储过程EXECsp_helptext例：例：使用使用sp_helptext查看触发器查看触发器delete_trigger的定义文本信息的定义文本信息USEpubsEXECsp_helptextdelete_triggergo31本讲稿第三十一页，共五十六页32本讲稿第三十二页，共五十六页可以通过指定可以通过指定WITHWITHENCRYPTIONENCRYPTION来对触发器的定来对触发器的定义文本信息进行加密，加密义文本信息进

21、行加密，加密后的触发器后的触发器无法无法用用spsp_helptexthelptext查看相关信息查看相关信息33本讲稿第三十三页，共五十六页还可使用系统存储过程还可使用系统存储过程spsp_helptriggerhelptrigger来查看来查看某特定表上存在的触发某特定表上存在的触发器的某些信息器的某些信息EXECsp_helptrigger34本讲稿第三十四页，共五十六页例：例：用系统存储过程用系统存储过程sp_helptrigger查查看表看表authors上存在的所有触发器的相上存在的所有触发器的相关信息关信息USEpubsEXECsp_helptriggerauthorsgo35本

22、讲稿第三十五页，共五十六页36本讲稿第三十六页，共五十六页2.使用系统表使用系统表例：例：用系统表用系统表sysobjectssysobjects查看数据库查看数据库pubs上的所有触发器的相关信息上的所有触发器的相关信息USEpubsSELECTnamefromsysobjectsWHEREtype=TRgo37本讲稿第三十七页，共五十六页3.用用“企业管理器企业管理器”查看触发查看触发器器38本讲稿第三十八页，共五十六页7.3.1使用触发器强制数据完整性使用触发器强制数据完整性约束约束和和触发器触发器都可以用来实施数据都可以用来实施数据完整性完整性，但两者各有优势，但两者各有优势触发器的可

23、以触发器的可以包含使用包含使用T-T-SQLSQL代码的复代码的复杂处理逻辑杂处理逻辑触发器可以支持约束的所有功能39本讲稿第三十九页，共五十六页实体完整性实体完整性应在最低级别上通过索应在最低级别上通过索引进行强制，这些索引或是引进行强制，这些索引或是PRIMARYPRIMARYKEYKEY和和UNIQUEUNIQUE约束的一部分，或是在约束的一部分，或是在约束之外独立创建的约束之外独立创建的域完整性域完整性应应通过通过CHECKCHECK约约束来强制束来强制引用完整性引用完整性应通应通过过FOREIGNFOREIGNKEYKEY约束来强制约束来强制40本讲稿第四十页，共五十六页以下应用场合

24、应考虑使用触发器：以下应用场合应考虑使用触发器：除非除非REFERENCESREFERENCES子句定义了级子句定义了级联引用操作，否则联引用操作，否则FOREIGN FOREIGN KEYKEY约束只能以与另一列中的约束只能以与另一列中的值完全匹配的值来验证列值值完全匹配的值来验证列值应用程序要求应用程序要求根据另一表中根据另一表中的列验证列值的列验证列值应用程序要求使用应用程序要求使用自定义信息和较为自定义信息和较为复杂的错误处理复杂的错误处理41本讲稿第四十一页，共五十六页7.3.2使用触发器强制业务规则使用触发器强制业务规则触发器在强制数据完整性触发器在强制数据完整性之外，还可强制实施

25、对之外，还可强制实施对CHECKCHECK约束来说过于复杂的约束来说过于复杂的业务规则，包括对其他表中业务规则，包括对其他表中行的状态进行检查行的状态进行检查42本讲稿第四十二页，共五十六页例：例：在在NorthwindNorthwind数据数据库中的库中的CustomersCustomers上建立上建立DELETEDELETE触发器触发器deletedelete_customerscustomers，使得在删除，使得在删除表表CustomersCustomers中记录的同时，自中记录的同时，自动检查表动检查表OrdersOrders中是否有该客户中是否有该客户的记录，如果存在该客户记录，的记

26、录，如果存在该客户记录，则取消删除。则取消删除。43本讲稿第四十三页，共五十六页USENorthwindgoCREATECREATETRIGGERTRIGGERdelete_customersONdbo.CustomersFORDELETEASIF(SELECTSELECTCOUNTCOUNT(*)FROMFROMOrdersOrdersINNERINNERJOINJOINDeletedDeletedONOrders.CustomerID=Deleted.CustomerID)044本讲稿第四十四页，共五十六页BEGINBEGINRAISERRORRAISERROR(Youcannotdele

27、tethecustomerwiththeorderrecord.Thetransactionwillbecancelled,10,1)ROLLBACKROLLBACKTRANSACTIONTRANSACTIONENDEND45本讲稿第四十五页，共五十六页7.4修改和删除触发器修改和删除触发器7.4.1修改触发器修改触发器ALTERALTERTRIGGERTRIGGERownerowner.trigge_nameONONowner.table|viewFORFOR|AFTERAFTER|INSTEADINSTEADOFOFINSERTINSERT,UPDATEUPDATE,DELETEDELET

28、EWITHWITHENCRYPTIONENCRYPTIONASAS46本讲稿第四十六页，共五十六页IFIFUPDATEUPDATE(column_name)and|orUPDATEUPDATE(column_name)sqlsql_statesmentsALTERALTERTRIGGERTRIGGERowner.trigge_nameowner.trigge_nameON owner.table|viewON owner.table|viewFOR|AFTER|INSTEAD OF FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEINSERT,UPDATE,D

29、ELETEWITH ENCRYPTIONWITH ENCRYPTIONAS AS IF UPDATE(column_name)IF UPDATE(column_name)and|or UPDATE(column_name)and|or UPDATE(column_name)sql_statesments sql_statesments 47本讲稿第四十七页，共五十六页例：例：修改修改Northwind库中的视图库中的视图CustomersCustomers上的上的INSTEAD OFINSTEAD OF触触发器发器delete_customers，使得用，使得用户在该视图上执行除删除外的户在该

30、视图上执行除删除外的增加，修改操作时，也自动给增加，修改操作时，也自动给出错误提示信息并撤销此次操出错误提示信息并撤销此次操作作48本讲稿第四十八页，共五十六页USENorthwindgoALTERALTERTRIGGERTRIGGERdelete_customersONCustomersINSTEADOFDELETE,INSERT,UPDATEASRAISERROR(Youcannotinsert,deleteorupdaterecordsfromthisview,10,1)49本讲稿第四十九页，共五十六页7.4.2删除触发器删除触发器DROPDROPTRIGGERTRIGGERowner.

31、trigge_name50本讲稿第五十页，共五十六页7.4.3禁止或启用触发器禁止或启用触发器当一个触发器被禁止后，它仍然存当一个触发器被禁止后，它仍然存在于触发器表上，只是触发器的动在于触发器表上，只是触发器的动作不再执行，直到该它被重新启用。作不再执行，直到该它被重新启用。启用启用/禁止禁止命令如下：命令如下：ALTERALTERTABLETABLEtabletable_nameENABLE|DISABLETRIGGERALLALL|trigger_name,n51本讲稿第五十一页，共五十六页7.5嵌套触发器和递归触发器嵌套触发器和递归触发器7.5.1嵌套触发器嵌套触发器若一个触发器在执行

32、操若一个触发器在执行操作时引发了另一个作时引发了另一个触发触发器器，而这个，而这个触发器触发器又接又接着引发下一个着引发下一个触发器触发器，所有触发器依次触发所有触发器依次触发嵌套触发器最深可嵌套 32 层52本讲稿第五十二页，共五十六页使用系统存储过程使用系统存储过程sp_config设置触设置触发器是否禁止嵌套：发器是否禁止嵌套：EXEC EXEC sp_configinested_TRIGGER,0|1允许嵌套禁止嵌套若在嵌套触发器的任意层中出错，则整个事务都将取消,且回滚 53本讲稿第五十三页，共五十六页用用“企业管理器企业管理器”来设置嵌套允许与否来设置嵌套允许与否54本讲稿第五十四

33、页，共五十六页7.5.2递归触发器递归触发器当修改表中数据的触发器激活了第当修改表中数据的触发器激活了第二个触发器，第二个触发器又通过二个触发器，第二个触发器又通过修改原始表中的数据激活了激活它修改原始表中的数据激活了激活它的触发器，从而形成递归触发器的触发器，从而形成递归触发器触发器触发器触发器触发器55本讲稿第五十五页，共五十六页递归递归触发触发器的器的递归递归方式方式直接直接递归递归间接间接递归递归触发器激发并执行一个操作，而该操作又使同一触发器再次激发 触发器激发并执行一个操作，该操作又使另一表中的某触发器激发，该触发器使原始表得到更新，从而再次引发第一个触发器 56本讲稿第五十六页，共五十六页