《SQL Server 数据库技术及应用》-第9章 存储过程、触发器

第9章存储过程、触发器能根据项目需求设计存储过程

。能根据项目需求设计触发器

。第9章存储过程、触发器

任务设计：9.1认识存储过程9.2认识触发器9.3任务训练第9章存储过程、触发器本章学习内容在数据库系统开发中的位置9.1任务1：认识存储过程

理解存储过程的概念1掌握存储过程的创建与使用方法2任务目标：9.1任务1：认识存储过程第一步存储过程基本知识第二步创建用户存储过程第三步调用存储过程9.1.1存储过程的概念

存储过程（StoredProcedure）是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库对象。它们可以接受参数、输出参数，返回单个或者多个结果集以及返回值。客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。存储过程与其它编程语言中的过程有些类似。1．存储过程的优点存储过程是一种独立的数据库对象，它在服务器上创建和运行，与存储在客户端计算机本地的T-SQL语句相比，有以下优点。（1）模块化设计每个存储过程是一个模块，具备一定的功能。一旦创建，以后可多次调用，从而极大地提高了程序的重用性。（2）执行速度较快存储过程在创建时，经过编译已经存储在系统表中，以后再次调用该存储过程时不必再进行编译和优化，执行步骤的减少提高了执行速度。（3）减少网络流量一个需要百行的T-SQL代码的操作可以通过一条执行存储过程代码的语句来调用，而不需要在网络中发送数百行代码，从而大大减轻了网络的负荷。（4）安全性增强授予用户执行存储过程的权限，不授予用户直接访问存储过程中涉及的表的权限，从而保证了表中数据的安全。9.1.1存储过程的概念

2．存储过程的分类SQLServer提供了3种类型的存储过程。（1）系统存储过程：数据库基础管理工作的一类特殊存储过程，存储在源数据库中，并且带有sp_前缀。SQLServer中的许多管理活动都是通过存储过程执行的。sp_rename、sp_help等。（2）用户自定义存储过程：用户在SQLServer中通过SQL语句创建存储过程，称为用户存储过程。本章后面介绍的存储过程主要是指用户存储过程。（3）扩展存储过程：扩展存储过程是指SQLServer的实例可以动态加载和运行的DLL（DynamicLinkLibrary，动态链接库）。扩展存储过程直接在SQLServer的实例的地址空间中运行，可以使用SQLServer扩展存储过程API完成编程。9.1.2创建存储过程（1）简化用户的操作。经常使用的查询可以定义为视图，从而使得用户不必为以后的操作每次都制定全部的条件。（2）提高安全性。通过视图，可通过制定限制条件和指定列限制用户对基本表的访问，用户只能查询和修改他们所能见到的数据，数据库中的其它数据既看不见也取不到，达到数据安全保护的目的。（3）提高逻辑数据独立性。视图可以使应用程序和数据库表在一定程度上独立。有了视图，程序可以建立在视图之上，从而使应用程序与数据库表被视图分隔开。9.1.2创建存储过程1．使用SSMS创建存储过程【例9-1】创建存储过程，完成对数据库“Library”实现简单查询功能。（详见P229）在模板中输入以下代码。USELibraryGOcreateprocedureborrowed_numasbeginselectrname,lendnumfromreaderwherername='杨静'end执行存储过程“borrowed_num”的代码如下：USELibraryGOborrowed_num或USELibraryEXECborrowed_num9.1.2创建存储过程2．使用T-SQL语句创建存储过程使用T-SQL语句创建存储过程与模板创建存储过程方法类似，其语法格式如下：CREATEPROC[EDURE]过程名[@形参名数据类型,……][,]--多个输入参数用逗号隔开[@变参名数据类型OUTPUT,]--多个输出参数用逗号隔开AS[BEGIN]T-SQL[END]9.1.2创建存储过程【例9-2】创建一个带有输入参数的存储过程borrowed_book，查询指定读者编号的读者的借书情况。--创建带有输入参数的存储过程USELibraryGOcreateprocedureborrowed_book--创建存储过程@namevarchar(10)--输入参数withencryption--对创建文本加密asbeginselectr.rid,r.rname,b.bid,k.bname,b.lenddatefromreaderrinnerjoinborrowbonr.rid=b.ridinnerjoinbookkonb.bid=k.bidwherername=@nameendGO调用存储过程：execborrowed_book'杨静'--常量传值调用方法或declare@temp1char(20)--变量传值调用方法set@temp1='杨静'execborrowed_book@temp19.1.2创建存储过程【例9-3】创建一个带有输入参数和输出参数的存储过程selected_sl，返回指定学生所选科目数量。--创建带有一个输入参数和一个输出参数的存储过程USEEDUCifexists(selectnamefromsysobjectswherename='selected_sl'andtype='p')dropprocedureselected_slGOcreateprocedureselected_sl--创建存储过程@namevarchar(10),--输入参数@slintoutput--输出参数withencryption--创建文本加密asbeginselect@sl=count(*)fromStudent,SCwhereStudent.Sname=@nameandStudent.SID=SC.SIDend调用存储过程：USEEDUCdeclare@stu_namechar(10)declare@stu_kmintset@stu_name='杨静'execselected_sl@stu_name,@stu_kmoutput--实参表print@stu_name+'所选科目数量是：'+convert(char(2),@stu_km)9.1.2管理存储过程存储过程的管理所涉及的内容包括查看存储过程中定义的T-SQL语句的文本信息、修改存储过程的定义，删除不需要的存储过程等。1．查看存储过程的信息sp_help'存储过程名'：用于查看存储过程的一般信息，如存储过程的名称、属性、类型和创建时间。sp_helptext'存储过程名'：用于查看存储过程的正文信息。sp_depends'存储过程名'|'表名'：用于查看指定存储过程所引用的表或者指定的表涉及到的所有存储过程。【例9-4】分别用sp_help、sp_helptext、sp_depends查询数据库“EDUC”的存储过程“selected_sl”的信息。（详见P233）9.1.2管理存储过程3．修改存储过程如果需要更改存储过程中的语句或参数，可以删除后重新创建该存储过程，也可以直接修改该存储过程。删除后重新创建存储过程时，所有与该存储过程相关的权限将丢失；修改存储过程时，过程或参数定义会更改，但权限保留。ALTERPROC[EDURE]过程名[@形参名数据类型,……][,]--多个输入参数用逗号隔开[@变参名数据类型OUTPUT,]--多个输出参数用逗号隔开AS[BEGIN]T-SQL[END]修改存储过程使用关键字ALTER，其语法格式与创建存储过程的语法格式基本一样，此处不再赘述。9.1.2管理存储过程

