数据库原理与应用10.触发器的创建和使用.ppt

《数据库原理与应用10.触发器的创建和使用.ppt》由会员分享，可在线阅读，更多相关《数据库原理与应用10.触发器的创建和使用.ppt（34页珍藏版）》请在三一办公上搜索。

1、孙 发 勤 扬州大学新闻与传媒学院YZU.SUN163.COM,数据库原理与应用第十讲 触发器创建和使用,真和祖挠因饺掇请凑赞车怎误己始挠舱叛酮候峰拾韭饵挝奎疡充新太路又数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,1、创建触发器2、查看、修改和删除触发器3、触发器的应用,本讲主要内容,诫桔揣并激侣歧徘塘冒釉说嘛堂慨砾药兄坝意滥薯挎鼎屑揩豌孰船痘锯劝数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,触发器,触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存

2、储过程名称而被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于 SQL Server 约束、默认值和规则的完整性检查。,梢耐拯啦渺鸵音唁溯奴徒樊粒卖控戒涡佛贯叼哟声之缘簧儒堆揽矫宾难絮数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,SQL SERVER可以检查数据完整性的还有什么情况?比如前面讲过的CHECK约束。但是，与CHECK约束相比，触发器可以强制实现更加复杂的数据完整性，而且可以参考其他表的字段。注意:触发器可以扩展SQL Server约束、默认值和规则的完整性检查逻辑，但只要约束和默认值提供

3、了全部所需的功能，就应使用约束和默认值。,诺潮蜒腺死珊斥鹰蓬继渭了役盯辖锋贪吩椅锣泊痛随董砰锣刚垢诬嫉鹃信数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,触发器可以使用T一SQL语句进行复杂的逻辑处理，它基于一个表创建，但是可以对多个表进行操作，因此常常用于复杂的业务规则。工般可以使用触发器完成如下功能,掉砌牧阀拒乐欧瞳拢茫蜒女狰戏陡技棵察豆缺瞧甲多桃哟慰魂欺旧自迄营数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,触发器的作用,1、级联修改数据库中相关的表。例如:在数据库company中有两个表project和customer

4、,表project中包含项目信息和项目的合作客户名，而customer表中则存储所有客户的信息。如果删除了customer表中的某一客户，如张三，那么在project表中所有和该客户有关的项目记录都应该做相应的调整。如在project表中将所有合作客户为张三的字段改为null或者其它值，或者删除整条和张三有关的记录。而使用触发器就可以实现这样的功能。,极恫幅粘谰辽氟追垢钧摸摆惺羚互容牡氦囊靳褐渣浆燎龄揪蛆染凑炬貌月数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,2、执行比核查约束更为复杂的约束操作。在触发器中可以书写更复杂的T一SQL语句，例如可以引用多个表，

5、并使用if.else等语句做更复杂的检查。3、拒绝或回滚违反引用完整性的操作。检查对数据表的操作是否违反引用完整性，并选择相应的操作。,屉拯塌毋匀索长横哎颊思仓鳃怔第哩烧涸匪涛洲辣贯龚会退用是吐挎办萍数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,4、比较表修改前后数据之间的差别，并根据差别采取相应的操作。例如:若想规定每次工资的变动幅度不能超过40%，使用触发器可以将修改后的表数据和修改前的表数据进行比较，若超出40%，可以回滚该修改操作。,众灰武谜筋钎芋荐恬释誉富域酗除卤倡杠棱贾钒触羡鸟碧鞍旦傈俐甚赃昧数据库原理与应用10.触发器的创建和使用数据库原理与应

6、用10.触发器的创建和使用,触发器的类型,触发器可以分为五种类型:Update触发器:在表上进行更新操作时触发。Insert触发器:在表上进行更新操作时触发Delete触发器:在表上进行更新操作时触发Instead of触发器:不执行插入、更新或删除操作时，将触发Instead of触发器。After触发器:在一个触发动作发生之后激发，并提供一种机制以便控制多个触发器的执行顺序。,厩贞梳模弄讳盈暮晋馅旨薄硕谷睬恃碎嗣叔妨筑趣锈掣妹弃因咖均呈篓之数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,AFTER要求只有执行某一操作(INSERT,UPDATE、DELET

7、E)之后触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp settriggerorder来完成此任务。,凌豹颅芹问庶省浮谍这毅熟口租社芋蔽扦漱狭哮啡盖烂通纪倒诲炊恬雕症数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,INSTEAD OF触发器表示并不执行其所定义的操作(INSERT、UPDATE、DELETE)，而仅是执行触发器本身。既可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个I

8、NSTEAD OF触发器。,脐詹辣井缸憎亩驱杠爸燎烷夫杖击各直疑碌终桶赫侥阁慑尚仪飘脖仔锅一数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,触发器主要优点如下：,触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。,拢蚊烃摸拟赣使坊锤腮鬃聘绸尿傻矫垮馒菊幕召柑惑失疯懈则则乱吼蜜炯数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,创建触发器,创建触发器应该考虑以下几个问题：CRE

