触发器数据库优秀PPT.ppt

《触发器数据库优秀PPT.ppt》由会员分享，可在线阅读，更多相关《触发器数据库优秀PPT.ppt（56页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、触发器数据库1现在学习的是第1页，共56页7.1.1触发器的基本概念 7.1触发器的基本概念和优点触发器的基本概念和优点 数据库触发器是一种在基表被修改时自动执行的内嵌过程，当使用UPDATE、INSERT或DELETE命令在指定表中对数据进行修改时，用来防止对数据进行的不正确或不一致的修改 2现在学习的是第2页，共56页通过触发器可以把事务通过触发器可以把事务规则从应用程序代码移到数规则从应用程序代码移到数据库中从而确保事务规则被据库中从而确保事务规则被遵守，并能显著提高性能遵守，并能显著提高性能3现在学习的是第3页，共56页7.1.2使用触发器的优点使用触发器的优点1.强制比CHCEK约束

2、更复杂的数据完整性 在CHECK约束中不允许引用其他表中的列来完成检查工作，而触发器则可以引用其他表中的列来完成数据完整性的约束 进出货：存货量定购量4现在学习的是第4页，共56页2.使用自定义的错误信息使用自定义的错误信息用户有时需要在数据完整用户有时需要在数据完整性遭到破坏或其他情况下，发性遭到破坏或其他情况下，发出预先自定义好的错误信息或出预先自定义好的错误信息或动态自定义的错误信息动态自定义的错误信息5现在学习的是第5页，共56页3.实现数据库中多张表的级联修改 在在titleauthor、sales及及roysched表中对各匹配行进行定位删除表中对各匹配行进行定位删除title_i

3、d列titles表在title_id列上定义一个删除触发器titleauthor表salessales表表roysched表此三列均有title_id字段6现在学习的是第6页，共56页4.比较数据库修改前后数据的状态 用户可在触发器用户可在触发器中引用由于修改中引用由于修改所影响的记录行所影响的记录行触发器提供访问由INSERT、UPDATE或DELETE语句引起的数据变化的前后状态7现在学习的是第7页，共56页5.维护非规范化数据维护非规范化数据非规范数据通非规范数据通常是指在表中常是指在表中的派生的、冗的派生的、冗余的数据值余的数据值维护非规范化数据应该通过使用触发器来实现表的级联是指不表

4、的级联是指不同表之间的主外同表之间的主外键关系，维护表键关系，维护表的级联可通过设的级联可通过设置表的主键与外置表的主键与外键的关系来实现键的关系来实现注意区别8现在学习的是第8页，共56页 触发器类型：分为两种：分为两种：AFTER触发器和触发器和INSTEADOF触发器触发器AFTER触发器：触发器：这种触发器将在数据变动这种触发器将在数据变动(insert,update,delete操作操作)完成完成以后才触发。对变动的数据进行检查，如果发现错误，以后才触发。对变动的数据进行检查，如果发现错误，则拒绝或回滚变动的数据。则拒绝或回滚变动的数据。INSTEADOF触发器：触发器：这种触发器将

5、在数据变动以前被触发。并取代变动数据这种触发器将在数据变动以前被触发。并取代变动数据的操作的操作(insert,update,delete操作操作)，转而去执行触发器定义，转而去执行触发器定义的操作。的操作。在建立触发器时，还必须指定触发操作：在建立触发器时，还必须指定触发操作：insert、update、delete操作，至少指定一种，也可指定多种。操作，至少指定一种，也可指定多种。9现在学习的是第9页，共56页触发事件发生After条件触发器执行示意图条件触发器执行示意图执行触发事件执行触发器动作触发事件发生Insteadof条件触发器执行示意图条件触发器执行示意图不执行触发事件不执行触发

6、事件执行触发器动作10现在学习的是第10页，共56页 触发器结构：事件UPDATEINSERTDELETE条件AFTERINSTEAD OF动作各种Transact-SQL语句有时，触发器也称主动规则，或称事件条件动作规则(ECA)11现在学习的是第11页，共56页7.2触发器的创建触发器的创建CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEWITHAPPENDNOTFORREPLICATIONAS12现在学习的是第12页，共56页IFUPDATE(column)

7、AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n SQL Server不支持在触发器中包含所有的create语句、DROP语句等13现在学习的是第13页，共56页创建触发器创建触发器在同一个数据表中可以创建多在同一个数据表中可以创建多个个after触发器，在表或视图上，触发器，在表或视图上，每个每个insert、update、delete语句语句最多可以定义一个最多可以定义一个insteadof

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

