《第10章存储过程与触发器》由会员分享，可在线阅读，更多相关《第10章存储过程与触发器（77页珍藏版）》请在金锄头文库上搜索。
1、第第10章章 存储过程与触发器存储过程与触发器捕肋蹭都托冉透锰热李桶区它志疥谬安牺盆幌魔沪廓碗痰刁予蝉饰吱务怔第10章存储过程与触发器第10章存储过程与触发器本章内容本章内容10.1 存储过程概述存储过程概述10.2 存储过程的创建与使用存储过程的创建与使用10.3 触发器概述触发器概述10.4 触发器的创建与使用触发器的创建与使用10.5 事务处理事务处理10.6 SQL Server的锁机制的锁机制败荚孟震钓歼右呢拱黔邵捶瘩渐铣洼蠢碴喜坏唤望疗碍峙沿喉寄摩淋掉产第10章存储过程与触发器第10章存储过程与触发器10.1 存储过程概述存储过程概述 n存储过程是存储过程是SQL Server服务
2、器上一组预编服务器上一组预编译的译的Transact-SQL语句，用于完成某项任语句，用于完成某项任务，它可以接受参数、返回状态值和参数务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。值，并且可以嵌套调用。粪旁洛膝夷依迎箱训称疆拷班烘芜才龟祥烟史褥挑铸门轰哮修趣桓澎楼淮第10章存储过程与触发器第10章存储过程与触发器10.1 10.1 存储过程概述存储过程概述nSQL Server存储过程的类型包括：存储过程的类型包括：系统存储过程系统存储过程用户定义存储过程用户定义存储过程临时存储过程临时存储过程扩展存储过程扩展存储过程1. 存储过程的类型存储过程的类型继唱饯夷起杏列饯敷陇赢琴疯觅
3、振卧难切优倍亚葵学汝晕蓉歉及游肇坪菱第10章存储过程与触发器第10章存储过程与触发器10.1 10.1 存储过程概述存储过程概述(1)系统存储过程系统存储过程 n是指由系统提供的存储过程，主要存储在是指由系统提供的存储过程，主要存储在master数据库中数据库中并并以以sp_为前缀为前缀，它从系统表中获取信息，从而为系统管，它从系统表中获取信息，从而为系统管理员管理理员管理SQL Server提供支持。提供支持。n通过系统存储过程，通过系统存储过程，SQL Server中的许多管理性或信息性中的许多管理性或信息性的活动的活动(例如使用例如使用sp_depends、sp_helptexts可以了
4、解数据可以了解数据数据库对象、数据库信息数据库对象、数据库信息)都可以顺利有效地完成。尽管都可以顺利有效地完成。尽管系统存储过程被放在系统存储过程被放在master数据库中，仍可以在其他数据数据库中，仍可以在其他数据库中对其进行调用库中对其进行调用(调用时，不必在存储过程名前加上数调用时，不必在存储过程名前加上数据库名据库名)。当创建一个新数据库时，一些系统存储过程会。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。在新数据库中被自动创建。糊漂驭位炬酱饯暂绚亡跌拇滚摈烧烤腔蒜蜒芦讼慢吧狂靶栏妈蚜昧浴瘪孰第10章存储过程与触发器第10章存储过程与触发器10.1 10.1 存储过程
5、概述存储过程概述(2)用户定义存储过程用户定义存储过程n是由用户创建并能完成某一特定功能是由用户创建并能完成某一特定功能(例如查询用例如查询用户所需数据信息户所需数据信息)的存储过程。的存储过程。n它处于用户创建的数据库中，存储过程名前它处于用户创建的数据库中，存储过程名前没有没有前缀前缀sp_。荆搔降峡欺篙卵讽谋原淡翼侣赶忍筹堰绳侠年泰祟疟欲订抿昭实额驱凹太第10章存储过程与触发器第10章存储过程与触发器10.1 10.1 存储过程概述存储过程概述(3)临时存储过程临时存储过程n临时存储过程与临时表类似，分为局部临时存储过程和全临时存储过程与临时表类似，分为局部临时存储过程和全局临时存储过程
6、，且可以分别向该过程名称前面添加局临时存储过程，且可以分别向该过程名称前面添加“#”或或“# #”前缀表示。前缀表示。“#”表示本地临时存储过程，表示本地临时存储过程，“# #”表示全局临时存储过程。使用临时存储过程必须创表示全局临时存储过程。使用临时存储过程必须创建本地连接，当建本地连接，当SQL Server关闭后，这些临时存储过程将关闭后，这些临时存储过程将自动被删除。自动被删除。n由于由于SQL Server支持重新使用执行计划，所以连接到支持重新使用执行计划，所以连接到SQL Server 2000的应用程序应使用的应用程序应使用sp_executesql系统存储过程，系统存储过程，
7、而不使用临时存储过程。而不使用临时存储过程。拍响哟氨虱坤拢夯酶窖值涌扒悬鸵稳局话卡锄尘疙驰萎凭蔫殖道猛衔骏镰第10章存储过程与触发器第10章存储过程与触发器10.1 10.1 存储过程概述存储过程概述(4)扩展存储过程扩展存储过程n扩展存储过程是扩展存储过程是SQL Server可以动态装载和执行可以动态装载和执行的动态链接库的动态链接库(DLL)。当扩展存储过程加载到。当扩展存储过程加载到SQL Server中，它的使用方法与系统存储过程一样。中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到扩展存储过程只能添加到master数据库中，其数据库中，其前前缀是缀是xp_。尽鼠酮悠杜赁哀
8、鼓舌郎啊卤六琐回皮缨陋拄刻阔侵挝既骨雁伞戳畔领俐醋第10章存储过程与触发器第10章存储过程与触发器10.1 10.1 存储过程概述存储过程概述2. 存储过程的功能特点存储过程的功能特点nSQL Server的存储过程可实现以下功能：的存储过程可实现以下功能：(1)接收输入参数并以输出参数的形式为调用过接收输入参数并以输出参数的形式为调用过程或批处理返回多个值。程或批处理返回多个值。(2)包含执行数据库操作的编程语句，包括调用包含执行数据库操作的编程语句，包括调用其他过程。其他过程。(3)为调用过程或批处理返回一个状态值，以表为调用过程或批处理返回一个状态值，以表示成功或失败示成功或失败(及失败
9、原因及失败原因)。星矽拳凌畸蚌栗历裙黍悟荔斋铺霖鄙注温棠汐拽肋壮哲诚矾几喧罪孜汽尖第10章存储过程与触发器第10章存储过程与触发器10.1 10.1 存储过程概述存储过程概述存储过程具有以下优点存储过程具有以下优点(1)(1)模块化编程。模块化编程。(2)(2)快速执行。快速执行。 (3)(3)减少网络通信量。减少网络通信量。(4)(4)提供安全机制。提供安全机制。(5)(5)保证操作一致性。保证操作一致性。宰云顶狗抡俞伶渔而菌荔存缝绢考泞五腿灰盲壶髓井墓案杰敲护卤噶饲冶第10章存储过程与触发器第10章存储过程与触发器10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2 存储过
10、程的创建与使用存储过程的创建与使用1. 使用企业管理器创建存储过程使用企业管理器创建存储过程存储过程创建存储过程创建膀凿资凛铝送妨睛华协瞩琅零宅辑金逞拖戈河锈犯薯熏携辟卿宫劣换趴伎第10章存储过程与触发器第10章存储过程与触发器10.2.1 10.2.1 创建存储过程创建存储过程2. 使用向导创建存储过程使用向导创建存储过程榜苦润蜒鸭脊瘫疼翼及虫郝慧册宁跋抉诡辣镍加渭奎亢修楞隘构扦傀携帮第10章存储过程与触发器第10章存储过程与触发器3. 使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程n使用使用CREATE PROCEDURE语句创建存储过程应该考虑以语句创建存储过程
11、应该考虑以下几个方面：下几个方面：(1)在一个批处理中，在一个批处理中，CREATE PROCEDURE语句不能语句不能与其他与其他SQL语句合并在一起。语句合并在一起。(2)数据库所有者具有默认的创建存储过程的权限，它可数据库所有者具有默认的创建存储过程的权限，它可把该权限传递给其他的用户。把该权限传递给其他的用户。(3)存储过程作为数据库对象其命名必须符合标识符的命存储过程作为数据库对象其命名必须符合标识符的命名规则。名规则。(4)只能在当前数据库中创建属于当前数据库的存储过程。只能在当前数据库中创建属于当前数据库的存储过程。10.2.1 10.2.1 创建存储过程创建存储过程活域怎羔寂艺
12、蛀挚鸽茵平么升古涎疮镍爸秤谨铃磊监卉拨危话狐洁郭暑雨第10章存储过程与触发器第10章存储过程与触发器创建存储过程语句的语法格式如下：创建存储过程语句的语法格式如下：CREATE PROCEDURE procedure_name ; number parameter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement ,.n 10.2.1 10.2.1 创建存储过程创建存储过程烩堡张贯龙鲤芋扎速兼通颐胜
13、骨黄观眼节晒酸楔抢吻惩位骇陌贝羞决讹郡第10章存储过程与触发器第10章存储过程与触发器例例10-1 创建存储过程，从表创建存储过程，从表goods和表和表goods_classification的联接中返回商品名、商品类别、的联接中返回商品名、商品类别、单价。单价。10.2.1 10.2.1 创建存储过程创建存储过程CREATE PROCEDURE goods_info ASSELECT goods_name, classification_name, unit_priceFROM goods g INNER JOIN goods_classification gcON g.classific
14、ation_id = gc.classification_idn存储过程创建后，存储过程的名称存放在存储过程创建后，存储过程的名称存放在sysobject表中，表中，文本存放在文本存放在syscomments表中。表中。剃昂偶忌咱赃糜雀骤散当催黔淆娱凝嗽三宽胀钻遏姑碳铂涨纺佑事饲目飘第10章存储过程与触发器第10章存储过程与触发器执行存储过程执行存储过程n执行存储过程的语法格式：执行存储过程的语法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|
15、DEFAULT ,.n WITH RECOMPILE 10.2 10.2 存储过程的创建与使用存储过程的创建与使用带柱咱耶寻翱暗蓝老澡勋暇施激狄诌骸暇块渴红牵局乃账住离件颠活碱敖第10章存储过程与触发器第10章存储过程与触发器例如，执行例例如，执行例10-1的存储过程的存储过程goods_infon在在SQL查询分析器中输入命令：查询分析器中输入命令：EXEC goods_infon运行的结果：运行的结果：10.2.2 10.2.2 执行存储过程执行存储过程嘎并联艳刺茫盆蜘岂赤批何岁跺迷凳虾玄男帮衫御患履坛禾汤胃粗嫁摘吨第10章存储过程与触发器第10章存储过程与触发器10.2 10.2 存储过
16、程的创建与使用存储过程的创建与使用修改存储过程修改存储过程1. 使用企业管理器修改存储过程使用企业管理器修改存储过程2. 使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程nALTER PROCEDURE的语法规则是：的语法规则是：ALTER PROCEDURE procedure_name ; number parameter data_typeVARYING=default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATION AS sql_statement .n 溅
17、构整千押背簿菲茧盎秽趟弥峪仿砸毒确渤羚炔人蛔冯亏江臭晦疥蚊草游第10章存储过程与触发器第10章存储过程与触发器10.2.3 10.2.3 修改存储过程修改存储过程查看存储过程的文本信息查看存储过程的文本信息SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = P AND o.name = procedure_name GO疽辩所浆竞湿补恩咯殴伴粱柄院璃庶籍宠剖呈堑戊副颧绅坐驰仑崇替抱思第10章存储过程与触发器第10章存储过程与触发器10.2 10.2 存储过程的创建与
18、使用存储过程的创建与使用删除存储过程删除存储过程 1.使用企业管理器删除存储过程使用企业管理器删除存储过程2. 使用使用DROP PROCEDURE删除存储过程删除存储过程nDROP PROCEDURE的语法如下：的语法如下：DROP PROCEDURE procedure_name ,.n n例如删除例例如删除例10-1创建的存储过程创建的存储过程goods_info：DROP PROCEDURE goods_infoGO肉削镀均转遂宗柴撅韶斥裔诵遂吐姥盼敢酷远陇座辙鞘损砍扩个蝎惯讨杠第10章存储过程与触发器第10章存储过程与触发器10.2 10.2 存储过程的创建与使用存储过程的创建与使用
19、存储过程参数与状态值存储过程参数与状态值n存储过程和调用者之间通过参数交换数据，可以按输入的存储过程和调用者之间通过参数交换数据，可以按输入的参数执行，也可由参数输出执行结果。调用者通过存储过参数执行，也可由参数输出执行结果。调用者通过存储过程返回的状态值对存储过程进行管理。程返回的状态值对存储过程进行管理。n1. 参数参数存储过程的参数在创建过程时声明。存储过程的参数在创建过程时声明。SQL Server支持两类参数：输入参数和输出参数。支持两类参数：输入参数和输出参数。n(1)输入参数输入参数输入参数允许调用程序为存储过程传送数据值。要定输入参数允许调用程序为存储过程传送数据值。要定义存储
20、过程的输入参数，必须在义存储过程的输入参数，必须在CREATE PROCEDURE语句中声明一个或多个变量及类型。语句中声明一个或多个变量及类型。税谎烈绵杖搐腆停愿大执呈杭毯练啮崖魂晶装句骸职根敬翁吕氏诧寒写糕第10章存储过程与触发器第10章存储过程与触发器例例10-3 创建带参数的存储过程，从表创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回的连接中返回输入的员工名输入的员工名、该、该员工销售的商品名、商品类别、销售量等信息。员工销售的商品名、商品类别、销售量等信息。CREATE PROC sell_info
21、employee_name varchar(20)AS SELECT employee_name, goods_name,classification_name, order_numFROM employee e INNER JOIN sell_order s ON e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification_id=g.classification_idWHERE employee_name LIKE emp
22、loyee_name哟噶曲署葫墓炸陋抽苑忙廖遂逃旭景弥磋竹救砌敢哑讫警播箭葛五赎宫捌第10章存储过程与触发器第10章存储过程与触发器10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n执行存储过程执行存储过程sell_info以以EXEC sell_info 东方牧东方牧n参数值可以包含通配符参数值可以包含通配符“%”，例如，查找所有姓，例如，查找所有姓“钱钱”的员工的销售情况可以使用以下命令：的员工的销售情况可以使用以下命令：EXEC sell_info 钱钱%抢蚊走捣牢酥养始逛哆霞倚竟椎钢渡禹障刹斑邦身惠纂猜乓致羌鹿般眺憋第10章存储过程与触发器第10章存储过程与触发器1
23、0.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值存储过程执行时，参数可以由位置标识，也可以由存储过程执行时，参数可以由位置标识，也可以由名字标识名字标识。n例如，定义一个具有例如，定义一个具有3个参数的存储过程：个参数的存储过程：CREATE PROC myproc val1 int, val2 int, val3 intAS .n参数以位置传递：参数以位置传递：EXEC myproc 10, 20, 15n参数以名字传递，每个值由对应的参数名引导：参数以名字传递，每个值由对应的参数名引导：EXEC myproc val2=20, val1=10, val3=15存储过程中参
24、数的标识存储过程中参数的标识几耍庄师滔数圣搞喊娥陛靡篙澳督蜘响磨蛀仰送刑疼扇苯劣伪羞委辛祷忧第10章存储过程与触发器第10章存储过程与触发器10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(2)输出参数输出参数n输出参数允许存储过程将数据值或游标变量传回调用程序。输出参数允许存储过程将数据值或游标变量传回调用程序。nOUTPUT关键字用以指出能返回到调用它的批处理或过程关键字用以指出能返回到调用它的批处理或过程中的参数。中的参数。n为了使用输出参数，在为了使用输出参数，在CREATE PROCEDURE和和EXECUTE语句中都必须使用语句中都必须使用OUTPUT关键字。关
25、键字。胖否虐谓椒吨癣沿百津胰稼帮混滴锨瞧窄陵郁飘敢岸宴降接郑偏鹤毅愈讹第10章存储过程与触发器第10章存储过程与触发器10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例例10-4 创建存储过程创建存储过程price_goods，通过，通过输入参数输入参数在在goods表表中查找商品，以中查找商品，以输出参数输出参数获取商品单价。获取商品单价。CREATE PROC price_goods goods_name varchar(80)=NULL, price_goods real OUTPUTASSELECT price_goods=unit_priceFROM goodsW
26、HERE goods_name=goods_name络浊斥抖淌间鸽情耙明捣略翼荫毡字茁埠蛊受搏行田蕴专嗣露说皇腔薪辉第10章存储过程与触发器第10章存储过程与触发器10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n执行执行price_goods存储过程的代码如下：存储过程的代码如下：DECLARE price realEXEC price_goods Canon LBP2900, price OUTPUTSELECT priceEXECUTE语句还需要关键字语句还需要关键字OUTPUT以允许以允许参数值返回给变量。参数值返回给变量。龄惋叹内茵遥龙癣郧茵萝电损阻心戌泻猖厂瓦譬
27、密具狭戎救了岔驱刁戒形第10章存储过程与触发器第10章存储过程与触发器10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(1)用用RETURN语句定义返回值语句定义返回值n存储过程可以返回整型状态值，表示过程是否成功执行，存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。或者过程失败的原因。n如果存储过程没有显式设置返回代码的值，则如果存储过程没有显式设置返回代码的值，则SQL Server返回代码为返回代码为 0，表示成功执行；若返回，表示成功执行；若返回-1 -99之间的整数，之间的整数，表示没有成功执行。也可以使用表示没有成功执行。也可以使用RETU
28、RN语句，用大于语句，用大于0或小于或小于-99的整数来定义自己的返回状态值，以表示不同的整数来定义自己的返回状态值，以表示不同的执行结果。的执行结果。2. 返回存储过程的状态返回存储过程的状态(2)捕获返回状态值捕获返回状态值n使用以下语句接收返回的状态值使用以下语句接收返回的状态值 EXECUTE status_var=procedure_name鲍赴羽诊食慌迪浚草父芭济犯阔泽斤虎歉淋妈捎畏夺施乾锈恼棉橡州另躁第10章存储过程与触发器第10章存储过程与触发器10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例例10-5 创建存储过程，创建存储过程，输入输入商品类别，商品类
29、别，返回返回各种商品名称。各种商品名称。在存储过程中，用值在存储过程中，用值15表示用户没有提供参数；值表示用户没有提供参数；值-l01表示表示没有输入商品类别；值没有输入商品类别；值0表示过程运行没有出错。表示过程运行没有出错。CREATE PROC cl_goods cl_name varchar(40)=NULLASIF cl_name=NULL RETURN 15IF NOT EXISTS (SELECT * FROM goods_classification WHERE classification_name=cl_name) RETURN -101SELECT g.goods_na
30、me FROM goods_classification gc,goods gWHERE gc.classification_id =g.classification_id AND gc.classification_name=cl_nameRETURN 0宜船原巧肥韧扫感凭碾草煎砒沦熟筋赛俱窃诽婆抹帧变商铂棘投的屈它文第10章存储过程与触发器第10章存储过程与触发器10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值DECLARE return_status intEXEC return_status=cl_goods 笔记本计算机笔记本计算机IF return_status
31、=15 SELECT 语法错误语法错误ELSE IF return_status=-101 SELECT 没有找到该商品类别没有找到该商品类别例例10-5的存储过程的存储过程cl_goods执行时使用以下语句：执行时使用以下语句：搓艳滔裕触惯捣弦枣殖痛男巩谴值现痊职赌爱钮案姚初铅铲爸肾豪勒糟馋第10章存储过程与触发器第10章存储过程与触发器n批处理是包含一个或多个批处理是包含一个或多个Transact-SQL语句的组，从应用程序语句的组，从应用程序一次性地发送到一次性地发送到SQL Server执行。执行。 SQL Server将批处理语句编将批处理语句编译成一个可执行单元，此单元称为执行计划
32、，每次执行其中的译成一个可执行单元，此单元称为执行计划，每次执行其中的一条语句。一条语句。n函数是由一个或多个函数是由一个或多个Transact-SQL语句组成的子程序，可用于语句组成的子程序，可用于封装代码以便重新使用。封装代码以便重新使用。n存储过程是一组存储过程是一组SQL语句的集合，类似于程序的模块。它与函语句的集合，类似于程序的模块。它与函数不同，因为它不返回取代其名称的值，也不能直接用在表达数不同，因为它不返回取代其名称的值，也不能直接用在表达式中。式中。 批处理、函数、存储过程的区别批处理、函数、存储过程的区别n存储过程、批处理文件和函数都是由一个或多个存储过程、批处理文件和函数
33、都是由一个或多个Transact-SQL语句组成的，通常可以完成一系列的操作，但是它们的适用范语句组成的，通常可以完成一系列的操作，但是它们的适用范围和产生的效果是不同的，使用时应当认真分析，区别对待。围和产生的效果是不同的，使用时应当认真分析，区别对待。掣蚌燥惦逊吠污瞳垛怂贡趾啡惹率酥轴淤册舀筋卫拆拦晚榴驱脉荤滔告脚第10章存储过程与触发器第10章存储过程与触发器n触发器是一种特殊类型的存储过程，用于实现比较复杂的触发器是一种特殊类型的存储过程，用于实现比较复杂的数据约束。数据约束。n触发器主要是通过事件进行触发而被执行的，而存储过程触发器主要是通过事件进行触发而被执行的，而存储过程可以通过
34、过程名字直接调用。可以通过过程名字直接调用。当对某一表进行当对某一表进行UPDATE、INSERT、DELETE操作时，操作时，SQL Server就会自动执行触就会自动执行触发器所定义的发器所定义的SQL语句，从而确保对数据的处理必须符合语句，从而确保对数据的处理必须符合由这些由这些SQL语句所定义的规则。语句所定义的规则。n触发器的主要作用就是能够实现由主键和外键所不能保证触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性。的参照完整性和数据的一致性。 10.3 触发器概述触发器概述泥唾岩锗融病竟鸽举的脯拉栅窃凿樊孤栏睛菇孙扩剐句厄认巡膳贺孰掌王第10章存储过程与
35、触发器第10章存储过程与触发器n触发器的其它功能触发器的其它功能强化约束：触发器能够实现比强化约束：触发器能够实现比CHEAK语句更为复杂的语句更为复杂的约束。约束。跟踪变化：触发器可以侦测数据库内的操作，从而不允跟踪变化：触发器可以侦测数据库内的操作，从而不允许数据库中不经许可的指定更新和变化。许数据库中不经许可的指定更新和变化。级联运行：触发器可以侦测数据库内的操作，并自动地级联运行：触发器可以侦测数据库内的操作，并自动地级联影响整个数据库地各项内容。级联影响整个数据库地各项内容。存储过程的调用：为了响应数据库更新，触发器可以调存储过程的调用：为了响应数据库更新，触发器可以调用一个或多个存
36、储过程，甚至可以通过外部过程的调用用一个或多个存储过程，甚至可以通过外部过程的调用而在而在DBMS本身之外进行操作。本身之外进行操作。绍不漂丑辛贞成苦暖呕砷谷冒嘛话竹眨藏罩放帅寺垫燃砾椅晕膨哭吓烁禽第10章存储过程与触发器第10章存储过程与触发器n名称：触发器有一个符合标志符命名规则的名称。名称：触发器有一个符合标志符命名规则的名称。n定义的目标：触发器必须定义在表或视图上。定义的目标：触发器必须定义在表或视图上。n触发条件：是触发条件：是UPDATE、INSERT，还是，还是DELETE语句。语句。n触发逻辑：触发之后如何处理。触发逻辑：触发之后如何处理。 触发器的四要素触发器的四要素励微尹
37、两金棠丝尤梭喀斥鸡廷卒克净逞士屠页辆怠酶所谈触搬绣赦火总怎第10章存储过程与触发器第10章存储过程与触发器1. 使用企业管理器创建触发器使用企业管理器创建触发器创建触发器创建触发器10.4 触发器的创建与使用触发器的创建与使用欺猫童蔡汹慈儡噶懊瘦绊坐裹冀帜亿妹紊究呢锡秉樱幂拍吗测跨裳验谚檬第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.1 创建触发器创建触发器亨薄黑居添梳负刹侧弱叼匹忙灶痔诡砖撞皑升擞复扎烃潭梅嘲稳逮某逃乒第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.1 创建触发器创建触发器2. 使用使用CREATE TRIGGER语句创建触发
38、器语句创建触发器nCREATE TRIGGER语句的语法格式如下：语句的语法格式如下：CREATE TRIGGER trigger_name ON table_name | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF -触发器的类型触发器的类型 INSERT , UPDATE , DELETE AS sql_statement . n 秋太簧饵避汾闪涤诀存铸晚街尝剖话助扫关袒惨杆之祥引央溜钾臭箱蒋蕾第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.1 创建触发器创建触发器触发器的类型触发器的类型nAFTER触发器触发器p该触
39、发器会在触发该触发器会在触发INSERT、UPDATE或或DELETE动作之后执动作之后执行。行。p该触发器只有在已插入一行或是多行和所有约束已被处理且通该触发器只有在已插入一行或是多行和所有约束已被处理且通过后才触发。过后才触发。p该触发器只能在表上定义，可以为表的同一操作定义多个触发该触发器只能在表上定义，可以为表的同一操作定义多个触发器。器。nINSTEAD OF触发器触发器p该触发器代替触发动作进行激活，即表示不执行其所定义的操该触发器代替触发动作进行激活，即表示不执行其所定义的操作作(INSERT、 UPDATE、DELETE)而仅执行触发器本身。而仅执行触发器本身。n两者的区别两者
40、的区别pAFTER触发器是告诉触发器是告诉SQL语句执行了语句执行了INSERT、UPDATE或或DELETE操作后干什么操作后干什么(操作已经做了，数据已经变动了操作已经做了，数据已经变动了)。pINSTEAD OF触发器是告诉当执行触发器是告诉当执行INSERT、 UPDATE或者或者DELETE操作时用什么别的操作来代替操作时用什么别的操作来代替(操作不做，而是做操作不做，而是做另外一种触发器定义的操作另外一种触发器定义的操作) 。卡靴眷望之侵丑荤虐晾嘉仗拇芳想淌楼抓灾召铬蔫欲涎茎岩泄锦得虐仁繁第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.1 创建触发器创建触发
41、器例例10-6 在在employee表上创建一个表上创建一个DELETE类型的触发器，该类型的触发器，该触发器的名称为触发器的名称为tr_employee.(1)创建触发器创建触发器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+个员工被删除个员工被删除 SELECT msg RETURN兢舍苔昏革耸匣巡暇蹲窍铜嗓桌旋睁促厨锚搽栓秦厄许辟栅纂膀佳贬铲盏第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.
42、1 创建触发器创建触发器(2)执行触发器执行触发器tr_employeen触发器不能通过名字来执行，而是在相应的触发器不能通过名字来执行，而是在相应的SQL语句语句被执行时自动触发的。被执行时自动触发的。n例如执行以下例如执行以下DELETE语句：语句：DELETE FROM employeeWHERE employee_name = 刘叶刘叶蚤讲拂酸好友援祖贡嫉濒院嵌升裁惕德四袍涩怨羡治织殴迈慑盯谣曲烩婶第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.1 创建触发器创建触发器n在触发器的执行过程中，在触发器的执行过程中，SQL Server建立和管理两个临时的虚拟建立
43、和管理两个临时的虚拟表：表：Deleted表和表和Inserted表。这两个表包含了在激发触发器的操表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。可以用这一特性来测试某些数据修作中插入或删除的所有记录。可以用这一特性来测试某些数据修改的效果，以及设置触发器操作的条件。这两个特殊表可供用户改的效果，以及设置触发器操作的条件。这两个特殊表可供用户浏览，但是用户不能直接改变表中的数据。浏览，但是用户不能直接改变表中的数据。n在执行在执行INSERT或或UPDATE语句之后所有被添加或被更新的记录语句之后所有被添加或被更新的记录都会存储在都会存储在Inserted表中。表中。n在执行在执
44、行DELETE或或UPDATE语句时，从触发程序表中被删除的行语句时，从触发程序表中被删除的行会发送到会发送到Deleted表。表。n对于更新操作，对于更新操作，SQL Server先将要进行修改的记录存储到先将要进行修改的记录存储到Deleted表中，然后再将修改后的数据复制到表中，然后再将修改后的数据复制到Inserted表以及触发程序表。表以及触发程序表。3. Deleted表和表和Inserted表表涩空纶冠瞥浑醇极惶码灶灯愤奈编械婶纂称索瘪缴聊灾屯祟癸组座眷崇求第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.1 创建触发器创建触发器Deleted表和表和Ins
45、erted表在执行触发程序时的情况表在执行触发程序时的情况Transact-SQL语句语句Inserted表表Deleted表表INSERT所要添加的行所要添加的行空空UPDATE新的行新的行旧的行旧的行DELETE空空删除的行删除的行怜长苑腰癸灯植怯类斟直傻台杂辫卒汤煎益痊蛤积爹怔稀网乃购朋筋浇胀第10章存储过程与触发器第10章存储过程与触发器10.4.1 10.4.1 创建触发器创建触发器例例10-7 为表为表customer创建一个名为创建一个名为test_tr的触发器，的触发器，当执行添加、更新或删除时，激活该触发器。当执行添加、更新或删除时，激活该触发器。n创建创建test_tr触发
46、器：触发器：CREATE TRIGGER test_trON customer FOR INSERT,UPDATE,DELETEAS SELECT * FROM inserted SELECT * FROM deletedncustomer表执行以下插入操作：表执行以下插入操作：INSERT INTO customer(customer_id,customer_name,telephone)VALUES(12346,张三张三,1234567)鲸兜嘉绑斡违免朱晰卧座诅园血滨鸦禾猜羔汗蚂测缠郊闭杯祝用趁辑肾洋第10章存储过程与触发器第10章存储过程与触发器10.4 10.4 触发器的创建与使用触发
47、器的创建与使用删除触发器删除触发器1. 使用企业管理器删除触发器使用企业管理器删除触发器涛息少千讼巢占厅颜惩意桓氢吼经仍直渍犊嗜蛀孪浙岭柿花衙保芬柳界淘第10章存储过程与触发器第10章存储过程与触发器10.4.2 10.4.2 删除触发器删除触发器2. 使用使用DROP TRIGGER删除指定触发器删除指定触发器n删除触发器语句的语法格式如下：删除触发器语句的语法格式如下：DROP TRIGGER trigger_name , .n n例如，删除例例如，删除例10-6的触发器的触发器tr_employee：DROP TRIGGER tr_employee矣搁棘醒愚聂闺俭和筏言特蝶扣渝车乎善棉靡
48、抨繁噎锨钢蜀七炭熊嚷筒砂第10章存储过程与触发器第10章存储过程与触发器修改触发器修改触发器n通过企业管理器、系统存储过程或通过企业管理器、系统存储过程或Transact_SQL语句，可语句，可以修改触发器的名字和正文。以修改触发器的名字和正文。10.4 10.4 触发器的创建与使用触发器的创建与使用1. 使用使用sp_rename系统存储过程修改触发器的名字：系统存储过程修改触发器的名字：语法格式为：语法格式为：sp_rename oldname, newname2. 使用企业管理器修改触发器的正文使用企业管理器修改触发器的正文3. 使用使用ALTER TRIGGER语句修改触发器语句修改触
49、发器萍衫饿口综磨居挞俘谰捕少方丘爆侠叮茸氖箍顶阴哆只在锋暇忱桑厄荚求第10章存储过程与触发器第10章存储过程与触发器10.4.3 10.4.3 修改触发器修改触发器nALTER TRIGGER修改触发器的语法如下：修改触发器的语法如下：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATE AS sql_statement .n 野涌债腻通耽炬裂浇杖咋骑栈瘴慌棕慢炳屋捐此走定瞩擒牛劝侦圈哭涪谅第10章存储过程与触发器第10章存储过程与触
50、发器10.4.3 10.4.3 修改触发器修改触发器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+个员工数据个员工数据被插入被插入SELECT msgRETURN例如，将例例如，将例10-6的触发器的触发器tr_employee修改为修改为INSERT操作后进行。操作后进行。n对对employee表执行以下插入语句：表执行以下插入语句：INSERT employee(employee_id,employee_name)VALUES (E016,
51、王五王五)n激活激活INSERT触发器触发器tr_employee，显示信息如下：，显示信息如下：1个员工数据被插入个员工数据被插入逞拍淌外毡嘉柴蜀激舆尹敖卜夸光譬拜冗咆矾语踩宜丸坑桨甸墨酶酵玻赂第10章存储过程与触发器第10章存储过程与触发器n事务事务(Transaction)是是SQL Server中的一个逻辑工作单元，中的一个逻辑工作单元，该单元将被作为一个整体进行处理。该单元将被作为一个整体进行处理。n事务可以是一组事务可以是一组SQL语句、一条语句、一条SQL语句或整个程序，在语句或整个程序，在通常情况下，一个应用程序里包含多个事务。通常情况下，一个应用程序里包含多个事务。n事务保证
52、连续多个操作必须全部执行成功，否则必须立即事务保证连续多个操作必须全部执行成功，否则必须立即回复到未执行任何操作的状态，即执行事务的结果要么全回复到未执行任何操作的状态，即执行事务的结果要么全部将数据所要执行的操作完成，要么全部数据都不修改。部将数据所要执行的操作完成，要么全部数据都不修改。10.5.1 事务概述事务概述10.5 事务处理事务处理拖榨译邹舀英息创绵呜裕抨祝巷苗牺秘鼓呈捂筷坐忻众儡化尊陪织拆桩釜第10章存储过程与触发器第10章存储过程与触发器10.5.1 10.5.1 事务概述事务概述n例如，企业取消了仓储部，需要将例如，企业取消了仓储部，需要将“仓储部仓储部”从从departm
53、ent表中表中删除，而删除，而employee表中的部门编号与仓储部相对应的员工也应删表中的部门编号与仓储部相对应的员工也应删除。因此，两个表都需要修改，这种修改只能通过两条语句除。因此，两个表都需要修改，这种修改只能通过两条语句DELETE进行。进行。假设仓储部编号为假设仓储部编号为D004，第一条，第一条DELETE语句修改语句修改department表为：表为：DELETE FROM department WHERE department_id = D004第二条第二条DELETE语句修改语句修改employee表为：表为：DELETE FROM employee WHERE depar
54、tment_id = D004 n因此，必须保证这两条因此，必须保证这两条DELETE语句同时执行，或都不执行。这语句同时执行，或都不执行。这时可以使用数据库中的事务技术来实现。时可以使用数据库中的事务技术来实现。1事务的由来事务的由来n在在SQL Server中，使用中，使用DELETE或或UPDATE语句对数据库进行更语句对数据库进行更新时一次只能操作一个表，这会带来数据库的数据不一致的问题。新时一次只能操作一个表，这会带来数据库的数据不一致的问题。术骚许砂躇面舱馆换糜蛙份赵剧标票党酮耶除稠炬寒枫踪略器竣妄蹈馏到第10章存储过程与触发器第10章存储过程与触发器2事务属性事务属性n事务是网络
55、数据库中逻辑操作的基本单位，事务的事务是网络数据库中逻辑操作的基本单位，事务的ACID属性必不可少。属性必不可少。n由于事务作为一个逻辑工作单元，当事务执行遇到错误时，由于事务作为一个逻辑工作单元，当事务执行遇到错误时，将取消事务所做的修改。一个逻辑单元必须具有将取消事务所做的修改。一个逻辑单元必须具有4个属性：个属性：原子性原子性(Atomicity)一致性一致性(Consistency)隔离性隔离性(Isolation)持久性持久性(Durability)n这些属性称为这些属性称为ACID。烤球协檄沸馒簧侩具纹舰榷绣矿帮糯甩菌瓶玉揖溅率图攘娱村帜堂报析德第10章存储过程与触发器第10章存储
56、过程与触发器事务的事务的ACID属性属性n原子性原子性(Atomicity)事务必须是工作的最小单位，即原子单元，对于其数据的修改，要么全事务必须是工作的最小单位，即原子单元，对于其数据的修改，要么全都执行，要么全都不执行。都执行，要么全都不执行。n一致性一致性(Consistency)事务在完成后，必须使所有的数据都保持一致性状态。在相关数据库中，事务在完成后，必须使所有的数据都保持一致性状态。在相关数据库中，事务必须遵守数据库的约束和规则，以保持所有数据的完整性。事务结事务必须遵守数据库的约束和规则，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。束时，所有的内部数据
57、结构都必须是正确的。n隔离性隔离性(Isolation)一个事务所作的修改必须与任何其他并发事务所作的修改隔离。事务查一个事务所作的修改必须与任何其他并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。使数
58、据结束时的状态与原始事务执行的状态相同。n持久性持久性(Durability)p事务完成后，它对于系统的影响是永久的。该修改即使出现系统故障也事务完成后，它对于系统的影响是永久的。该修改即使出现系统故障也将一直保持。将一直保持。律徊芍死氨申沤俏讥箔娇浩贴衰雹歌扛纽中划脐憋并房错涅汝漂扼裸臆虎第10章存储过程与触发器第10章存储过程与触发器3事务模式事务模式n应用程序主要通过指定事务启动和结束的时间来控制事务。应用程序主要通过指定事务启动和结束的时间来控制事务。这可以使用这可以使用Transact-SQL语句来控制事务的启动和结束。系语句来控制事务的启动和结束。系统还必须能够正确处理那些在事务完
59、成之前便中止事务的错统还必须能够正确处理那些在事务完成之前便中止事务的错误。误。n事务是在连接层进行管理的。当事务在一个连接上启动时，事务是在连接层进行管理的。当事务在一个连接上启动时，在该连接上执行的所有在该连接上执行的所有Transact-SQL语句在该事务结束之前语句在该事务结束之前都是该事务的一部分。都是该事务的一部分。n引入事务后，所有对数据库的操作就是以事务为逻辑单位进引入事务后，所有对数据库的操作就是以事务为逻辑单位进行。一个事务最终只有两种状态：行。一个事务最终只有两种状态：提交状态提交状态（事务被正常执（事务被正常执行）和行）和未提交状态未提交状态（事务必须被回滚）。所以事务
60、概念的引（事务必须被回滚）。所以事务概念的引入解决了不可预见的用户操作的发生。入解决了不可预见的用户操作的发生。烛考我捻析普蝗解蚊斩煤蒂四莹必壶芒也若曝汇枫涪益盆招帅拟碗扰墒伟第10章存储过程与触发器第10章存储过程与触发器nSQL Server以以3种事务模式管理事务种事务模式管理事务(1) 自动提交事务模式：每条单独的语句都是一个事务。在此自动提交事务模式：每条单独的语句都是一个事务。在此模式下，每条模式下，每条Transact-SQL语句在成功执行完成后，都被自动语句在成功执行完成后，都被自动提交，如果遇到错误，则自动回滚该语句。该模式为系统默认提交，如果遇到错误，则自动回滚该语句。该模
61、式为系统默认的事务管理模式。的事务管理模式。(2) 显式事务模式：该模式允许用户定义事务的启动和结束。显式事务模式：该模式允许用户定义事务的启动和结束。事务以事务以BEGIN TRANSACTION语句显式语句显式开始开始，以，以COMMIT或或ROLLBACK语句语句显式显式结束结束。(3) 隐性事务模式：在当前事务完成提交或回滚后，新事务自隐性事务模式：在当前事务完成提交或回滚后，新事务自动启动。隐性事务不需要使用动启动。隐性事务不需要使用BEGIN TRANSACTION语句标语句标识事务的开始，但需要以识事务的开始，但需要以COMMIT或或ROLLBACK语句来提语句来提交或回滚事务。
62、交或回滚事务。n提示：提示：日常管理和开发应用系统时推荐使用显式事务模式。日常管理和开发应用系统时推荐使用显式事务模式。址旱成攻析泞收文专哼骤锄除共豁躇俘扯费覆答俺符坍绒赤商莱到氮乏嘻第10章存储过程与触发器第10章存储过程与触发器1启动和结束事务启动和结束事务n启动事务语句的语法格式如下（定义事务）：启动事务语句的语法格式如下（定义事务）：BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description n结束事务语句的语法格式如下（提交事务）：结束事务语句的语法格式如下（提交事务）：COMMIT TR
63、ANSACTION transaction_name | tran_name_variable 10.5.2 事务管理事务管理nSQL Server 按事务模式进行事务管理，设置事务启动和按事务模式进行事务管理，设置事务启动和结束的时间，正确处理事务结束之前产生的错误。结束的时间，正确处理事务结束之前产生的错误。傻氯埂藉焰微淤邻泌解灸址迪痘哄瞅款宪捉呐岗计拔隔箱咨绥痈憾绘靴卢第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理例例10-8 建立一个显式事务以显示建立一个显式事务以显示Sales数据库的数据库的employee表的数据。表的数据。BEGIN
64、 TRANSACTION SELECT * FROM employee COMMIT TRANSACTIONn本例创建的事务以本例创建的事务以BEGIN TRANSACTION语句开始，以语句开始，以COMMIT TRANSACTION语句结束。语句结束。胸朽乱吠舀偏熙维邯欺倘诲酋臭粳抓偿控担拴纶兄约粮牛雪铆蛊华痴两寸第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理DECLARE transaction_name varchar(32)SELECT transaction_name=tran_deleteBEGIN TRANSACTION trans
65、action_name DELETE FROM department WHERE department_id=D004 DELETE FROM employee WHERE department_id=D004COMMIT TRANSACTION tran_delete例例10-9 建立一个显式命名事务以删除建立一个显式命名事务以删除department表表的的“仓储部仓储部”记录行。记录行。n本例命名了一个本例命名了一个tran_delete事务，该事务用于事务，该事务用于department表的表的“仓储部仓储部”记录行及相关数据。记录行及相关数据。逞变陌锥牢东儒谢嘛搅祸迄旋护宅砂摩闷恒湍
66、必恕赚献豁弛骤掳吱杂淫蓟第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理CREATE TABLE imp_tran( num char(2) NOT NULL, cname char(6) NOT NULL)GO SET IMPLICIT_TRANSACTIONS ON -启动隐性事务模式启动隐性事务模式GO- 第一个事务由第一个事务由INSERT语句启动语句启动INSERT INTO imp_tran VALUES (01, Zhang)INSERT INTO imp_tran VALUES (02, Wang)COMMIT TRANSACTION
67、 -提交第一个隐性事务提交第一个隐性事务GO- 第二个隐式事务由第二个隐式事务由SELECT语句启动语句启动SELECT COUNT(*) FROM imp_tranINSERT INTO imp_tran VALUES (03, Li)SELECT * FROM imp_tranCOMMIT TRANSACTION -提交第二个隐性事务提交第二个隐性事务GOSET IMPLICIT_TRANSACTIONS OFF -关闭隐性事务模式关闭隐性事务模式GO例例10-10 隐性事务处理过程。隐性事务处理过程。瑶日谷拌袋笺乖轴蜂潍保哥强敛铣莫黄洱亩今欣凝络掌炊蒋烦鬼橡瞩钻瞩第10章存储过程与触发器
68、第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理n事务回滚使用事务回滚使用ROLLBACK TRANSACTION语句实现，语句实现，其语法格式如下：其语法格式如下：ROLLBACK TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable p其中，其中， savepoint_name用于指定回滚到某一指定位置用于指定回滚到某一指定位置的标记名称，的标记名称， savepoint_variable为存放该标记名称为存放该标记名称的变量。的变量。2事务回滚事务
69、回滚n当事务执行过程中遇到错误时，该事务修改的所有数据都当事务执行过程中遇到错误时，该事务修改的所有数据都恢复到事务开始时的状态或某个指定的位置，事务占用的恢复到事务开始时的状态或某个指定的位置，事务占用的资源将被释放。这个操作过程叫资源将被释放。这个操作过程叫事务回滚事务回滚(Transaction Rollback)。麓溜魁尔决尖牡捡郡仓伊渊读粪核尹口母吉碍撑虾釉蛔弄涅闻铂宦摔衔索第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理例例10-11 使用使用ROLLBACK TRANSACTION语句标语句标识事务结束。识事务结束。BEGIN TRAN
70、SACTION UPDATE goods SET stock_quantity=stock_quantity-5 WHERE goods_id=G00006 INSERT INTO sell_order(order_id1,goods_id,order_num,order_date) VALUES(S00005,G00006,5,getdate( )ROLLBACK TRANSACTION囚毋儡具洼擎芭滁蚜侠桩贡蝇刘闭俐简浮叙敢炭零远冲古骆显彩韶屑柑杀第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理事务回滚到指定位置事务回滚到指定位置n如果要让事务回
71、滚到指定位置，则需要在事务中设定保存如果要让事务回滚到指定位置，则需要在事务中设定保存点点(SavePoint)。n所谓所谓保存点保存点是指定其所在位置之前的事务语句，不能回滚是指定其所在位置之前的事务语句，不能回滚的语句，即此语句前面的操作被视为有效。其语法格式如的语句，即此语句前面的操作被视为有效。其语法格式如下：下：SAVE TRANSACTION savepoint_name | savepoint_variablen对于长事务，可以在事务中设置若干个保存点。保存点好对于长事务，可以在事务中设置若干个保存点。保存点好比是对事务做上若干个标记，这样回滚事务就不必回滚整比是对事务做上若干个
72、标记，这样回滚事务就不必回滚整个事务，而是可以回滚到指定的保存点。个事务，而是可以回滚到指定的保存点。崩荐苔偶谤猛妻焚糙蛋狰庚婆蛀傈劫笋乖古椅拼灶宰欺定妆窖知纯揪匙鹰第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理BEGIN TRANSACTION my_transaction_delete DELETE FROM department WHERE department_id=D005 SAVE TRANSACTION after_delete -设置保存点设置保存点 UPDATE employee SET department_id=D001 WH
73、ERE department_id=D005 IF (error =0 OR rowcount =0)- 判断是否出错或无记录判断是否出错或无记录 BEGIN ROLLBACK TRANSACTION after_delete - 如果出错回滚到保存点如果出错回滚到保存点 COMMIT TRANSACTION my_transaction_delete - 出错回滚后提交出错回滚后提交 END ELSE COMMIT TRANSACTION my_transaction_delete - 未出错进行提交未出错进行提交GO例例10-12 删除仓储部，再将仓储部的职工划分到总经理办。删除仓储部，再
74、将仓储部的职工划分到总经理办。官喇定二岛冗淘恨扁瘦侈戈嘘镜宾几斯戮轩瓢雇刃耘酮桨盒旋池枯脚贴址第10章存储过程与触发器第10章存储过程与触发器CREATE TRIGGER trig_uptab ON goodsFOR UPDATEASSAVE TRANSACTION tran_uptab -设置保存点设置保存点INSERT INTO newgoods SELECT * FROM insertedIF (error0) -IF语句控制回滚操作语句控制回滚操作BEGIN ROLLBACK TRANSACTION tran_uptabEND例例10-13 为表为表goods定义触发器定义触发器tri
75、g_uptab，如果，如果goods表更新数据，则把新数据复制到表表更新数据，则把新数据复制到表newgoods中，中，若出错，则取消复制操作。若出错，则取消复制操作。漳咨锄雪迪磊层胰补玉身零扼毡缺录拿圃篮芭奖揪仆培削绞鲍黔蘸淮斑名第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理n和和BEGINEND语句类似，语句类似，BEGIN TRANSACTION和和COMMIT TRANSACTION语句也可以进行嵌套，即事务语句也可以进行嵌套，即事务可以嵌套执行。可以嵌套执行。3事务嵌套事务嵌套杯瘁到瓣剐磐颅碘礁库癣蛊仙拳阻具似荷攫盼栽酚乾穗底议宏矗逾凿蓑
76、鲜第10章存储过程与触发器第10章存储过程与触发器10.5.2 10.5.2 事务管理事务管理CREATE TABLE employee_tran( num char(2) NOT NULL, cname char(6) NOT NULL)GO BEGIN TRANSACTION Tran1 -TRANCOUNT为为1 INSERT INTO employee_tran VALUES (01, Zhang) BEGIN TRANSACTION Tran2 -TRANCOUNT为为2 INSERT INTO employee_tran VALUES (02, Wang) BEGIN TRANSA
77、CTION Tran3 -TRANCOUNT为为3 PRINT TRANCOUNT INSERT INTO employee_tran VALUES (03, Li) COMMIT TRANSACTION Tran3 -TRANCOUNT为为2 PRINT TRANCOUNT COMMIT TRANSACTION Tran2 -TRANCOUNT为为1 PRINT TRANCOUNTCOMMIT TRANSACTION Tran1 -TRANCOUNT为为0PRINT TRANCOUNT例例10-14 提交事务。提交事务。运行结果：运行结果：3210凋膝毅较迢霞锈修皖啄肇亏桓培侦肖丸夸杰丈好裁
78、钻赋痈嘴移谜买乌掇砌第10章存储过程与触发器第10章存储过程与触发器n锁锁(Lock)作为一种安全机制，用于控制多个用户的并发操作为一种安全机制，用于控制多个用户的并发操作，以防止用户读取正在由其他用户更改的数据或者多个作，以防止用户读取正在由其他用户更改的数据或者多个用户同时修改同一数据，从而确保事务完整性和数据库一用户同时修改同一数据，从而确保事务完整性和数据库一致性。致性。n锁机制用于解决并发事务对同一资源或者数据的竞争，从锁机制用于解决并发事务对同一资源或者数据的竞争，从而可能导致的数据不一致问题。而可能导致的数据不一致问题。n虽然虽然SQL Server会自动强制执行锁，但是用户可以
79、通过对会自动强制执行锁，但是用户可以通过对锁进行了解并在应用程序中自定义锁来设计出更有效率的锁进行了解并在应用程序中自定义锁来设计出更有效率的应用程序。应用程序。10.6 SQL Server的锁机制的锁机制紧挪襄搐裕腑敝鬃脖琉仑百惭流绝投启肾验厌母狐饲逝诽享吐涉喻岛怀庶第10章存储过程与触发器第10章存储过程与触发器n当对一个数据源加锁后，此数据源就有了一定的访问限制，当对一个数据源加锁后，此数据源就有了一定的访问限制，称对此数据源进行了称对此数据源进行了“锁定锁定”。nSQL Server有多种粒度锁，允许一个事务锁定不同类型的有多种粒度锁，允许一个事务锁定不同类型的资源。资源。 数据行数
80、据行(Row)：数据页中的单行数据。：数据页中的单行数据。 索引行索引行(Key)：索引页中的单行数据，即索引的键值。：索引页中的单行数据，即索引的键值。 页页(Page)：页是：页是SQL Server存取数据的基本单位，其存取数据的基本单位，其大小为大小为8KB。 扩展盘区扩展盘区(Extent)：一个盘区由：一个盘区由8个连续的页组成。个连续的页组成。 表表(Table)。 数据库数据库(Database)。 10.6.1 锁模式锁模式辰诣都睹卓珊噪晴亏符护庇蓖衫枚蠢曙裕浴想访躲阿溅揖卉万笑豪歪曙诣第10章存储过程与触发器第10章存储过程与触发器nSQL Server使用不同的锁模式锁定
81、资源，这些锁模式确定使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。了并发事务访问资源的方式。(1) 共享锁共享锁(Shared Lock)(2) 排它锁排它锁(Exclusive Lock)(3) 更新锁更新锁(Update Lock)辜醛诱雅灯削揖牛抵貌胀入梆巫哗德闷勉栏都闽美出氟秀农踞谚隧菠懈触第10章存储过程与触发器第10章存储过程与触发器从程序员的角度，锁可以分为以下两种类型：从程序员的角度，锁可以分为以下两种类型： (1) 乐观锁乐观锁(Optimistic Lock)。乐观锁假定在处理数据时，。乐观锁假定在处理数据时，不需要在应用程序的代码中做任何事情就可以直接
82、在记录不需要在应用程序的代码中做任何事情就可以直接在记录上加锁，即完全依靠数据库来管理锁的工作。一般情况下，上加锁，即完全依靠数据库来管理锁的工作。一般情况下，当执行事务处理时，当执行事务处理时，SQL Server会自动对事务处理范围内会自动对事务处理范围内更新到的表做锁定。更新到的表做锁定。(2) 悲观锁悲观锁(Pessimistic Lock)。悲观锁需要程序员直接。悲观锁需要程序员直接管理数据或对象上的加锁处理，并负责获取、共享和放弃管理数据或对象上的加锁处理，并负责获取、共享和放弃正在使用的数据上的任何锁。正在使用的数据上的任何锁。10.6.1 10.6.1 锁模式锁模式喝劳杨弱坎蹋
83、坠霜熟沃匹办沏柏董梅鲜赤娩待齿税阻锨袋傀糜么累迎校毯第10章存储过程与触发器第10章存储过程与触发器n隔离隔离(Isolation)是计算机安全技术中的概念，其本质上是一是计算机安全技术中的概念，其本质上是一种封锁机制。它是指自动数据处理系统中的用户和资源的相关种封锁机制。它是指自动数据处理系统中的用户和资源的相关牵制关系，也就是用户和进程彼此分开，且和操作系统的保护牵制关系，也就是用户和进程彼此分开，且和操作系统的保护控制也分开来。控制也分开来。n事务准备接受不一致数据的级别称为隔离级别事务准备接受不一致数据的级别称为隔离级别(Isolation Level)。n隔离级别是一个事务必须与其他
84、事务进行隔离的程度。较低的隔离级别是一个事务必须与其他事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。应用程序要求的隔离级别确定了面影响。应用程序要求的隔离级别确定了SQL Server使用的锁使用的锁行为。行为。10.6.2 隔离级别隔离级别磋纽吉极又蹲牛运绷相漾堵猴雇揖贺挟悍化娱镇志棍彼湛穷音朝遣型绎鹤第10章存储过程与触发器第10章存储过程与触发器10.6.2 10.6.2 隔离级别隔离级
85、别(1) 提交读提交读(READ COMMITTED)。它是。它是SQL Server的默认级别。的默认级别。在此隔离级别下，在此隔离级别下，SELECT语句不会也不能返回尚未提交语句不会也不能返回尚未提交(Committed)的数据的数据(即脏数据即脏数据)。(2) 未提交读未提交读(READ UNCOMMITTED)。与提交读隔离级别相反，。与提交读隔离级别相反，它允许读取脏数据，即已经被其他用户修改但尚未提交的数据。它它允许读取脏数据，即已经被其他用户修改但尚未提交的数据。它是最低的事务隔离级别，仅可保证不读取物理损坏的数据。是最低的事务隔离级别，仅可保证不读取物理损坏的数据。(3) 可
86、重复读可重复读(REPEATABLE READ)。在此隔离级别下，用。在此隔离级别下，用SELECT语句读取的数据在整个语句执行过程中不会被更改。此选项会影响语句读取的数据在整个语句执行过程中不会被更改。此选项会影响系统的效能，非必要情况最好不用此隔离级别。系统的效能，非必要情况最好不用此隔离级别。(4) 可串行读可串行读(SERIALIZABLE)。将共享锁保持到事务完成，而不是。将共享锁保持到事务完成，而不是不管事务是否完成都在不再需要所需的表或数据页时就立即释放共不管事务是否完成都在不再需要所需的表或数据页时就立即释放共享锁。它是最高的事务隔离级别，事务之间完全隔离。享锁。它是最高的事务
87、隔离级别，事务之间完全隔离。SQL Server支持以下支持以下4种隔离级别种隔离级别烫褂罚鳃剃闽攫需遮麓蜗喂镇厚悬乏匀乱欠釜缴烩藏阎协赋蒲拇帖强舞靛第10章存储过程与触发器第10章存储过程与触发器10.6.2 10.6.2 隔离级别隔离级别使用使用SET TRANSACTION ISOLATION LEVEL语句设置会话的隔离级别语句设置会话的隔离级别 n其语法格式如下：其语法格式如下：SET TRANSACTION ISOLATION LEVELREAD COMMITTED| READ UNCOMMITTED| REPEATABLE READ| SERIALIZABLE p一次只能设置一个
88、选项。一次只能设置一个选项。侨柞考丰棵铱抚嘎贬册牺闷愿汹婶截恢耻弱芬昭谐什败胃花趟仲肇杯轮毙第10章存储过程与触发器第10章存储过程与触发器1用企业管理器查看和终止锁用企业管理器查看和终止锁10.6.3 查看和终止锁查看和终止锁徽夏吾扼拢望嘴戳徐庞褪枉勃瑟救冤饶搬递烁垛撅惮剁程师汇疡羊歼钵扭第10章存储过程与触发器第10章存储过程与触发器n系统存储过程系统存储过程sp_lock的语法格式如下：的语法格式如下：sp_lock spidspid是是SQL Server的进程编号，它可以在的进程编号，它可以在master.dbo.sysprocesses系统表中查到。系统表中查到。spid数据类型为
89、数据类型为int，如果不指定，如果不指定spid，则显示所有的锁。，则显示所有的锁。2用系统存储过程用系统存储过程sp_lock 查看锁查看锁n例：例： USE masterEXEC sp_lock n例：例： USE masterEXEC sp_lock 52焙避终苇囊透芳湾翰移歉羽认挞姚才眉杜得蚀冒潜蹄儡丫雍梯噪坎蓖益皇第10章存储过程与触发器第10章存储过程与触发器10.6.4 死锁及其防止死锁及其防止n死锁死锁(Deadlocking)是在多用户或多进程状况下，为使用是在多用户或多进程状况下，为使用同一资源而产生的无法解决的争用状态。同一资源而产生的无法解决的争用状态。n死锁会造成资源
90、的大量浪费，甚至会使系统崩溃。因此，死锁会造成资源的大量浪费，甚至会使系统崩溃。因此，在在SQL Server 2000中，通常由锁监视器线程自动定期对中，通常由锁监视器线程自动定期对死锁进行检测。当识别死锁后，死锁进行检测。当识别死锁后，SQL Server自动设置一个自动设置一个事务结束死锁进程。事务结束死锁进程。nSQL Server 2000提供了自动发现和解除死锁的机制，除提供了自动发现和解除死锁的机制，除非特殊情况，非特殊情况，DBA不用手工干预锁的授予和死锁的解除。不用手工干预锁的授予和死锁的解除。nSQL Server解决死锁的原则是解决死锁的原则是“牺牲一个比两个都死强牺牲一
91、个比两个都死强”，即挑出一个进程作为牺牲者，将其事务回滚，并向执行，即挑出一个进程作为牺牲者，将其事务回滚，并向执行此进程的程序发送编号为此进程的程序发送编号为1205的错误信息。的错误信息。n虽然死锁不能完全避免，但可以使死锁的数量减至最少。虽然死锁不能完全避免，但可以使死锁的数量减至最少。侣硝盗企妮陨殊钓敞对逢曙棵胞匠界迎搪昭眯威围臀瘁铂筷清横所蔫节趴第10章存储过程与触发器第10章存储过程与触发器防止死锁的途径防止死锁的途径n防止死锁的途径就是不能让满足死锁条件的情况防止死锁的途径就是不能让满足死锁条件的情况发生，为此，用户需要遵循以下原则：发生，为此，用户需要遵循以下原则：(1) (1
92、) 尽量避免并发地执行涉及到修改数据的语句。尽量避免并发地执行涉及到修改数据的语句。(2) (2) 要求每个事务一次就将所有要使用的数据全部加要求每个事务一次就将所有要使用的数据全部加锁，否则就不予执行。锁，否则就不予执行。(3) (3) 预先规定一个封锁顺序，所有的事务都必须按这预先规定一个封锁顺序，所有的事务都必须按这个顺序对数据执行封锁。例如，不同的过程在事务内个顺序对数据执行封锁。例如，不同的过程在事务内部对对象的更新执行顺序应尽量保持一致。部对对象的更新执行顺序应尽量保持一致。(4) (4) 每个事务的执行时间不可太长，对程序段长的事每个事务的执行时间不可太长，对程序段长的事务可考虑
93、将其分割为几个事务。务可考虑将其分割为几个事务。化庞族子沁胁辟呆壕缘兄矾尧她板冬律巫兆嘶淀扶炸狮眼膜炭腻啄庶楚起第10章存储过程与触发器第10章存储过程与触发器(1)存储过程是一组存储过程是一组SQL语句和流程控制语句的集合，以一个名字存储并语句和流程控制语句的集合，以一个名字存储并作为一个单元处理。存储过程用于完成某项任务，它可以接受参数、返回作为一个单元处理。存储过程用于完成某项任务，它可以接受参数、返回状态值和参数值，并且实现嵌套调用。状态值和参数值，并且实现嵌套调用。(2) 触发器就其本质而言是一种特殊的存储过程，有触发器就其本质而言是一种特殊的存储过程，有3种类型：插入触发种类型：插
94、入触发器、更新触发器和删除触发器。器、更新触发器和删除触发器。(3)创建、删除、查看、修改存储过程和触发器可以使用企业管理器或创建、删除、查看、修改存储过程和触发器可以使用企业管理器或Transact-SQL语句。语句。(4)存储过程和触发器的各种信息的查看、修改还可以使用系统存储过程存储过程和触发器的各种信息的查看、修改还可以使用系统存储过程sp_helptext、sp_rename、sp_helptrigger、sp_depends实现。实现。 (5)事务是一个操作序列，它包含了一组数据库操作命令，所有的命令作事务是一个操作序列，它包含了一组数据库操作命令，所有的命令作为一个整体一起向系统
95、提交或撤消操作请求，即要么都执行，要么都不执为一个整体一起向系统提交或撤消操作请求，即要么都执行，要么都不执行。行。 (6)锁是在多用户环境下对资源访问的一种限制。当对一个数据源加锁后，锁是在多用户环境下对资源访问的一种限制。当对一个数据源加锁后，此数据源就有了一定的访问限制。此数据源就有了一定的访问限制。(7) 事务与锁也是保证数据完整性和正确性的机制，可以确保数据能够正事务与锁也是保证数据完整性和正确性的机制，可以确保数据能够正确地被存储、修改，而不会造成数据在存储或修改过程中因事故或其他用确地被存储、修改，而不会造成数据在存储或修改过程中因事故或其他用户的中断而导致的数据不完整。户的中断而导致的数据不完整。本章小结本章小结崔节讶赴醇郡臭销挣棒务霄暖一舰珐毒臀险赃合铡茧矽趁童曹桓沂册器血第10章存储过程与触发器第10章存储过程与触发器
