存储过程与触发器节

8.6节存储过程与触发器8.6.1存储过程的基本概念8.6.2创建存储过程8.6.3执行存储过程8.6.4管理存储过程8.6.5触发器的基本概念8.6.6创建触发器8.6.7管理触发器8.6.1存储过程的基本概念在使用Transact-SQL语言编程的过程中，可以将某些多次调用以实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer服务器通过过程名调用它们，这些过程就叫做存储过程。

存储过程分为系统存储过程和用户自定义的存储过程。系统存储过程由SQLServer2000提供，用户可以直接使用。用户自定义存储过程需要用户自己创建和维护。系统存储过程使用“sp_”作为前缀。存储过程的优点：可以在单个存储过程中执行一系列SQL语句。可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。存储过程一旦执行一次后，其执行的计划就会驻留在计算机的高速缓冲存储器中。其后对同一个存储过程的调用就可以直接利用编译后在高速缓存中的二进制形式来完成操作。8.6.2创建存储过程在SQL-Server中，可以使用三种方法创建存储过程：使用向导创建存储过程。

使用SQL-Server企业管理器创建存储过程。使用Transact-SQL语句中的CreateProcedure命令创建存储过程。默认情况下，创建存储过程的许可权归属数据库的所有者，数据库的所有者可以授权给其他用户。

使用向导创建存储过程

（1）在企业管理器中选中某个SQL-Server服务器中的数据库，这里选中scdb数据库。选择工具菜单中的“向导”菜单项，系统会弹出“选择向导”对话框。选中“创建存储过程向导”选项。（2）单击“确定”按钮，出现“欢迎使用创建存储过程向导”对话框。（3）单击“下一步”按钮，出现“选择数据库”对话框，该对话框用于选择创建存储过程中使用的数据库。（4）选择数据库，单击“下一步”按钮，出现“选择操作对象”对话框，在该对话框中，列出了所有可选择的表，以及可以对表进行的数据库操作，即插入删除和更新。图9-4“选择操作对象”对话框

（5）单击“下一步”按钮，出现确认存储过程信息对话框。

使用企业管理器创建存储过程

（1）在企业管理器中选中某个SQL-Server服务器中的数据库，这里选中scdb数据库。右键单击数据库下的“存储过程”选项，弹出快捷菜单，（2）在快捷菜单中选择“新建存储过程”命令出现“新建存储过程”对话框。（3）在“新建存储过程”对话框的“文本”列表框中输入存储过程名称和程序语句

CREATEPROCEDUREquery_procASSelect*fromsc存储过程query_proc完成的功能是从sc表中查询全部数据。（4）单击“检查语法”按钮，执行语法正确性检验。（5）单击“确定”按钮，返回企业管理器窗口，可以看到所创建的存储过程。

使用Transact-SQL语句创建存储过程CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement

procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。

@parameter：过程中的参数。

data_type：参数的数据类型。

Default：参数的默认值。OUTPUT：表明参数是返回参数。

AS：指定过程要执行的操作。

sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。例：不带有参数的存储过程下面的存储过程从scdb数据库的三个表中返回学生选课情况，结果包括学号、姓名、课程名、成绩。该存储过程不使用任何参数。usezzzGOCREATEPROCEDUREproc1ASSELECTs.sno,sname,cname,scoreFROMs,c,scwheres.sno=sc.snoando=ogoexecproc1例：带有参数的存储过程设计一个带有参数的存储过程，该参数用于传递查询成绩的范围，根据该参数检索出符合该分数段的学生选课信息。

usezzzGOCREATEPROCEDUREproc2(@lowint,@highint)ASSELECTs.sno,sname,cname,scoreFROMs,c,scwheres.sno=sc.snoando=oand(scorebetween@lowand@high)go8.6.3执行存储过程[[EXEC[UTE]]{[@return_status=]{procedure_name]}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]EXEC[UTE]：为执行存储过程的关键字。如果所执行存储过程语句为批处理中的第一个语句时，则可以省略EXECUTE关键字。@return_status：是一个可选的整型变量，保存存储过程的返回状态。@parameter：是过程参数，在CREATEPROCEDURE语句中定义。Value：是过程中参数的值。@variable：是用来保存参数或者返回参数的变量。OUTPUT：指定存储过程必须返回一个参数。