2．删除存储过程删除存储过程的语法格式如为：DROPPROC[EDURE]存储过程名[,…,n]【例9-5】删除数据库“Library”的存储过程“borrowed_num”。USELibrarydropprocborrowed_num也可使用SSMS方式删除存储过程，其操作方式类似于前面章节删除数据库、数据表，此处不再赘述。注意，如果存储过程被其它对象所依赖，则该过程不能被删除，可用“sp_depends”来查看。

9.2任务2：认识存触发器理解触发器的概念1掌握触发器的创建与使用的方法2任务目标：9.2.1触发器概述1.触发器的概念触发器是一种特殊的存储过程，基于表/视图/服务器/数据库创建，满足一定条件下自动执行的，不由用户直接调用，以保证数据库的完整性、正确性和安全性。当触发器所保护的数据发生变化（UPDATE、INSERT、DELETE）或当服务器、数据库中发生数据定义（CREATE、ALTER、DROP）后，自动运行以保证数据的完整性和正确性。通俗地说：用户对数据库或表进行了诸如UPDATE、INSERT、DELETE、CREATE、ALTER、DROP等操作时，SQLServer就会自动执行触发器所事先定义好的语句。9.2.1触发器概述2．触发器的分类（1）DML(DataManipulationLanguage)触发器

在数据库中发生数据操作语言（DML）事件时将启用。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其它表，还可以包含复杂的T-SQL语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，多表操作不一致、磁盘空间不足等），则整个事务即自动回滚。AFTER触发器：将在数据变动（insert、update、delete操作）完成以后才被激发。对变动的数据进行检查，如发现错误，将拒绝或回滚变动的数据。该触发器只能创建在表上，不能创建在视图上；一个表可以有多个after触发器。INSTEADOF触发器：将在数据变动以前被激发，并取代变动数据的操作（insert、update、delete操作），转而去执行触发器定义的操作。一个表只有一个该触发器，可以创建在表上，也可以创建在视图上。9.2.1触发器概述2．触发器的分类（2）DDL(DataDefinitionLanguage)触发器当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相反，它们会为响应多种数据定义语言（DDL）语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。9.2.1触发器概述2．触发器的分类（3）登录触发器登录触发器将为响应LOGON事件而激发存储过程。与SQLServer实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成后且用户会话事件建立之前激发。可以使用登录触发器来审核和控制服务器会话，如通过跟踪登录活动、限制SQLServer的登录名或限制特定登录名的会话数。9.2.2创建DML触发器

