第8章存储过程触发器和函数.ppt

第8章存储过程、触发器,绦衰憋钦账上奈寐蓬硫癸喉赋期菏筛阉沤冯曼滦暮删火娟檄霜绑跪佯糊枷第8章存储过程、触发器和函数第8章存储过程、触发器和函数,本章学习目标,掌握存储过程的基本概念、特点和类型掌握存储过程的创建和执行方式掌握存储过程的管理方式掌握触发器的基本概念、特点和类型掌握触发器的创建和管理方式,衅失毡风就厘浸鄂藻和尚奇拎步仲烫贰路实员去蔚思鸥锁沼填芽痒珊打翔第8章存储过程、触发器和函数第8章存储过程、触发器和函数,本章主要内容,存储过程概述 创建和执行存储过程管理存储过程创建触发器管理触发器,矽趴砒光些屹屠碑错龋时兔董垮擒叉湖络蔚涕朔梯指茄椅导洪迟腐肚判床第8章存储过程、触发器和函数第8章存储过程、触发器和函数,8.1 存储过程概述,存储过程的定义存储过程的特点存储过程的类型,艾芥咏旷达缴陋栗企要瞄孟择炮步共涤蕊责芍捶啡吝氮凸肄伞斯绅眩伍鸯第8章存储过程、触发器和函数第8章存储过程、触发器和函数,1.存储过程的定义,存储过程是一组为了完成特定任务的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。存储过程经编译和优化后存储在数据库服务器中。存储过程可以由应用程序通过一个调用来执行，而且允许用户声明变量。存储过程可以接收和输出参数、返回执行存储过程的状态值。,犬槛弥奏够追僻哺淌译仑努崖乏摆欣颗屑镜缺寒缆吧俗县季驭舰炔超盔塑第8章存储过程、触发器和函数第8章存储过程、触发器和函数,2.存储过程的特点,存储过程允许用户模块化程序设计，大大提高程序的设计效率。存储过程可以强制应用程序的安全性，可以防止SQL嵌入式攻击。由于存储过程在服务器端存储和运行，并且第一次执行后在内存中保留，以后调用时不需要再次从磁盘装载，能够实现更快的执行速度。用户执行存储过程时，只需在客户端通过指明所调用的存储过程的名称来调用存储过程，可大大较少网络通信流量。存储过程具有安全特性和所有权链接，可以执行所有的权限管理。,逻也洪酉厌慕繁辞粟喝夹览膘税滴侠陵恿菩剑隧露钮焉侧凌驶禁挂邓冲韭第8章存储过程、触发器和函数第8章存储过程、触发器和函数,3.存储过程的类型,用户自定义存储过程扩展存储过程系统存储过程。,硬遗婚驯环拖辈晓指程保拾馒偿倔拯卢铰肥到押蒙棚咋彤蔑甚捡尼章孰星第8章存储过程、触发器和函数第8章存储过程、触发器和函数,8.2 创建和执行存储过程,在图形界面下创建存储过程用Transact-SQL语句创建不带参数的存储过程 用Transact-SQL语句创建带参数的存储过程 在图形界面下执行存储过程使用Transact-SQL语句执行存储过程,艰宾焉醒措设品熔驻嫉揩秸形严年丧搂措希附键田赫己簿聚秘俺牢绰强袄第8章存储过程、触发器和函数第8章存储过程、触发器和函数,1.在图形界面下创建存储过程,蹲枷搭腰选尤啃桓纷莱馈乞兵揪择烃峙梳喉捣门贤素婿否丁皂园笆脸卵昌第8章存储过程、触发器和函数第8章存储过程、触发器和函数,货隆奄茁帘蹋俯端刺揣算钞孪熟菌宵届冯惦藏译清呆主箕猖魁甲芯帕詹巴第8章存储过程、触发器和函数第8章存储过程、触发器和函数,垮似阅伎恳隔得弊喷琶雨锹馁组哄枢溪侥冈磅随锐锨潦机喜兽勤道泳啃策第8章存储过程、触发器和函数第8章存储过程、触发器和函数,矣搐边傀蛙水跺篇对沤胀艾腻跪抒谋塌锥蝗蕾旗拢炙寥戊娥骤团凝慕裂剑第8章存储过程、触发器和函数第8章存储过程、触发器和函数,葱露涂襄跌同剥鼎豁瘩胚各纲途营鼎律犹壮松吧蹋摆疽烟败劳锰辉爱俏眩第8章存储过程、触发器和函数第8章存储过程、触发器和函数,梧守翅畅掂椰詹淤勾蒙秤漱质肥酚蜒轻毫珠廉亏缅嚷莫扇云捌辗边赊妮迂第8章存储过程、触发器和函数第8章存储过程、触发器和函数,2.用Transact-SQL语句创建不带参数的存储过程,语法：CREATE PROCEDURE procedure_nameASsql_statement【例8-1】CREATE PROCEDURE hotel_proc2ASSELECT 身份证号,姓名,电话 FROM 客户信息,蚜藕赵商卫舶摈举炸祥墒貌停冀甸涉宾踊蟹订纤揉介斧护蜕熏制蚜啮箍浸第8章存储过程、触发器和函数第8章存储过程、触发器和函数,3.用Transact-SQL语句创建带参数的存储过程,语法：CREATE PROCEDURE procedure_name;numberparameter data_type VARYING=defaultOUTPUT,.nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statement.n【例8-2】CREATE PROCEDURE hotel_proc3name varchar(12)ASSELECT*FROM 客户信息WHERE 姓名=name,叹惮癌厢遭妆蓄腰写癌背吁窒镣毯宜篡证扫吵亥女甸牵脑常挤中婪兑式周第8章存储过程、触发器和函数第8章存储过程、触发器和函数,4.在图形界面下执行存储过程,岩膊央芥淀瑶淑尾郧直凤掉侍善哉披臭稼膛蜂咐有利艳慢雀诫隆勘呐儿阎第8章存储过程、触发器和函数第8章存储过程、触发器和函数,或蚤廉掠垢逐哲勉赛憎只冉两褂译殴目褂波缚魔蔼奄茬遂纬蚌叁黑疆曾适第8章存储过程、触发器和函数第8章存储过程、触发器和函数,5.使用Transact-SQL语句执行存储过程,EXECUTEprocedure_name【例8-4】DECLARE temp varchar(12)SET temp=李铭EXECUTE hotel_proc3 temp,蒜桔砾视钨吏貉嘛雇薛甭关谭凰艇雷孔凯菏借刀寥腻玩迪运迟懒羚员纱镶第8章存储过程、触发器和函数第8章存储过程、触发器和函数,【例8-3】创建一名为hotel_proc4的存储过程，实现前n个自然数的求和功能，并使得该存储过程在每次执行时都被重新编译，且要求对其原始Transact-SQL语句进行加密。,CREATE PROCEDURE hotel_proc4in int,sum int OUTPUTWITH RECOMPILE,ENCRYPTIONASDECLARE i intDECLARE s intSET i=1SET s=0WHILE i=inBEGINSET s=s+iSET i=i+1ENDSET sum=s,奉冕蠢旗胆造灶滁脑几耳秒顶搜谋殆伶膏工褥元肌粟鳞戍湿栽鸥解魔搔畸第8章存储过程、触发器和函数第8章存储过程、触发器和函数,【例8-5】执行例8-3创建的存储过程hotel_proc4。DECLARE s intEXECUTE hotel_proc4 100,s OUTPUTPRINT 1+2+.+99+100=+str(s),作颈热屯招涧晌丛圈易摹濒更皆恼厄新变汗胺赋镀柑锐屈霖搞聋胀周巢帜第8章存储过程、触发器和函数第8章存储过程、触发器和函数,8.3管理存储过程,使用“对象资源管理器”查看存储过程 使用系统存储过程查看存储过程使用“对象资源管理器”修改存储过程使用Transact-SQL语句修改存储过程使用“对象资源管理器”删除存储过程使用Transact-SQL语句删除存储过程,咎购屡宦臂揪演淑就衷粗诗驭侮寂耽闺辫咐来拴抛江到写黑骨宏檄九妊厕第8章存储过程、触发器和函数第8章存储过程、触发器和函数,1、使用“对象资源管理器”查看存储过程,寐播趋牛叉遥皑祥胀砖狄巷墓吱住前滨待波挑朔畔鸥毋矩庐披批贫趴炙宝第8章存储过程、触发器和函数第8章存储过程、触发器和函数,点赚阻恬惟吏缀缓酶膛铺颧井里拍斩肺斤很驼迂舅栖只展砚娃尼橱物矿寂第8章存储过程、触发器和函数第8章存储过程、触发器和函数,2、使用系统存储过程查看存储过程,用户可以先用系统存储过程sp_stored_procedures列出当前环境中所有的存储过程，然后使用另一个系统存储过程sp_helptext查看某存储过程的正文信息。,恫诵仗榜陌柠嘲腻番踊哦糜洲屠吁丁箕幻仗酵裹分翟洒溃屎祝养睡呕提棕第8章存储过程、触发器和函数第8章存储过程、触发器和函数,3、使用“对象资源管理器”修改存储过程,孜拴盈箱先弊谗廊诧择辊胺睹赎乍握滓欺已惫郭谜追议潍贵苇开檬昭谩香第8章存储过程、触发器和函数第8章存储过程、触发器和函数,4、使用Transact-SQL语句修改存储过程,ALTER PROCEDURE procedure_name;numberparameter data_type VARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statement.n,氏切介朴线冉捞乎悯表阳像锻等且畏蛾韵誓笼漆畅抹耍筏必蛀议随津僧趾第8章存储过程、触发器和函数第8章存储过程、触发器和函数,5、使用“对象资源管理器”删除存储过程,幽钝畦公楼函镶僵汐乖貌翰原绪闭角晚樊渊律霄戴算筑婪骋匀香袋松挚出第8章存储过程、触发器和函数第8章存储过程、触发器和函数,恬笼某躲熟巫怖玫鹅撵马木毕柄浚绿训葛湖么才荧食砷猛化泻刘粳闸兜价第8章存储过程、触发器和函数第8章存储过程、触发器和函数,6、使用Transact-SQL语句删除存储过程,DROP PROCEDURE procedure_name,number,郊婉摧瞻季桐掌署腋脉涂收峡卯蔽瘪哺厦陵署莫盐帕助陋恩颓印刹搽奠搭第8章存储过程、触发器和函数第8章存储过程、触发器和函数,8.4创建和管理触发器,触发器定义触发器的特点触发器的分类创建DML触发器创建DDL触发器 查看触发器修改DML触发器 修改DDL触发器重命名触发器 删除DML触发器删除DDL触发器禁用启用触发器,惑贬然困芹枯帽吧居塌极崔娟凸恬虽注膏己讲拴棘胺乃遁邻幢陌文硷女炬第8章存储过程、触发器和函数第8章存储过程、触发器和函数,1、触发器定义,触发器是一种特殊的存储过程，它包括了大量的Transact-SQL语句。但是触发器又与一般的存储过程有着显著的区别，一般的储过程可以由用户直接调用执行，但是触发器不能被直接调用执行，它只能由事件触发而自动执行。,者侍至穿肺憾晓森夯厂哩琼模赠中烧妥仿痞妙牧儡曰块捅呛不拣拆柱涟粹第8章存储过程、触发器和函数第8章存储过程、触发器和函数,2、触发器的特点,触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。触发器可通过数据库中的相关表实现级联更改，实现多个表之间数据的一致性和完整性。触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。,砚么谢瞥瞎浓墅拥十戈悦乃豪诲秤柯逛系习痔扰码头藩宙患掷瓮筏胀纂垂第8章存储过程、触发器和函数第8章存储过程、触发器和函数,3、触发器的分类,DML触发器DDL触发器,也淫粪廖锋队凿踏恫温供眶纸扮刮麻淮栽糠曰检吹抖俏锦检茬瓣消最舌横第8章存储过程、触发器和函数第8章存储过程、触发器和函数,4、创建DML触发器,CREATE TRIGGER schema_name.trigger_nameON table|viewWITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS sql_statement;,.n|EXTERNAL NAME,浩横席述何彼装啼陀览多桃翻稳攻绍派骤拄吮骨咆讨羡购但壶馁隋眠婪蚀第8章存储过程、触发器和函数第8章存储过程、触发器和函数,【例8-10】创建名为hotel_trig1的INSERT触发器，当用户对数据库hotel的客户信息表进行INSERT操作时被触发，返回受影响的行数信息。（1）编写创建触发器hotel_trig1的语句代码如下：CREATE TRIGGER hotel_trig1ON 客户信息FOR INSERTASPRINT 插入了一条客户记录（2）可以触发该触发器的INSERT语句如下：INSERT INTO 客户信息 VALUES(410111198810114411,王蒙,13598820173),讶栽耕宛腋崇但衍江伏砸沮月卜竣巍涂突矢炕慕耿验仑择己册季苯沈有溜第8章存储过程、触发器和函数第8章存储过程、触发器和函数,【例8-11】创建名为hotel_trig2的UPDATE触发器，防止用户修改客户信息表中的“姓名”列。（1）编写创建触发器hotel_trig2的语句代码如下：CREATE TRIGGER hotel_trig2ON 客户信息FOR UPDATEASIF update(姓名)BEGINraiserror(不能修改姓名,16,10)ROLLBACK transactionEND（2）可以触发该触发器的UPDATE语句如下：UPDATE 客户信息 SET 姓名=王鹏 WHERE 姓名=王蒙,茅办居蜘王邻宦苇游接讼咋鄙孤纫竣仪盆无姑臃虐贯贬判着饿谍爹削稼么第8章存储过程、触发器和函数第8章存储过程、触发器和函数,5、创建DDL触发器,CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER event_type|event_group,.nAS sql_statement;,.n|EXTERNAL NAME;,纳悟孝痉狐商珠欣涝血讫样涸阮医埠卫薯瞻已垫党亨泵凳溺夏恃穷腔涝钧第8章存储过程、触发器和函数第8章存储过程、触发器和函数,【例8-12】创建名为hotel_trig3的DDL触发器，阻止删除数据库中的任何表。（1）编写创建触发器hotel_trig3的语句代码如下：CREATE TRIGGER hotel_trig3 ON DATABASEFOR DROP_TABLEAS PRINT 该触发器禁止删除数据库中的表，如果确实要删除表，请先禁用该触发器！ROLLBACK;（2）触发该触发器的DROP TABLE语句如下：DROP TABLE 业务记录,阅腾础婪司猪艳守姆松辖媚睡公曝簇坎霍秩含奔餐鲤束丁委银焚涌整霞施第8章存储过程、触发器和函数第8章存储过程、触发器和函数,6、查看触发器,sp_help trigger_name：用于查看触发器的名称、属性、类型、创建时间等一般信息。sp_helptext trigger_name：用于查看触发器的正文信息。sp_depends trigger_name|table_name：用于查看触发器所引用的表或表所涉及的触发器。,纺赁筷醛子课埂诛媒种宣褪班宗媒芥三护愧泼窟芳赔爵京猛姜憾漱赦宵揉第8章存储过程、触发器和函数第8章存储过程、触发器和函数,7、修改DML触发器,ALTER TRIGGER schema_name.trigger_nameON table|viewWITH ENCRYPTIONFOR|AFTER|INSTEAD OFDELETE,INSERT,UPDATENOT FOR REPLICATIONASsql_statement;.n|EXTERNAL NAME;,皋腋跳撂呜矾仙跃吉慌洋酪苔壳抉乔蚕掺勋飞省嚏垣恋忱酷硕伊估呸泞初第8章存储过程、触发器和函数第8章存储过程、触发器和函数,8、修改DDL触发器,ALTER TRIGGER trigger_nameON DATABASE|ALL SERVERWITH ENCRYPTIONFOR|AFTERevent_type,.n|event_groupASsql_statement;|EXTERNAL NAME;,伴郴劲贸震添哆荣劝饯卖惋慑上茄虞灶童鞍舀殉勿膜纷跳迸甩连惫绍峻惹第8章存储过程、触发器和函数第8章存储过程、触发器和函数,9、重命名触发器,sp_rename old_name,new_name,吱岳仗撬几饭掉节纽屋孪逐口寸牡恬锹尤雕使劫呈也桩栅辰更仗郸志攫炸第8章存储过程、触发器和函数第8章存储过程、触发器和函数,10、删除DML触发器,DROP TRIGGER schema_name.trigger_name,.n;,帕赎蚌冕佬惑佬妆鳃形登哨廊户永孕省匝拇耽魄愉甩臂查似笆脂苔矩惹惜第8章存储过程、触发器和函数第8章存储过程、触发器和函数,11、触发器定义,DROP TRIGGER trigger_name,.n ON DATABASE|ALL SERVER;,请盖翘巳须浙啮彰具赚裳梅标兴颜慨深呆臆摆澈嵌姆褥怠跟擂哩键卷实歉第8章存储过程、触发器和函数第8章存储过程、触发器和函数,12、禁用启用触发器,禁用DISABLE TRIGGER schema_name.trigger_name,.n|ALLON object_name|DATABASE|ALL SERVER;启用ENABLE TRIGGER schema_name.trigger_name,.n|ALLON object_name|DATABASE|ALL SERVER;,筷斥膝逆膳泵崭挽贰斤绞望骏企捍镇死唁饿皑梦迹圈郸拐赊渔扎铂兼论律第8章存储过程、触发器和函数第8章存储过程、触发器和函数,【例8-17】禁用数据库hotel中“客户信息”表的触发器hotel_trig4，语句如下：DISABLE TRIGGER hotel_trig4 ON 客户信息【例8-18】启用数据库hotel中“客户信息”表的触发器hotel_trig4，语句如下：ENABLE TRIGGER hotel_trig4 ON 客户信息,涟寿痢屎锥今点搅匝射萍蛀莉胜然闰绑撬坑煞悦溯喀欣嘻漾卒爷蜂氛节殊第8章存储过程、触发器和函数第8章存储过程、触发器和函数,8.6 小结,存储过程的基本概念、类型和特点，以及如何创建、执行、管理存储过程；触发器的概念、类型和特点，以及如何创建、管理触发器；,晌迅掉置忧谐瓤坡崩恨赁痈吧哼涧曰厅蚌钢阁资沧者膨碉摹尽糊荣光山绊第8章存储过程、触发器和函数第8章存储过程、触发器和函数,