9、第15页，共56页创建触发器应该考虑以下几个问题：创建触发器应该考虑以下几个问题：虽然不能在临时表或系统表上创建触发器，但虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。是触发器可以引用临时表。在为某数据表创建外键约束时在为某数据表创建外键约束时,选中选中级联删除级联删除相关字段相关字段，不能定义，不能定义INSTEADOFDELETE触发器；选中触发器；选中级联更新相关字段级联更新相关字段，不能定义，不能定义INSTEADOFUPDATE触发器。触发器。虽然虽然TRUNCATETABLE语句类似于没有语句类似于没有WHERE子句（用于删除行）的子句（用于删除行）的DELETE

10、语语句，但它并不会引发句，但它并不会引发DELETE触发器，因为触发器，因为TRUNCATETABLE语句没有记录。语句没有记录。16现在学习的是第16页，共56页7.2.1INSERT触发器触发器例：例：在在pubs库的库的authors表上创建表上创建my_trigger1触发器，该触发器被操作触发器，该触发器被操作INSERT所触发所触发USEpubsgoCREATETRIGGERmy_trigger1ONauthorsFORINSERTASraiserror(unauthorized,10,1)17现在学习的是第17页，共56页当向表当向表authors插入数据时将触发触发插入数据时将

11、触发触发器，但是数据仍能被插入表中器，但是数据仍能被插入表中INSERTINTOauthorsvalues(172-33-1234,White,John,408496-7223,10932BiggeRd.,MenloPark,CA,94025,1)定义触发器时指定了定义触发器时指定了FORFOR选项，选项，AFTERAFTER为为默默认值，触发器只在认值，触发器只在INSERTINSERT中指定的操作都中指定的操作都执行后才激发，因此仍能插入数据执行后才激发，因此仍能插入数据18现在学习的是第18页，共56页有没有什么办法能实现触有没有什么办法能实现触发器被执行的发器被执行的同时同时，取，取消

12、触发触发器的消触发触发器的SQL语语句的操作呢？句的操作呢？用INSTEAD OF关键字来实现19现在学习的是第19页，共56页例例:在在pubspubs库的库的authors表上创建触发器表上创建触发器mymy_trigger2，它被操作，它被操作DELETEDELETE所触发，且所触发，且要求触发触发器的要求触发触发器的DELETEDELETE语句在执行后语句在执行后被取消被取消USEPubsGOCREATETRIGGERmy_trigger2ONauthorsINSTEADOFDELETEASraiserror(NoRightToDeleteIt,10,1)20现在学习的是第20页，共5

13、6页如果在表如果在表authors中删除前面例中删除前面例子中新增的记录，就会报错，子中新增的记录，就会报错，如：如：DELETE*fromauthorsWhereau_id=172-33-123421现在学习的是第21页，共56页7.2.2UPDATE触发器触发器通过定义IF UPDATE 实现当特定列被更新时触发触发器不论该更新影响表中的多少行若用户需要实现多个特定列中的任意一列被更新时触发触发器，可以通过在触发器定义中使用多个IF UPDATE 语句来实现 22现在学习的是第22页，共56页例例:在在pubspubs库的库的authorsauthors表上建触发器表上建触发器member

14、_trigger，它将被，它将被UPDATEUPDATE操作操作激活激活,且不允许修改且不允许修改au_lname字段字段USEpubsgoCREATETRIGGERmember_triggerONauthorsFORUPDATEAS23现在学习的是第23页，共56页不使用INSTEAD OF而是通过rollback transaction子句恢复原来的数据的方法来实现字段不被修改IFUPDATE(au_lname)BEGINraiserror(Unauthorized!,10,1)rollbacktransactionEND24现在学习的是第24页，共56页建好触发器后试着执行建好触发器后试

15、着执行UPDATEUPDATE操作操作USEpubsgoUPDATEauthorsSETau_lname=WangtiWHEREau_lname=White运行结果显示：运行结果显示：“Unauthorized!”25现在学习的是第25页，共56页在在“查询分析器查询分析器”中运行如下命令：中运行如下命令：USEpubsgoSELECTau_lnameFROMauthorsWHEREau_lnameLIKEW%查询结果中只有两行的White的内容，可见刚才的更新操作并不能实现对表中au_lname字段的更新 26现在学习的是第26页，共56页UPDATEUPDATE可对未建立保护性触发的字段进