DEFAULT：根据过程的定义，提供参数的默认值。

1．不带参数的存储过程的调用：USEzzzEXECUTEproc12．带参数的存储过程的调用：Execproc275，85或者execproc2@low=75,@high=853．自动执行的存储过程可以通过调用一个系统存储过程来设置一个存储过程为自动执行的存储过程。这样的存储过程可以在SQLserver启动时自动执行。这个系统存储过程是sp_procoption,其调用语法格式为：sp_procoption[@ProcName=]'procedure',[@OptionName=]'option',[@OptionValue=]'value'

[@ProcName=]'procedure'：是要为其设置或查看选项的过程名。

[@OptionName=]'option'：要设置的选项的名称。option的唯一值是startup，该值设置存储过程的自动执行状态。设置为自动执行的存储过程会在每次MicrosoftSQLServer启动时运行。

[[@OptionValue=]'value']：表示选项是设置为开（true或on）还是关（false或off）。8.6.4管理存储过程

查看存储过程

创建存储过程之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程查看用户创建的存储过程。

1．使用企业管理器查看用户创建的存储过程在企业管理器中，打开指定的服务器和数据库项，并单击scdb中的“存储过程”项，此时在右边的窗口中显示出scdb数据库中的所有存储过程。

右键单击要查看的存储过程，从弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”选项。或者左键双击要查看的存储过程，也可以弹出“存储过程属性”选项。在此对话框中能够看到存储过程的源代码。

2．使用系统存储过程查看用户创建的存储过程

sp_helpsp_helptextsp_dependssp_stored_procedures

修改存储过程1．使用企业管理器修改存储过程

2．使用Transact-SQL语句修改存储过程ALTERPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statementTHANKYOUSUCCESS2023/12/626可编辑

重命名存储过程1．使用企业管理器重命名存储过程名称在企业管理器中，右键单击要更名的存储过程，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。

2．使用系统存储过程重命名存储过程名称

sp_renameold_name，new_name例如：USEzzzGOsp_renameproc1,proc3GO

删除存储过程1．使用企业管理器删除存储过程在企业管理器中，右键单击要更名的存储过程，从弹出的快捷菜单中选择“删除”选项，弹出“除去对象”对话框，在该对话框中，单击“全部除去”按钮，即可完成删除操作。2．使用Transact-SQL语句删除存储过程

DROPPROCEDURE{procedureNmae}[,...n]

ProcedureName是要删除的存储过程或存储过程组的名称。例如：USEsalesGODROPPRODEDUREproc1GO8.6.5触发器概述

触发器的概念

触发器（trigger）是一种特殊的存储过程，它不同于一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。触发器是一个功能强大的工具，与表紧密连接，可以看作是表格定义的一部分。当用户修改（INSERT、UPDATE或DELETE）指定表或视图中的数据时，该表中的相应的触发器就会自动执行。通常可以在触发器内编写一段自动执行的程序，用于保证数据操作的完整性，从而扩展了对默认值、约束和规则的完整性检查。

触发器的优点1．触发器是自动执行的，当用户对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。

2．触发器可以通过数据库中的相关表进行层叠更改，实现多个表之间数据的一致性和完整性。

3．触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。

触发器的分类

AFTER触发器：又称后触发器（AfterTrigger）这种类型的触发器将在数据变动（UPDATE、INSERT和DELETE操作）完成后才被触发。指定AFTER与指定FOR相同。

AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。Server2000默认的为AFTER触发器。

INSTEADOF触发器：又称前触发器（InsteadOfTrigger）INSTEADOF触发器在数据变动以前被触发，并取代变动数据的操作（UPDATE、INSERT和DELETE操作），而去执行触发器定义的操作。INSTEADOF触发器可以在表或视图上定义。在表或视图上，每个UPDATE、INSERT和DELETE语句最多可以定义一个INSTEADOF触发器。deleted表和inserted表

