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

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

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

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

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

5、ver允许在存储过程创建时引用一个不存在的对象，系统只检查创建存储过程的语法。执行时，存储过程引用了一个不存在的对象，则这次执行操作将会失败。 存储过程可以嵌套使用。嵌套的最大层次可以用 NESTLEVEL函数来查看。,8.1 存储过程概述,8.1.3 常用系统存储过程的使用 SQL Server 2005提供了许多系统存储过程,下面介绍几种常用的系统存储过程。 （1）sp_helpdb 用于查看数据库名称及大小。 （2）sp_helptext 用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。 （3）sp_renamedb 用于重命名数据库。 （4）sp_rename

6、 用于更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。 （5）sp_helplogins查看所有数据库用户登录信息。 （6）sp_helpsrvrolemember 用于以查看所有数据库用户所属的角色信息。,8.2 创建和管理存储过程,8.2.1 创建存储过程 1使用SQL Server Management Studio创建存储过程 利用SQL Server Management Studio创建存储过程就是创建一个模板，通过改写模板创建存储过程。具体参考步骤如下。 （1）启动SQL Server Management Studio，在对象资源管理器中，展开“数据库”| t

7、eaching |“可编程性”|“存储过程”。 （2）如图8.1所示，右击“存储过程”节点，选择“新建存储过程”菜单命令。,8.2 创建和管理存储过程,8.2.1 创建存储过程 1使用SQL Server Management Studio创建存储过程 （3）系统弹出存储过程模板，如图8.2所示，用户可以参照模板在其中输入合适的Transact-SQL语句。 （4）单击工具栏中的“执行”按钮，即可将存储过程保存在数据库中。 （5）刷新“存储过程”节点，可以观察到下方出现了新建的存储过程。,8.2 创建和管理存储过程,8.2.1 创建存储过程 2使用CREATE PROCEDURE语句创建存储过

8、程 CREATE PROCEDURE语句的语法格式如下： CREATE PROC EDURE procedure_name ; number parameter_data_type VARYING = default OUTPUT ,n WITH RECOMPILE | ENCRYPTION FOR REPLICATION AS sql_statament ,n ,例8.1创建一个存储过程，输出所有学生的姓名、课程名称和期末成绩信息。 程序代码如下: CREATE PROCEDURE student_score AS SELECT sname, cname, final FROM studen

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

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

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

12、entno=sc. studentno and c.courseno=sc.courseno and (s.sname= student_name or student_name IS NULL),8.2 创建和管理存储过程,8.2.2 修改存储过程 1. 利用SQL Server Management Studio修改存储过程 修改存储过程的参考操作步骤如下。 （1）在“对象资源管理器”中展开“数据库” | teaching |“可编程性”|“存储过程”。 （2）右击要修改的用户存储过程如student_score，在弹出快捷菜单中选择“修改”命令。 （3）在查询编辑器中出现存储过程的源代码

13、，用户可以直接进行修改。 （4）修改完毕，执行该存储过程，将修改后的存储过程保存到数据库中。,8.2 创建和管理存储过程,8.2.2 修改存储过程 2. 使用ALTER PROCEDURE语句修改存储过程 使用ALTER PROCEDURE语句可以修改存储过程。 ALTER PROCEDURE语句的语法格式如下： ALTER PROC EDURE procedure_name ; number parameter_data_type VARYING = default OUTPUT ,n WITH RECOMPILE | ENCRYPTION FOR REPLICATION AS sql_st

14、atament ,n ,例8.5 修改存储过程student_score，使其以加密方式存储在系统表syscomments中。 程序代码如下: ALTER PROCEDURE student_score WITH ENCRYPTION AS SELECT sname, cname, final FROM student s ,course c ,score sc WHERE s.studentno=sc. Studentno and c.courseno=sc.courseno,8.2 创建和管理存储过程,8.2.3 执行存储过程 利用EXECUTE 语句可以执行存储过程。对于存储过程的所有者

15、或任何一名对此过程拥有EXECUTE特权的用户，都可以执行此存储过程。 EXECUTE语句的语法格式如下： EXEC UTE return_status = procedure_name ; number parameter1 = value | parameter1=variable OUTPUT . WITH RECOMPILE ,例8.6 执行存储过程student_score2。 分析：由于该存储过程有输出参数，那么必须在执行存储过程前定义一个变量，以接收存储过程要传出的值。然后可以使用如下语句输出变量ave的值。 程序代码如下: DECLARE ave numeric(6,2) EX

16、EC student_score2 student_name=何影, average =ave OUTPUT SELECT ave,例8.7 使用默认值执行的存储过程student_score3。 程序代码如下: DECLARE ave numeric(6,2) EXEC student_score3 average =ave OUTPUT SELECT ave,8.3 触发器概述,触发器（Trigger）是一种响应数据操作语言（DML）事件或数据定义语言（DDL）事件而执行的特殊类型的存储过程，是在用户对某一表中的数据进行UPDATE、INSERT和DELETE操作时被触发执行的一段程序。

17、触发器有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。,8.3 触发器概述,8.3.1 触发器的作用 触发器可以实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。 （1）触发器的主要作用。 触发器可以对数据库进行级联修改。 实现比CHECK约束更为复杂的限制。 强制表的修改要合乎业务规则。,8.3 触发器概述,8.3.1 触发器的作用 （2）使用触发器的优点。 触发器可通过数据库中的相关表实现级联更改。 触发器可以强制比用CHECK约束定义的约束更为复杂的约束。 触发器也可以监测数据修改前后的表状态，并根据其差异采取对策。 一个表中的多个同类触发器（INS

