《SQLServer配套资料（第3版）》-模块9 存储过程、触发器

重庆航天职业技术学院数据库应用（SQLServer）/detail/235897295第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创建存储过程在模板中输入以下代码。USEEDUCGOCREATEPROCEDUREselected_nameASBEGINselectSnamefromStudentwhereSID='2020051001'ENDGO9.1.2创建存储过程2．使用T-SQL语句创建存储过程使用T-SQL语句创建存储过程与模板创建存储过程方法类似，其语法格式如下：CREATEPROC[EDURE]过程名[@形参名数据类型,……][,]--多个输入参数用逗号隔开[@变参名数据类型OUTPUT,]--多个输出参数用逗号隔开AS[BEGIN]T-SQL[END]9.1.2创建存储过程【例9-2】创建一个带有输入参数的存储过程selected_sc，查询指定学生选课成绩情况。--创建带有输入参数的存储过程useEDUCgocreateprocedureselected_sc--创建存储过程@idvarchar(10)--输入参数withencryption--对创建文本加密asbeginselects.sid,sname,c.cid,cname,scorefromStudentsinnerjoinSCxons.sid=x.sidinnerjoincourseconx.cid=c.cidwheres.sid=@idendGo调用存储过程：exec

selected_sc

'2020051001'

--常量传值调用方法或declare@temp1char(20)--变量传值调用方法set@temp1='2020051001'execselected_sc@temp19.1.2创建存储过程【例9-3】创建一个带有输入参数和输出参数的存储过程borrowed_num，返回指定读者借阅图书本数。--创建带有一个输入参数和两个输出参数的存储过程useLibraryifexists(selectnamefromsysobjectswherename='borrowed_num'andtype='p')dropprocedureborrowed_numgocreateprocedureborrowed_num--创建存储过程@idvarchar(10),--输入参数@slintoutput,@namechar(8)output--多个输出参数withencryption--创建文本加密asbeginselect@sl=count(*)--统计读者借阅本数fromReaderinnerjoinBorrowonReader.RID=Borrow.RIDwhereReader.RID=@idselect@name=Reader.Rname--查询读者姓名fromReaderwhereReader.RID=@idend调用存储过程：declare@R_idchar(10)declare@R_bsintdeclare@Rnamechar(8)set@R_id='2021051001'execborrowed_num@R_id,@R_bsoutput,@Rnameoutput--实参表print@R_id+'：'+@Rname+'借阅图书本数：'+convert(char(2),@R_bs)9.1.2管理存储过程存储过程的管理所涉及的内容包括查看存储过程中定义的T-SQL语句的文本信息、修改存储过程的定义，删除不需要的存储过程等。1．查看存储过程的信息sp_help'存储过程名'：用于查看存储过程的一般信息，如存储过程的名称、属性、类型和创建时间。sp_helptext'存储过程名'：用于查看存储过程的正文信息。sp_depends'存储过程名'|'表名'：用于查看指定存储过程所引用的表或者指定的表涉及到的所有存储过程。【例9-4】分别用sp_help、sp_helptext、sp_depends查询数据库“EDUC”的存储过程“selected_sl”的信息。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触发器

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事件触发器①INSTEADOF触发器

INSERT事件的INSTEADOF触发器是对于指定的表，在执行数据行插入语句INSERT事件之前被激发的一段程序代码。9.2.2创建DML触发器

【9-6】定义一个INSERT触发器，在数据库“EDUC”的表“Student”中添加一行学生选课信息前，查询表“Student”和表“Course”对应学号与课程号是否存在。useEDUCifexists(selectnamefromsysobjects--如果已有触发器tr_borrow_datewherename='tr_sc_insteadofinsert'andtype='tr')droptriggertr_sc_insteadofinsert--删除触发器tr_borrow_dategocreatetriggertr_sc_insteadofinsert--创建触发器tr_borrow_dateonsc--基于表"borrow"insteadofinsert--insert事件之后触发asbegindeclare@sidchar(10)declare@cidchar(8)--inserted表中查询出读者编号rid赋值给局部变量@dzbhset@sid=(selectsidfrominserted)set@cid=(selectcidfrominserted)ifnotexists(select*fromstudentwheresid=@sid)ornotexists(select*fromcoursewhereCID=@cid)beginrollbacktransactionprint'输入学号或课程号不存在！(insteadof触发器)'endelsebegininsertintoscselect*frominsertedPRINT'已经成功录入学生'endendGo9.2.2创建DML触发器

②AFTER触发器INSERT事件的AFTER触发器是对于指定的表，在执行数据行插入语句INSERT事件之后被激发的一段程序代码。9.2.2创建DML触发器

【9-7】定义一个INSERT触发器，在数据库“EDUC”的表“Student”中添加一行学生选课信息后，查询表“Student”和表“Course”对应学号与课程号是否存在，存在插入成功，不存在回滚插入操作。useEDUCifexists(selectnamefromsysobjects--如果已有触发器tr_borrow_datewherename='tr_sc_afterinsert'andtype='tr')droptriggertr_sc_afterinsert--删除触发器tr_borrow_dategocreatetriggertr_sc_afterinsert--创建触发器tr_borrow_dateonsc--基于表"borrow"afterinsert--insert事件之后触发asbegindeclare@sidchar(10)declare@cidchar(8)--inserted表中查询出读者编号rid赋值给局部变量@dzbhset@sid=(selectsidfrominserted)set@cid=(selectcidfrominserted)ifnotexists(select*fromstudentwheresid=@sid)ornotexists(select*fromcoursewhereCID=@cid)beginrollbacktransactionprint'输入学号或课程号不存在！'endaelsebeginPRINT'已经成功录入学生'endendGo9.2.2创建DML触发器

