触发器的操作与管理精选文档.ppt

触发器的操作与管理本讲稿第一页，共三十二页Trigger ViewTriggerisaspecialkindofstoredprocedure,itcanbeeffectiveautomaticallywhileexecutingLanguageevent.触发器是一种特殊的存储过程，它在执行语言事件时自动生效。SQLServer2005包括两大类触发器：DML触发器和DDL触发器。（1）DMLTriggerwillstartwhileoperatingdataindatabase.DMLeventsincludestheINSERT,UPDATEandDELETEstatement.DMLtrigger,DMLtriggercanqueryothertables,canincludecomplexTransact-SQLstatement.TheTriggerandthestatementbelongstothetriggeristreatedasasingletransactionwhichcanberolledback.Ifthefaultisdetected(forexamplediskisfull),thewholetransactionwillberolledback.DML触发器在数据库中发生数据操作语言(DML)事件时将启用。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其他表，还可以包含复杂的Transact-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。本讲稿第二页，共三十二页（2）DDLTriggeristhenewincreasedfunctionofSQLServer2005.DMLTriggerwillstartwhileDDLeventoccurs.DDL触发器是SQLServer2005的新增功能。当服务器或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。本讲稿第三页，共三十二页11.1 DML触发器的创建和应用TriggerwillbecalledWhentheDMLeventofdatabaseoccurs,itmakessuredataoperationtoobeytheruleofsuchSQLstatement.当数据库中发生数据操作语言(DML)事件时将调用DML触发器。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。本讲稿第四页，共三十二页DML触发器的主要优点如下：（1）DMLTriggercanmaketherelatedtablescascadechange.Forexample,Youcanmakeadeletingtriggeronthecolomnsnoofthetablestudent,whenyoudeletethedataofstudenttable,therelatedrecordsofthetablescwillbealsodeleted.DML触发器可通过数据库中的相关表实现级联更改。例如，可以在student表的sno列上写入一个删除触发器，以使其他表中的各匹配行采取删除操作。该触发器用sno列作为惟一键，在sc表中对各匹配行进行定位。本讲稿第五页，共三十二页（2）DMLtriggercanpreventthemaliceorthefaultoftheoperationsofINSERT,UPDATEandDELETE,andenforcetoexecutethemorecomplexconstraintthenalimitationofacheck.DML触发器可以防止恶意或错误的INSERT、UPDATE以及DELETE操作，并强制执行比CHECK约束定义的限制更为复杂的其他限制。与CHECK约束不同，DML触发器可以引用其他表中的列。（3）DMLtriggercanevaluatethestatesofatablebeforechangingorafterchanging,itcantakesomeactionsaccordingtothedifference.DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。本讲稿第六页，共三十二页11.1.1 DML触发器创建Whenyoucreateatrigger,thefollowingoptionsmustbedenoted:当创建一个触发器时必须指定如下选项：（1）Anameofthetrigger名称；（2）Thetablewhichthetriggerisdefinedon在其上定义触发器的表；（3）Thetimewhenthetriggeristriggered:触发器将何时激发；（4）Thedatachangingstatementwhichcanwakethetrigger,moredatachangingstatementscanwakeonesametrigger.激活触发器的数据修改语句，有效选项为INSERT、UPDATE或DELETE，多个数据修改语句可激活同一个触发器；（5）Theprogrammingstatementwhichexecutetriggeroperation.执行触发操作的编程语句。本讲稿第七页，共三十二页11.1.1 DML触发器创建DMLtriggeruseadeletedlogicaltableandainsertedlogicaltable.Theyhavethesamestructureasthebasictable,SQLServercancreateandmanagethetablesautomatically.Thetwologicaltableswhichstayatthememorytemporarycanbeusedtotesttheeffectiveofdatachangingandcansettheconditionoftriggeroperation.DML触发器使用deleted和inserted逻辑表。它们在结构上和触发器所在的表的结构相同，SQLServer会自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。本讲稿第八页，共三十二页DeletedtableisusedtostorethecopyofthedataDeletedorupdated.Whenthestatementexecutes,thedataisdeletedandstoredintothedeletedtable.表用于存储delete，update语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。Insertedtableisusedtostorethecopyofthedatainsertedorupdated.Inainsertingoraupdatingtransaction,thenewrecordwillbeinsertedintotheinsertedtableandthebasictable.Therecordoftheinsertedtableisthedatacopyofthebasictable.表用于存储Insert或update语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到Inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。本讲稿第九页，共三十二页11.1.1 DML触发器创建使用使用SQL Server管理平台创建触发器的过程如下：管理平台创建触发器的过程如下：在SQLServer管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如图9-1所示，右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图9-2所示。最后，单击“执行”按钮，即可成功创建触发器。图9-1新建触发器对话框 图9-2新建触发器窗口 本讲稿第十页，共三十二页11.1.1 DML触发器创建使用使用CREATE TRIGGER命令创建命令创建DML触发器的语法形式如下：触发器的语法形式如下：CREATE TRIGGER schema_name.trigger_name ON table|view WITH ENCRYPTION EXECUTE AS Clause,.n FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE WITH APPEND NOT FOR REPLICATION AS sql_statement;.n|EXTERNAL NAME :=assembly_name.class_name.method_name本讲稿第十一页，共三十二页11.1.1 DML触发器创建例例9-1示例说明示例说明inserted，deleted表的作用。执表的作用。执行结果如右图。行结果如右图。程序清单如下：程序清单如下：create table sc(sno char(10),cno char(2),score real)GoCREATE TRIGGER tr1ON scFOR INSERT,UPDATE,DELETE AS PRINT inserted表：表：Select*from insertedPRINT deleted表：表：Select*from deletedGo图9-3触发器的执行结果 本讲稿第十二页，共三十二页11.1.1 DML触发器创建例例9-2create a trigger,which is for inserting and updating data of table s 创建一个触发器，在创建一个触发器，在 s 表上创建一个插入、更新类型的表上创建一个插入、更新类型的触发器。触发器。程序清单如下：程序清单如下：CREATE TRIGGER tr_sON sFOR INSERT,UPDATE AS BeginDECLARE bh varchar(6)SELECT bh=inserted.sno FROM inserted/*获取插入或更新操作获取插入或更新操作时的新值（学号）时的新值（学号）*/End本讲稿第十三页，共三十二页11.1.2DML触发器的应用1.使用使用INSERT触发器触发器INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中数据满触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。足要求的标准，以确保数据的完整性。例例9-3建立一个触发器，当向建立一个触发器，当向sc表中添加数据时，如果添加的数据与表中添加数据时，如果添加的数据与s表中的数据不表中的数据不匹配（没有对应的学号），则将此数据删除。匹配（没有对应的学号），则将此数据删除。(this trigger is used to replace the foreign constraint)程序清单如下：程序清单如下：CREATE TRIGGER sc_ins ON sc FOR INSERT ASBEGINDECLARE bh char(5)Select bh=Inserted.sno from InsertedIf not exists(select sno from s where s.sno=bh)Delete sc where sno=bhEND本讲稿第十四页，共三十二页11.1.2DML触发器的应用1.使用使用INSERT触发器触发器例例9-4创建一个触发器，当插入或更新成绩列时，该触发器检查插入的数据是否处创建一个触发器，当插入或更新成绩列时，该触发器检查插入的数据是否处于设定的范围内于设定的范围内(this is trigger is used to replace check constraint)。程序清单如下：程序清单如下：CREATE TRIGGER sc_insupdON scFOR INSERT,UPDATEASDECLARE cj int,SELECT cj=inserted.score from insertedIF（cj 100）BEGIN RAISERROR（成绩的取值必须在成绩的取值必须在0到到100之间之间,16,1）ROLLBACK TRANSACTIONEND本讲稿第十五页，共三十二页11.1.2DML触发器的应用2.使用使用UPDATE触发器触发器当在一个有当在一个有UPDATE触发器的表中修改记录时，表中原来的记录被移动到删除表触发器的表中修改记录时，表中原来的记录被移动到删除表中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入表以及被中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入表以及被修改的表，以确定如何完成数据库操作。修改的表，以确定如何完成数据库操作。例例9-5创建一个修改触发器，该触发器防止用户修改表创建一个修改触发器，该触发器防止用户修改表s的入学成绩。的入学成绩。程序清单如下：程序清单如下：create trigger tri_s_updon sfor updateasif update（escore）beginraiserror（不能修改入学成绩不能修改入学成绩,16,10）rollback transactionend go本讲稿第十六页，共三十二页11.1.2DML触发器的应用2.使用使用UPDATE触发触发例9-6DAS数据库由存放实时数据的数据表以及存放历史数据的历史表组成。由于存放实时数据的数据表不断更新，为了保存更新过的数据，在实时表和历史表之间建立了触发器。程序清单如下：CREATETRIGGERDasD_UTRIGGERONDasDFORUPDATEASBEGINIFUpdate(TV)/*数据更新*/BEGINUPDATEDasDSETUT=getdate()/*更新时间*/FROMDasD,insertedWHEREDasD.ID=inserted.IDINSERTDasDHis(ID,TV,UT)SELECTinserted.ID,inserted.TV,DasD.UTFROMDasD,insertedWHEREDasD.ID=inserted.ID/*将更新过的数据送入历史库*/ENDEND本讲稿第十七页，共三十二页11.1.2DML触发器的应用3.使用使用DELETE触发器触发器DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除，第二种情况是执行可删除主记录的子记录的级联删除操作。例9-8建立一个与s表结构一样的表s1，当删除表s中的记录时，自动将删除掉的记录存放到s1表中。程序清单如下：CREATETRIGGERtr_delONs/*建立触发器FORDELETE/*对表删除操作ASinserts1select*fromdeleted/*将删除掉的数据送入表s1中*/GO本讲稿第十八页，共三十二页11.1.2DML触发器的应用3.使用使用DELETE触发器触发器例9-9当删除表s中的记录时，自动删除表sc中对应学号的记录。程序清单如下：CREATETRIGGERtr_del_sONsFORDELETEBEGINDECLAREbhchar(5)Selectbh=deleted.snofromdeletedDeletescwheresno=bhEND本讲稿第十九页，共三十二页11.2 DDL触发器的创建和应用DDL 触发器会为响应多种数据定义语言触发器会为响应多种数据定义语言(DDL)语句而激发。语句而激发。这些语句主要是以这些语句主要是以 CREATE、ALTER 和和 DROP 开头的语句。开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。触发器可用于管理任务，例如审核和控制数据库操作。DDL 触发器一般用于以下目的：触发器一般用于以下目的：（1）防止对数据库架构进行某些更改；）防止对数据库架构进行某些更改；（2）希望数据库中发生某种情况以响应数据库架构中的更改；）希望数据库中发生某种情况以响应数据库架构中的更改；（3）要记录数据库架构中的更改或事件。）要记录数据库架构中的更改或事件。仅在运行触发仅在运行触发 DDL 触发器的触发器的 DDL 语句后，语句后，DDL 触发器触发器才会激发。才会激发。DDL 触发器无法作为触发器无法作为 INSTEAD OF 触发器使用。触发器使用。本讲稿第二十页，共三十二页11.2.1 创建DDL触发器使用CREATETRIGGER命令创建DDL触发器的语法形式如下：CREATETRIGGERtrigger_nameONALLSERVER|DATABASEWITH,.nFOR|AFTERevent_type|event_group,.nASsql_statement;.n|EXTERNALNAME;其中：:=ENCRYPTIONEXECUTEASClause:=assembly_name.class_name.method_name本讲稿第二十一页，共三十二页11.2.2 DDL触发器的应用在响应当前数据库或服务器中处理的Transact-SQL事件时，可以激发DDL触发器。触发器的作用域取决于事件。例9-11使用DDL触发器来防止数据库中的任一表被修改或删除。程序清单如下：CREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINTYoumustdisableTriggersafetytodroporaltertables!ROLLBACK本讲稿第二十二页，共三十二页11.2.2 DDL触发器的应用例9-12使用DDL触发器来防止在数据库中创建表。程序清单如下：CREATETRIGGERsafetyONDATABASEFORCREATE_TABLEASPRINTCREATETABLEIssued.SELECTEVENTDATA().value(/EVENT_INSTANCE/TSQLCommand/CommandText)1,nvarchar(max)RAISERROR(Newtablescannotbecreatedinthisdatabase.,16,1)ROLLBACK本讲稿第二十三页，共三十二页11.3 DDL查看、修改和删除触发器11.3.1查看触发器11.3.2修改触发器11.3.3删除触发器本讲稿第二十四页，共三十二页11.3.1 查看触发器如果要显示作用于表上的触发器究竟对表有哪些操作，必须查看触发器信息。在SQLServer中，有多种方法可以查看触发器信息，其中最常用的有如下两种：（1）使用SQLServer管理平台查看触发器信息；（2）使用系统存储过程查看触发器。本讲稿第二十五页，共三十二页11.3.1 查看触发器（1）使用SQLServer管理平台查看触发器信息。在SQLServer管理平台中，展开服务器和数据库，选择并展开表，然后展开触发器选项，右击需要查看的触发器名称，如图9-4所示，从弹出的快捷菜单中，选择“编写触发器脚本为create到新查询编辑器窗口”，则可以看到触发器的源代码。图9-4查看触发器 本讲稿第二十六页，共三十二页11.3.1 查看触发器（2）使用系统存储过程查看触发器。系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。其具体用途和语法形式如下。sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help触发器名称sp_helptext：用于查看触发器的正文信息。sp_helptext触发器名称sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。sp_depends触发器名称sp_depends表名本讲稿第二十七页，共三十二页11.3.2 修改触发器通过SQLServer管理平台、存储过程，可以修改触发器的正文和名称。1使用SQLServer管理平台修改触发器正文。在管理平台中，展开指定的表，右击要修改的触发器，从弹出的快捷菜单中选择“修改”选项，则会出现触发器修改窗口，如图9-5所示。在文本框中修改触发器的SQL语句，单击“语法检查”按钮，可以检查语法是否正确，单击“执行”按钮，可以成功修改此触发器。图9-5触发器修改窗口 本讲稿第二十八页，共三十二页11.3.2 修改触发器修改修改DML触发器的语法形式如下：触发器的语法形式如下：ALTERTRIGGERschema_name.trigger_nameON(table|view)WITH,.n(FOR|AFTER|INSTEADOF)DELETE,INSERT,UPDATENOTFORREPLICATIONASsql_statement;.n|EXTERNALNAME;:=ENCRYPTION&lEXECUTEASClause:=assembly_name.class_name.method_name修改修改DDL触发器的语法形式如下：触发器的语法形式如下：ALTERTRIGGERtrigger_nameONDATABASE|ALLSERVERWITH,.nFOR|AFTERevent_type,.n|event_groupASsql_statement;|EXTERNALNAME;:=ENCRYPTION&lEXECUTEASClause:=assembly_name.class_name.method_name本讲稿第二十九页，共三十二页11.3.2 修改触发器例例9-13 修改触发器。修改触发器。程序清单如下：程序清单如下：CREATE TRIGGER s_reminder ON S WITH ENCRYPTION AFTER INSERT,UPDATE AS RAISERROR(不能对该表执行添加、更新操作不能对该表执行添加、更新操作,16,10)ROLLBACK GO-下面修改触发器下面修改触发器.ALTER TRIGGER s_reminder ON S AFTER INSERT AS RAISERROR(不能对该表执行添加操作不能对该表执行添加操作,16,10)ROLLBACK GO本讲稿第三十页，共三十二页11.3.2 修改触发器2使用使用sp_rename命令修改触发器的名称。命令修改触发器的名称。sp_rename命令的语法形式如下：命令的语法形式如下：sp_rename oldname,newname本讲稿第三十一页，共三十二页11.3.3 删除触发器由于某种原因，需要从表中删除触发器或者需要使用新的触发器，这就由于某种原因，需要从表中删除触发器或者需要使用新的触发器，这就必须首先删除旧的触发器。只有触发器所有者才有权删除触发器。删除必须首先删除旧的触发器。只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法：已创建的触发器有三种方法：（1）使用系统命令）使用系统命令DROP TRIGGER删除指定的触发器。其语法形式如删除指定的触发器。其语法形式如下：下：DROP TRIGGER trigger ,.n （2）删除触发器所在的表。删除表时，）删除触发器所在的表。删除表时，SQL Server将会自动删除与该表相将会自动删除与该表相关的触发器。关的触发器。（3）在）在SQL Server管理平台中，展开指定的服务器和数据库，选择管理平台中，展开指定的服务器和数据库，选择并展开指定的表，右击要删除的触发器，从弹出的快捷菜单中选择并展开指定的表，右击要删除的触发器，从弹出的快捷菜单中选择“删除删除”选项，即可删除该触发器。选项，即可删除该触发器。本讲稿第三十二页，共三十二页