数据库应用技术 SQL Server 2005

第9章 存储过程和触发器 能力目标: 任务设计： 知识要求： 能根据项目需求设计存储过程。 第9章 存储过程和触发器 SQL Server 2005 能力目标: 能根据项目需求设计存储过程。 能根据项目逻辑设计中数据完整性要求设计编写触发器。 任务设计： 编写简单的存储过程。 编写简单的触发器实现完整性控制。 知识要求： 存储过程和触发器的基本概念。 存储过程和触发器的编程方法。

9.1 存储过程 SQL Server 2005 存储过程 的 基本知识 存储过程 的 参数 创建用户 存储过程 第一步 第二步 第三步

9.1.1 存储过程的基本知识 概念 优点 允许标准组件式编程，增强重用性和共享性 能够实现较快的执行速度 能够减少网络流量 SQL Server 2005 概念 存储过程（Stored Procedure）一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。 优点 使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括： 允许标准组件式编程，增强重用性和共享性 能够实现较快的执行速度 能够减少网络流量 可被作为一种安全机制来充分利用

9.1.1 存储过程的基本知识 SQL Server 2005 分类 在SQL Server 2005中存储过程分为三类：系统提供的存储过程、用户自定义存储过程和扩展存储过程。 系统：系统提供的存储过程，sp_*，例如：sp_rename 扩展：SQL Server环境之外的动态链接库DLL，xp_ 远程：远程服务器上的存储过程 用户：创建在用户数据库中的存储过程 临时：属于用户存储过程，#开头（局部：一个用户会话）,##（全 局：所有用户会话）

9.1.2 创建用户存储过程 使用存储过程模板创建存储过程 SQL Server 2005 使用存储过程模板创建存储过程 在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示：

9.1.2 创建用户存储过程 在右侧查询编辑器中出现存储过程的模板，用户可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。 SQL Server 2005 在右侧查询编辑器中出现存储过程的模板，用户可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。

9.1.2 创建用户存储过程 例9-1：创建一个简单的存储过程 USE Library GO SQL Server 2005 例9-1：创建一个简单的存储过程 USE Library GO CREATE PROCEDURE borrowed_num AS SELECT Rname,Lendnum FROM Reader WHERE Rname='赵良宇' 存储过程建好了，什么时候，怎么用呢？ 执行存储过程： borrowed_num 或 EXEC borrowed_num 执行结果：

9.1.2 创建用户存储过程 使用T-SQL语句创建表 格式： CREATE PROC 过程名 @形参名 类型 @变参名 类型 OUTPUT SQL Server 2005 使用T-SQL语句创建表 格式： CREATE PROC 过程名 @形参名 类型 @变参名 类型 OUTPUT AS SQL语句

9.1.2 创建用户存储过程 例9-2：创建一个多表查询的存储过程。 USE Library GO SQL Server 2005 例9-2：创建一个多表查询的存储过程。 USE Library GO CREATE PROCEDURE borrowed_book1 AS SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate FROM reader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k. BID WHERE Rname='程鹏' 执行存储过程：

9.1.2 创建用户存储过程 borrowed_book1 或 EXEC borrowed_book1 执行结果： SQL Server 2005 borrowed_book1 或 EXEC borrowed_book1 执行结果：

9.1.3 管理用户存储过程 获得存储过程的信息 sp_helptext [[@objname=]存储过程名] 语法： 说明: SQL Server 2005 获得存储过程的信息 语法： sp_helptext [[@objname=]存储过程名] 说明: 要显示用来创建过程的文本，可在存储过程所在的数据库中执行sp_helptext，并使用过程名作为参数。使用ENCRYPTION选项创建的存储过程不能使用sp_helptext查看。

9.1.3 管理用户存储过程 修改存储过程 @形参名 数据类型 @变参名 数据类型 OUTPUT 说明: SQL Server 2005 修改存储过程 语法： ALTER PROC [ EDURE] 存储过程名[，n] @形参名 数据类型 @变参名 数据类型 OUTPUT 说明: 如果需要更改存储过程中的语句或参数，可以删除或重新创建该存储过程，也可以直接修改该存储过程。删除或重新创建 存储过程时，所有与该存储过程相关的权限都将丢失；而修改 存储过程时，过程或参数定义会更改，但权限将保留。修改存 储过程使用语句ALTER PROCEDURE来完戍。