（2）创建DELETE事件触发器①INSTEADOF触发器DELETE事件的INSTEADOF触发器是对于指定的表或视图，在执行DELETE语句事件发生时用来替代DELETE事件的一段程序代码。9.2.2创建DML触发器

【9-7】定义一个INSERT触发器，在数据库“EDUC”的表“Student”中添加一行学生选课信息后，查询表“Student”和表“Course”对应学号与课程号是否存在，存在插入成功，不存在回滚插入操作。useEDUCifexists(selectnamefromsysobjects--如果已有触发器tr_borrow_datewherename='tr_sc_afterinsert'andtype='tr')droptriggertr_sc_afterinsert--删除触发器tr_borrow_dategocreatetriggertr_sc_afterinsert--创建触发器tr_borrow_dateonsc--基于表"borrow"afterinsert--insert事件之后触发asbegindeclare@sidchar(10)declare@cidchar(8)--inserted表中查询出读者编号rid赋值给局部变量@dzbhset@sid=(selectsidfrominserted)set@cid=(selectcidfrominserted)ifnotexists(select*fromstudentwheresid=@sid)ornotexists(select*fromcoursewhereCID=@cid)beginrollbacktransactionprint'输入学号或课程号不存在！'endaelsebeginPRINT'已经成功录入学生'endendGo9.2.2创建DML触发器

（2）创建DELETE事件触发器①INSTEADOF触发器DELETE事件的INSTEADOF触发器是对于指定的表或视图，在执行DELETE语句事件发生时用来替代DELETE事件的一段程序代码。9.2.2创建DML触发器

【9-8】定义一个DELETE事件的INSTEADOF触发器，在数据库“Library”的表“reader”中删除一行借读者信息前，检查该读者是否有书未还，若有书未还不能删除该读者。--创建delete事件的insteadof触发器uselibraryifexists(selectnamefromsysobjectswherename='tr_reader_delinstead'andtype='tr')droptriggertr_reader_delinsteadgocreatetriggertr_reader_delinsteadonreaderINSTEADOFdelete--创建基于delete事件的INSTEADOF触发器asbegindeclare@num_borrowintselect@num_borrow=lendnumfromdeleted--从临时表中获取借阅数量if@num_borrow>0--借阅数量大于说明有未还的图书beginprint'该读者不能删除！还有'+cast(@num_borrowaschar(2))+'本书没还'rollback--事务回滚取消所删除的数据行endelsedeletereaderwhererid='2021051001'print'该读者已被删除！！！'--显示行已经删除endgo9.2.2创建DML触发器

②AFTER触发器DELETE事件的AFTER触发器是对于指定的表，在执行数据行DELETE语句事件之后被激发的一段程序代码。9.2.2创建DML触发器

【9-9】定义一个DELETE事件的AFTER触发器，在数据库“Library”的表“reader”中删除一行借读者信息，检查该读者是否有书未还，若有书未还不能删除该读者。--创建delete事件的after触发器uselibraryifexists(selectnamefromsysobjectswherename='tr_reader_delafter'andtype='tr')droptriggertr_reader_delaftergocreatetriggertr_reader_delafteronreaderafterdelete--创建基于delete事件的AFTER触发器asbegindeclare@num_borrowintselect@num_borrow=lendnumfromdeleted--从临时表中获取借阅数量if@num_borrow>0--借阅数量大于说明有未还的图书beginprint'该读者不能删除！还有'+cast(@num_borrowaschar(2))+'本书没还'rollback--事务回滚取消所删除的数据行endelseprint'该读者已被删除！！！'--显示行已经删除endGo9.2.2创建DML触发器

（3）创建UPDATE事件的AFTER触发器UPDATE事件的AFTER触发器是对于指定的表，在执行数据行UPDATE语句事件之后被激发的一段程序代码。【9-10】定义一个UPDATE事件触发器，保护数据库“EDUC”的表“SC”中“成绩”不能任意修改。useEDUCgocreatetriggerrt_updateonSCforupdateasif(update(Score))--update函数保护属性列Beginprint'事务不能被处理，基础数据不能修改！'rollbackendelseprint'数据修改成功！'9.2.2创建DML触发器

【9-11】定义一个UPDATE事件触发器，同步更新保护数据库“EDUC”的表“Student”中的“SID”与选课表“SC”中的“SID”。useEDUCgoifexists(selectnamefromsysobjectswherename='tr_stu_update'andtype='tr')droptriggertr_stu_updategocreatetriggertr_stu_updateonStudentforupdateasifupdate(SID)begindeclare@idinsertchar(10) select@idinsert=SIDfrominserted declare@iddelchar(10) select@iddel=SIDfromdeletedupdateSCsetSC.SID=@idinsertwhereSC.SID=@iddelEnd9.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触发器来防止服务器中的任意一个数据库被修改或删除。create