1．使用触发器模块创建触发器在【对象资源管理器】窗口中，展开“数据库”—“具体数据库”—“表”节点下要创建触发器的的“具体表”节点，用右键单击“触发器”节点，从弹出的快捷菜单中选择“新建触发器”命令，如图9-11所示。9.2.2创建DML触发器

【例9-6】创建基于表“Reader”、执行DELETE操作的AFTER触发器。当删除一名读者之后，提示“读者被删除！”的信息。USELibraryGOcreatetriggerreader_d--创建触发器onreader--基于表readerfordelete--AFTER为默认值asbeginprint'读者被删除！'endGO单击工具栏中的“执行”按钮，运行成功后，在【对象资源管理器】窗口中，展开“数据库”—“Library”—“表”—“Reader”—“触发器”节点，刷新后，可看到新建的触发器“reader_d”，如图9-13所示。9.2.2创建DML触发器

2．使用T-SQL语句创建DML触发器语法格式如下：CREATETRIGGER触发器ON<表名|视图名>FOR|AFTER|INSTEADOF[INSERT][，][DELETE][，][UPDATE]AS[BEGIN]T-SQL语句[END]将DML触发器分为INSERT、DELETE、UPDATE这3类。INSERT触发器：当数据库表的INSERT触发器执行时，同时将插入新记录到该数据库表和inserted表。DELETE触发器：当数据库表的DELETE触发器执行时，从数据库表删除的数据首先放到deleted表。UPDATE触发器：当数据库的UPDATE触发器执行时系统首先删除原有的记录，并将原有的记录插入到deleted表中，插入的新记录也同时插入到inserted表中。9.2.2创建DML触发器

（1）创建INSERT事件的AFTER触发器INSERT事件的AFTER触发器是对于指定的表，在执行数据行插入语句INSERT…INTO…VALUES事件之后被激发的一段程序代码。【9-7】定义一个INSERT触发器，在数据库“Library”的表“borrow”中添加一行借阅信息时，借阅该书的读者表“reader”中的借阅数量“lendum”增加1本。9.2.2创建DML触发器

--创建insert事件after触发器USELibraryifexists(selectnamefromsysobjects--如果已有触发器tr_borrow_datewherename='tr_borrow_date'andtype='tr')droptriggertr_borrow_date--删除触发器tr_borrow_dateGOcreatetriggertr_borrow_date--创建触发器tr_borrow_dateonborrow--基于表"borrow"afterinsert--insert事件之后触发asbegindeclare@dzbhchar(10)--inserted表中查询出读者编号rid赋值给局部变量@dzbhset@dzbh=(selectridfrominserted)updatereader--读者表中该读者借阅数量加1setlendnum=lendnum+1whererid=@dzbhendGO执行以上代码后，即可在数据库“Library”表“borrow”中创建存储过程“tr_borrow_date”。若执行插入数据行T-SQL语句USELibraryinsertintoborrow(rid,bid)values('2001050001','TP311-051')9.2.2创建DML触发器

（2）创建DELETE事件的AFTER触发器DELETE事件的AFTER触发器是对于指定的表，在执行数据行DELETE语句事件之后被激发的一段程序代码。【9-8】定义一个DELETE触发器，在数据库“Library”的表“reader”中删除一行借读者信息时，检查该读者是否有书未还，若有书未还不能删除该读者。--创建delete事件的after触发器USELibraryifexists(selectnamefromsysobjectswherename='reader_delafter'andtype='tr')droptriggerreader_delafterGOcreatetriggerreader_delafteronreaderfordelete--创建基于delete事件的after触发器asbegin9.2.2创建DML触发器

declare@data_jyintselect@data_jy=lendnum--从临时表中获取借阅数量

fromdeletedif@data_jy>0--借阅数量大于说明有未还的图书

beginprint'该读者不能删除！还有'+convert(char(2),@data_jy)+'本书没还'rollback--事务回滚取消所删除的数据行

endelseprint'该读者已被删除！！！'--显示行已经删除endGO执行以上代码后，即可在数据库“Library”表“reader”中创建存储过程“reader_delafter”。若执行删除数据行T-SQL语句：USELibraryGOdeletereaderwhererid='2009051001'执行以上代码后，会出现如下提示信息：该读者不能删除！还有2本书没还事务在触发器中结束。批处理已中止。9.2.2创建DML触发器

