SQL Server数据库应用与开发：第08章 存储过程和触发器

1、SQL Server 2005 数据库应用与开发第08章 存储过程和触发器 内容提要:存储过程（Stored Procedure）是一组完成特定功能的Transact- SQL语句的集合。存储过程是通过用户、其他过程或触发器来调用执行。利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。存储过程主要应用于控制访问权限、为数据库表中的活动创建审计追踪、将关系到数据库及其所有相关应用程序的数据定义语句和数据操作语句分隔开。 触发器（Trigger）是一种特殊的存储过程。触发器通常在特定的表上定义，当该表的相应事件发生时自动执行，用于实现强制业务规则和数据完整性等。第08章 存储

2、过程和触发器 本章内容:8.1 存储过程概述8.2 创建和管理存储过程8.3 触发器概述8.4 创建和管理触发器8.5小结8.1 存储过程概述存储过程的主要用途：提高了处理复杂任务的能力。增强了代码的复用率和共享性。存储过程一旦创建后即可在程序中调用任意多次。减少了网络中数据的流量。存储过程在服务器注册，加快了过程的运行速度。加强了系统的安全性。存储过程具有安全特性（例如权限）和所有权链接，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。8.1 存储过程概述8.1.1 存储过程的类型SQL Server 2005支持的存储过程的类型主要有如下4类。（1）系统存储过程。

3、SQL Server 2005 中的许多管理活动都是存储过程执行的。从物理意义上讲，系统存储过程存储在源数据库中，并且带有 sp_ 前缀。从逻辑上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的 sys 构架中。（2）用户定义的存储过程。用户可以自己创建存储过程。存储过程是指封装了可重用代码的模块或例程。用户存储过程有两种类型：8.1 存储过程概述8.1.1 存储过程的类型Transact-SQL 存储过程是指保存的 Transact-SQL 语句集合，可接受和返回用户提供的参数。CLR 存储过程是指对 Microsoft .NET Framework 公共语言运行时 (CLR) 方

4、法的引用，可以接受和返回用户提供的参数（3）临时存储过程。以“#”或“#”为前缀，表示局部临时存储过程和全局临时存储过程。（4）扩展存储过程。以xp_为前缀，是SQL Server 2005的实例可以动态加载和运行的 DLL。8.1 存储过程概述8.1.2 存储过程的设计原则用户创建存储过程时，应注意遵循以下几点原则。存储过程最大不能超过128MB。用户定义的存储过程只能在当前数据库中创建。存储过程是为了处理那些需要被多次运行的Transact-SQL语句集。SQL Server允许在存储过程创建时引用一个不存在的对象，系统只检查创建存储过程的语法。执行时，存储过程引用了一个不存在的对象，则这

5、次执行操作将会失败。存储过程可以嵌套使用。嵌套的最大层次可以用 NESTLEVEL函数来查看。8.1 存储过程概述8.1.3 常用系统存储过程的使用SQL Server 2005提供了许多系统存储过程,下面介绍几种常用的系统存储过程。（1）sp_helpdb 用于查看数据库名称及大小。（2）sp_helptext 用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。（3）sp_renamedb 用于重命名数据库。（4）sp_rename 用于更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。（5）sp_helplogins查看所有数据库用户登录信息。（6）

6、sp_helpsrvrolemember 用于以查看所有数据库用户所属的角色信息。8.2 创建和管理存储过程8.2.1 创建存储过程1使用SQL Server Management Studio创建存储过程利用SQL Server Management Studio创建存储过程就是创建一个模板，通过改写模板创建存储过程。具体参考步骤如下。（1）启动SQL Server Management Studio，在对象资源管理器中，展开“数据库”| teaching |“可编程性”|“存储过程”。（2）如图8.1所示，右击“存储过程”节点，选择“新建存储过程”菜单命令。8.2 创建和管理存储过程8.2

7、.1 创建存储过程1使用SQL Server Management Studio创建存储过程（3）系统弹出存储过程模板，如图8.2所示，用户可以参照模板在其中输入合适的Transact-SQL语句。（4）单击工具栏中的“执行”按钮，即可将存储过程保存在数据库中。（5）刷新“存储过程”节点，可以观察到下方出现了新建的存储过程。8.2 创建和管理存储过程8.2.1 创建存储过程2使用CREATE PROCEDURE语句创建存储过程CREATE PROCEDURE语句的语法格式如下：CREATE PROC EDURE procedure_name ; number parameter_data_ty

