SQLSERVER2000数据库及应用第8章存储过程和触发器的创建与维护

1、第8章 存储过程和触发器的创建与维护北华航天工业学院李伟红本章学习目标n了解存储过程的概念；n掌握创建、执行存储过程的方法；n掌握查看和修改存储过程的方法；n掌握删除存储过程的方法；n了解触发器和一般存储过程的主要区别；n掌握创建和查看触发器的方法；n掌握修改和删除触发器的方法。8.1 概 述8.1.1 存储过程概述n在SQL Server 2000中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。8.1.1 存储过程概述n应该提倡多使用存储过程，原因有以下几点。n（1）实现了模块

2、化编程。n（2）调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。n（3）存储过程可以接受输入参数并可以返回输出值。n（4）存储过程具有对数据库立即访问的功能。n（5）使用存储过程可以加快程序的运行速度。n（6）使用存储过程可以减少网络流量。n（7）使用存储过程可以提高数据库的安全性。8.1.1 存储过程概述n在SQL Server 2000中，存储过程分为两类：系统存储过程和用户自定义的存储过程。系统存储过程是由系统自动创建的，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。用户自定义存储过程是由用户创建并能完成某一特定功能的存

3、储过程。8.1.2 触发器概述n触发器是一种特殊类型的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。8.1.2 触发器概述n触发器可以用于SQL Server 2000约束、默认值和规则的完整性检查，和CHECK约束相比较，触发器可以强制实现更加复杂的数据完整性，而且可以引用其他表中的字段。8.1.2 触发器概述n在SQL Server 2000中，触发器分为两种，即AFTER触发器和INSTEAD OF触发器。nAFTER触发器：这种类型的触发器将在数据变动（INSERT、UPDATE和DELETE操作）完成以后才被触发。可以对变动的数据进行检查

4、，如果发现错误，将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。8.1.2 触发器概述nINSTEAD OF触发器：这种类型的触发器将在数据变动以前被触发，并取代变动数据的操作（INSERT、UPDATE和DELETE操作），而去执行触发器定义的操作。在表或视图上，每个INSERT、UPDATE和DELETE语句最多只可以定义一个INSTEAD OF触发器。8.2 存储过程8.2.1 创建存储过程n在SQL Server 2000中，可以使用三种方法创建存储过程。n使用创建存储过程向导创建存储过程。n使用SQL Server 2000企

5、业管理器创建存储过程。n使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。1使用创建存储过程向导创建存储过程n（1）在企业管理器中，选中某个SQL Server服务器，选择要创建存储过程的数据库，选择“工具”菜单中的“向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创建存储过程向导”选项。n（2）单击“确定”按钮。2使用SQL Server 2000企业管理器创建存储过程n使用SQL Server 2000企业管理器创建存储过程的步骤如下：n（1）在SQL Server 2000企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹

6、出的快捷菜单中依次选择“新建”“存储过程”命令；或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程”命令。3使用Transact-SQL语句创建存储过程n可以使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程，只能在当前数据库中创建存储过程。3使用Transact-SQL语句创建存储过程nCREATE PROCEDURE的语法形式如下：nCREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCR

7、YPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n n其中各参数的含义如下：nprocedure_name：用于指定所要创建存储过程的名称。nnumber：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。3使用Transact-SQL语句创建存储过程nparameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。ndata_type：用于指定参数的数据类型。nVARYING：用于指定作为输出OUTPUT参数支持的结果

8、集。仅适用于游标参数。3使用Transact-SQL语句创建存储过程ndefault：用于指定参数的默认值。nOUTPUT：表明该参数是一个返回参数。nRECOMPILE：表明SQL Server 2000不会保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。3使用Transact-SQL语句创建存储过程nENCRYPTION：表示对存储过程文本进行加密。nFOR REPLICATION：用于指定该存储过程只能在数据复制时使用。本选项不能和WITH RECOMPILE选项一起使用。3使用Transact-SQL语句创建存储过程nAS：用于指定该存储过程要执行的操作。nsql_stat

9、ement：是存储过程中包含的任意数目和类型的Transact-SQL语句。3使用Transact-SQL语句创建存储过程n例8-1 创建一个带有参数的存储过程“存储过程_读者借阅信息”，该存储过程根据传入的读者编号，返回此人的基本信息及此人借阅的图书信息。3使用Transact-SQL语句创建存储过程8.2.2 管理存储过程n1查看存储过程n存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。1查看存储过程n在企业管理器中查看用户创建的存储过程的方法如下。n（1）在企业管理

10、器中，打开指定的服务器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的所有存储过程。1查看存储过程n（2）右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。1查看存储过程n也可以使用系统存储过程来查看用户创建的存储过程。可供使用的系统存储过程及其语法形式如下：n（1）sp_help：用于显示存储过程的参数及其数据类型nsp_help objname= namen参数name为要查看的存储过程的名称。1查看存储过程n（2）sp_helptext：用于显示存储过程的源代码nsp_helptext objname= namen参数name