存储过程（Stored Procedure）一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。 回顾 SQL Server 2005 存储过程（Stored Procedure）一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。 CREATE PROC 过程名 @形参名 类型 @变参名 类型 OUTPUT AS SQL语句

9.2 触发器 SQL Server 2005 触发器 的 基本知识 1 创建DDL触发器 2 修改触发器 3 删除触发器 4 查看触发器

9.2.1 触发器的基本知识 基本概念 触发器是特殊的存储过程，基于一个表/视图/服务器/数据库。 SQL Server 2005 基本概念 触发器是特殊的存储过程，基于一个表/视图/服务器/数据库。 注意：触发器里也包含一系列的T-SQL语句，但它的执行不是用exec主动调用的，而是在满足一定条件下自动执行的。 创建，主要作用就是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。 当触发器所保护的数据发生变化（update,insert,delete），或者当服务器、数据库中发生数据定义（create、alter、drop）时，系统将自动运行触发器以保证数据库的完整性、正确性和安全性。通俗的说：通过一个动作（update,insert,delete）调用一个存储过程（触发器）。

9.2.1 触发器的基本知识 SQL Server 2005 触发器的分类 DML触发器 DDL触发器

9.2.1 触发器的基本知识 SQL Server 2005 类型 DML触发器 在数据库中发生数据操作语言（DML）事件时将启用。 DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 T-SQL 语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

9.2.1 触发器的基本知识 SQL Server 2005 DDL 触发器 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相反，它们会为响应多种数据定义语言（DDL）语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。

9.2.2创建DML触发器 使用存储过程模板创建存储过程 SQL Server 2005 使用存储过程模板创建存储过程 在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开“表”节点，右击要创建触发器的“表”，选择“新建触发器”命令，如图所示：

9.2.2创建DML触发器 在查询编辑器中出现触发器设计模板，用户可以在此基础上编辑触发器，单击“执行”按钮，即可创建该触发器。 SQL Server 2005 在查询编辑器中出现触发器设计模板，用户可以在此基础上编辑触发器，单击“执行”按钮，即可创建该触发器。

FOR[update,insert,delete ] AS SQL语句 9.2.2创建DML触发器 SQL Server 2005 使用T-SQL语句创建表 CREATE TRIGGER 触发器 ON 表名 FOR[update,insert,delete ] AS SQL语句 例9-3：创建基于表reader ，DELETE操作的触发器。 USE Library GO IF EXISTS(SELECT name FROM sysobjects WHERE name='reader_d' AND type='TR') DROP TRIGGER reader_d --如果已经存在触发器reader_d则删除

9.2.2创建DML触发器 GO CREATE TRIGGER reader_d --创建触发器 ON reader --基于表 SQL Server 2005 GO CREATE TRIGGER reader_d --创建触发器 ON reader --基于表 FOR DELETE --删除事件 AS set nocount on PRINT ‘数据被删除!’ --执行显示输出 试试吧！ 应用： USE Library DELETE reader where Rname='aaa'

9.2.2创建DML触发器 执行结果： 数据被删除! （所影响的行数为 1 行） SQL Server 2005 执行结果： 数据被删除! （所影响的行数为 1 行） 例9-4：在表borrow中添加借阅信息记录时，得到该书的应还日期。 说明：在表borrow中增加一个应还日期SReturnDate。 USE Library IF EXISTS (SELECT name FROM sysobjects WHERE name ='T_return_date' AND type='TR') DROP TRIGGER T_return_date GO CREATE TRIGGER T_return_date --创建触发器 ON Borrow --基于表borrow

9.2.2创建DML触发器 AFTER INSERT --插入操作 AS --查询插入记录INSERTED中读者的类型 SQL Server 2005 AFTER INSERT --插入操作 AS --查询插入记录INSERTED中读者的类型 DECLARE @type int,@dzbh char(10),@tsbh char(15) SET @dzbh=(SELECT RID FROM inserted) SET @tsbh=(SELECT BID FROM inserted) SELECT @type= TypeID FROM reader WHERE RID=(SELECT RID FROM inserted)--副本 /*把Borrow表中的应还日期改为 当前日期加上各类读者的借阅期限*/ UPDATE Borrow SET SReturnDate=getdate()+ CASE WHEN @type=1 THEN 90

9.2.2创建DML触发器 WHEN @type=2 THEN 60 WHEN @type=3 THEN 30 END SQL Server 2005 WHEN @type=2 THEN 60 WHEN @type=3 THEN 30 END WHERE RID=@dzbh and BID=@tsbh 应用： USE Library INSERT INTO borrow(RID,BID) VALUES('2000186010','TP85-08') 查看记录：