8、pe VARYING = default OUTPUT ,n WITH RECOMPILE | ENCRYPTION FOR REPLICATION AS sql_statament ,n 例8.1创建一个存储过程，输出所有学生的姓名、课程名称和期末成绩信息。程序代码如下:CREATE PROCEDURE student_scoreASSELECT sname, cname, final FROM student s ,course c ,score sc WHERE s.studentno=sc. studentno and c.courseno=sc.courseno8.2 创建和管理存储

9、过程8.2.1 创建存储过程例8.2创建一个存储过程，输出指定学生的姓名及课程名称、期末成绩信息。程序代码如下:CREATE PROCEDURE student_score1 student_name nchar(8) ASSELECT sname, cname, final FROM student s ,course c ,score sc WHERE s.studentno=sc. studentno and c.courseno=sc.courseno and .sname=student_name AS sql_statament ,n 例8.3 创建一个存储过程，用输出参数返回指定

10、学生的所有课程的期末成绩的平均值程序代码如下:CREATE PROCEDURE student_score2 student_name nchar(8), average numeric(6,2) OUTPUT ASSELECT average=AVG(final) FROM student s ,course c ,score sc WHERE s.studentno=sc.studentno and c.courseno=sc.coursenoand s.sname=student_name例8.4 创建一个存储过程，用输出参数返回指定学生的所有课程的期末成绩的平均值，若不指定学生姓名，则

11、返回所有学生的所有课程的期末成绩的平均值。程序代码如下:CREATE PROCEDURE student_score3 student_name nchar(8)=NULL, average numeric(6,2) OUTPUTASSELECT average =AVG(final) FROM student s ,course c ,score sc WHERE s.studentno=sc. studentno and c.courseno=sc.coursenoand (s.sname= student_name or student_name IS NULL)8.2 创建和管理存储过

12、程8.2.2 修改存储过程1. 利用SQL Server Management Studio修改存储过程修改存储过程的参考操作步骤如下。（1）在“对象资源管理器”中展开“数据库” | teaching |“可编程性”|“存储过程”。（2）右击要修改的用户存储过程如student_score，在弹出快捷菜单中选择“修改”命令。（3）在查询编辑器中出现存储过程的源代码，用户可以直接进行修改。（4）修改完毕，执行该存储过程，将修改后的存储过程保存到数据库中。 8.2 创建和管理存储过程8.2.2 修改存储过程2. 使用ALTER PROCEDURE语句修改存储过程使用ALTER PROCEDURE语

13、句可以修改存储过程。ALTER PROCEDURE语句的语法格式如下：ALTER PROC EDURE procedure_name ; number parameter_data_type VARYING = default OUTPUT ,n WITH RECOMPILE | ENCRYPTION FOR REPLICATION AS sql_statament ,n 例8.5 修改存储过程student_score，使其以加密方式存储在系统表syscomments中。程序代码如下:ALTER PROCEDURE student_scoreWITH ENCRYPTIONASSELECT s

14、name, cname, final FROM student s ,course c ,score sc WHERE s.studentno=sc. Studentno and c.courseno=sc.courseno8.2 创建和管理存储过程8.2.3 执行存储过程利用EXECUTE 语句可以执行存储过程。对于存储过程的所有者或任何一名对此过程拥有EXECUTE特权的用户，都可以执行此存储过程。EXECUTE语句的语法格式如下： EXEC UTE return_status = procedure_name ; number parameter1 = value | parameter

15、1=variable OUTPUT . WITH RECOMPILE 例8.6 执行存储过程student_score2。分析：由于该存储过程有输出参数，那么必须在执行存储过程前定义一个变量，以接收存储过程要传出的值。然后可以使用如下语句输出变量ave的值。程序代码如下:DECLARE ave numeric(6,2)EXEC student_score2 student_name=何影, average =ave OUTPUTSELECT ave例8.7 使用默认值执行的存储过程student_score3。程序代码如下:DECLARE ave numeric(6,2)EXEC studen