（3）创建UPDATE事件的AFTER触发器UPDATE事件的AFTER触发器是对于指定的表，在执行数据行UPDATE语句事件之后被激发的一段程序代码。【9-9】定义一个UPDATE触发器，保护数据库“Library”的表“ReaderType”中“限借数量”和“限借天数”不能任意修改。USELibraryGOcreatetriggerrt_updateonReaderTypeforupdateasif(update(LimitNum)orupdate(LimitDays))--update函数保护两属性列

beginprint'事务不能被处理，基础数据不能修改！'rollbackendelseprint'数据修改成功！'9.2.2创建DML触发器

执行以上代码后，即可在数据库“Library”表“ReaderType”中创建存储过程“rt_update”。若执行更新数据行T-SQL语句：

updateReaderTypesetLimitNum=10whereTypeID=2执行以上代码后，会出现如下提示信息：事务不能被处理，基础数据不能修改！事务在触发器中结束。批处理已中止。9.2.2创建DML触发器

（4）创建DELETE事件的INSTEADOF触发器DELETE事件的INSTEADOF触发器是对于指定的表或视图，在执行DELETE语句事件发生时用来替代DELETED事件的一段程序代码。【9-10】定义一个INSTEADOF触发器，在数据库“Library”的表“reader”中删除一行借读者信息前，检查该读者是否有书未还，若有书未还不能删除该读者。--创建delete事件的insteadof触发器USELibraryifexists(selectnamefromsysobjectswherename='reader_delinstead'andtype='tr')droptriggerreader_delinsteadGOcreatetriggerreader_delinsteadonreaderINSTEADOFdelete--创建基于delete事件的INSTEADOF触发器as9.2.2创建DML触发器

begindeclare@data_jyintselect@data_jy=lendnum--从临时表中获取借阅数量

fromdeletedif@data_jy>0--借阅数量大于说明有未还的图书

beginprint'该读者不能删除！还有'+convert(char(2),@data_jy)+'本书没还'rollback--事务回滚取消所删除的数据行

endelseprint'该读者已被删除！！！'--显示行已经删除endGO执行以上代码后，即可在数据库“Library”表“reader”中创建存储过程“reader_delinstead”。若执行删除数据行T-SQL语句：USELibraryGOdeletereaderwhererid='2009051001'执行以上代码后，会出现如下提示信息：该读者不能删除！还有2本书没还事务在触发器中结束。批处理已中止。9.2.3创建DDL触发器DDL触发器会为响应多种数据定义语言(DDL)语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。其语法格式如下：CREATETRIGGERtrigger_nameON<ALLSERVER|DATABASE>[WITHENCRYPTION][FOR|AFTER]<事件类型或事件组>[,...n]AS[BEGIN]T-SQL语句[END]9.2.3创建DDL触发器参数说明如下：ALLSERVER：将DDL触发器的作用域应用于当前服务器。DATABASE：将dDEL触发器的作用域应用于当前数据库。WITHENCRYPTION：对CREATETRIGGER语句的文本进行加密。使用WITHENCRYPTION可以防止将触发器作为SQLServer复制的一部分进行发布。事件类型：执行之后将导致激发DDL触发器的T-SQL语句事件的名称。例如CREATE、ALTER、DROP等操作。事件组：预定义的T-SQL语句事件分组的名称。执行任何属于事件组的T-SQL语句事件之后，都将激发DDL触发器。T-SQL语句：指定触发器所执行的T-SQL语句。9.2.3创建DDL触发器【例9-11】在服务器上创建DDL触发器来防止服务器中的任意一个数据库被修改或删除。createtriggertr_safety1onallserver--保护当前服务器中的所有数据库fordrop_database,alter_database--被删除或修改asbeginprint'要删除和修改数据库之前，你必须先禁用触发器tr_safety1!'rollbackendGO

单击工具栏中的“执行”按钮，运行成功后，在【对象资源管理器】窗口中，展开“服务器”—“服务器对象”—“触发器”，可以看到新建的触发器“”，如图9-16所示。当用户试图使用DROP或ALTER命令删除或修改服务器中的数据库时，调用此DDL触发器，此触发器的事务回滚语句ROLLBACK将撤销DROP或ALTER命令的执行。9.2.3创建DDL触发器【例9-12】在数据库上创建DDL触发器来防止数据库中的任意一个表被修改或删除USELibrary--保护"Library"数据库GOcreatetriggertr_safety2ondatabase--保护数据库中的数据表fordrop_table,alter_table--被删除或修改asbeginprint'要删除和修改表之前，你必须先禁用触发器tr_safety2!'rollbackendGO9.2.3创建DDL触发器当用户试图使用DROP或ALTER命令删除或修改数据库中的表