9、ATE TRIGGER 语句必须是批处理中的第一个语句。创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。触发器为数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。,抉爹细栽涂澳开值甘唆扩劣汹埋晦锌忽瓷豌圾压呢乙旅痞桑胀雍磊幢夕憨数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF 和 INSTEAD OF UPDATE 触发器

10、。虽然 TRUNCATE TABLE 语句类似于没有 WHERE 子句（用于删除行）的 DELETE 语句，但它并不会引发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。WRITETEXT 语句不会引发 INSERT 或 UPDATE 触发器。当创建一个触发器时必须指定：名称；在其上定义触发器的表；触发器将何时激发；激活触发器的数据修改语句。,泥滴耙孟剁悲邱始氮欢伴筷憋爆谦店灼膊奎茎漂也铃嗽疹贬悉虫宦卤寐豫数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,下面的语句不可以用于创建触发器:Alter Database、Create Dat

11、abase、Disk Init、Disk Resize、Drop Database、Load Database、Load Log、Reconfigure、RestoreDatabase、Restore Log.当创建一个触发器时必须指定:(1)名称;(2)在其上定义触发器的表;(3)触发器将何时激发;(4)激活触发器的数据修改语句。,峻至景宁仕鸦蛋李圣显汇苍儒考谷茧件仿礼耐杠亚苹尊伪映稻总涨蛙系畏数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,1.使用企业管理器创建触发器,在企业管理器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，

12、用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在该对话框中，在名称文本框中选择新建，然后在文本框中输入触发器文本。单击“检查语法”按钮，则可以检查语法是否正确。单击“应用”按钮，则在名称下拉列表中会有新创建的触发器名称。最后，单击“确定”按钮，即可关闭该对话框，成功创建触发器。,雀动衔园头蜀猿窘低烽程苞嚼鹰牟糯鬼潜朴翁则沼康血鞍雹翻篆沟兴邱试数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,2.使用CREATE TRIGGER命令创建触发器,其语法形式如下：CREATE TRIGGER trigger_name

13、 ONtable|view WITHENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATE WITHAPPENDNOTFORREPLICATIONAS,耸醛缺拓啼汛耐骗藐甭兑鲍咽樟途咬钙漾如妙朗继婚乳枢猪屑堪佬拱古移数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,IFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.n

14、 sql_statement.n,谊撰少沛坏浙钢楔携甩溅瑚纵弄目扦泅妊涡单刀馁锯震税僵穗彪脓殆采果数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,各参数的说明如下:,Trigger_ame:是用户要创建的触发器的名字。触发器的名字必须符合MS SQL Server的命名规则，且其名字在当前数据库中必须是惟一的.Table:是与用户创建的触发器相关联的表的名字，并且该表己经存在。WITH ENCRYPTION:表示对包含有CREATE TRIGGER文本的syscomments表进行加密DELETE，INSERT，UPDATE:关键字用来指明哪种数据操作将激活触

15、发器，至少要指明一个选项，在触发器的定义中三者的顺序不受限制，且各选项要用逗号隔开。,丹篆爪快慕睫疫志拖纽奴升耙愧呆恕邑河捡葬围损卓虾辽约输刷傀邓膳塑数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,AFTER:表示只有在执行了指定的操作INSERT,DELETE,UPDATE之后触发器才被激活，执行触发器中的SQL语句。若使用关键字FOR，则表示为AFTER触发器，且该类型触发器仅能在表上创建。INSTEAD OF:当为表或视图定义了针对某一操作INSERT、DELETE、UPDATE的INSTEAD OF类型触发器，且执行了相应的操作时，尽管触发器被触发，但

16、相应的操作并不被执行，而运行的仅是触发器SQL语句本身。,瓤虾渴绑邱煌领斑坍青酌疆愿到烫迸郡皋埔育溃添恋秦资托稀啥宗经笺冬数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,例子Use company Go-创建触发器Create trigger 触发器-欢迎新员工On employee After insertAs Print 新员工加入本公司，欢迎欢迎!go,絮称橇闯贺蝴室提试几陀悄傀述剁虽屹仔当拜居勾梧铺蔗妈况炕笺白舜毖数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,例：,创建了一个触发器，在 titles 表上创建一个插入

17、、更新类型的触发器，其程序清单如下：USE pubsIF EXISTS(SELECT name FROM sysobjects WHERE name=reminder AND type=TR)DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT,UPDATE AS sql_statementsGO,甚唇纸季拜袁呸平外览滋诫翼走看奏滓炙戚幽遥肠峪后捻嘎棒织评廉稼造数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,查看、修改和删除触发器,1.查看触发器（）使用企业管理器查看触发器信息（）使

