《第9章触发器的操作与管理》由会员分享，可在线阅读，更多相关《第9章触发器的操作与管理（28页珍藏版）》请在金锄头文库上搜索。
1、第第9 9章章 触发器的操作与管理触发器的操作与管理耕延宫终刨锁孩淄夺贤钱扛咀沽降蒂宇榷窥恭敷歌夺盖嫩药篡搔曝忿榆缚第9章触发器的操作与管理第9章触发器的操作与管理触发器概述触发器是一种特殊的存储过程，它在执行语言事件时自动生效。SQLServer2005包括两大类触发器：DML触发器和DDL触发器。（1）DML触发器在数据库中发生数据操作语言(DML)事件时将启用。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其他表，还可以包含复杂的Transact-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果
2、检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。（2）DDL触发器是SQLServer2005的新增功能。当服务器或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。漏笔挎吹龄讳折赤绢唁溅煤氛熟呜吩鼠姬申拌霓善谨代难沪闲酌氰吼辰琉第9章触发器的操作与管理第9章触发器的操作与管理9.1 DML触发器的创建和应用当数据库中发生数据操作语言(DML)事件时将调用DML触发器。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。DML触发器的主要优点如下：（1）DML触发器可通过数据库中的相关表实现级联更改。例如，可以在titles表的title_id列上写入一个删除触发器，以使
3、其他表中的各匹配行采取删除操作。该触发器用title_id列作为惟一键，在titleauthor、sales及roysched表中对各匹配行进行定位。（2）DML触发器可以防止恶意或错误的INSERT、UPDATE以及DELETE操作，并强制执行比CHECK约束定义的限制更为复杂的其他限制。与CHECK约束不同，DML触发器可以引用其他表中的列。（3）DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。沛拨迹白楔羔扩蘸零脊画委憎卖虐实胳邑姚赏娩广蓟淄旋滥安牌昨赌骗陛第9章触发器的操作与管理第9章触发器的操作与管理9.1.1 DML触发器创建当创建一个触发器时必须指定如下选项：（1）
4、名称；（2）在其上定义触发器的表；（3）触发器将何时激发；（4）激活触发器的数据修改语句，有效选项为INSERT、UPDATE或DELETE，多个数据修改语句可激活同一个触发器；（5）执行触发操作的编程语句。例泌噶滥差巫鹰掩整苍渭福和腮庸月守豢忻煌皮急利涅苍霖蚕蝇翁剧屯秒第9章触发器的操作与管理第9章触发器的操作与管理9.1.1 DML触发器创建DML触发器使用deleted和inserted逻辑表。它们在结构上和触发器所在的表的结构相同，SQLServer会自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。Deleted表用于存储delete
5、，update语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。Inserted表用于存储Insert或update语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到Inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。郧越鸵凶速揍证羌诌伊芽槐瘫帕耙泰埔孩泊吩婉酝陌靛咬熄拳重垫侵咨瓢第9章触发器的操作与管理第9章触发器的操作与管理9.1.1 DML触发器创建使用使用SQL Server管理平台创建触发器的过程如下：管理平台创建触发器的过程如下：在SQLServer管理平台中，展开指定的服务
6、器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如图9-1所示，右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图9-2所示。最后，单击“执行”按钮，即可成功创建触发器。图9-1新建触发器对话框 图9-2新建触发器窗口 殷端呜硅驴赛漓视表鸣屋萌漆泳溶苹绘容邹趾癣燃宁犊偿虐周蓝挎萤眺讹第9章触发器的操作与管理第9章触发器的操作与管理9.1.1 DML触发器创建使用使用CREATE TRIGGER命令创建命令创建DML触发器的语法形式如触发器的语法形式如下：下： CREATE TRIGGER schema_name.trigger_name ON t
7、able|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慰肃浮爬业存俺灾恼拐称驾矫垢序砌谊懦晃川某肩倪复芽钳素土哎斤渭琵第9章触发器的操作与管理第9章触发器的操作与管理9.1.1 DML触发器创建例例9-1示例说明示例说明inserted，deleted表
8、表的作用。执行结果如右图。的作用。执行结果如右图。程序清单如下：程序清单如下：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触发器的执行结果 纱臃抒海廉蝉痴颠钉劳磺负辈犯祷即士淳消女苍淘硼菏脏叔应朔百竿模赢第9章触发器的操作与管理第9章触发器的操作与管理9.
9、1.1 DML触发器创建例例9-2创建一个触发器，在创建一个触发器，在 s 表上创建一个插入、更表上创建一个插入、更新类型的触发器。新类型的触发器。程序清单如下：程序清单如下：CREATE TRIGGER tr_sON sFOR INSERT, UPDATE AS BeginDECLARE bh varchar(6)SELECT bh =inserted.sno FROM inserted /*获取获取插入或更新操作时的新值（学号）插入或更新操作时的新值（学号）*/End佯归马映煤梨宿统甄纪茸假沈疾唯嗓手提韧转贤观生帕尧盆似鸥能地冕燎第9章触发器的操作与管理第9章触发器的操作与管理9.1.2D
10、ML触发器的应用1. 使用使用INSERT触发器触发器INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。段中数据满足要求的标准，以确保数据的完整性。例例9-3建立一个触发器，当向建立一个触发器，当向sc表中添加数据时，如果添加的数据与表中添加数据时，如果添加的数据与s表中的表中的数据不匹配（没有对应的学号），则将此数据删除。数据不匹配（没有对应的学号），则将此数据删除。程序清单如下：程序清单如下：CREATE TRIGGER sc_ins ON sc FOR INSERT
11、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赡铂榨撬未谁姐粥近界膨胶碧饵狡全憋趾尤理哩拔唐灶送阂踞谊膘惩于得第9章触发器的操作与管理第9章触发器的操作与管理9.1.2DML触发器的应用1. 使用使用INSERT触发器触发器例例9-4创建一个触发器，当插入或更新成绩列时，该触发器检查插入的创建一个触发器，当插入或更新成绩列时，该触发器检查插入的数据是否处于设定的范围内。数据是否
12、处于设定的范围内。程序清单如下：程序清单如下：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侵承袜霄蒲继茵栖屏获胆吐殖龚谨疹州赢计场寺逐科詹核栽己寡墟庞龄禽第9章触发器的操作与管理第9章触发器的操作与管理9.1.2DML触发器的应用2. 使用使用UPDATE触发器触发
13、器当在一个有当在一个有UPDATE触发器的表中修改记录时，表中原来的记录被移动到触发器的表中修改记录时，表中原来的记录被移动到删除表中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入删除表中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入表以及被修改的表，以确定如何完成数据库操作。表以及被修改的表，以确定如何完成数据库操作。例例9-5创建一个修改触发器，该触发器防止用户修改表创建一个修改触发器，该触发器防止用户修改表s的入学成绩。的入学成绩。程序清单如下：程序清单如下：create trigger tri_s_updon sfor updateasif update（esc
14、ore）beginraiserror（不能修改入学成绩不能修改入学成绩,16,10）rollback transactionend go窍拇智蒋夯蛾歹自亥序澳泥烂贾艺汽慰呼愉葛甸护此厉嘻浩愉纱纂胸崇烩第9章触发器的操作与管理第9章触发器的操作与管理9.1.2DML触发器的应用2.使用使用UPDATE触发触发例9-6DAS数据库由存放实时数据的数据表以及存放历史数据的历史表组成。由于存放实时数据的数据表不断更新，为了保存更新过的数据，在实时表和历史表之间建立了触发器。程序清单如下：CREATETRIGGERDasD_UTRIGGERONDasDFORUPDATEASBEGINIFUpdate(T
15、V)/*数据更新*/BEGINUPDATEDasDSETUT=getdate() /*更新时间*/FROMDasD,insertedWHEREDasD.ID=inserted.IDINSERTDasDHis(ID,TV,UT)SELECTinserted.ID,inserted.TV,DasD.UTFROMDasD,insertedWHEREDasD.ID=inserted.ID/*将更新过的数据送入历史库*/ENDEND檀半嫂苏悬投程汽制絮话播甩鹰攒文沦荐临秽轰器智匆吼舍张勒蝶醋忿沙第9章触发器的操作与管理第9章触发器的操作与管理9.1.2DML触发器的应用3. 使用使用DELETE触发器触
16、发器DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除，第二种情况是执行可删除主记录的子记录的级联删除操作。例9-8建立一个与s表结构一样的表s1，当删除表s中的记录时，自动将删除掉的记录存放到s1表中。程序清单如下：CREATETRIGGERtr_delONs/*建立触发器FORDELETE/*对表删除操作ASinserts1select*fromdeleted/*将删除掉的数据送入表s1中*/GO该蜗哆拿喘勺钳澈彰围哀哩风瓮免泌材粟售沮脊祈物贮玉深又樊锗黄鹰尤第9章触发器的操作与管理第9章触发器的操作与管理9.1.2DML触发器的应用
17、3. 使用使用DELETE触发器触发器例9-9当删除表s中的记录时，自动删除表sc中对应学号的记录。程序清单如下：CREATETRIGGERtr_del_sONsFORDELETEBEGINDECLAREbhchar(5)Selectbh=deleted.snofromdeletedDeletescwheresno=bhEND桑牡绿染庄玩内矫想擒策托氛庶近毯刊跺界娜箍唁兴衫绝泉赂君弧滨休注第9章触发器的操作与管理第9章触发器的操作与管理9.2 DDL触发器的创建和应用DDL 触发器会为响应多种数据定义语言触发器会为响应多种数据定义语言 (DDL) 语句而语句而激发。这些语句主要是以激发。这些语
18、句主要是以 CREATE、ALTER 和和 DROP 开头的语句。开头的语句。DDL 触发器可用于管理任务，例触发器可用于管理任务，例如审核和控制数据库操作。如审核和控制数据库操作。DDL 触发器一般用于以下目的：触发器一般用于以下目的： （1）防止对数据库架构进行某些更改；）防止对数据库架构进行某些更改；（2）希望数据库中发生某种情况以响应数据库架构中）希望数据库中发生某种情况以响应数据库架构中的更改；的更改； （3）要记录数据库架构中的更改或事件。）要记录数据库架构中的更改或事件。仅在运行触发仅在运行触发 DDL 触发器的触发器的 DDL 语句后，语句后，DDL 触触发器才会激发。发器才会
19、激发。DDL 触发器无法作为触发器无法作为 INSTEAD OF 触发器使用。触发器使用。镣玖霖堡话垮癣隋岳扰聚挟喜量疼蓉届毫醇予灾屑臆岛冀资百娠实聘躲睫第9章触发器的操作与管理第9章触发器的操作与管理9.2.1 创建DDL触发器使用CREATETRIGGER命令创建DDL触发器的语法形式如下：CREATETRIGGERtrigger_nameONALLSERVER|DATABASEWITH,.nFOR|AFTERevent_type|event_group,.nASsql_statement;.n|EXTERNALNAME;其中：:=ENCRYPTIONEXECUTEASClause:=as
20、sembly_name.class_name.method_name烹浚卫哨硫渺涎话卑扒莎奇渡唆洼炔拱挺宫宁拭鞍答碑张庚眺逼高揖舒异第9章触发器的操作与管理第9章触发器的操作与管理9.2.2 DDL触发器的应用在响应当前数据库或服务器中处理的Transact-SQL事件时，可以激发DDL触发器。触发器的作用域取决于事件。例9-11使用DDL触发器来防止数据库中的任一表被修改或删除。程序清单如下：CREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINTYoumustdisableTriggersafetytodroporalter
21、tables!ROLLBACK航栖蔚征散凸登删烙致磊每德阎勾进玫屿黄俱两解烽喉闭垣涎枯懈萌棵适第9章触发器的操作与管理第9章触发器的操作与管理9.2.2 DDL触发器的应用例9-12使用DDL触发器来防止在数据库中创建表。程序清单如下：CREATETRIGGERsafetyONDATABASEFORCREATE_TABLEASPRINTCREATETABLEIssued.SELECTEVENTDATA().value(/EVENT_INSTANCE/TSQLCommand/CommandText)1,nvarchar(max)RAISERROR(Newtablescannotbecreated
22、inthisdatabase.,16,1)ROLLBACK熟析念薄陡垣宏得常裴欢谍迅驻御僵甚澳奥淮捧沼群浚喘铰旋邓书彻瑶吹第9章触发器的操作与管理第9章触发器的操作与管理9.3 DDL查看、修改和删除触发器9.3.1查看触发器9.3.2修改触发器9.3.3删除触发器灶妹舞炬屠杜悔恐寅砧赛恨沃谭唬疽襄汝稀豪腺任需鼎啃矿擦腮祁炔搏裸第9章触发器的操作与管理第9章触发器的操作与管理9.3.1 查看触发器如果要显示作用于表上的触发器究竟对表有哪些操作，必须查看触发器信息。在SQLServer中，有多种方法可以查看触发器信息，其中最常用的有如下两种：（1）使用SQLServer管理平台查看触发器信息；（
23、2）使用系统存储过程查看触发器。踪钦字向素住娟瑶擂议晌率脆耕卡贿调牟钝碴鸵肺举顷鬃掀横蚕矾两宽纶第9章触发器的操作与管理第9章触发器的操作与管理9.3.1 查看触发器（1）使用SQLServer管理平台查看触发器信息。在SQLServer管理平台中，展开服务器和数据库，选择并展开表，然后展开触发器选项，右击需要查看的触发器名称，如图9-4所示，从弹出的快捷菜单中，选择“编写触发器脚本为create到新查询编辑器窗口”，则可以看到触发器的源代码。图9-4查看触发器 伎沂晦殃千鲍稽逞呈祖霍刻摇啊疯亏沧罚彤美稀谐竖赋缠根厌记涨呛曙阮第9章触发器的操作与管理第9章触发器的操作与管理9.3.1 查看触发
24、器（2）使用系统存储过程查看触发器。系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。其具体用途和语法形式如下。sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help触发器名称sp_helptext：用于查看触发器的正文信息。sp_helptext触发器名称sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。sp_depends触发器名称sp_depends表名诗扁膝晰蛰缀吵絮徐辗扑衬洪忱贫塌魁庞孕溃豆安摔煎柔吗悦捍斑粒质裤第9章触发器的操作与管理第9章触发器的操作与管理9
25、.3.2 修改触发器通过SQLServer管理平台、存储过程，可以修改触发器的正文和名称。1使用SQLServer管理平台修改触发器正文。在管理平台中，展开指定的表，右击要修改的触发器，从弹出的快捷菜单中选择“修改”选项，则会出现触发器修改窗口，如图9-5所示。在文本框中修改触发器的SQL语句，单击“语法检查”按钮，可以检查语法是否正确，单击“执行”按钮，可以成功修改此触发器。图9-5触发器修改窗口 芝营使谆况臣迈硕撩腕物玻叹姆材蹭壹馆兜怖吮窿稽潍执秦萄宅醉柯顽梗第9章触发器的操作与管理第9章触发器的操作与管理9.3.2 修改触发器修改修改DML触发器的语法形式如下：触发器的语法形式如下：AL
26、TERTRIGGERschema_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|AFTE
27、Revent_type,.n|event_groupASsql_statement;|EXTERNALNAME;:=ENCRYPTION&lEXECUTEASClause:=assembly_name.class_name.method_name涯腊甩捕寂坟喀五魄厚衔桌姜杉秀蛆丈饿曝瘤德豢铰往催侠妹沽晶凶狸镭第9章触发器的操作与管理第9章触发器的操作与管理9.3.2 修改触发器例例9-13 修改触发器。修改触发器。 程序清单如下：程序清单如下： CREATE TRIGGER s_reminder ON S WITH ENCRYPTION AFTER INSERT, UPDATE AS RAIS
28、ERROR (不能对该表执行添加、更新操作不能对该表执行添加、更新操作, 16, 10) ROLLBACK GO - 下面修改触发器下面修改触发器. ALTER TRIGGER s_reminder ON S AFTER INSERT AS RAISERROR (不能对该表执行添加操作不能对该表执行添加操作, 16, 10) ROLLBACK GO细甥羽符致淌粪炳俱矾院伦讽盼像穆这浓剃备蔑粳孰垛鳖糕歉艘惜唯尖帆第9章触发器的操作与管理第9章触发器的操作与管理9.3.2 修改触发器2使用使用sp_rename命令修改触发器的名称。命令修改触发器的名称。 sp_rename命令的语法形式如下：命令
29、的语法形式如下：sp_rename oldname,newname衣眯碟蛔戍栓岳郧曙龙伸吴啊卉胞贞筛杭晦譬泡陛瀑忍盗劳憾楼请铆兽沏第9章触发器的操作与管理第9章触发器的操作与管理9.3.3 删除触发器由于某种原因，需要从表中删除触发器或者需要使用新的触发器，由于某种原因，需要从表中删除触发器或者需要使用新的触发器，这就必须首先删除旧的触发器。只有触发器所有者才有权删除触发这就必须首先删除旧的触发器。只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法：器。删除已创建的触发器有三种方法： （1）使用系统命令）使用系统命令DROP TRIGGER删除指定的触发器。其语法删除指定的触发器。
30、其语法形式如下：形式如下： DROP TRIGGER trigger ,.n （2）删除触发器所在的表。删除表时，）删除触发器所在的表。删除表时，SQL Server将会自动删除将会自动删除与该表相关的触发器。与该表相关的触发器。 （3）在）在SQL Server管理平台中，展开指定的服务器和数据库，选管理平台中，展开指定的服务器和数据库，选择并展开指定的表，右击要删除的触发器，从弹出的快捷菜单中选择并展开指定的表，右击要删除的触发器，从弹出的快捷菜单中选择择“删除删除”选项，即可删除该触发器。选项，即可删除该触发器。尊赁曹储涣俐苇等款绵盘娜系绦度馏晨舍尔垦骨扶型凰失勿敷队劲羽再羊第9章触发器的操作与管理第9章触发器的操作与管理