deleted表用于存储DELETE和UPDATE语句所影响的行的备份。在执行DELETE或UPDATE语句时，行从基本表中删除，并转移到deleted表中。deleted表和基本表通常没有相同的行。inserted表用于存储INSERT和UPDATE语句所影响的行的备份。在一个插入或更新事务处理中，新建行被同时添加到inserted表和基本表中。inserted表中的行是基本表中新行的备份。8.6.6创建触发器创建触发器前应考虑下列问题：

CREATETRIGGER语句必须是批处理中的第一个语句。创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。触发器为数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEADOFDELETE和INSTEADOFUPDATE触发器。虽然TRUNCATETABLE语句类似于没有WHERE子句（用于删除行）的DELETE语句，但它并不会引发DELETE触发器，因为TRUNCATETABLE语句没有记录。创建触发器时需指定：

触发器名称。在其上定义触发器的表。触发器将何时激发。激活触发器的数据修改语句。有效选项为INSERT、UPDATE或DELETE。多个数据修改语句可激活同一个触发器。执行触发操作的编程语句。

使用SQL-Server企业管理器创建触发器

①打开企业管理器，在控制台根目录下依次展开服务器组、服务器节点、数据库节点。②选择某一个数据库，并双击【表】节点，在右侧窗口的列表中选择其中的一张表sc表。③右击所选中的表，在弹出的下拉菜单中选择【所有任务】，并继续选择级联菜单中的【管理触发器】命令，将打开【触发器属性】对话框。④在【触发器属性】对话框的文本框中，将[TRIGGERNAME]修改为新建触发器的名称，并输入触发器所包含的SQL语句。⑤通过点击【检查语法】按钮来判断触发器的语法是否正确，最后，点击【确定】按钮保存新建的触发器。

（6）单击“应用”按钮，在名称下拉列表中出现新创建的TR_UpdateSalers触发器的名称，单击“确定”按钮，即可关闭该对话框，成功创建触发器。

使用Transact-SQL语句创建触发器

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]}]sql_statement[...n]}}trigger_name：是触发器的名称。Table|view：是在其上执行触发器的表或视图。AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。如果仅指定FOR关键字，则AFTER是默认设置。INSTEADOF：指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。AS：是触发器要执行的操作。sql_statement：是触发器的条件和操作。IFUPDATE(column)：测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。【例】在数据库SCDB中设计一个触发器，该触发器的作用为：当在s表中删除某一个学生时，在sc表中有关该学生的选课信息也全部删除。

提示：在此例中，由于涉及到了对学生表删除操作，因而需要设计一个DELETE类型的触发器。

在查询分析器中运行如下命令：

USEscdb GO

CREATETRIGGERdel_studentONSAFTERDELETE

AS

DELETEFROMscWHEREsnoIN(SELECTsnoFROMDELETED)Print'删除表sc中的相关记录条数为'+str(@@rowcount)+'条'

GO

【例】在数据库scdb中设计一个触发器，该触发器能够保证在sc表中添加新的记录时，学生的学号必须已经存在于s表中。提示：设计该触发器有助于实现学生信息的完整性。在此例中由于涉及到了sc表中的添加操作，因而需要设计一个INSERT类型的触发器。

在查询分析器中运行如下命令：USExscjGOCREATETRIGGERinsert_SCONSCAFTERINSERTASIFEXISTS(SELECT*FROMINSERTEDWHEREsnoIN(SELECTsnoFROMs))PRINT'添加成功！'ELSEBEGINPRINT‘添加的学生不存在！'ROLLBACKTRANSACTIONEND创建了触发器insert_sc之后，我们可以添加新的学生纪录进行测试，例如：

insertintoscvalues(‘10’,‘C1’,80)

由于S表中不存在编号为10的学生，因而添加操作将会被取消。ROLLBACKTRANSACTION用于回滚已经完成的操作。运行结果如图所示。8.6.3修改与删