16、t_score3 average =ave OUTPUTSELECT ave8.3 触发器概述触发器（Trigger）是一种响应数据操作语言（DML）事件或数据定义语言（DDL）事件而执行的特殊类型的存储过程，是在用户对某一表中的数据进行UPDATE、INSERT和DELETE操作时被触发执行的一段程序。触发器有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。8.3 触发器概述8.3.1 触发器的作用触发器可以实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。（1）触发器的主要作用。触发器可以对数据库进行级联修改。实现比CHECK约束更为复杂的限制。强制表的

17、修改要合乎业务规则。8.3 触发器概述8.3.1 触发器的作用（2）使用触发器的优点。 触发器可通过数据库中的相关表实现级联更改。触发器可以强制比用CHECK约束定义的约束更为复杂的约束。 触发器也可以监测数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。 8.3 触发器概述8.3.2 触发器的分类两种类型的触发器：DML触发器和DDL触发器。1. DML触发器DML触发器是在执行INSERT、UPDATE或DELETE语句时被激活的触发器。DML触发器又分为AFTER和INSTEAD

18、 OF两种触发器:AFTER 触发器是在激活它的语句执行成功后才激活.INSTEAD OF触发器的激活将替代相应的触发语句。当数据库中发生数据操作语言（DML）事件时将调用 DML 触发器。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误,则整个事务即自动回滚。8.3 触发器概述8.3.2 触发器的分类。1. DML触发器: DML 触发器通常用于以下场合： DML 触发器可通过数据库中的相关表实现级联更改。DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE

19、操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。 与 CHECK 约束不同，DML 触发器可引用其他表中的列。DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。一个表中的多个同类DML触发器允许采取多个不同的操作来响应同一个修改语句。8.3 触发器概述8.3.2 触发器的分类。2. DDL触发器DDL触发器是在执行CREATE、ALTER和DROP语句时被激活的触发器，是由数据定义语言引起的。如果要执行以下操作，可以使用DDL触发器： 要防止对数据库架构进行某些更改。希望数据库中发生某种情况以响应数据库架构中的更改。 要记录数据库架构中的更改或事件。仅在运行触

20、发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。8.3 触发器概述8.3.3 触发器的工作原理在触发器的执行过程中，SQL Server为每个触发器创建和管理两个特殊的表，一个是插入表inserted表，一个是删除表deleted表。这两个表建在数据库服务器的内存中，与触发器所在数据表的结构是完全一致。当由INSERT或UPDATE语句激活相应触发器之后，所有被添加或被更新的记录都被存储到inserted表。当由DELETE或UPDATE语句激活相应触发器之后，所有被删除的记录都被送到deleted表。在触发器的执行过程中

21、，可以读取这两个表中的内容，但不能修改它们。当触发器的工作完成之后，这两个表也将从内存中删除。8.3 触发器概述8.3.4 创建触发器前应该注意的问题在创建触发器前，需要注意以下一些问题：CREATE TRIGGER 语句必须是批处理中的第一个语句，而且只能用于一个表或视图。创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。在含有用 DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF和 I

22、NSTEAD OF UPDATE触发器。8.4 创建和管理触发器8.4.1 创建触发器创建触发器可以通过SQL Server Management Studio和CREATE TRIGGER语句两种方法。创建触发器时需要指定如下的选项： 触发器名称和需要定义触发器的表。触发器将何时激发。激活触发器的数据修改语句。有效选项为INSERT、UPDATE或 DELETE。多个数据修改语句可激活同一个触发器。8.4 创建和管理触发器8.4.1 创建触发器1. 在SQL Server Management Studio中创建触发器（1）启动SQL Server Management Studio，在“对

23、象资源管理器”中展开“数据库”|teaching|“表” 。（2）选择要创建触发器的表student并展开。右单“触发器”节点，在弹出的快捷菜单中选择“新建触发器”命令。（3）此时弹出如图8.4所示的新建触发器编辑窗口，其中包含触发器模板，用户可以参照模板在其中输入触发器的Transact-SQL语句。（4）单击工具栏中的“！”执行按钮，将触发器保存到数据库中。8.4 创建和管理触发器8.4.1 创建触发器2. 使用CREATE TRIGGER语句创建触发器创建触发器的语法格式如下：CREATE TRIGGER trigger_name ON table | view WITH ENCRYPT

24、ION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statament ,n 例8.8 为student表创建一个触发器，用来禁止更新学号字段的值。程序代码如下:CREATE TRIGGER update_s_tr ON student AFTER UPDATEASIF UPDATE(studentno) BEGIN RAISERROR(不能修改学号,16,2) ROLLBACKEND此时，若有更新语句如下：UPDATE student SET studentno=0837221508WHERE studentno=0937