18、ERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。,8.3 触发器概述,8.3.2 触发器的分类 两种类型的触发器：DML触发器和DDL触发器。 1. DML触发器 DML触发器是在执行INSERT、UPDATE或DELETE语句时被激活的触发器。 DML触发器又分为AFTER和INSTEAD OF两种触发器: AFTER 触发器是在激活它的语句执行成功后才激活. INSTEAD OF触发器的激活将替代相应的触发语句。 当数据库中发生数据操作语言（DML）事件时将调用 DML 触发器。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语

19、句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误,则整个事务即自动回滚。,8.3 触发器概述,8.3.2 触发器的分类。 1. DML触发器: DML 触发器通常用于以下场合： DML 触发器可通过数据库中的相关表实现级联更改。 DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。 与 CHECK 约束不同，DML 触发器可引用其他表中的列。 DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。 一个表中的多个同类DML触发器允许采取多个不同的操作来响应同

20、一个修改语句。,8.3 触发器概述,8.3.2 触发器的分类。 2. DDL触发器 DDL触发器是在执行CREATE、ALTER和DROP语句时被激活的触发器，是由数据定义语言引起的。如果要执行以下操作，可以使用DDL触发器： 要防止对数据库架构进行某些更改。 希望数据库中发生某种情况以响应数据库架构中的更改。 要记录数据库架构中的更改或事件。 仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。,8.3 触发器概述,8.3.3 触发器的工作原理 在触发器的执行过程中，SQL Server为每个触发器创建和管理两个特

21、殊的表，一个是插入表inserted表，一个是删除表deleted表。这两个表建在数据库服务器的内存中，与触发器所在数据表的结构是完全一致。 当由INSERT或UPDATE语句激活相应触发器之后，所有被添加或被更新的记录都被存储到inserted表。当由DELETE或UPDATE语句激活相应触发器之后，所有被删除的记录都被送到deleted表。 在触发器的执行过程中，可以读取这两个表中的内容，但不能修改它们。当触发器的工作完成之后，这两个表也将从内存中删除。,8.3 触发器概述,8.3.4 创建触发器前应该注意的问题 在创建触发器前，需要注意以下一些问题： CREATE TRIGGER 语句必

22、须是批处理中的第一个语句，而且只能用于一个表或视图。 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。 触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。 不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。 在含有用 DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF和 INSTEAD OF UPDATE触发器。,8.4 创建和管理触发器,8.4.1 创建触发器 创建触发器可以通过SQL Server Management Studio和CREATE TRIGGER语句两种方法。 创建

23、触发器时需要指定如下的选项： 触发器名称和需要定义触发器的表。 触发器将何时激发。 激活触发器的数据修改语句。有效选项为INSERT、UPDATE或 DELETE。多个数据修改语句可激活同一个触发器。,8.4 创建和管理触发器,8.4.1 创建触发器 1. 在SQL Server Management Studio中创建触发器 （1）启动SQL Server Management Studio，在“对象资源管理器”中展开“数据库”|teaching|“表” 。 （2）选择要创建触发器的表student并展开。右单“触发器”节点，在弹出的快捷菜单中选择“新建触发器”命令。 （3）此时弹出如图8.

24、4所示的新建触发器编辑窗口，其中包含触发器模板，用户可以参照模板在其中输入触发器的Transact-SQL语句。 （4）单击工具栏中的“！”执行按钮，将触发器保存到数据库中。,8.4 创建和管理触发器,8.4.1 创建触发器 2. 使用CREATE TRIGGER语句创建触发器 创建触发器的语法格式如下： CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statament ,n ,例8.8 为student表创

25、建一个触发器，用来禁止更新学号字段的值。 程序代码如下: CREATE TRIGGER update_s_tr ON student AFTER UPDATE AS IF UPDATE(studentno) BEGIN RAISERROR(不能修改学号,16,2) ROLLBACK END,此时，若有更新语句如下： UPDATE student SET studentno=0837221508 WHERE studentno=0937221508 则提示“不能修改学号”， 更新语句得不到执行。,8.4 创建和管理触发器,8.4.1 创建触发器 例8.9 为course表创建一个触发器，用来防止

26、用户删除任何必修课程的课程记录。 程序代码如下: CREATE TRIGGER delete_c_tr ON course INSTEAD OF DELETE AS IF EXISTS ( SELECT * FROM course WHERE type=必修 ) BEGIN RAISERROR(不能删除必修课程,16,2) ROLLBACK END,此时，若删除新语句如下： DELETE FORM course WHERE type=必修 则提示“不能删除必修课程”，删除语句得不到执行。,8.4 创建和管理触发器,8.4.1 创建触发器 例8.10 为score表创建一个触发器，用来防止用户对

27、score表中的数据进行任何修改。 程序代码如下: CREATE TRIGGER update_sc_tr ON score INSTEAD OF UPDATE AS RAISERROR(不能修改成绩表中的数据,16,2) 此时，若有更新语句如下： UPDATE score SET final=60 则提示“不能修改成绩表中的数据”，更新语句得不到执行。同一个触发器。,8.4 创建和管理触发器,8.4.2 修改触发器 1. 使用SQL Server Management Studio修改触发器 修改触发器的操作步骤如下。 （1）在“对象资源管理器”中展开“数据库”。 （2）选择触发器所在的数据

28、库，如teaching数据库，并展开该数据库的“表”节点。 （3）选择触发器所在的表score，展开表中的“触发器”。 （4）右击要修改的触发器，在弹出的快捷菜单中选择“修改”命令。 （5）在弹出的触发器编辑窗口，用户可以直接进行修改。修改完毕，单击工具栏中的“！”按钮执行该触发器，将修改后的触发器保存到数据库中。,8.4 创建和管理触发器,8.4.2 修改触发器 2. 使用ALTER TRIGGER语句修改触发器 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 UPDATE AS IF UPDATE(studentno) OR UP