11、为要查看的存储过程的名称。1查看存储过程n（3）sp_depends：用于显示和存储过程相关的数据库对象nsp_depends objname=objectn参数object为要查看依赖关系的存储过程的名称。1查看存储过程n（4）sp_stored_procedures：用于返回当前数据库中的存储过程列表nsp_stored_proceduressp_name=namen,sp_owner=ownern,sp_qualifier = qualifier1查看存储过程n其中，sp_name = name 用于指定返回目录信息的过程名；sp_owner = owner 用于指定过程所有者的名称；s

12、p_qualifier = qualifier 用于指定过程限定符的名称。1查看存储过程n例8-2 使用系统存储过程查看“存储过程_读者借阅信息”的参数及其数据类型。n程序清单如下：n-查看存储过程的参数及其数据类型nUSE BOOKSnGOnsp_help 存储过程_读者借阅信息nGO1查看存储过程n例8-3 使用系统存储过程查看“存储过程_读者借阅信息”的相关的数据库对象。n程序清单如下：n-查看相关的数据库对象nUSE BOOKSnGOnsp_depends 存储过程_读者借阅信息nGO2修改存储过程定义n在企业管理器中，单击目录树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹

13、出的快捷菜单中选择“属性”命令，则会出现存储过程属性对话框。2修改存储过程定义n其语法形式如下：nALTER PROCEDURE procedure_name;numbernparameterdata_typenVARYING=defaultOUTPUT,.nnWITHnRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONnFOR REPLICATIONnASnsql_statement .n 3重命名存储过程n在企业管理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储

14、过程的名称了。3重命名存储过程n也可以使用系统存储过程sp_rename修改存储过程的名称，其语法形式如下：nsp_rename 原存储过程名称，新存储过程名称8.2.3 执行存储过程n在SQL Server 2000中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：nEXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 8.2.3 执行存储过程n其中各选项的含义如下：nEXECUTE：执行存储

15、过程的命令关键字，如果此语句是批处理中的第一条语句，可以省略此关键字。nreturn_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必须在批处理、存储过程或函数中声明过。8.2.3 执行存储过程nprocedure_name：指定执行的存储过程的名称。n;number：用来指定该存储过程与其他同名存储过程同组时的标识号。nprocedure_name_var：是局部定义变量名，代表存储过程名称。8.2.3 执行存储过程nparameter：是在创建存储过程时定义的过程参数。调用时向存储过程所传递的参数值由value参数或variable变量提供，或者使用DEFA

16、ULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。nWITH RECOMPILE：指定在执行存储过程时重新编译执行计划。8.2.3 执行存储过程n例8-4 执行前面创建的无参存储过程“存储过程_读者信息”。n程序清单如下：nUSE BOOKSnEXEC 存储过程_读者信息n或直接写存储过程的名称：nUSE BOOKSnGOn存储过程_读者信息8.2.3 执行存储过程n例8-5 执行有参存储过程“存储过程_读者借阅信息”，该存储过程有一个输入参数“读者编号”。n程序清单如下：nUSE BOOKSnGOnEXECUTE 存储过程_读者借阅信息JSJ03n或：nUSE BO

17、OKSnGOnEXECUTE 存储过程_读者借阅信息 读者编号=JSJ038.2.4 删除存储过程n1使用企业管理器删除存储过程n在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”命令，会弹出“除去对象”对话框。在该对话框中，单击“全部除去”按钮，即可完成删除操作。2使用Transact-SQL语句删除存储过程n删除存储过程也可以使用Transact-SQL语言中的DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：ndrop procedure procedure ,n2使用Transact-SQL语句删除存储过程n例8-6

18、 使用DROP命令删除存储过程“insert_读者基本信息表_1”。n程序清单如下：nUSE BOOKSnGOnDrop procedure insert_读者基本信息表_1nGO8.3 触发器8.3.1 创建触发器n只能在当前数据库中创建触发器，创建触发器时可以引用其他数据库中的对象。只有表的所有者有权创建触发器，且不能将该权限转给其他用户。在SQL Server 2000中，可以使用企业管理器或者Transact-SQL语句来创建触发器。1使用企业管理器创建触发器n使用企业管理器创建触发器的步骤如下：n在企业管理器中，展开指定的服务器和数据库，这里展开LIBRARY服务器下面的BOOKS数

19、据库，右击“图书基本信息表”，从弹出的快捷菜单中依次选择“所有任务”“管理触发器”命令。1使用企业管理器创建触发器n当创建一个触发器时必须指定以下几项内容：n1）触发器的名称；n2）在其上定义触发器的表；n3）触发器将何时激发；n4）执行触发操作的编程语句。1使用企业管理器创建触发器n例8-7 创建了“触发器_图书信息表_插入”后，查看向图书基本信息表中插入数据时，此触发器所完成的功能。2使用Transact-SQL语句创建触发器n使用CREATE TRIGGER命令也可以创建触发器。：nCREATE TRIGGER trigger_name nONtable|view nWITH ENCRY