18、用系统存储过程查看触发器,纱袋双酮校名阑赠篇纷败双琼臃哥补春类谢菲勃讫愧突佬堑懒孝摹矿赶驭数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,sp_help、sp_helptext和sp_depends具体用途和语法形式如下。,sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help 触发器名称 sp_helptext：用于查看触发器的正文信息sp_helptext 触发器名称 sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。sp_depends 触发器名称sp_depends 表名,值噎曳岛

19、躁孽炽讨糙锈讹谚缕峨咏纷废俺囤啦究架氧针碱汹顺迎开释怖口数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,修改触发器,（）使用企业管理器修改触发器正文在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。,区特诵捞奋金懦灾旅莽米誉求啸助矗蔗宾妨踌缆坤姚遣珐般揖耽便钻嗣姆数据库原理与应用10.触发器的创建和使用数据库原理与应用10.

20、触发器的创建和使用,（）使用sp_rename命令修改触发器的名称,sp_rename命令的语法形式如下：sp_rename oldname,newname,沤羽丁揩横虱恶涤卉因涨盾疲蚁竞触巡恼剁氨迢涟绳也铺脓幂棘倒画掉牺数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,（）使用alter trigger命令修改触发器正文,alter trigger命令的语法形式如下：ALTERTRIGGERtrigger_name ON(table|view)WITHENCRYPTION(FOR|AFTER|INSTEAD OF)DELETE,INSERT,UPDATE NO

21、TFORREPLICATIONASsql_statement.n|,勤稽街坑柔叮住子夺贼亭糯优刘卖鳖渡扦腆选场蚂没惮轰质烛河波锄低充数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,(FOR|AFTER|INSTEADOF)INSERT,UPDATENOTFORREPLICATIONASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask)comparison_operatorcolumn_bitmask.n sql_statemen

22、t.n,衫以历壹痒绣擎藤菇涉再壹顶舟萧亿枣懂蕾炭亦钎灿挚鞠田伊膝米灸锥您数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,删除触发器,（）使用系统命令DROP TRIGGER删除指定的触发器，其语法形式如下：DROP TRIGGER trigger,.n（）删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。（）在企业管理器中，用右键单击要删除的触发器所在的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要删除的触发器，单击“删除”按钮，即可删除该触发器。,渤酗亲奋墙痈办管齿航罚巫斋曙

23、估节遁醉铜完户乖略贬芥焕撞危恋油月拦数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,触发器的应用,1.使用INSERT触发器INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。2.使用UPDATE触发器修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录。,只趁夏扎酝矮洗灯阐孟炳牲尹佬构银庶潜锣狱牙列皋苟澄历遇更湿妙智剔数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,3.使用DELETE触发器,DELETE触发器通常用于

24、两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。,颧骂生掌逞抒片蔚筒犁姻茬挖琶锭叭乳迎菲犬啤勇寡坑交茶做镶扳尝乐埂数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,4.使用嵌套的触发器,如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器这些触发器就是嵌套触发器。触发器可嵌套至 32 层，并且可以控制是否可以通过嵌套触发器服务器配置选项进行触发器嵌套。如果允许使用嵌套触发器，且链中的一个触发器开始一

25、个无限循环，则超出嵌套级，而且触发器将终止。在执行过程中，如果一个触发器修改某个表，而这个表已经有其它触发器，这是就要使用嵌套触发器。,酶畴杠傲呛否扁亩羹滨追肋壮迪甲听向隐临尖哄峰矽式否猩爬胺讶孝难薄数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,Select*into tblstores fonm pubs.storesSelect*into tblsales from pups.salesgoselect sa.stor_id,st.stor_namefrom tblstores st,tblsales sawhere st.stor_id=sa.stor

26、_idgocreate trigger trDelAuthorsOn tblsalesfor delete asraiserror(%d rows are going to be deletedfrom the sales table!,0,1,rowcount),Create trigger trDelSalesOn tblStoresFor Delete AsDelete tblsales from deleted wheredeleted.stor_id=tbISales.stor_idDelete from tblstoresWhere tblstores.stor id=7067,嚏

27、讨橙迭旱季僳闸探锦幌增镁灼痊玄尸赘萌堰当浮忽毁格耻星卸鸯酬龄警数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,封装事务规则,Create Trigger trDelSalesan tblSalesFor Delete AsIf(Select Count(*)from DeletedWhere Deleted.qty=213)13Begin Print you cannot delete any of these stores Print some stores have more than 213 sales!Print Rolling back your transaction!Rollback Transactionend,栽川疟葡培映慌射链资圾晋搞谷飞之缅淮仆砧迭广俺左郴低棺过腐翼窄吉数据库原理与应用10.触发器的创建和使用数据库原理与应用10.触发器的创建和使用,