25、221508则提示“不能修改学号”， 更新语句得不到执行。raiserror 是由单词 raise error 组成 raise 增加; 提高; 提升raiserror 的作用： raiserror 是用于抛出一个错误。 以下资料来源于sql server 2005的帮助 其语法如下：RAISERROR(msg_id|msg_str|local_variable,severity,state,argument,.n)WITHoption,.n简要说明一下：第一个参数： msg_id | msg_str | local_variable msg_id：表示可以是一个sys.messages表中定

26、义的消息代号； 使用 sp_addmessage 存储在 sys.messages 目录视图中的用户定义错误消息号。 用户定义错误消息的错误号应当大于 50000。 msg_str：表示也可以是一个用户定义消息，该错误消息最长可以有 2047 个字符； （如果是常量，请使用Nxxxx，因为是nvarchar的） 当指定 msg_str 时，RAISERROR 将引发一个错误号为 5000 的错误消息。 local_variable：表示也可以是按照 msg_str 方式的格式化字符串变量。第二个参数：severity 用户定义的与该消息关联的严重级别。（这个很重要） 任何用户都可以指定 0 到

27、 18 之间的严重级别。 0,10的闭区间内，不会跳到catch； 如果是11,19,则跳到catch； 如果20,无穷)，则直接终止数据库连接；第三个参数：state 如果在多个位置引发相同的用户定义错误， 则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。 介于 1 至 127 之间的任意整数。（state 默认值为1） 当state 值为 0 或大于 127 时会生成错误！第四个参数：argument 用于代替 msg_str 或对应于 msg_id 的消息中的定义的变量的参数。第五个参数：option 错误的自定义选项，可以是下表中的任一值： LOG ：在错误日志和应用程序日志

28、中记录错误； NOWAIT：将消息立即发送给客户端； SETERROR：将 ERROR 值和 ERROR_NUMBER 值设置为 msg_id 或 50000；8.4 创建和管理触发器8.4.1 创建触发器例8.9 为course表创建一个触发器，用来防止用户删除任何必修课程的课程记录。程序代码如下:CREATE TRIGGER delete_c_tr ON courseINSTEAD OF DELETE ASIF EXISTS ( SELECT * FROM course WHERE type=必修 ) BEGIN RAISERROR(不能删除必修课程,16,2) ROLLBACK END此

29、时，若删除新语句如下：DELETE FORM course WHERE type=必修则提示“不能删除必修课程”，删除语句得不到执行。8.4 创建和管理触发器8.4.1 创建触发器例8.10 为score表创建一个触发器，用来防止用户对score表中的数据进行任何修改。程序代码如下:CREATE TRIGGER update_sc_tr ON scoreINSTEAD OF UPDATEASRAISERROR(不能修改成绩表中的数据,16,2)此时，若有更新语句如下：UPDATE score SET final=60则提示“不能修改成绩表中的数据”，更新语句得不到执行。同一个触发器。8.4 创

30、建和管理触发器8.4.2 修改触发器1. 使用SQL Server Management Studio修改触发器修改触发器的操作步骤如下。（1）在“对象资源管理器”中展开“数据库”。（2）选择触发器所在的数据库，如teaching数据库，并展开该数据库的“表”节点。（3）选择触发器所在的表score，展开表中的“触发器”。（4）右击要修改的触发器，在弹出的快捷菜单中选择“修改”命令。（5）在弹出的触发器编辑窗口，用户可以直接进行修改。修改完毕，单击工具栏中的“！”按钮执行该触发器，将修改后的触发器保存到数据库中。8.4 创建和管理触发器8.4.2 修改触发器2. 使用ALTER TRIGGER

31、语句修改触发器ALTER TRIGGER语句的语法格式如下：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFINSERT, UPDATE , DELETE AS sql_statament ,n 例8.11 使用ALTER TRIGGER语句修改触发器update_s_tr，用来禁止更新学号字段和姓名字段的值。程序代码如下:ALTER TRIGGER update_s_tr ON student AFTER UPDATEASIF UPDATE(studentno) OR UPDATE(sname) BEGIN RAISERROR(不能修改学号或姓名,16,2) ROLLBACK END8.4 创建和