20、PTION nn FOR | AFTER | INSTEAD OF DELETE, INSERT , UPDATE n WITH APPENDn NOT FOR REPLICATIONnASn IF UPDATE(column)n AND|ORUPDATE(column)n .nn |IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)n comparison_operatorcolumn_bitmask.nn n sql_statement.n n n 2使用Transact-SQL语句创建触发器n其中各参数的说明如下：ntrigger_n

21、ame：用于指定触发器的名称。ntable | view：用于指定在其上执行触发器的表或视图。nWITH ENCRYPTION：用于加密syscomments表中包含CREATE TRIGGER语句文本的条目。nAFTER：用于规定此触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。2使用Transact-SQL语句创建触发器nINSTEAD OF：用于规定执行的是触发器而不是执行触发SQL语句，从而用触发器替代触发语句的操作。n DELETE , INSERT , UPDATE ：用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。如果指定的选

22、项多于一个，需用逗号分隔这些选项。2使用Transact-SQL语句创建触发器nWITH APPEND：用于指定应该添加现有类型的其他触发器。nNOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。nAS：触发器要执行的操作。nsql_statement：触发器的条件和操作。2使用Transact-SQL语句创建触发器nIF UPDATE（column）：用于测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。可以指定多列。nIF （COLUMNS_UPDATED（）：用于测试是否插入或更新了所涉及的列，仅用于INSERT或

23、UPDATE触发器中。2使用Transact-SQL语句创建触发器nbitwise_operator：用于比较运算的位逻辑运算符。nupdated_bitmask：整型位掩码，表示实际更新或插入的列。ncomparison_operator：是比较运算符。ncolumn_bitmask：是要检查列的整型位掩码，用来检查是否已更新或插入了这些列。2使用Transact-SQL语句创建触发器n在创建触发器时，可以使用两个特殊的临时表：inserted表和deleted表，这两个表都存在于内存中。n在inserted表中存储着被INSERT和UPDATE语句影响的新的数据行。在执行INSERT或UP

24、DATE语句时，新的数据行被添加到基本表中，同时这些数据行的备份被复制到inserted临时表中。2使用Transact-SQL语句创建触发器n在deleted表中存储着被DELETE和UPDATE语句影响的旧数据行。在执行DELETE或UPDATE语句时，指定的数据行从基本表中删除，然后被转移到了deleted表中。在基本表和deleted表中一般不会存在相同的数据行。2使用Transact-SQL语句创建触发器n一个UPDATE操作实际上是由一个DELETE操作和一个INSERT操作组成的。在执行UPDATE操作时，旧的数据行从基本表中转移到deleted表中，然后将新的数据行同时插入基本

25、表和inserted表中。2使用Transact-SQL语句创建触发器n例8-8 创建一个AFTER触发器，要求实现以下功能：在图书基本信息表上创建一个插入、更新类型的触发器“触发器_定价检查”，当在定价字段中插入或修改数据后，触发该触发器，检查定价的数据值是否大于0。2使用Transact-SQL语句创建触发器n例8-9 创建一个AFTER触发器，要求如下：在“读者基本信息表”上创建一个修改类型的触发器“触发器_级联修改图书借阅信息表”，当在“读者基本信息表”中修改某个记录的读者编号后，触发该触发器，在“图书借阅信息表”中查找此人是否借阅了图书，如果在图书借阅信息表中存在此人的借阅信息，则级

26、联修改此人的读者编号，使之与读者基本信息表中修改后的读者编号一致。2使用Transact-SQL语句创建触发器8.3.2 管理触发器n1查看触发器n使用企业管理器查看触发器信息的方法如下。n（1）查看触发器定义信息。n在企业管理器中，展开指定的服务器，选择某个数据库，并右击其中的某个数据表，从弹出的快捷菜单中依次选择“所有任务”“管理触发器”命令，会打开触发器属性对话框。1查看触发器n（2）查看与触发器有依赖关系的其他数据库对象。n右击表，从弹出的快捷菜单中依次选择“所有任务”“显示相关性”命令，会出现相关性对话框。在“对象”下拉列表框中可以选择要查看的数据库对象名称。左边的页框中会显示依赖于

27、该对象的其他对象，右边的页框中显示的是该对象依赖的其他对象。1查看触发器n使用系统存储过程sp_help、sp_helptext和sp_depends可分别查看触发器的不同信息。nsp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。nsp_help 触发器名称nsp_helptext：用于查看触发器的正文信息。1查看触发器nsp_helptext 触发器名称nsp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。nsp_depends 触发器名称 nsp_depends 表名1查看触发器n例8-10 使用系统存储过程查看“触发器_级联修改