16、可对未建立保护性触发的字段进行更新而不激发触发器，例如：行更新而不激发触发器，例如：USEpubsgoUPDATEauthorsSETau_fname=WangtiWHEREau_fname=Johnson检索表检索表authorsauthors可看到表内的信息被更可看到表内的信息被更新了，这是由于在新了，这是由于在au_fnameau_fname字段上未建字段上未建立立UPDATEUPDATE的触发的触发返回消息：“所影响的行数为 1 行”27现在学习的是第27页，共56页7.2.3DELETE触发器触发器 利用利用DELETEDELETE触发器，能触发器，能在相应的表中实现当遇到删在相应的

17、表中实现当遇到删除动作时自动发出报警除动作时自动发出报警 28现在学习的是第28页，共56页例：例：在在pubspubs库的库的sales表上建立名为表上建立名为delete_trigger的的DELETEDELETE触发器，实现触发器，实现对删除对删除sales表中销售记录的操作给表中销售记录的操作给出报警，并取消当前的删除操作出报警，并取消当前的删除操作USE pubsUSE pubsgogoCREATE TRIGGER delete_trigger ON salesCREATE TRIGGER delete_trigger ON salesFOR DELETEFOR DELETEAS B

18、EGINAS BEGIN raiserror(Unauthorized!,10,1)raiserror(Unauthorized!,10,1)rollback transaction rollback transactionEND END 29现在学习的是第29页，共56页7.2.4查看触发器信息查看触发器信息象存储过程一样，触发器在创象存储过程一样，触发器在创建后，建后，系统系统把触发器的名称把触发器的名称保存在系统表保存在系统表sysobjectssysobjects中，中，并把创建的源代码保存在系统并把创建的源代码保存在系统表表syscommentssyscomments中中30现在学习

19、的是第30页，共56页1.使用系统存储过程使用系统存储过程EXEC sp_helptext 例：例：使用使用sp_helptext查看触发器查看触发器delete_trigger的定义文本信息的定义文本信息USEpubsEXECsp_helptextdelete_triggergo31现在学习的是第31页，共56页32现在学习的是第32页，共56页可以通过指定可以通过指定WITHWITHENCRYPTIONENCRYPTION来对触发器的定来对触发器的定义文本信息进行加密，加密义文本信息进行加密，加密后的触发器后的触发器无法无法用用spsp_helptexthelptext查看相关信息查看相关

20、信息33现在学习的是第33页，共56页还可使用系统存储过程还可使用系统存储过程spsp_helptriggerhelptrigger来查看来查看某特定表上存在的触发某特定表上存在的触发器的某些信息器的某些信息EXECsp_helptrigger34现在学习的是第34页，共56页例：例：用系统存储过程用系统存储过程sp_helptrigger查查看表看表authors上存在的所有触发器的相上存在的所有触发器的相关信息关信息USEpubsEXECsp_helptriggerauthorsgo35现在学习的是第35页，共56页36现在学习的是第36页，共56页2.使用系统表使用系统表例：例：用系统表

21、用系统表sysobjectssysobjects查看数据库查看数据库pubs上的所有触发器的相关信息上的所有触发器的相关信息USEpubsSELECTnamefromsysobjectsWHEREtype=TRgo37现在学习的是第37页，共56页3.用用“企业管理器企业管理器”查看触发查看触发器器38现在学习的是第38页，共56页7.3.1 使用触发器强制数据完整性约束和触发器都可以用来实施数据完整性，但两者各有优势触发器的可以包含使用 T-SQL 代码的复杂处理逻辑触发器可以支持约束的所有功能39现在学习的是第39页，共56页实体完整性实体完整性应在最低级别上通过索应在最低级别上通过索引进

22、行强制，这些索引或是引进行强制，这些索引或是PRIMARYPRIMARYKEYKEY和和UNIQUEUNIQUE约束的一部分，或是在约束的一部分，或是在约束之外独立创建的约束之外独立创建的域完整性域完整性应应通过通过CHECKCHECK约约束来强制束来强制引用完整性引用完整性应通应通过过FOREIGNFOREIGNKEYKEY约束来强制约束来强制40现在学习的是第40页，共56页以下应用场合应考虑使用触发器：除非除非REFERENCESREFERENCES子句定义了级子句定义了级联引用操作，否则联引用操作，否则FOREIGN KEYFOREIGN KEY约束只能以与另一列中的值完全约束只能以与