9.2.2创建DML触发器 USE Library IF EXISTS(SELECT name FROM sysobject SQL Server 2005 例9-5：在数据库Library中，当读者还书时，实际上要修改表brorrowinf中相应记录还期列的值，请计算出是否过 USE Library IF EXISTS(SELECT name FROM sysobject WHERE name='T_fine_js' AND type='TR') DROP TRIGGER T_fine_js GO CREATE TRIGGER T_fine_js ON borrow AFTER UPDATE AS

9.2.2创建DML触发器 DECLARE @days int,@dzbh char(10),@tsbh char(15) SQL Server 2005 DECLARE @days int,@dzbh char(10),@tsbh char(15) SET @dzbh=(select RID from inserted) SET @tsbh=(select BID from inserted) SELECT @days=DATEDIFF(day, ReturnDate, SReturnDate) --DATEDIFF函数返回两个日期之差，单位为DAY FROM borrow WHERE RID=@dzbh and BID=@tsbh IF @days>0 PRINT '没有过期！' ELSE PRINT '过期'+convert(char(6),@days)+'天' GO 应用：

9.2.2创建DML触发器 USE Library UPDATE borrow SET ReturnDate='2007-12-12' SQL Server 2005 USE Library UPDATE borrow SET ReturnDate='2007-12-12' WHERE RID='2000186010' and BID='TP85-08' GO 执行结果： 过期-157 天 (1 行受影响) 例9-6：对Library库中Reader表的 DELETE操作定义触发器。 IF EXISTS(SELECT name FROM sysobjects WHERE name='reader_d' AND type='TR') DROP TRIGGER reader_d

9.2.2创建DML触发器 CREATE TRIGGER reader_d ON Reader FOR DELETE AS SQL Server 2005 CREATE TRIGGER reader_d ON Reader FOR DELETE AS DECLARE @data_yj int SELECT @data_yj=Lendnum FROM deleted IF @data_yj>0 BEGIN PRINT '该读者不能删除！还有'+convert(char(2),@data_yj)+'本书没还。 ROLLBACK END

9.2.2创建DML触发器 ELSE PRINT '该读者已被删除！' GO 应用： USE Library SQL Server 2005 ELSE PRINT '该读者已被删除！' GO 应用： USE Library DELETE Reader WHERE RID='2005216119' 执行结果： 该读者不能删除！还有4 本书没还。

9.2.3 创建DDL触发器 SQL Server 2005 DDL 触发器会为响应多种数据定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。 语法形式： CREATE TRIGGER trigger_name ON {ALL SERVER|DATABASE}[WITH <ddl_trigger_option> [ ,...n ]] {FOR|AFTER} {event_type|event_group}[,...n] AS {sql_statement[;] [...n]|EXTERNAL NAME <method specifier>[;]}

9.2.3 创建DDL触发器 例9-7：使用DDL触发器来防止数据库中的任一表被修改或删除。 SQL Server 2005 其中： <ddl_trigger_option>::=[ENCRYPTION] EXECUTE AS Clause] <method_specifier> ::= assembly_name.class_name.method_name 例9-7：使用DDL触发器来防止数据库中的任一表被修改或删除。 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK 例9-8：使用 DDL 触发器来防止在数据库中创建表。 FOR CREATE_TABLE

9.2.3 创建DDL触发器 PRINT 'CREATE TABLE Issued.' SELECT SQL Server 2005 PRINT 'CREATE TABLE Issued.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') RAISERROR ('New tables cannot be created in this database.', 16, 1) ROLLBACK

9.2.4 管理触发器 查看触发器 sp_helptext trigger_name sp_helptrigger table_name SQL Server 2005 查看触发器 sp_helptext trigger_name sp_helptrigger table_name sp_help ’触发器名称’ sp _depends ‘触发器名称’ sp _depends ‘表名’ 修改触发器 ALTER TRIGGER 触发器 删除触发器 DROP TRIGGER 触发器

9.3 小结 SQL Server 2005 本章介绍了存储过程和触发器的概念、创建和调用方法。其中重点介绍了存储过程的创建和调用，以及DDL和DML触发器的创建方法。希望同学们通过课后习题和上机练习加深对存储过程和触发器的理解，提高T-SQL编程水平。