28、图书借阅信息表”的一般信息。n程序清单如下：nUSE BOOKSnGOnsp_help 触发器_级联修改图书借阅信息表nGO1查看触发器n例8-11 使用系统存储过程查看“触发器_级联修改图书借阅信息表”涉及的数据表的相关信息。n程序清单如下：nUSE BOOKSnGOnsp_depends 触发器_级联修改图书借阅信息表nGO2修改触发器n修改触发器包括修改触发器的正文和修改触发器的名称。n在企业管理器中，展开指定的服务器，选择需要的数据库，右击某一个数据表，这里右击图书基本信息表，从弹出的快捷菜单中依次选择“所有任务”“管理触发器”命令。在名称选项框中选择要修改的触发器的名称，然后在文本框

29、中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。2修改触发器n也可以使用Transact-SQL语句中的alter trigger命令来修改触发器正文。nalter trigger命令的语法形式如下：nALTER TRIGGER trigger_name nON(table|view) nWITH ENCRYPTION n 2修改触发器n ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE n NOT FOR REPLICATIONn ASn sql_statement.nn | 2修改触发器n (FOR|AFTER

30、|INSTEAD OF)INSERT,UPDATEn NOT FOR REPLICATIONn ASn IF UPDATE(column)n AND|ORUPDATE(column)n .n2修改触发器n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask )n comparison_operatorcolumn_bitmask.nn n sql_statement.n n n2修改触发器n可以使用系统存储过程sp_rename来修改触发器的名称。nsp_rename命令的语法形式如下：nsp_rename oldname,n

31、ewname8.3.3 删除触发器n只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法：直接删除触发器所在的数据表、使用企业管理器删除触发器或者使用Transact-SQL语句删除触发器。1直接删除触发器所在的数据表n如果触发器所在的数据表已经不再使用了，可以直接删除此数据表。删除数据表时，SQL Server 2000将会自动删除与该表相关的所有触发器。2使用企业管理器删除触发器n在企业管理器中，右击要删除的触发器所在的数据表，从弹出的快捷菜单中依次选择“所有任务”“管理触发器”命令，会出现触发器属性对话框。在“名称”选项框中选择要删除的触发器，然后单击“删除”按钮，即可删除该触

32、发器。3使用Transact-SQL语句删除触发器n使用Transact-SQL语句中的DROP TRIGGER命令也可以删除指定的一个或多个触发器。nDROP TRIGGER trigger ,.n 3使用Transact-SQL语句删除触发器n例8-12 使用Transact-SQL语句删除“触发器_图书信息表_插入”触发器。nUSE BOOKSnGOnDROP TRIGGER 触发器_图书信息表_插入nGO8.4 本章小结n在SQL Server 2000中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server 2000服务器通过过

33、程名来调用它们，这些过程就叫做存储过程。8.4 本章小结n在SQL Server 2000中，存储过程可分为两类，即系统存储过程和用户自定义存储过程。系统存储过程是由系统自动创建的，主要存储在master数据库中。系统存储过程完成的功能主要是从系统表中获取信息。可以随时调用系统存储过程，在调用时不必在存储过程名称前加上数据库名。用户自定义存储过程是由用户创建并能完成某一特定功能的存储过程。8.4 本章小结n在SQL Server 2000中，可以使用三种方法创建存储过程：使用存储过程向导创建存储过程、使用SQL Server 2000企业管理器创建存储过程和使用Transact SQL语句中的

34、CREATE PROCEDURE命令创建存储过程。8.4 本章小结n存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。使用Transact SQL语句中的ALTER PROCEDURE命令可以修改已创建的存储过程的定义。可以使用系统存储过程sp_rename修改存储过程的名称。8.4 本章小结n在SQL Server 2000中可以使用EXECUTE命令来直接执行存储过程。n使用企业管理器或Transact SQL语句中的DROP PROCEDURE命令删除无用的存储过程。

35、8.4 本章小结n触发器是一种特殊类型的存储过程。它主要是通过事件进行触发而被执行。触发器可以用于SQL Server 2000约束、默认值和规则的完整性检查。触发器还可以完成难以用普通约束实现的复杂功能。8.4 本章小结n在SQL Server 2000中，触发器分为两种：AFTER触发器和INSTEAD OF触发器。AFTER触发器在数据变动（INSERT、UPDATE和DELETE操作）完成以后才被触发。可以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。8.4 本章小结nINSTEAD OF触发器在数据变动以前被触发，并取代变动数据的操作（INSERT、UPDATE和DELETE操作），而去执行触发器定义的操作。INSTEAD OF触发器可以在表或视图上定义。在表或视图上，每个INSERT、UPDATE和DELETE语句