23、另一列中的值完全匹配的值来验证列值匹配的值来验证列值应用程序要求应用程序要求根据另一表中根据另一表中的列验证列值的列验证列值应用程序要求使用应用程序要求使用自定义信息和较为自定义信息和较为复杂的错误处理复杂的错误处理41现在学习的是第41页，共56页7.3.2使用触发器强制业务规则使用触发器强制业务规则触发器在强制数据完整性触发器在强制数据完整性之外，还可强制实施对之外，还可强制实施对CHECKCHECK约束来说过于复杂的约束来说过于复杂的业务规则，包括对其他表业务规则，包括对其他表中行的状态进行检查中行的状态进行检查42现在学习的是第42页，共56页例：例：在在NorthwindNorthw

24、ind数据数据库中的库中的CustomersCustomers上建立上建立DELETEDELETE触发器触发器deletedelete_customerscustomers，使得在删除，使得在删除表表CustomersCustomers中记录的同时，自中记录的同时，自动检查表动检查表OrdersOrders中是否有该客户中是否有该客户的记录，如果存在该客户记录，的记录，如果存在该客户记录，则取消删除。则取消删除。43现在学习的是第43页，共56页USE NorthwindgoCREATE TRIGGER delete_customersON dbo.CustomersFOR DELETEAS

25、IF(SELECT COUNT(*)FROMOrders INNER JOIN DeletedON Orders.CustomerID=Deleted.CustomerID)044现在学习的是第44页，共56页BEGINBEGINRAISERRORRAISERROR(Youcannotdeletethecustomerwiththeorderrecord.Thetransactionwillbecancelled,10,1)ROLLBACKROLLBACKTRANSACTIONTRANSACTIONENDEND45现在学习的是第45页，共56页7.4修改和删除触发器修改和删除触发器7.4.1修

26、改触发器修改触发器ALTER TRIGGER owner.trigge_nameON owner.table|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS46现在学习的是第46页，共56页IFIFUPDATEUPDATE(column_name)and|orUPDATEUPDATE(column_name)sqlsql_statesmentsALTERALTERTRIGGERTRIGGERowner.trigge_nameowner.trigge_nameON owner.table|viewON owner.ta

27、ble|viewFOR|AFTER|INSTEAD OF FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEINSERT,UPDATE,DELETEWITH 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现在学习的是第47页，共56页例：例：修改修改Northwind库中的视图库中的视图Cu

28、stomersCustomers上的上的INSTEAD OFINSTEAD OF触触发器发器delete_customers，使得，使得用户在该视图上执行除删除外用户在该视图上执行除删除外的增加，修改操作时，也自动的增加，修改操作时，也自动给出错误提示信息并撤销此次给出错误提示信息并撤销此次操作操作48现在学习的是第48页，共56页USE NorthwindgoALTER TRIGGER delete_customersON CustomersINSTEAD OF DELETE,INSERT,UPDATEAS RAISERROR(You can not insert,delete or upd

29、ate records from this view,10,1)49现在学习的是第49页，共56页7.4.2删除触发器删除触发器DROP TRIGGER owner.trigge_name 50现在学习的是第50页，共56页7.4.3禁止或启用触发器禁止或启用触发器当一个触发器被禁止后，它仍然当一个触发器被禁止后，它仍然存在于触发器表上，只是触发器存在于触发器表上，只是触发器的动作不再执行，直到该它被重的动作不再执行，直到该它被重新启用。新启用。启用启用/禁止禁止命令如下：命令如下：ALTERALTERTABLETABLEtabletable_nameENABLE|DISABLETRIGGER

30、ALLALL|trigger_name,n51现在学习的是第51页，共56页7.5嵌套触发器和递归触发器嵌套触发器和递归触发器7.5.1 嵌套触发器 若一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，所有触发器依次触发嵌套触发器最深可嵌套 32 层52现在学习的是第52页，共56页使用系统存储过程使用系统存储过程sp_config设置设置触发器是否禁止嵌套：触发器是否禁止嵌套：EXEC EXEC sp_configinested_TRIGGER,0|1允许嵌套禁止嵌套若在嵌套触发器的任意层中出错，则整个事务都将取消,且回滚 53现在学习的是第53页，共56页用用“企业管理器企业管理器”来设置嵌套允许与否来设置嵌套允许与否54现在学习的是第54页，共56页7.5.2递归触发器递归触发器当修改表中数据的触发器激活了第二个触发器，第二个触发器又通过修改原始表中的数据激活了激活它的触发器，从而形成递归触发器 触发器触发器触发器55现在学习的是第55页，共56页递归触发器的递归方式 直接递归 间接递归 触发器激发并执行一个操作，而该操作又使同一触发器再次激发 触发器激发并执行一个操作，该操作又使另一表中的某触发器激发，该触发器使原始表得到更新，从而再次引发第一个触发器 56现在学习的是第56页，共56页