SQL Server 2000
数据库应用技术
SQL SERVER 2000 数据库应用技术构架
SQL Server
2000是一个性能优越、
面向客户 /服务器的关系型网络数据库管理系统支持多种操作系统平台四大模块
SQL Server数据的网页发布
16-18 章应用实施篇数据库基础与 SQL Server 概述数据库管理与数据完整性表与视图的管理存储过程与触发器
1-3 章系统基础篇
4-10 章
SQL技术篇
VB/SQL Server应用程序开发
SQL SERVER 2000应用实例教参系统组成与服务器管理
Transact-SQL 语言与数据查询游标与安全管理数据库复制与数据转换数据备份与恢复11-15 章系统管理篇
课程概要? 第 一 章 数据库基础
第 二 章 SQL Server 2000概述
第 三 章 系统组成与服务器管理
第 四 章 数据库管理
第 五 章 数据完整性
第 六 章 表的管理与使用
第 七 章 视 图
第 八 章 Transact-SQL 语言
第 九 章 数据查询
第 十 章 存储过程与触发器
第十一章 游 标
第十二章 安全管理
第十三章 数据备份与恢复
第十四章 数据库复制
第十五章 数据转换
第十六章 SQL Server数据的网页发布
第十七章 VB / SQL Server应用程序开发
第十八章 SQL Server 2000应用实例第 10章 存储过程与触发器
10-1 存 储过 程
10-2 触发 器
10-3 存 储过 程 与触发应 用
10-4 本章小 结在大型数据库系统中，存储过程和触发器具有很重要的作用。存储过程是 SQL语句和控制流语句组成的集合,触发器是一种特殊的存储过程。本章主要介绍存储过程和触发器的概念，优点和基本操作。
10-1-1 存 储过 程基 础
10-1-2 创 建存 储过 程
10-1-3 管理存 储过 程
10-1-4 修改存 储过 程
10-1-5 执 行存 储过 程
10-1-6 删 除存 储过 程
10-1 存 储过 程定 义,存 储过 程 （ stored procedure） 是 SQL 语 句和可 选 控制流 语句的 预编译 集合,SQL Server 会将该 集合中的 语 句 编译 成一 个执 行 单 位。
存 储过 程可包含程序流,逻辑 以及 对数 据 库 的 查询 。可以接受 输 入 参数,输出 参数,返回 单个 或多 个结 果集以及返回 值 。可源于任何使用 SQL 语 句的目的 来 使用存 储过 程,
它 具有以下 优 点：
1 可以在 单个 存 储过 程中 执 行一系列 SQL 语 句。
2 可以 从 自己的存 储过 程 内 引用其 它 存 储过 程,这 可以 简 化一系列 复杂语句。
3 存 储过 程在 创 建 时 即在服 务 器上 进 行 编译,所以 执 行起 来 比 单个 SQL 语句快。
存 储过 程 虽 然 既 有 参数 又有返回 值,但是 它与 函 数 不同。存 储过 程的返回 值只是指明 执 行是否成功,并 且 它 不能像函 数 那 样 被直接用在表 达 式中。
存 储过 程分 为 三 种类 型：
1 系 统 存 储过 程
2 扩 展存 储过 程
3 用 户 自定 义 存 储过 程
10-1-1 存 储过 程基 础
SQL Server 存 储过 程是用 Transact-SQL语 句 CREATE
PROCEDURE 创 建的,并 可用 ALTER PROCEDURE 语 句进 行修改。
存 储过 程定 义 主要包含 过 程名及其 参数 的 说 明和 过 程的主体
（其中包含 执 行 过 程操作 Transact-SQL 语 句） 两 部分。
可以 创 建一 个过 程供永久使用，或在一 个会话 中 临时 使用
（局部 临时过 程），或在所有 会话 中 临时 使用（全局 临时过程）。
在 SQL Server中，可以使用三 种 方法 创 建存 储过 程,
1 用 Transact-SQL语 句中的 CREATE PROCEDURE命令 创建存 储过 程。
2 用 SQL Server 企 业 管理器 创 建存 储过 程。
3 用 创 建存 储过 程向 导创 建存 储过 程。
10-1-2 创 建存 储过 程用 CREATE PROCEDURE创 建存 储过 程的 语 法形式如下：
CREATE PROC [ EDURE ] 存 储过 程名 [; number ]
[ { @parameter data_type }[ VARYING ] [ = default ]
[ OUTPUT ] ] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION } ]
[ FOR REPLICATION ]
AS
sql_statement [,..n ]
创 建存 储过 程 时,需要确定存 储过 程的三 个组 成部分：
1 所有 输 入 参数 以及 传给调 用者的 输 出 参数 。
2 被 执 行的 针对数 据 库 的操作 语 句，包括 调 用其 它 存 储过 程的 语 句。
3 返回 给调 用者的 状态值,以指明 调 用是成功 还 是失 败
1．用 T-SQL语 句 创 建存 储过 程
number：是可 选 的整 数,用 来对 同名的 过 程分 组,以便用一 条
DROP PROCEDURE 语 句即可 将 同 组 的 过 程一起 删 除。
VARYING：用于指定作 为输 出 参数 支持的 结 果集（由存 储过 程动态构 造,内 容可以 变 化）,该选项仅 用于游 标参数 。
OUTPUT：是一 个 返回 参数,使用 该选项 的 参数 可把信息返回给调 用 过 程；
WITH RECOMPILE：表示 该过 程在 运 行 时将 重新 编译 。
WITH ENCRYPTION：表示加密 选项 被指定，用 户 无法 浏览
syscommerits系 统 表中存放的定 义 且无法 将 其解密,该选项 防止把 过 程作 为 SQLServer复 制的一部分 发 布。
FOR REPLICATION：指定存 储过 程 筛选,只能在 复 制 过 程中执 行。
AS：指定 过 程 执 行的操作。
sql_statement：是存 储过 程中要包含的任意 数 目和 类 型的
Transact-SQL语 句。
参数说 明：
【 例 10-1】 创 建一 个带 SELECT查询语 句的名 为,第一 个 存 储过 程,的存 储过 程。
存 储过 程只能建立在 当 前 数 据 库 上，故需先用 USE 语 句 来 指定 数 据 库 。 在 【 存储过 程 属 性－新建存 储过 程 】 窗口的 编辑 框中，使用存 储过 程的名 称来 代替
[OWNER].[PROCEDURE NAME]，在本例中，即 为,查询 成 绩 表,，然后 编辑该存 储过 程的 内 容。下 图为编辑 好的 查询 成 绩 表存 储过 程。
2 用 SQL Server 企 业 管理器 创 建存 储过 程
（ 1） 在企 业 管理器中展 开 服 务 器。展 开 【 数 据 库 】 文件 夹,再 选 中要在其中 创 建存 储过 程的 数 据 库 。在 【 工具 】 菜 单 上 单击 【 向 导 】 命令。
（ 2） 在 弹 出的 【 选择 向 导 】 窗口展 开 【 数 据 库 】 文件 夹 。 【 创 建存 储过程向 导 】 。
（ 3） 选择 下一步,弹 出 【 选择数 据 库 】 窗口,选择 欲建立存 储过 程的 数据 库 。 选择 【 下一步 】,进 入 【 选择 存 储过 程 】 窗口，在此可以看到在 当前 数 据 库 所建立的所有用 户 表名 称,并 有三 个 包含 复选 框的行，分 别 代表用户 所能建立的三 种 存 储过 程,插 入,删 除及更新 资 料,请选择 合适的 复选 框。
按 【 下一步 】 显 示 【 正在完成 创 建存 储过 程向 导 】 窗口,这个 窗口列出所建的存 储过 程名 称 及描述。
（ 4） 欲重新命名或 编辑 存 储过 程，在 【 正在完成 创 建存 储过 程向 导 】 窗口中按一下 编辑,进 入 【 编辑 存 储过 程 属 性 】 窗口,该 窗口列出此存 储过 程会 影 响 的列，在 选择 列中 选 中的列名 称将会 被存 储过 程使用。
（ 5） 要重新命名存 储过 程,请清 除 名 称 编辑 文本框中的名 称,并 用新的名 称 替代。要 编辑 存 储过 程,进 入 【 编辑 存 储过 程 SQL】 对话 框,它 可以 检查 存 储过 程的 Transact-SQL 程序代 码 。可以在 编辑区 域直接 编辑 代 码,完成后，可以按分析按 钮检查语 法 错误,按确定返回 【 正在完成 创 建存 储过 程向 导 】 窗口。
（ 6） 在 【 正在完成 创 建存 储过 程向 导 】 窗口中 单击 确定按 钮,即完成建立存 储过 程。
3,用 创 建存 储过 程向 导创 建存 储过 程存 储过 程被 创 建之后,它 的名字就存 储 在系 统 表 sysobjects中,它 的源代 码 存放在系 统 表
syscomments中。可以使用企 业 管理器或系 统 存 储过 程 来查 看用 户创 建的存 储过 程。
1．在企 业 管理器中 查 看存 储过 程的定 义
(1)业 管理器中展 开 服 务 器 组,然后展 开 服 务 器。
（ 2)展 开 【 数 据 库 】 文件 夹,展 开 存 储过 程所 属 的 数 据 库,单击 【 存 储过 程 】 文件 夹 。
(3)右 侧 的 详细 信息窗格中，鼠 标 右 键单击 需要 查 看的存 储过 程,弹 出快捷菜 单,在 弹出菜 单 中 选择 【 属 性 】 命令。 弹 出存 储过 程 属 性 对话 框,从 中可以 查 看存 储过 程的定 义 。
10-1-3 管理存 过 程
（ 1) 在企 业 管理器中展 开 服 务 器 组,然后展 开 服 务 器。
（ 2) 展 开 【 数 据 库 】 文件 夹,展 开 存 储过 程所 属 的 数 据 库,单击 【 存 储过 程 】 文件 夹 。
（ 3) 在右 侧 的 详细 信息窗格中，鼠 标 右 键单击 需要 查 看的存 储过 程，在 弹 出菜 单 中 选择 【 所有任 务 】 菜 单,然后 单击 【 显 示相 关 性 】 命令 3．
3,在企 业 管理器中 查 看 关 于 扩 展存 储过 程的信息
（ 1） 在企 业 管理器中展 开 服 务 器 组,然后展 开 服 务 器 。
（ 2） 展 开 【 数 据 库 】 文件 夹,展 开 master 数 据 库,然后 单击 【 扩 展存 储过 程 】 文件 夹 。
（ 3） 在右 侧 的 详细 信息窗格中,右 击扩 展存 储过 程,然后 单击 【 属 性 】 命令 。
（ 4） 单击 （ "..."） 按 钮,查 找包含此 扩 展存 储过 程的 DLL。 （ 可 选 ）
（ 5） 单击 【 权 限 】,查 看或 设 置此 扩 展存 储过 程的 权 限 。 （ 可 选 ）
4,使用系 统 存 储过 程 来查 看用 户创 建的存 储过 程可供使用的系 统 存 储过 程及其 语 法形式如下：
sp_help,报 告有 关数 据 库对 象,用 户 定 义数 据 类 型或 SQL Server所提供的 数据 类 型的信息 （ 语 法格式,sp_help [ [ @objname = ] name ]） 。
sp_helptext：用于 显 示 规则,默 认值,未加密的存 储过 程,用 户 定 义 函 数,触发 器或 视图 的文本 。 （ 语 法格式,sp_helptext [ @objname = ] 'name'） 。
sp_depends：用于 显 示有 关数 据 库对 象相 关 性的信息 （ 语 法格式,sp_depends
[ @objname = ] 'object'） 。
sp_stored_procedures：用于返回 当 前 环 境中的存 储过 程列表 。
【 例 10-2】 查询显 示,信息管理,存 储过 程的源代 码 信息和 参数 及其 数 据 类 型 。
sp_helptext 信息管理
sp_help信息管理
go
2．在企 业 管理器中 查 看存 储过 程的相 关 性有 2种 方法：
1 先做 删 除然后再重建
2 使用 ALTER PROCEDURE语 句一次性地完成修改操作
ALTER PROCEDURE语 句修改存 储过 程 语 法格式如下：
ALTER PROC[EDURE]存 储过 程名 [； number]
[ { @parameter data_type }[ VARYING ] [ = default ] [ OUTPUT ] ]
[,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION } ]
[ FOR REPLICATION ]
AS
sql_statement [,..n ]
修改 过 程 与创 建 过 程的 语 法基本上是一致的。若要修改存 储过 程的名 称,可企 业 管理器中或用系 统 提供的 sp_rename存 储过 程 进 行重命名操作。
sp_rename命令的 语 法形式如下：
sp_rename 更改前名字,更改后名字
【 例 10-3】 将,信息管理,存 储过 程更名 为,计 算机科 学,。
sp_rename 信息管理,计 算机科 学
10-1-4 修改存 储过 程可用 EXECUTE语 句 运 行一 个 存 储过 程，也可以令存 储过 程自 动运 行。 当 一 个 存 储过程 标识为 自 动运 行，在每次 启动 SQL Server 2000时,该 存 储过 程便 会 自 动运 行。
(由 sysadmin固定服 务 器角色使用 sp_procoption过 程可置存 储过 程 为 自 动启动 。 )
执 行存 储过 程 语 法如下：
[EXEC[UTE]]{[@返回 状态码 =]{过 程名 [:分 组号数 ]|@过 程名 变 量 }}
[[@参数 名 =]{参数值 |@参数变 }[[OUTPUT]|[DEFAULT]][,… ]]
[WITH RECOMPILE]
参数说 明：
返回 状态码,是一 个 可 选 的整型 变 量，用于保存存 储过 程的返回 状态 。 0表示成功 执行； -1～ -99表示 执 行出 错 。 调 用存 储过 程的批 处 理或 应 用程序可 对该状态值进 行判断,以便 转 至不同的 处 理流程。
分 组号数,同 创 建存 储过 程中的 number。
@参数 名,参数值,在 给 定 参数值时,如果 没 有指定 参数 名,那 么 所有 参数值 都必 须以 CREATE PROC语 句中定 义 的 顺 序 给 出；若使用,@参数 名,参数值,格式,则参数值 无需 严 格按定 义时 的 顺 序出 现 。 只要有一 个参数 使用了,@参数 名； 参数值,格式,
则 所有的 参数 都必 须 使用 这种 格式 。
OUTPUT：是指定存 储过 程必 须 返回一 个参数 。 如果 该参数 在 CREATE PROC语 句中不是定 义为 OUTPUT的 话,则 存 储过 程不能 执 行 。 如果指定 OUTPUT,参数 的目的是 为 了使用其返回 值,那 么参数传递 必 须 使用 变 量,即要用,@参数 名,@参数变 量,
这种 格式 。
WITH RECOMPILE：意 为 强制重新 编译 (计划 )。 若无需要,建 议尽 量少用 该选项,
因 为它 消耗 较 多的系 统资 源 。
10-1-5 执 行存 储过 程在 执 行字符串的 语 法格式中，字符串常量可以是 nvarchar或 varchar数据 类 型。 如果包含 N选项,则该 字符串解 释为 nvarchar数 据 类 型。
use 信息管理
EXEC 第一 个 存 储过 程
GO
如果 调 用 该 存 储过 程的 语 句是一 个 批次中的 语 句,并 且不是 该 批次操作的第一 个语 句，就必 须 使用 EXECUTE（可 缩写为 EXEC） 关键词调 用；如果 调 用程序的 语 句 为该 批次操作的第一 个语 句，或 为该 批次操作的唯一 语 句，
用 户 也可以不使用 EXECUTE 关键词 。
第一 个 存 储过 程
GO
【 例 10-4】 执 行例 10-1创 建的存 储过 程 。
如果另一 个 存 储过 程 调 用某 个 已 删 除的存 储过 程,则 SQL Server会 在 执 行 该调 用 过 程时显 示一 条错误 信息。但如果定 义 了同名和 参数 相同的新存 储过 程 来 替 换 已 删 除存 储过程，那 么 引用 该过 程的其 它过 程仍能 顺 利 执 行。存 储过 程分 组 后,将 无法 删 除 组内 的 单个 存 储过 程。 删 除一 个 存 储过 程 会将 同一 组内 的所有存 储过 程都 删 除。
1．在企 业 管理器中 删 除存 储过 程 步 骤 如下：
（ 1）展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹,展 开 存 储过 程所 属数 据 库,然后 单击 【 存 储过 程 】 文件 夹 。
（ 3） 在 详细 信息窗格中右 击 要 删 除的存 储过 程，然后 从 快捷菜 单 中 选择删 除,会 出现 所示的除去（存 储过 程） 对 象 对话 框。
（ 4） 若要 查 看 删 除此存 储过 程 对数 据 库 的影 响,单击 【 显 示相 关 性 】 命令。
（ 5） 按全部除去即可 删 除存 储过 程。
2．在企 业 管理器中 删 除 扩 展存 储过 程 步 骤 如下：
（ 2） 展 开 【 数 据 库 】 文件 夹,展 开 master 数 据 库,然后 单击 【 扩 展存 储过 程 】 文件 夹 。（ 1）（ 3）（ 4）（ 5） 与 1 删 除存 储过 程一致
3．用 T-SQL语 句 删 除存 储过 程用 DROP PROCEDURE语 句 删 除存 储过 程的 语 法格式如下：
DROP PROCEDURE {存 储过 程名 }[,… ]
【 例 10-5】 删 除例 10-1所 创 建的存 储过 程 。
USE 信息管理
GO
DROP PROCDURE 第一 个 存 储过 程
10-1-6 删 除存 储过 程
10-2-1 触发 器基础
10-2-2 创 建 触发器
10-2-3 管理 触发 器
10-2-4 修改 触发 器
10-2-5 删 除 触发器
10-2 触发器定 义 触发 器（ trigger） 是一 种 特殊的存 储过 程,它与 表 紧 密相 连,基于表而建立，可 视 作表的一部分。
用 户创 建 触发 器后，就能控制 与触发 器 关联 的表。 当 表中的 数 据 发 生 插 入,删除或修改 时,触发 器自 动运 行。 触发 器是一 种维 持 数 据引用完整性的 极 好方法。
设 置 触发 器使得多 个 不同的用 户 能 够 在保持 数 据完整性和一致性的良好 环 境下进 行每 个 修改操作。
触发 器不同于前面介 绍过 的存 储过 程。 触发 器主要是通 过 事件 进 行 触发 而被 执行的，而存 储过 程可以通 过 存 储过 程名而被直接 调 用。 触发 器是一 个 功能强大的工具,它 使每 个 站点可以在有 数 据修改 时 自 动 强制 执 行其 业务规则 。 触发 器可以用于 SQL Server 约 束、默 认值 和 规则 的完整性 检查 。
触发 器有五 种类 型：
UPDATE,INSERT,DELETE,INSTEAD OF 和 AFTER。
有了 触发 器，只要 对该 表格更新,插 入或 删 除 时,就 会触动对应 的 UPDATE、
INSERT 或 DELETE 触发 器。 INSTEAD OF 和 AFTER 是 SQL 2000 新增的 两项触发 器,INSTEAD OF触发 器 会 取代 插 入、更新和 删 除操作而 执 行。
AFTER 触发 器 会 在 触发动 作之后再 触动,可 视为 控制 触发 器激活 时间 的机制。
10-2-1 触发 器基 础
（ 1） 触发 器只在 触发它 的 语 句完成后 执 行。 举 例 来说,如果 UPDATE
语 句成功,UPDATE 触发 器才 会 被 触动 。
（ 2） 如果 语 句在表中 执 行 违 反 条 件 约 束或引起 错误,触发 器不 会触动 。
（ 3） 触发 器 视为单 一事 务 中的一部 份,因此可以由原 触发 器 复 原事 务,如果在事 务过 程中 侦测 到 严 重的 错误 （如用 户 中 断联 机）,则会 自 动复 原整 个事 务 。
（ 4） 一 个语 句只能 触动 一次 触发 器。
当触发 器 触动,若 产 生任何 结 果，就 会 如存 储过 程一 样,将结 果 传 回其 调 用的 应 用程序。一般 来说,INSERT,UPDATE 或 DELETE 的 语 句（ 触动触发 器的 语 句）不 会将结 果 传 回； 结 果通常由 SELECT 查询传 回。因此，
为 了避免 触发 器 传 回 结 果 给应 用程序,请 勿在 触发过 程定 义 中引入
SELECT 语 句或指派 变 量。如果希望 从触发 器中 传 回 结 果，在允 许 修改 触发 表的每 个应 用程序中都必 须 撰 写 特殊的程序，才能使 应 用程序收到 传 回的资 料 并进 行正确的 处 理。如果必 须 在 触发 器中指派 变 量，可在 触发 器的起始位置使用 SET NOCOUNT ON 语 句以防止 传 回任何 结 果列。
SET NOCOUNT 语 句 指定是否 传 回 查询 或受 语 句影 响 的列 数 目的信息（如影 响 23 个 列）。 SET NOCOUNT 的默 认值 是 设 在 OFF，也就是 说会传回受影 响 列的 讯 息。
该设 定 并 不影 响 SELECT 语 句 实际结 果的 传 回，只 传 回 计数 。
下面是 关 于 触发 器的一些其 它规 定：
创 建一 个触发 器,内 容主要包括 触发 器名 称,与触发 器 关联 的表，激活 触发 器的语 句和 条 件,触发 器 应 完成的操作等。
创 建 触发 器方法主要有：
1.使用 T-SQL语 句 创 建 触发 器 2．在企 业 管理器中 创 建 触发 器
1.使用 T-SQL语 句 创 建 触发 器创 建 触发 器可以使用 CREATE TRIGGER语 句，其 语 法格式如下：
CREATE TRIGGER 触发 器名
ON {表名 |视图 名 }
[ WITH ENCRYPTION ]
{ { FOR | AFTER | INSTEAD
OF}{[DELETE][,][INSERT][,][UPDATE]}
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE (列名 )[ { AND | OR } UPDATE (列名 ) ][,..n ]
|IF(COLUMNS_UPDATED(){位 运 算符 }位掩 码 ){比 较运 算符 }检验值
[,..n ]}]
sql_statement [,..n ]
}
10-2-2 创 建 触发 器
WITH ENCRYPTION：加密 选项,可防止 触发 器作 为 SQL Server复 制的一部分 发 布。
AFTER：表示在引起 触发 的 SQL语 句中所有的操作 (包括引用 级联 操作和 约 束 检查 等 )成功 执 行后，才激活本 触发 器的 执 行；如果 仅 指定 FOR,则 AFTER是默 认设 置，不能在 视图 上定 义 AFTER触发 器。 INSTEAD OF：指定 执 行本 触发 器而不是 执 行引起 触发 的 SQL
语 句，即 触发 器替代 触发语 句的操作；每 个 更新 语 句 (DELETE,INSERT,UPDATE)最多只能定 义 一 个 INSTEAD OF触发 器。
[DELETE][,][INSERT][,][UPDATE]：表示指定 执 行 哪 些更新 语 句 时将 激活 触发 器，
至少要指定一 个选项,若 选项 多于一 个,需用逗 号 分隔 这 些 选项 。
WITH APPEND：指定 应该 添加 现 有 类 型的其他 触发 器。
NOT FOR REPLICATION：在 复 制 进 程更改 触发 器所涉及的表 时,不 执 行 该触发 器。
IF UPDATE（列名）,测试 在指定的列上 进 行的 INSERT或 UPDATE操作，不能用于
DELETE操作,对 INSERT操作,测试将 返回 TRUE值,因 为 在指定列上 输 入了 显 式 值 或隐 性 (NULL)值 ；若要同 时对 多 个 列 进 行 测试,可使用 逻辑运 算符 连 接多 个 UPDATE(列名 )子句
IF （ COLUMNS_UPDATED()）,测试 是否 插 入或更新了提及的列，不能用于
DELETE操作。 测试 返回 值 是 varbinary位模式。位模式中,从 右到左第 1位表示表的第 1
列，第 2位表示表的第 2列 …… 。通 过 位比 较运 算就知道 哪 些列已被更新。 对 INSERT操作，
测试将对 所有列返回 TRUE值,因 为 在 这 些列上 输 入了 显 式 值 或 隐 性（ NULL） 值 。
正确 设 置位掩 码,检验值 以及使用比 较运 算,就能确切地知道 实际 更新或 插 入了 哪 些列 。
例如,假 设 表 TI由五 个 列 构 成 （ C1～ C5）,若要 检查 列 C2,C3或 C4是否有更新,可使用 这样 的子句 实现,IF(COLUMN_UPDATE() & 14)>0
参数说 明：
创 建一 个触发 器,当 用 户试图 向 cj表中添加或修改 数 据 时,该触发 器向客 户端 显 示一 条 消息。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'CJ_IU' AND type = ' TR')
DROP TRIGGER CJ_IU
GO
CREATE TRIGGER CJ_IU
ON cj
FOR INSERT,UPDATE
AS PRINT '插 入或更新了 CJ库 '
GO
其中,“插 入或更新了 CJ库,是 触发 器所 发 出的消息 内 容。 PRINT用于 将 用 户 定 义 的消息返回客 户 端。
2．在企 业 管理器中 创 建 触发 器
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹与 含 触发 器的表所 属 的 数 据 库,然后 单击 【 表 】 文件 夹 。
（ 3）在 详细 信息窗格中，右 击将 在其上 创 建 触发 器的表，指向 【 所有任 务 】 菜 单,然后单击 【 管理 触发 器 】 命令,弹 出所示的 对话 框。
（ 4） 在 【 名 称 】 中 选择 【 新建 】,在 【 文本 】 框中 输 入 创 建 触发 器的 语 句 。
（ 5） 若要 检查语 法,单击 【 检查语 法 】 命令 。
【 例 10-6】
1．使用企 业 管理器 查 看 触发 器信息
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹与 含 触发 器的表所 属 的 数 据 库,然后 单击 【 表 】 文件 夹 。
（ 3）在 详细 信息窗格中，右 击触发 器所在的表，指向 【 所有任 务 】 菜 单,然后 单击
【 管理 触发 器 】 命令。
2．使用企 业 管理器 查 看 触发 器的相 关 性
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹,展 开触发 器所 属 的 数 据 库,然后 单击 【 表 】 文件 夹 。
（ 3）在 详细 信息窗格中，右 击触发 器所 属 的表，指向 【 所有任 务 】 菜 单,然后 单击
【 显 示相 关 性 】 命令。
（ 4）在 【 对 象 】 中,单击 要 查 看其相 关 性的 触发 器。
3．使用系 统 存 储过 程 查 看 触发 器
（ 1） sp_helptrigger '表名 ',返回指定表中定 义 的 当 前 数 据 库 的 触发 器 类 型。
（ 2） sp_help '触发 器名 称 ',用于 查 看 触发 器的一般信息，如 触发 器的名 称,属 性、
类 型和 创 建 时间 。
（ 3） sp_helptext '触发 器名 称 ',用于 查 看 触发 器的正文信息。
（ 4） sp_depends '触发 器名 称 /表名 ',用于 查 看指定 触发 器所引用的表或者指定的表涉及到的所有 触发 器。
10-2-3 管理 触发 器创 建一 个 名 为 ‘信息管理 ’的 触发 器,当书 的 销 售 总 量达 到 10 时,就 产 生 错误,50010。然后 执 行 sp_helptrigger
列出表 sales 中 触发 器的相 关 信息。
USE pubs
CREATE TRIGGER 信息管理
ON sales
FOR INSERT,UPDATE
AS RAISERROR (50010,16,10)
EXEC sp_helptrigger sales
Go
下面是 结 果集：
trigger_name trigger_owner isupdate isdelete
isinsert
--------------------------------------------
--------------------------------------------
----------
信息管理 dbo 1 0 1
(1 row(s) affected)
【 例 10-7】
修改 触发 器定 义与 修改一 个 存 储过 程的操作相 类 似。 有三 种 方法,如下：
1 先做 删 除然后再重建
2 使用企 业 管理器
3 ALTER TRIGGER语 句一次性地完成修改操作
2．使用企 业 管理器修改 触发 器正文
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2） 展 开 【 数 据 库 】 文件 夹与 含 触发 器的表所 属 的 数 据 库,然后 单击 【 表 】
文件 夹 。
（ 3） 在 详细 信息窗格中，右 击触发 器所在的表，指向 【 所有任 务 】 菜 单,然后 单击 【 管理 触发 器 】 命令。
（ 4） 在 【 名 称 】 框中 选择触发 器的名 称 。
（ 5） 按需要在 【 文本 】 字段中更改 触发 器的 语 句文本。
（ 6） 若要 检查触发 器的 语 法,单击 【 检查语 法 】 命令。
3．使用 ALTER TRIGGER命令修改 触发 器正文
ALTER TRIGGER语 句 与 CREATE TRIGGER语 句的 语 法基本上是一致的,两者只是 语 句的第一 个关键单词 不相同而已，因而 这 里就不再 赘 述了。
若要修改 触发 器的名 称,用系 统 提供的 sp_rename存 储过 程 进 行重命名操作。
使用 sp_rename命令修改 触发 器的名 称语 法形式如下：
sp_rename 更改前名字,更改后名字
10-2-4 修改 触发 器删 除一 个触发 器 时,该触发 器所 关联 的表和 数 据不 会 受到任何影 响,删 除触发 器所在的表 时,SQL Server将会 自 动删 除 与该 表相 关 的 触发 器。
1 在企 业 管理器中 删 除 触发 器 过 程
2 使用系 统 命令 DROP TRIGGER删 除指定的 触发 器
1 在企 业 管理器中 删 除 触发 器 过 程 为,
在企 业 管理器中，用右 键单击 要 删 除的 触发 器所在的表,从弹 出的快捷菜 单 中 选择 所有任 务 子菜 单 下的管理 触发 器 选 项,则会 出 现触发 器属 性 对话 框。在名 称选项 框中 选择 要 删 除的 触发 器,单击 【 删 除 】 按 钮,
即可 删 除 该触发 器。
2 使用系 统 命令 DROP TRIGGER删 除指定的 触发 器，其 语 法形式如下,
DROP TRIGGER {触发 器名 } [,...n ]
例 10-8】 删 除名 为 computer的 触发 器。
USE 信息管理
GO
DROP TRIGGER computer
10-2-5 删 除 触发 器
10-3-1 存 储过 程 应 用
10-3-2 触发 器的 应 用
10-3 存 储过 程 与触发 器的 应 用
1．何 时 使用存 储过 程可以出于任何使用 SQL 语 句的目的 来 使用存 储过 程，使用 SQL
Server 2000 创 建 应 用程序 时,Transact-SQL 编 程 语 言是 应用程序和 SQL Server 数 据 库 之 间 的主要 编 程接口。
使用 Transact-SQL 程序 时,可用 两种 方法存 储 和 执 行程序 。
(1)可以在本地存 储 Transact-SQL程序，在 应 用程序中向 SQL
Server 发 送命令
(2)也可以 将 Transact-SQL程序作 为 存 储过 程存 储 在 SQL
Server 中，在 应 用程序中 调 用存 储过 程。
2．使用 参数在 调 用存 储过 程 时,可以 传递参数给它 。要想在存 储过 程中指定输 入 参数,请 以 @ 前 缀为参数 名 称 的前置字，例如 @参数 _1，
一 个 存 储过 程可指定高 达 1024 个参数 。
10-3-1 存 储过 程 应 用建立 参数 @性 别 _1。 当执 行存 储过 程 时,输 入性 别,该 存 储过 程就 会 根据性 别显 示 学 生信息。
USE 信息管理
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
RETURN
在建立存 储过 程前，要先确定是否有重 复 的名 称 存在，或是先 删 除已 经 存在的名 称 后再重新命名。修改后的 Transact-SQL 语 句如下：
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据性 别显 示 学 生信息 ' AND type = 'P')
DROP PROCEDURE 根据性 别显 示 学 生信息
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
RETURN
执 行此存 储过 程 时,必 须 先提供 输 入 参数,否 则会显 示如下的 错误讯 息：服 务 器,消息
201,级别 16,状态 4,过 程 student_select_b，行 0过 程 'student_select_b' 需要 参数 '@para_性 别 '，但未提供 该参数 。 执 行以下 语 句 将 返回 学 生表中所有女同 学 的信息：
USE 信息管理
GO
根据性 别显 示 学 生信息 '女 '
GO
也可以 为参数设 定默 认值,它将 适用于 当调 用存 储过 程 时没 有 参数 的情 况 。
【 例 10-9】
将 存 储过 程的 参数 默 认值设为,男,，其 Transact-SQL
存 储过 程程序代 码变 更如下（只 变 更 CREATE PROCEDURE 行）：
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据性 别显 示 学 生信息 ' AND type = 'P')
DROP PROCEDURE 根据性 别显 示 学 生信息
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)='男 '
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
RETURN
如果在 执 行根据性 别显 示 学 生信息 时没 有提供 参数,存 储过 程 将 使用
“男,为 @性 别 _1的默 认值 。即使 预设参数 已 经 确定，仍然可以提供 输 入参数,此 参数会 覆 写 默 认值 。
如果要在存 储过 程中 传 回 值给调 用程序,请 在 参数 名 称 后使用
OUTPUT 关键词 。要 将值储 存在 变 量中以供 调 用存 储过 程的 应 用程序使用，也可以在 调 用 该 存 储过 程 时,使用 OUTPUT 关键词 。
【 例 10-10】
根据 学号 返回高等 数学 成 绩 中,@学号 _1为学号 的 输 入 参数,@成 绩 _1输出 参数将传 回成 绩,在 调 用存 储过 程 时声 明名 称为 @成 绩 的局部 变 量，用于 储 存 传 回 值 。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据 学号 返回高等 数学 成 绩 ' AND type = 'P')
DROP PROCEDURE根据 学号 返回高等 数学 成 绩
GO
CREATE PROC 根据 学号 返回高等 数学 成 绩 @学号 _1 char(6),@成 绩 _1 NUMERIC
OUTPUT
AS SELECT @成 绩 _1=成 绩 FROM cj WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号
_1
RETURN
必 须 首先 声 明 变 量，才可以在 调 用存 储过 程 时 使用 该变 量。如在下面的程序代 码 中我 们 先声 明 @成 绩变 量 并将 其 变 量 类 型 设为 numeric（ 它 必 须 符合 OUTPUT参数变 量 类 型），然后 执 行此存 储过 程：
DECLARE @成 绩 numeric
EXECUTE 根据 学号 返回高等 数学 成 绩 '040201',@成 绩 _1 = @成 绩 OUTPUT
PRINT CONVERT(varchar(6),@成 绩 )
GO
PRINT 语 句 为 @成 绩传 回 值 85。
请 注意我 们 用 CONVERT 语 句 将 @成 绩 由原 来 的 numeric 变 量 类 型,转换为 varchar数 据型 别,才可以 将该值当 成字符串、字符 变 量 类 型，或以不直接的方式 转换 成字符 来 打印
（ 这 些是 PRINT 语 句的打印要求）。 请 注意在存 储过 程和 调 用程序中 为 OUTPUT 使用不同名 称 的 变 量，以便于理解。
【 例 10-11】
在 执 行存 储过 程 时,也可以使用 变 量指定 输 入 值,让 存 储过 程接收 来 自 调 用程序的 数值,然后修改 该值 或利用 该值 以 执 行某种 作 业,继 而再 将 新的 值传 回 调 用程序。其操作方法 为 在 执 行存 储过 程前，先 为调 用程序中的某 变 量分配一 个值 （或 执 行 查询 以在 变 量中 插 入 值 ），再 将该变 量 传 送到存 储过 程中。
DECLARE 关键词 用于建立局部 变 量，在建立局部 变 量 时,要指定局部 变 量名 称 及 变 量 类 型，而名 称 必 须 以 @ 前 缀为 前置字。一但 变 量 声 明，其 值会 先被 设为 NULL。
局部 变 量可在批 处 理或存 储过 程中 声 明。存 储过 程中的 变 量通常用 来储 存 条 件 语 句 传 回的 值,或是 储 存存 储过 程 RETURN
语 句 传 回的 值 。 变 量也常被用 来当 作 计数 器。 变 量范 围从变 量的 声 明 处开 始,声 明 该变 量的存 储过 程 结 束后,该变 量就不再有效。
3．使用局部 变 量建立一 个 包含局部 变 量的存 储过 程。
该 存 储过 程使用 WHILE 循 环结构插 入五 个 列到表中。首先建立一 个 范例 数 据表，命名为测试 局部 变 量表，然后建立一 个 存 储过 程，命名 为插 入行。在程序中 将 使用 @循 环计数 和 @循 环变 量 这两个 局部 变 量，我 们会 一起 声 明 这两个变 量,并 且以逗 号将两变 量 区隔。使用 Transact-SQL程序代 码 建立表和存 储过 程,
USE 信息管理
GO
CREATE TABLE 测试 局部 变 量表
( 列 1 int,列 2 char(8) )
GO
CREATE PROCEDURE 插 入行 @初始 值 int
AS
DECLARE @循 环计数 int,@循 环变 量 int
SET @循 环变 量 = @初始 值 - 1
SET @循 环计数 = 0
WHILE ( @循 环计数 < 3)
BEGIN
INSERT INTO 测试 局部 变 量表 VALUES (@循 环变 量 + 1,'新增一行 ')
PRINT (@循 环变 量 )
SET @循 环变 量 = @循 环变 量 + 1
SET @循 环计数 = @循 环计数 + 1
END
GO
【 例 10-12】
现 在 执 行起始 值为 1 的存 储过 程，如下所示：
插 入行 1
GO
执 行后 会 打印三 个 @循 环变 量 值,0,1和 2。使用下面的 语 句 从测试 局部 变 量表中 选择 所有的列,select * from 测试 局部 变 量表在 执 行 SELECT 语 句后,会 出 现 如下的 输 出：
1 列 2
-----------------------------
1 新增一行
2 新增一行
3 新增一行
（所影 响 的行 数为 3 行）
当结 束存 储过 程后,@循 环计数 和 @循 环变 量就无法再被存取。先用下面的 T-SQL
语 句 尝试 打印,PRINT @循 环变 量,就 会 得到 这样 的 错误讯 息：
服 务 器,消息 137,级别 15,状态 2，行 1
必 须声 明 变 量 '@循 环变 量 '。
执 行批 处 理 时,此 变 量范 围 的 规则 也适用。一旦 声 明 关键词 GO（表示批 处 理结 束），批 处 理所 声 明的局部 变 量 将 不能再被使用，局部 变 量的范 围仅 限 该 批 处 理使用。以下的存 储过 程 调 用能 让 用 户 了解 这个规则,
DECLARE @成 绩 numeric
EXECUTE 根据 学号 返回高等 数学 成 绩 '040201',@成 绩 _1 = @成 绩 OUTPUT
PRINT CONVERT(varchar(6),@成 绩 ) /GO
PRINT CONVERT(varchar(6),@成 绩 ) /GO
第一 个 PRINT 语 句打印了批次操作中的局部 变 量 @成 绩 ；第二 个 PRINT 语 句企 图在批次操作 结 束后，打印此局部 变 量 。
（ 1）使用 RETURN回 传值如前文所述，在任何 时 刻使用 RETURN 关键词 都可以无 条 件退出存 储过 程以回到 调 用程序，也可用于退出批 处 理。存 储过 程 执 行到 RETURN语 句即停止 执 行,并 回到 调 用程序中的下一 个语句,RETURN 也可 传 回整 数值 。
【 例 10-13】 修改根据性 别显 示 学 生信息程序 来检查 是否提供了 输 入 值 。
说 明：如果 没 有,PRINT信息 并 回到 调 用程序。 将输 入 参数 默 认值 定 义为 NULL，然后 检查 程序中的 值 是否 为 NULL，如果是表示 没 有 输 入 值 。以下是 这个 程序的程序代 码,
USE 信息管理
GO
ALTER PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)=NULL
AS
IF @性 别 _1=NULL
BEGIN
PRINT '请输 入一 个 性 别 作 为 存 储过 程的 参数 '
RETURN
END
ELSE
BEGIN
SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
END
GO
执 行不 带参数 的存 储过 程：
exec 根据性 别显 示 学 生信息输 出 结 果如下：
请输 入一 个 性 别 作 为 存 储过 程的 参数
4,值 的回 传
【 例 10-14】 使用 RETURN 传 回 值 到 调 用程序中。
*传 回的 值 必 须 是一 个 整 数,常 数 或 变 量皆可。 变 量必 须 透 过声 明才可在 调 用程序中使用。 输 入 参数为 高等 数学 成 绩优 秀的 学号时,传 回 值 1，否 则传 回 99。
CREATE PROC 根据 学号检查 高等 数学 成 绩 是否 优 秀 @学号 _1 char(6)
AS
IF (SELECT 成 绩 FROM 成 绩 WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号
_1)<85
RETURN 0
ELSE
RETURN 1
GO
为 了 调 用存 储过 程 并 能使用 传 回 值,在 调 用程序中 声 明 变 量 并将该变 量 设为 存 储过 程的 传 回 值,
DECLARE @返回 值 int
EXECUTE @返回 值 = 根据 学号检查 高等 数学 成 绩 是否 优 秀 '040201'
IF (@返回 值 = 1) PRINT '恭喜 你,高等 数学 成 绩优 秀！ '
GO
由于指定的 产 品 单 价 为 $17，因此 传 回 值为 1,并 打印 Unit price is less than
$100。 当变 量用于保存 传 回 值时,记 住其 变 量 类 型必 须 被 声 明 为 整 数,这 是
RETURN 语 句的要求。
（ 2）使用 SELECT 回 传值在存 储过 程中可以使用 SELECT 语 句 传 回 数 据，可以 从 SELECT 查询 或 传 回变 量 值 中 传 回 结 果集。
【 例 10-15】 建立一 个 名 为 ‘根据 学号 返回 学号 和高 数 成 绩 ’存 储过 程,该 存 储过 程 会传 回 输入 参数 所指定的 产 品 单 价（透 过 ProductID）。
CREATE PROCEDURE 根据 学号 返回 学号 和高 数 成 绩 @学号 _1 char(6)
AS SELECT 学号,成 绩 FROM 成 绩 WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号 _1
GO 注：要使用 SELECT 语 句 传 回 变 量 值,请 在 语 句后面接着 输 入 变 量名 称 。
【 例 10-16】 在此范例中，重建 ‘根据 学号检查 高等 数学 成 绩 是否 优 秀 ’存 储过 程,传 回一 变 量 值,
并 指定 输 出 标题 。
ALTER PROC 根据 学号检查 高等 数学 成 绩 是否 优 秀 @学号 _1 char(6)
AS
DECLARE @变 量 _1 int
IF (SELECT 成 绩 FROM 成 绩 WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号 _1)<85
SET @变 量 _1 = 0
ELSE
SET @变 量 _1 = 1
SELECT '优 秀否 ' = @变 量 _1
PRINT '这 里可以添加其 它 TSQL语 句 '
GO
执 行存 储过 程,根据 学号检查 高等 数学 成 绩 是否 优 秀 '040201'
结 果如右,优 秀否
-----------
1
（ 所影 响 的行 数为 1 行）
这 里可以添加其 它 Trans这 里可以添加其 它 Transact-SQL语 句,用于强 调 使用 SELECT 传 回 值与 使用 RETURN 传 回 值 之 间 的差 别 。 当调 用 RETURN 时,存 储过 程跟着 结 束； 当调 用
SELECT 时,存 储过 程 则 在 SELECT 传 回 结 果集后,继续执 行。在前面的范例中，如果 没 有指定 输 出 标题,只使用 SELECT @varl,输 出 结 果 将没 有 标题,所示如右,-----------
1
（所影 响 的行 数为 1 行）
1．何时使用触发器触发器和条件约束相同，可用来维持资料的完整性和商业规则，但是触发器不能取代条件约束。用户还可以利用触发器执行比 CHECK 条件约束更复杂的资料检测。由于触发器可以引用其它表中的行，因此才有可能执行复杂的资料检测；反之,CHECK 条件约束只限于在其所定义的表上执行。
用户还可以建立多重触发器，当资料修改时即触动所有触发器。（请记住，
如果在表或检视表中为一个事件定义多重触发器，每个触发器都必须有一个自己的名称）。或者用户可以建立单一触发器，在资料修改时即被触动。也就是每一次当被定义的事件发生，触发器就被触动一次。因此，若是在表上定义 INSERT,UPDATE 和 DELETE 的触发器，每次定义的事件产生时，触发器就会触动。
2,deleted和 inserted表在建立触发器时,SQL Server 会为触发器建立两个暂时表，用户可以参考这两个表，用 Transact-SQL撰写触发过程定义。这两个表固定储存在与触发器一起的内存中，每个触发器只能存取自己的暂时表，暂时表即为触发器所在表的一个副本。用户可以使用这两个表比较资料修改前后状态。这两个表虽被称为表，其实并不同于一般的数据库表，它们储存在内存中，而非在磁盘上。这两个表被命名为 deleted和 inserted。
10-3-2 触发 器的 应 用两个表的结构（相同的列及同一类型的资料）类似于定义触发器的表。
deleted表会储存因 DELETE 及 UPDATE 语句而受影响的行副本。当行因触发器被删除或更新时，被删除或更新的行会传送到 delete表；在触发器中即可使用 deleted表。 inserted表会储存被 INSERT 及 UPDATE 语句影响的行副本，在插入或更新事务时，新的行会同时被加至触发器表与
inserted表。由于执行 UPDATE 语句时，会被视为插入或删除事务，旧的行值会保留一份副本在 deleted表中，而新的行值的副本则保留在触发器表与 inserted表。
inserted和 deleted表中的值只限于在触发器中使用。一旦触发器完成就无法再使用。
3,DELETE 触发器利用 DELETE 语句可激活表的串联更新。
【 例 10-17】 建立一个触发器，当从班级表中删除一行时，在学生、成绩表中对应的行也会被删除。当从 titles表中删除列时，在信息管理数据库内其它的表（如学生表，成绩表），与该行相关的行都会被一并删除。我们会利用 deleted 表指出哪些列该从相关表中删除。
（当从触发器表中删除行时，在 deleted表中会有删除数据的副本，所以可以从 deleted表查询所有被触发器删除的行）。
USE 信息管理
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = '删 除班 级 ' AND
type = 'TR')
DROP TRIGGER 删 除班 级
GO
CREATE TRIGGER 删 除班 级
ON 班 级
FOR DELETE
AS
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始 '
DELETE 学 生
FROM 学 生,deleted
WHERE 学 生,班 级编号 = deleted.班 级编号
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束 '
SELECT * FROM deleted
使用以下的 DELETE 语 句 测试触发 器：
SELECT * FROM 班 级
SELECT 学号,姓名,班 级编号 FROM 学 生
DELETE 班 级 WHERE 班 级编号 ='00000311'
GO
假 设 用 户 已 删 除稍早提及的外部索引 键,一旦 执 行 这个 DELETE 语 句,触发 器便 会触动,用 户会 在 title表上看到 资 料修改事件影 响 列的 讯 息，接着是 从触发 器中三行
PRINT 语 句 说 明的 讯 息,并 列出每 个 表中受影 响 的列 数,输 出 结 果如下：
班 级编号 班 级 名 称
-----------------------------
00000311 会计学
00000402 计 算机
00000411 管理 学
（所影 响 的行 数为 3 行）
学号 姓名 班 级编号
---------------------------------
040201 刘华 德 00000402
040202 许 慧 00000402
031101 陈顺发 00000311
031102 黎和生 00000311
（所影 响 的行 数为 4 行）
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始
（所影 响 的行 数为 2 行）
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束班 级编号 班 级 名 称
---------------------------------
00000311 会计学
（所影 响 的行 数为 1 行）
如果前面做 过备份,下列 语 句用于恢 复 班 级 和 学 生 数 据 库
DELETE 班 级
INSERT INTO 班 级 SELECT * FROM bj
DELETE 学 生
INSERT INTO 学 生 SELECT * FROM student
GO
deleted表的另一 个 用途，是 将 表中被 删 除的列 储 存到 备份 表中，以供日后的 资料分析。例如，用 户 可以利用以下的 语 法,从 成 绩 表 将 被 删 除的列存入一 个 命名为 成 绩备份 的表中。
USE 信息管理
GO
CREATE TABLE 成 绩备份
( 学号 char(6) NOT NULL,课 程名 char(12) NOT NULL,课 程 号 char(6) NOT
NULL,
成 绩 numeric(18,1) NULL,补 考成 绩 numeric(18,1) NULL )
GO
CREATE TRIGGER 成 绩备份触发 器
ON 成 绩
FOR DELETE
AS
INSERT INTO 成 绩备份 SELECT * FROM deleted
GO
SELECT * FROM 成 绩备份
GO
4,INSERT 触发 器
【 例 10-18】 在此 将 通 过 建立一 个 INSERT 触发 器（ 当 INSERT 语 句 执 行 时,会让此 触发 器 触发 ） 来 表述 应 用。 当 我 们 在成 绩 表中 插 入新 值,触发 器 会显 示相 关 信息
（在 触发 器 内 引用 SELECT 语 句以 显 示 inserted表中所含的 资 料）。
USE 信息管理
GO
CREATE TRIGGER 成 绩插 入 触发 器
ON 成 绩
FOR INSERT
AS
SELECT * FROM inserted
PRINT '可以在 这 里 插 入其 它 T-SQL语 句，可以使用 inserted表 '
Go
当 下列 INSERT 语 句 执 行 时,会触发触发 器：
INSERT INTO 成 绩 VALUES(040201,'工 业会计 ',030107,80.0,NULL)
GO
用 户将 看到如下的 运 行 结 果（ 触发 的 显 示信息）：
学号 课 程名 课 程 号 成 绩 补 考成 绩
------------------------------------------------------------
-----------
40201 工 业会计 30107 80.0 NULL
（所影 响 的行 数为 1 行）
（所影 响 的行 数为 1 行）
可以在 这 里 插 入其 它 T-SQL语 句，可以使用 inserted表
5,UPDATE 触发 器
【 例 10-19】 在此,将 建立一 个 UPDATE 触发 器,当 更新成 绩 表中的成 绩 列 时,该触发 器 会检查 成 绩 提高是否超 过 10%。如果超 过 了 10%,将 以 ROLLBACK 语 句 来复 原 触发 器和 调 用 触发器的 语 句。如果在一 个较 大的事 务 中激活 触发 器，整 笔 事 务将 被 复 原。在 这个 范例中，我 们 利用
deleted和 inserted表 来测试 成 绩 的 变 化。
USE 信息管理
GO
ALTER TRIGGER 成 绩 更新 触发 器
ON 成 绩
FOR UPDATE
AS
DECLARE @更改前成 绩 numeric,@更改后成 绩 numeric
SELECT @更改前成 绩 = 成 绩 from deleted
PRINT '更改前成 绩 ='
PRINT CONVERT(varchar(6),@更改前成 绩 )
SELECT @更改后成 绩 = 成 绩 from inserted
PRINT '更改后成 绩 ='
PRINT CONVERT(varchar(6),@更改后成 绩 )
IF(@更改后成 绩 > (@更改前成 绩 * 1.10))
BEGIN
PRINT '成 绩 更改升幅太大，更改失 败 '
ROLLBACK
END
ELSE
PRINT '成 绩 更改成功 '
GO
执 行以下的 语 句：
UPDATE 成 绩 SET 成 绩 =成 绩 * 1.2 WHERE 学号 ='040201' AND 课 程 号
='030107'
GO
用 户会 看到以下的 结 果：
更改前成 绩 =
80
更改后成 绩 =
96
成 绩 更改升幅太大，更改失 败当 建立一 个 UPDATE 触发 器 时,可以指定 触发 器 仅 在特定的列更新 时,才 执 行 语 句。
例如，用 户 可以 设 定只有在成 绩 行被更新 时,才 检查该 行，确 认触发 器正确 触发 。 请 使用下面的 IF UPDATE 子句：
USE 信息管理
GO
ALTER TRIGGER 成 绩 更新 触发 器
ON 成 绩
FOR UPDATE
AS
IF UPDATE(成 绩 )
BEGIN
DECLARE @更改前成 绩 numeric,@更改后成 绩 numeric
SELECT @更改前成 绩 = 成 绩 from deleted
PRINT '更改前成 绩 ='
PRINT CONVERT(varchar(6),@更改前成 绩 )
SELECT @更改后成 绩 = 成 绩 from inserted
PRINT '更改后成 绩 ='
PRINT CONVERT(varchar(6),@更改后成 绩 )
IF(@更改后成 绩 > (@更改前成 绩 * 1.10))
BEGIN
PRINT '成 绩 更改升幅太大，更改失 败 '
ROLLBACK
END
ELSE
PRINT '成 绩 更改成功 '
END
GO
说 明：如果成 绩 表的更新不包括成 绩 行（ 仅 更新其 它 行）,触发 器 会 跳 过 AS后的第一个 BEGIN 和 END 之 间 的 语 句，即跳 过该触发 器。要 测试 以上的 触发 器是否如 预 期般跳 过执 行 BEGIN 和 END 之 间 的 语 句，用 户 可以用下面的 Transact-SQL 语 句，更新一 个 非成 绩 行的 资 料。
UPDATE 成 绩
SET 学号 ='040202'
WHERE 学号 ='040201' AND 课 程 号 ='030107'
GO
用 户 可以看到行的更新不在成 绩 列，外部 IF 条 件回 传 FALSE，因此 输 出 结 果不激活触发 器的打印 语 句。利用 这种 方式可以避免 SQL Server 处 理不必要的 语 句。
6,触发 器嵌套触发 器嵌套（ nested trigger） 是由其 它触发 器所激活的 触发 器，和 递归触发 器不同的地方在于,触发 器嵌套 并 不自行激活，而是 当 修改事件 产 生时,才由激活其 它 的 触发 器所激活。 SQL Server 2000 和 SQL Server
7.0 相同，第一 个触发 器可以激活第二 个触发 器，第二 个触发 器接着激活第三 个触发 器，依此 类 推，可以高 达 32 个层级 的 触发 器。在 SQL
Server 2000 中,触发 器嵌套的 预设状态设为启 用,nested trigger服 务器 设 定 参数 可用 来 控制 触发 器能否巢 状触发 。要停止 触发 器嵌套，可 执 行以下的指令：
sp_configure "nested triggers",0
go
将 nested triggers设 成 0时,则 不激活 触发 器嵌套； 将 nested triggers设成 1时,则 可激活 触发 器嵌套。
例 10-20】 建立一 个 基于 ‘删 除 ’触发 的嵌套 触发 器。
IF EXISTS (SELECT name FROM sysobjects WHERE name = '删 除班 级 ' AND type = 'TR')
DROP TRIGGER 删 除班 级
GO
CREATE TRIGGER 删 除班 级
ON 班 级
FOR DELETE
AS
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始 '
DELETE 学 生
FROM 学 生,deleted
WHERE 学 生,班 级编号 = deleted.班 级编号
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束 '
GO
CREATE TRIGGER 删除学生
ON 学生
FOR DELETE
AS
PRINT '使用存储过程从成绩库中删除相关行 —— 开始 '
DELETE 成绩
FROM 成绩,deleted
WHERE 成绩,学号 = deleted.学号
PRINT '使用存储过程从成绩库中删除相关行 —— 结束 '
SELECT * FROM deleted
GO
执行以下的语句：
DELETE 班级 WHERE 班级编号 ='00000402'
GO
用 户 可以看到如下的 结 果：
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始使用存 储过 程 从 成 绩库 中 删 除相 关 行 —— 开 始
（所影 响 的行 数为 4 行）
使用存 储过 程 从 成 绩库 中 删 除相 关 行 —— 结 束
（所影 响 的行 数为 2 行）
（所影 响 的行 数为 1 行）
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束当 一 组触发 器嵌套的任一 层 失 败 事 务会 被取消，且所有 资 料修改 将 被 复 原至整 组触发 器 触发 前的 状态 。
存储过程 是 SQL 语句和可选控制流语句的预编译集合,SQL Server 会将该集合中的语句编译成一个执行单位。存储过程可包含程序流、逻辑以及对数据库的查询。可以接受输入参数、输出参数、返回单个或多个结果集以及返回值。触发器是一种特殊的存储过程，它与表紧密相连，基于表而建立，可视作表的一部分。
用户创建触发器后，就能控制与触发器关联的表。当表中的数据发生插入、删除或修改时，触发器自动运行。触发器是一种维持数据引用完整性的极好方法。
本章介绍了存储过程的基本概念和如何使用企业管理器与 Transact-SQL语言在查询分析器中创建、管理、执行和修改存储过程；介绍了触发器的基本概念和使用企业管理器与 Transact-SQL语言在查询分析器中创建、管理、执行和修改触发器。并通过一系列实例来叙述参数、变量,RETURN,SELECT在存储过程中的使用和
INSERT,UPDATE,DELETE和嵌套触发器的应用。
习题十
1,何谓存储过程？简述其作用及分类。
2,何谓触发器？简述其作用及分类。
3,试说明 deleted和 inserted表。
4.试根据学号创建一个存储过程，用于显示学生学号和姓名。
5,修改书中存储过程例题：“根据学号检查高等数学成绩是否优秀”，要求根据不同学号有三种返回：用户没有选高等数学这门课、没有达到优秀和达到优秀。
10-4 本章小 结
6,在,成绩,表上创建一个触发器,成绩插入,。当用户插入记录时触发。
7,在,成绩,表上创建一个触发器,成绩删除,。当用户删除记录时触发。
8,在,成绩,表上创建一个触发器,成绩更新,。当用户更新记录时触发。
9,简 述下列程序的 运 行 结 果。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据性 别显 示 学 生信息 ' AND type = 'P')
DROP PROCEDURE 根据性 别显 示 学 生信息
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
10,简 述下列程序的 运 行 结 果。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'CJ_IU' AND type = 'TR')
DROP TRIGGER CJ_IU
GO
CREATE TRIGGER CJ_IU
ON cj
FOR INSERT,UPDATE
AS PRINT '插 入或更新了 CJ库 '
GO
数据库应用技术
SQL SERVER 2000 数据库应用技术构架
SQL Server
2000是一个性能优越、
面向客户 /服务器的关系型网络数据库管理系统支持多种操作系统平台四大模块
SQL Server数据的网页发布
16-18 章应用实施篇数据库基础与 SQL Server 概述数据库管理与数据完整性表与视图的管理存储过程与触发器
1-3 章系统基础篇
4-10 章
SQL技术篇
VB/SQL Server应用程序开发
SQL SERVER 2000应用实例教参系统组成与服务器管理
Transact-SQL 语言与数据查询游标与安全管理数据库复制与数据转换数据备份与恢复11-15 章系统管理篇
课程概要? 第 一 章 数据库基础
第 二 章 SQL Server 2000概述
第 三 章 系统组成与服务器管理
第 四 章 数据库管理
第 五 章 数据完整性
第 六 章 表的管理与使用
第 七 章 视 图
第 八 章 Transact-SQL 语言
第 九 章 数据查询
第 十 章 存储过程与触发器
第十一章 游 标
第十二章 安全管理
第十三章 数据备份与恢复
第十四章 数据库复制
第十五章 数据转换
第十六章 SQL Server数据的网页发布
第十七章 VB / SQL Server应用程序开发
第十八章 SQL Server 2000应用实例第 10章 存储过程与触发器
10-1 存 储过 程
10-2 触发 器
10-3 存 储过 程 与触发应 用
10-4 本章小 结在大型数据库系统中，存储过程和触发器具有很重要的作用。存储过程是 SQL语句和控制流语句组成的集合,触发器是一种特殊的存储过程。本章主要介绍存储过程和触发器的概念，优点和基本操作。
10-1-1 存 储过 程基 础
10-1-2 创 建存 储过 程
10-1-3 管理存 储过 程
10-1-4 修改存 储过 程
10-1-5 执 行存 储过 程
10-1-6 删 除存 储过 程
10-1 存 储过 程定 义,存 储过 程 （ stored procedure） 是 SQL 语 句和可 选 控制流 语句的 预编译 集合,SQL Server 会将该 集合中的 语 句 编译 成一 个执 行 单 位。
存 储过 程可包含程序流,逻辑 以及 对数 据 库 的 查询 。可以接受 输 入 参数,输出 参数,返回 单个 或多 个结 果集以及返回 值 。可源于任何使用 SQL 语 句的目的 来 使用存 储过 程,
它 具有以下 优 点：
1 可以在 单个 存 储过 程中 执 行一系列 SQL 语 句。
2 可以 从 自己的存 储过 程 内 引用其 它 存 储过 程,这 可以 简 化一系列 复杂语句。
3 存 储过 程在 创 建 时 即在服 务 器上 进 行 编译,所以 执 行起 来 比 单个 SQL 语句快。
存 储过 程 虽 然 既 有 参数 又有返回 值,但是 它与 函 数 不同。存 储过 程的返回 值只是指明 执 行是否成功,并 且 它 不能像函 数 那 样 被直接用在表 达 式中。
存 储过 程分 为 三 种类 型：
1 系 统 存 储过 程
2 扩 展存 储过 程
3 用 户 自定 义 存 储过 程
10-1-1 存 储过 程基 础
SQL Server 存 储过 程是用 Transact-SQL语 句 CREATE
PROCEDURE 创 建的,并 可用 ALTER PROCEDURE 语 句进 行修改。
存 储过 程定 义 主要包含 过 程名及其 参数 的 说 明和 过 程的主体
（其中包含 执 行 过 程操作 Transact-SQL 语 句） 两 部分。
可以 创 建一 个过 程供永久使用，或在一 个会话 中 临时 使用
（局部 临时过 程），或在所有 会话 中 临时 使用（全局 临时过程）。
在 SQL Server中，可以使用三 种 方法 创 建存 储过 程,
1 用 Transact-SQL语 句中的 CREATE PROCEDURE命令 创建存 储过 程。
2 用 SQL Server 企 业 管理器 创 建存 储过 程。
3 用 创 建存 储过 程向 导创 建存 储过 程。
10-1-2 创 建存 储过 程用 CREATE PROCEDURE创 建存 储过 程的 语 法形式如下：
CREATE PROC [ EDURE ] 存 储过 程名 [; number ]
[ { @parameter data_type }[ VARYING ] [ = default ]
[ OUTPUT ] ] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION } ]
[ FOR REPLICATION ]
AS
sql_statement [,..n ]
创 建存 储过 程 时,需要确定存 储过 程的三 个组 成部分：
1 所有 输 入 参数 以及 传给调 用者的 输 出 参数 。
2 被 执 行的 针对数 据 库 的操作 语 句，包括 调 用其 它 存 储过 程的 语 句。
3 返回 给调 用者的 状态值,以指明 调 用是成功 还 是失 败
1．用 T-SQL语 句 创 建存 储过 程
number：是可 选 的整 数,用 来对 同名的 过 程分 组,以便用一 条
DROP PROCEDURE 语 句即可 将 同 组 的 过 程一起 删 除。
VARYING：用于指定作 为输 出 参数 支持的 结 果集（由存 储过 程动态构 造,内 容可以 变 化）,该选项仅 用于游 标参数 。
OUTPUT：是一 个 返回 参数,使用 该选项 的 参数 可把信息返回给调 用 过 程；
WITH RECOMPILE：表示 该过 程在 运 行 时将 重新 编译 。
WITH ENCRYPTION：表示加密 选项 被指定，用 户 无法 浏览
syscommerits系 统 表中存放的定 义 且无法 将 其解密,该选项 防止把 过 程作 为 SQLServer复 制的一部分 发 布。
FOR REPLICATION：指定存 储过 程 筛选,只能在 复 制 过 程中执 行。
AS：指定 过 程 执 行的操作。
sql_statement：是存 储过 程中要包含的任意 数 目和 类 型的
Transact-SQL语 句。
参数说 明：
【 例 10-1】 创 建一 个带 SELECT查询语 句的名 为,第一 个 存 储过 程,的存 储过 程。
存 储过 程只能建立在 当 前 数 据 库 上，故需先用 USE 语 句 来 指定 数 据 库 。 在 【 存储过 程 属 性－新建存 储过 程 】 窗口的 编辑 框中，使用存 储过 程的名 称来 代替
[OWNER].[PROCEDURE NAME]，在本例中，即 为,查询 成 绩 表,，然后 编辑该存 储过 程的 内 容。下 图为编辑 好的 查询 成 绩 表存 储过 程。
2 用 SQL Server 企 业 管理器 创 建存 储过 程
（ 1） 在企 业 管理器中展 开 服 务 器。展 开 【 数 据 库 】 文件 夹,再 选 中要在其中 创 建存 储过 程的 数 据 库 。在 【 工具 】 菜 单 上 单击 【 向 导 】 命令。
（ 2） 在 弹 出的 【 选择 向 导 】 窗口展 开 【 数 据 库 】 文件 夹 。 【 创 建存 储过程向 导 】 。
（ 3） 选择 下一步,弹 出 【 选择数 据 库 】 窗口,选择 欲建立存 储过 程的 数据 库 。 选择 【 下一步 】,进 入 【 选择 存 储过 程 】 窗口，在此可以看到在 当前 数 据 库 所建立的所有用 户 表名 称,并 有三 个 包含 复选 框的行，分 别 代表用户 所能建立的三 种 存 储过 程,插 入,删 除及更新 资 料,请选择 合适的 复选 框。
按 【 下一步 】 显 示 【 正在完成 创 建存 储过 程向 导 】 窗口,这个 窗口列出所建的存 储过 程名 称 及描述。
（ 4） 欲重新命名或 编辑 存 储过 程，在 【 正在完成 创 建存 储过 程向 导 】 窗口中按一下 编辑,进 入 【 编辑 存 储过 程 属 性 】 窗口,该 窗口列出此存 储过 程会 影 响 的列，在 选择 列中 选 中的列名 称将会 被存 储过 程使用。
（ 5） 要重新命名存 储过 程,请清 除 名 称 编辑 文本框中的名 称,并 用新的名 称 替代。要 编辑 存 储过 程,进 入 【 编辑 存 储过 程 SQL】 对话 框,它 可以 检查 存 储过 程的 Transact-SQL 程序代 码 。可以在 编辑区 域直接 编辑 代 码,完成后，可以按分析按 钮检查语 法 错误,按确定返回 【 正在完成 创 建存 储过 程向 导 】 窗口。
（ 6） 在 【 正在完成 创 建存 储过 程向 导 】 窗口中 单击 确定按 钮,即完成建立存 储过 程。
3,用 创 建存 储过 程向 导创 建存 储过 程存 储过 程被 创 建之后,它 的名字就存 储 在系 统 表 sysobjects中,它 的源代 码 存放在系 统 表
syscomments中。可以使用企 业 管理器或系 统 存 储过 程 来查 看用 户创 建的存 储过 程。
1．在企 业 管理器中 查 看存 储过 程的定 义
(1)业 管理器中展 开 服 务 器 组,然后展 开 服 务 器。
（ 2)展 开 【 数 据 库 】 文件 夹,展 开 存 储过 程所 属 的 数 据 库,单击 【 存 储过 程 】 文件 夹 。
(3)右 侧 的 详细 信息窗格中，鼠 标 右 键单击 需要 查 看的存 储过 程,弹 出快捷菜 单,在 弹出菜 单 中 选择 【 属 性 】 命令。 弹 出存 储过 程 属 性 对话 框,从 中可以 查 看存 储过 程的定 义 。
10-1-3 管理存 过 程
（ 1) 在企 业 管理器中展 开 服 务 器 组,然后展 开 服 务 器。
（ 2) 展 开 【 数 据 库 】 文件 夹,展 开 存 储过 程所 属 的 数 据 库,单击 【 存 储过 程 】 文件 夹 。
（ 3) 在右 侧 的 详细 信息窗格中，鼠 标 右 键单击 需要 查 看的存 储过 程，在 弹 出菜 单 中 选择 【 所有任 务 】 菜 单,然后 单击 【 显 示相 关 性 】 命令 3．
3,在企 业 管理器中 查 看 关 于 扩 展存 储过 程的信息
（ 1） 在企 业 管理器中展 开 服 务 器 组,然后展 开 服 务 器 。
（ 2） 展 开 【 数 据 库 】 文件 夹,展 开 master 数 据 库,然后 单击 【 扩 展存 储过 程 】 文件 夹 。
（ 3） 在右 侧 的 详细 信息窗格中,右 击扩 展存 储过 程,然后 单击 【 属 性 】 命令 。
（ 4） 单击 （ "..."） 按 钮,查 找包含此 扩 展存 储过 程的 DLL。 （ 可 选 ）
（ 5） 单击 【 权 限 】,查 看或 设 置此 扩 展存 储过 程的 权 限 。 （ 可 选 ）
4,使用系 统 存 储过 程 来查 看用 户创 建的存 储过 程可供使用的系 统 存 储过 程及其 语 法形式如下：
sp_help,报 告有 关数 据 库对 象,用 户 定 义数 据 类 型或 SQL Server所提供的 数据 类 型的信息 （ 语 法格式,sp_help [ [ @objname = ] name ]） 。
sp_helptext：用于 显 示 规则,默 认值,未加密的存 储过 程,用 户 定 义 函 数,触发 器或 视图 的文本 。 （ 语 法格式,sp_helptext [ @objname = ] 'name'） 。
sp_depends：用于 显 示有 关数 据 库对 象相 关 性的信息 （ 语 法格式,sp_depends
[ @objname = ] 'object'） 。
sp_stored_procedures：用于返回 当 前 环 境中的存 储过 程列表 。
【 例 10-2】 查询显 示,信息管理,存 储过 程的源代 码 信息和 参数 及其 数 据 类 型 。
sp_helptext 信息管理
sp_help信息管理
go
2．在企 业 管理器中 查 看存 储过 程的相 关 性有 2种 方法：
1 先做 删 除然后再重建
2 使用 ALTER PROCEDURE语 句一次性地完成修改操作
ALTER PROCEDURE语 句修改存 储过 程 语 法格式如下：
ALTER PROC[EDURE]存 储过 程名 [； number]
[ { @parameter data_type }[ VARYING ] [ = default ] [ OUTPUT ] ]
[,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION } ]
[ FOR REPLICATION ]
AS
sql_statement [,..n ]
修改 过 程 与创 建 过 程的 语 法基本上是一致的。若要修改存 储过 程的名 称,可企 业 管理器中或用系 统 提供的 sp_rename存 储过 程 进 行重命名操作。
sp_rename命令的 语 法形式如下：
sp_rename 更改前名字,更改后名字
【 例 10-3】 将,信息管理,存 储过 程更名 为,计 算机科 学,。
sp_rename 信息管理,计 算机科 学
10-1-4 修改存 储过 程可用 EXECUTE语 句 运 行一 个 存 储过 程，也可以令存 储过 程自 动运 行。 当 一 个 存 储过程 标识为 自 动运 行，在每次 启动 SQL Server 2000时,该 存 储过 程便 会 自 动运 行。
(由 sysadmin固定服 务 器角色使用 sp_procoption过 程可置存 储过 程 为 自 动启动 。 )
执 行存 储过 程 语 法如下：
[EXEC[UTE]]{[@返回 状态码 =]{过 程名 [:分 组号数 ]|@过 程名 变 量 }}
[[@参数 名 =]{参数值 |@参数变 }[[OUTPUT]|[DEFAULT]][,… ]]
[WITH RECOMPILE]
参数说 明：
返回 状态码,是一 个 可 选 的整型 变 量，用于保存存 储过 程的返回 状态 。 0表示成功 执行； -1～ -99表示 执 行出 错 。 调 用存 储过 程的批 处 理或 应 用程序可 对该状态值进 行判断,以便 转 至不同的 处 理流程。
分 组号数,同 创 建存 储过 程中的 number。
@参数 名,参数值,在 给 定 参数值时,如果 没 有指定 参数 名,那 么 所有 参数值 都必 须以 CREATE PROC语 句中定 义 的 顺 序 给 出；若使用,@参数 名,参数值,格式,则参数值 无需 严 格按定 义时 的 顺 序出 现 。 只要有一 个参数 使用了,@参数 名； 参数值,格式,
则 所有的 参数 都必 须 使用 这种 格式 。
OUTPUT：是指定存 储过 程必 须 返回一 个参数 。 如果 该参数 在 CREATE PROC语 句中不是定 义为 OUTPUT的 话,则 存 储过 程不能 执 行 。 如果指定 OUTPUT,参数 的目的是 为 了使用其返回 值,那 么参数传递 必 须 使用 变 量,即要用,@参数 名,@参数变 量,
这种 格式 。
WITH RECOMPILE：意 为 强制重新 编译 (计划 )。 若无需要,建 议尽 量少用 该选项,
因 为它 消耗 较 多的系 统资 源 。
10-1-5 执 行存 储过 程在 执 行字符串的 语 法格式中，字符串常量可以是 nvarchar或 varchar数据 类 型。 如果包含 N选项,则该 字符串解 释为 nvarchar数 据 类 型。
use 信息管理
EXEC 第一 个 存 储过 程
GO
如果 调 用 该 存 储过 程的 语 句是一 个 批次中的 语 句,并 且不是 该 批次操作的第一 个语 句，就必 须 使用 EXECUTE（可 缩写为 EXEC） 关键词调 用；如果 调 用程序的 语 句 为该 批次操作的第一 个语 句，或 为该 批次操作的唯一 语 句，
用 户 也可以不使用 EXECUTE 关键词 。
第一 个 存 储过 程
GO
【 例 10-4】 执 行例 10-1创 建的存 储过 程 。
如果另一 个 存 储过 程 调 用某 个 已 删 除的存 储过 程,则 SQL Server会 在 执 行 该调 用 过 程时显 示一 条错误 信息。但如果定 义 了同名和 参数 相同的新存 储过 程 来 替 换 已 删 除存 储过程，那 么 引用 该过 程的其 它过 程仍能 顺 利 执 行。存 储过 程分 组 后,将 无法 删 除 组内 的 单个 存 储过 程。 删 除一 个 存 储过 程 会将 同一 组内 的所有存 储过 程都 删 除。
1．在企 业 管理器中 删 除存 储过 程 步 骤 如下：
（ 1）展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹,展 开 存 储过 程所 属数 据 库,然后 单击 【 存 储过 程 】 文件 夹 。
（ 3） 在 详细 信息窗格中右 击 要 删 除的存 储过 程，然后 从 快捷菜 单 中 选择删 除,会 出现 所示的除去（存 储过 程） 对 象 对话 框。
（ 4） 若要 查 看 删 除此存 储过 程 对数 据 库 的影 响,单击 【 显 示相 关 性 】 命令。
（ 5） 按全部除去即可 删 除存 储过 程。
2．在企 业 管理器中 删 除 扩 展存 储过 程 步 骤 如下：
（ 2） 展 开 【 数 据 库 】 文件 夹,展 开 master 数 据 库,然后 单击 【 扩 展存 储过 程 】 文件 夹 。（ 1）（ 3）（ 4）（ 5） 与 1 删 除存 储过 程一致
3．用 T-SQL语 句 删 除存 储过 程用 DROP PROCEDURE语 句 删 除存 储过 程的 语 法格式如下：
DROP PROCEDURE {存 储过 程名 }[,… ]
【 例 10-5】 删 除例 10-1所 创 建的存 储过 程 。
USE 信息管理
GO
DROP PROCDURE 第一 个 存 储过 程
10-1-6 删 除存 储过 程
10-2-1 触发 器基础
10-2-2 创 建 触发器
10-2-3 管理 触发 器
10-2-4 修改 触发 器
10-2-5 删 除 触发器
10-2 触发器定 义 触发 器（ trigger） 是一 种 特殊的存 储过 程,它与 表 紧 密相 连,基于表而建立，可 视 作表的一部分。
用 户创 建 触发 器后，就能控制 与触发 器 关联 的表。 当 表中的 数 据 发 生 插 入,删除或修改 时,触发 器自 动运 行。 触发 器是一 种维 持 数 据引用完整性的 极 好方法。
设 置 触发 器使得多 个 不同的用 户 能 够 在保持 数 据完整性和一致性的良好 环 境下进 行每 个 修改操作。
触发 器不同于前面介 绍过 的存 储过 程。 触发 器主要是通 过 事件 进 行 触发 而被 执行的，而存 储过 程可以通 过 存 储过 程名而被直接 调 用。 触发 器是一 个 功能强大的工具,它 使每 个 站点可以在有 数 据修改 时 自 动 强制 执 行其 业务规则 。 触发 器可以用于 SQL Server 约 束、默 认值 和 规则 的完整性 检查 。
触发 器有五 种类 型：
UPDATE,INSERT,DELETE,INSTEAD OF 和 AFTER。
有了 触发 器，只要 对该 表格更新,插 入或 删 除 时,就 会触动对应 的 UPDATE、
INSERT 或 DELETE 触发 器。 INSTEAD OF 和 AFTER 是 SQL 2000 新增的 两项触发 器,INSTEAD OF触发 器 会 取代 插 入、更新和 删 除操作而 执 行。
AFTER 触发 器 会 在 触发动 作之后再 触动,可 视为 控制 触发 器激活 时间 的机制。
10-2-1 触发 器基 础
（ 1） 触发 器只在 触发它 的 语 句完成后 执 行。 举 例 来说,如果 UPDATE
语 句成功,UPDATE 触发 器才 会 被 触动 。
（ 2） 如果 语 句在表中 执 行 违 反 条 件 约 束或引起 错误,触发 器不 会触动 。
（ 3） 触发 器 视为单 一事 务 中的一部 份,因此可以由原 触发 器 复 原事 务,如果在事 务过 程中 侦测 到 严 重的 错误 （如用 户 中 断联 机）,则会 自 动复 原整 个事 务 。
（ 4） 一 个语 句只能 触动 一次 触发 器。
当触发 器 触动,若 产 生任何 结 果，就 会 如存 储过 程一 样,将结 果 传 回其 调 用的 应 用程序。一般 来说,INSERT,UPDATE 或 DELETE 的 语 句（ 触动触发 器的 语 句）不 会将结 果 传 回； 结 果通常由 SELECT 查询传 回。因此，
为 了避免 触发 器 传 回 结 果 给应 用程序,请 勿在 触发过 程定 义 中引入
SELECT 语 句或指派 变 量。如果希望 从触发 器中 传 回 结 果，在允 许 修改 触发 表的每 个应 用程序中都必 须 撰 写 特殊的程序，才能使 应 用程序收到 传 回的资 料 并进 行正确的 处 理。如果必 须 在 触发 器中指派 变 量，可在 触发 器的起始位置使用 SET NOCOUNT ON 语 句以防止 传 回任何 结 果列。
SET NOCOUNT 语 句 指定是否 传 回 查询 或受 语 句影 响 的列 数 目的信息（如影 响 23 个 列）。 SET NOCOUNT 的默 认值 是 设 在 OFF，也就是 说会传回受影 响 列的 讯 息。
该设 定 并 不影 响 SELECT 语 句 实际结 果的 传 回，只 传 回 计数 。
下面是 关 于 触发 器的一些其 它规 定：
创 建一 个触发 器,内 容主要包括 触发 器名 称,与触发 器 关联 的表，激活 触发 器的语 句和 条 件,触发 器 应 完成的操作等。
创 建 触发 器方法主要有：
1.使用 T-SQL语 句 创 建 触发 器 2．在企 业 管理器中 创 建 触发 器
1.使用 T-SQL语 句 创 建 触发 器创 建 触发 器可以使用 CREATE TRIGGER语 句，其 语 法格式如下：
CREATE TRIGGER 触发 器名
ON {表名 |视图 名 }
[ WITH ENCRYPTION ]
{ { FOR | AFTER | INSTEAD
OF}{[DELETE][,][INSERT][,][UPDATE]}
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE (列名 )[ { AND | OR } UPDATE (列名 ) ][,..n ]
|IF(COLUMNS_UPDATED(){位 运 算符 }位掩 码 ){比 较运 算符 }检验值
[,..n ]}]
sql_statement [,..n ]
}
10-2-2 创 建 触发 器
WITH ENCRYPTION：加密 选项,可防止 触发 器作 为 SQL Server复 制的一部分 发 布。
AFTER：表示在引起 触发 的 SQL语 句中所有的操作 (包括引用 级联 操作和 约 束 检查 等 )成功 执 行后，才激活本 触发 器的 执 行；如果 仅 指定 FOR,则 AFTER是默 认设 置，不能在 视图 上定 义 AFTER触发 器。 INSTEAD OF：指定 执 行本 触发 器而不是 执 行引起 触发 的 SQL
语 句，即 触发 器替代 触发语 句的操作；每 个 更新 语 句 (DELETE,INSERT,UPDATE)最多只能定 义 一 个 INSTEAD OF触发 器。
[DELETE][,][INSERT][,][UPDATE]：表示指定 执 行 哪 些更新 语 句 时将 激活 触发 器，
至少要指定一 个选项,若 选项 多于一 个,需用逗 号 分隔 这 些 选项 。
WITH APPEND：指定 应该 添加 现 有 类 型的其他 触发 器。
NOT FOR REPLICATION：在 复 制 进 程更改 触发 器所涉及的表 时,不 执 行 该触发 器。
IF UPDATE（列名）,测试 在指定的列上 进 行的 INSERT或 UPDATE操作，不能用于
DELETE操作,对 INSERT操作,测试将 返回 TRUE值,因 为 在指定列上 输 入了 显 式 值 或隐 性 (NULL)值 ；若要同 时对 多 个 列 进 行 测试,可使用 逻辑运 算符 连 接多 个 UPDATE(列名 )子句
IF （ COLUMNS_UPDATED()）,测试 是否 插 入或更新了提及的列，不能用于
DELETE操作。 测试 返回 值 是 varbinary位模式。位模式中,从 右到左第 1位表示表的第 1
列，第 2位表示表的第 2列 …… 。通 过 位比 较运 算就知道 哪 些列已被更新。 对 INSERT操作，
测试将对 所有列返回 TRUE值,因 为 在 这 些列上 输 入了 显 式 值 或 隐 性（ NULL） 值 。
正确 设 置位掩 码,检验值 以及使用比 较运 算,就能确切地知道 实际 更新或 插 入了 哪 些列 。
例如,假 设 表 TI由五 个 列 构 成 （ C1～ C5）,若要 检查 列 C2,C3或 C4是否有更新,可使用 这样 的子句 实现,IF(COLUMN_UPDATE() & 14)>0
参数说 明：
创 建一 个触发 器,当 用 户试图 向 cj表中添加或修改 数 据 时,该触发 器向客 户端 显 示一 条 消息。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'CJ_IU' AND type = ' TR')
DROP TRIGGER CJ_IU
GO
CREATE TRIGGER CJ_IU
ON cj
FOR INSERT,UPDATE
AS PRINT '插 入或更新了 CJ库 '
GO
其中,“插 入或更新了 CJ库,是 触发 器所 发 出的消息 内 容。 PRINT用于 将 用 户 定 义 的消息返回客 户 端。
2．在企 业 管理器中 创 建 触发 器
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹与 含 触发 器的表所 属 的 数 据 库,然后 单击 【 表 】 文件 夹 。
（ 3）在 详细 信息窗格中，右 击将 在其上 创 建 触发 器的表，指向 【 所有任 务 】 菜 单,然后单击 【 管理 触发 器 】 命令,弹 出所示的 对话 框。
（ 4） 在 【 名 称 】 中 选择 【 新建 】,在 【 文本 】 框中 输 入 创 建 触发 器的 语 句 。
（ 5） 若要 检查语 法,单击 【 检查语 法 】 命令 。
【 例 10-6】
1．使用企 业 管理器 查 看 触发 器信息
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹与 含 触发 器的表所 属 的 数 据 库,然后 单击 【 表 】 文件 夹 。
（ 3）在 详细 信息窗格中，右 击触发 器所在的表，指向 【 所有任 务 】 菜 单,然后 单击
【 管理 触发 器 】 命令。
2．使用企 业 管理器 查 看 触发 器的相 关 性
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2）展 开 【 数 据 库 】 文件 夹,展 开触发 器所 属 的 数 据 库,然后 单击 【 表 】 文件 夹 。
（ 3）在 详细 信息窗格中，右 击触发 器所 属 的表，指向 【 所有任 务 】 菜 单,然后 单击
【 显 示相 关 性 】 命令。
（ 4）在 【 对 象 】 中,单击 要 查 看其相 关 性的 触发 器。
3．使用系 统 存 储过 程 查 看 触发 器
（ 1） sp_helptrigger '表名 ',返回指定表中定 义 的 当 前 数 据 库 的 触发 器 类 型。
（ 2） sp_help '触发 器名 称 ',用于 查 看 触发 器的一般信息，如 触发 器的名 称,属 性、
类 型和 创 建 时间 。
（ 3） sp_helptext '触发 器名 称 ',用于 查 看 触发 器的正文信息。
（ 4） sp_depends '触发 器名 称 /表名 ',用于 查 看指定 触发 器所引用的表或者指定的表涉及到的所有 触发 器。
10-2-3 管理 触发 器创 建一 个 名 为 ‘信息管理 ’的 触发 器,当书 的 销 售 总 量达 到 10 时,就 产 生 错误,50010。然后 执 行 sp_helptrigger
列出表 sales 中 触发 器的相 关 信息。
USE pubs
CREATE TRIGGER 信息管理
ON sales
FOR INSERT,UPDATE
AS RAISERROR (50010,16,10)
EXEC sp_helptrigger sales
Go
下面是 结 果集：
trigger_name trigger_owner isupdate isdelete
isinsert
--------------------------------------------
--------------------------------------------
----------
信息管理 dbo 1 0 1
(1 row(s) affected)
【 例 10-7】
修改 触发 器定 义与 修改一 个 存 储过 程的操作相 类 似。 有三 种 方法,如下：
1 先做 删 除然后再重建
2 使用企 业 管理器
3 ALTER TRIGGER语 句一次性地完成修改操作
2．使用企 业 管理器修改 触发 器正文
（ 1） 展 开 服 务 器 组,然后展 开 服 务 器。
（ 2） 展 开 【 数 据 库 】 文件 夹与 含 触发 器的表所 属 的 数 据 库,然后 单击 【 表 】
文件 夹 。
（ 3） 在 详细 信息窗格中，右 击触发 器所在的表，指向 【 所有任 务 】 菜 单,然后 单击 【 管理 触发 器 】 命令。
（ 4） 在 【 名 称 】 框中 选择触发 器的名 称 。
（ 5） 按需要在 【 文本 】 字段中更改 触发 器的 语 句文本。
（ 6） 若要 检查触发 器的 语 法,单击 【 检查语 法 】 命令。
3．使用 ALTER TRIGGER命令修改 触发 器正文
ALTER TRIGGER语 句 与 CREATE TRIGGER语 句的 语 法基本上是一致的,两者只是 语 句的第一 个关键单词 不相同而已，因而 这 里就不再 赘 述了。
若要修改 触发 器的名 称,用系 统 提供的 sp_rename存 储过 程 进 行重命名操作。
使用 sp_rename命令修改 触发 器的名 称语 法形式如下：
sp_rename 更改前名字,更改后名字
10-2-4 修改 触发 器删 除一 个触发 器 时,该触发 器所 关联 的表和 数 据不 会 受到任何影 响,删 除触发 器所在的表 时,SQL Server将会 自 动删 除 与该 表相 关 的 触发 器。
1 在企 业 管理器中 删 除 触发 器 过 程
2 使用系 统 命令 DROP TRIGGER删 除指定的 触发 器
1 在企 业 管理器中 删 除 触发 器 过 程 为,
在企 业 管理器中，用右 键单击 要 删 除的 触发 器所在的表,从弹 出的快捷菜 单 中 选择 所有任 务 子菜 单 下的管理 触发 器 选 项,则会 出 现触发 器属 性 对话 框。在名 称选项 框中 选择 要 删 除的 触发 器,单击 【 删 除 】 按 钮,
即可 删 除 该触发 器。
2 使用系 统 命令 DROP TRIGGER删 除指定的 触发 器，其 语 法形式如下,
DROP TRIGGER {触发 器名 } [,...n ]
例 10-8】 删 除名 为 computer的 触发 器。
USE 信息管理
GO
DROP TRIGGER computer
10-2-5 删 除 触发 器
10-3-1 存 储过 程 应 用
10-3-2 触发 器的 应 用
10-3 存 储过 程 与触发 器的 应 用
1．何 时 使用存 储过 程可以出于任何使用 SQL 语 句的目的 来 使用存 储过 程，使用 SQL
Server 2000 创 建 应 用程序 时,Transact-SQL 编 程 语 言是 应用程序和 SQL Server 数 据 库 之 间 的主要 编 程接口。
使用 Transact-SQL 程序 时,可用 两种 方法存 储 和 执 行程序 。
(1)可以在本地存 储 Transact-SQL程序，在 应 用程序中向 SQL
Server 发 送命令
(2)也可以 将 Transact-SQL程序作 为 存 储过 程存 储 在 SQL
Server 中，在 应 用程序中 调 用存 储过 程。
2．使用 参数在 调 用存 储过 程 时,可以 传递参数给它 。要想在存 储过 程中指定输 入 参数,请 以 @ 前 缀为参数 名 称 的前置字，例如 @参数 _1，
一 个 存 储过 程可指定高 达 1024 个参数 。
10-3-1 存 储过 程 应 用建立 参数 @性 别 _1。 当执 行存 储过 程 时,输 入性 别,该 存 储过 程就 会 根据性 别显 示 学 生信息。
USE 信息管理
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
RETURN
在建立存 储过 程前，要先确定是否有重 复 的名 称 存在，或是先 删 除已 经 存在的名 称 后再重新命名。修改后的 Transact-SQL 语 句如下：
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据性 别显 示 学 生信息 ' AND type = 'P')
DROP PROCEDURE 根据性 别显 示 学 生信息
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
RETURN
执 行此存 储过 程 时,必 须 先提供 输 入 参数,否 则会显 示如下的 错误讯 息：服 务 器,消息
201,级别 16,状态 4,过 程 student_select_b，行 0过 程 'student_select_b' 需要 参数 '@para_性 别 '，但未提供 该参数 。 执 行以下 语 句 将 返回 学 生表中所有女同 学 的信息：
USE 信息管理
GO
根据性 别显 示 学 生信息 '女 '
GO
也可以 为参数设 定默 认值,它将 适用于 当调 用存 储过 程 时没 有 参数 的情 况 。
【 例 10-9】
将 存 储过 程的 参数 默 认值设为,男,，其 Transact-SQL
存 储过 程程序代 码变 更如下（只 变 更 CREATE PROCEDURE 行）：
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据性 别显 示 学 生信息 ' AND type = 'P')
DROP PROCEDURE 根据性 别显 示 学 生信息
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)='男 '
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
RETURN
如果在 执 行根据性 别显 示 学 生信息 时没 有提供 参数,存 储过 程 将 使用
“男,为 @性 别 _1的默 认值 。即使 预设参数 已 经 确定，仍然可以提供 输 入参数,此 参数会 覆 写 默 认值 。
如果要在存 储过 程中 传 回 值给调 用程序,请 在 参数 名 称 后使用
OUTPUT 关键词 。要 将值储 存在 变 量中以供 调 用存 储过 程的 应 用程序使用，也可以在 调 用 该 存 储过 程 时,使用 OUTPUT 关键词 。
【 例 10-10】
根据 学号 返回高等 数学 成 绩 中,@学号 _1为学号 的 输 入 参数,@成 绩 _1输出 参数将传 回成 绩,在 调 用存 储过 程 时声 明名 称为 @成 绩 的局部 变 量，用于 储 存 传 回 值 。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据 学号 返回高等 数学 成 绩 ' AND type = 'P')
DROP PROCEDURE根据 学号 返回高等 数学 成 绩
GO
CREATE PROC 根据 学号 返回高等 数学 成 绩 @学号 _1 char(6),@成 绩 _1 NUMERIC
OUTPUT
AS SELECT @成 绩 _1=成 绩 FROM cj WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号
_1
RETURN
必 须 首先 声 明 变 量，才可以在 调 用存 储过 程 时 使用 该变 量。如在下面的程序代 码 中我 们 先声 明 @成 绩变 量 并将 其 变 量 类 型 设为 numeric（ 它 必 须 符合 OUTPUT参数变 量 类 型），然后 执 行此存 储过 程：
DECLARE @成 绩 numeric
EXECUTE 根据 学号 返回高等 数学 成 绩 '040201',@成 绩 _1 = @成 绩 OUTPUT
PRINT CONVERT(varchar(6),@成 绩 )
GO
PRINT 语 句 为 @成 绩传 回 值 85。
请 注意我 们 用 CONVERT 语 句 将 @成 绩 由原 来 的 numeric 变 量 类 型,转换为 varchar数 据型 别,才可以 将该值当 成字符串、字符 变 量 类 型，或以不直接的方式 转换 成字符 来 打印
（ 这 些是 PRINT 语 句的打印要求）。 请 注意在存 储过 程和 调 用程序中 为 OUTPUT 使用不同名 称 的 变 量，以便于理解。
【 例 10-11】
在 执 行存 储过 程 时,也可以使用 变 量指定 输 入 值,让 存 储过 程接收 来 自 调 用程序的 数值,然后修改 该值 或利用 该值 以 执 行某种 作 业,继 而再 将 新的 值传 回 调 用程序。其操作方法 为 在 执 行存 储过 程前，先 为调 用程序中的某 变 量分配一 个值 （或 执 行 查询 以在 变 量中 插 入 值 ），再 将该变 量 传 送到存 储过 程中。
DECLARE 关键词 用于建立局部 变 量，在建立局部 变 量 时,要指定局部 变 量名 称 及 变 量 类 型，而名 称 必 须 以 @ 前 缀为 前置字。一但 变 量 声 明，其 值会 先被 设为 NULL。
局部 变 量可在批 处 理或存 储过 程中 声 明。存 储过 程中的 变 量通常用 来储 存 条 件 语 句 传 回的 值,或是 储 存存 储过 程 RETURN
语 句 传 回的 值 。 变 量也常被用 来当 作 计数 器。 变 量范 围从变 量的 声 明 处开 始,声 明 该变 量的存 储过 程 结 束后,该变 量就不再有效。
3．使用局部 变 量建立一 个 包含局部 变 量的存 储过 程。
该 存 储过 程使用 WHILE 循 环结构插 入五 个 列到表中。首先建立一 个 范例 数 据表，命名为测试 局部 变 量表，然后建立一 个 存 储过 程，命名 为插 入行。在程序中 将 使用 @循 环计数 和 @循 环变 量 这两个 局部 变 量，我 们会 一起 声 明 这两个变 量,并 且以逗 号将两变 量 区隔。使用 Transact-SQL程序代 码 建立表和存 储过 程,
USE 信息管理
GO
CREATE TABLE 测试 局部 变 量表
( 列 1 int,列 2 char(8) )
GO
CREATE PROCEDURE 插 入行 @初始 值 int
AS
DECLARE @循 环计数 int,@循 环变 量 int
SET @循 环变 量 = @初始 值 - 1
SET @循 环计数 = 0
WHILE ( @循 环计数 < 3)
BEGIN
INSERT INTO 测试 局部 变 量表 VALUES (@循 环变 量 + 1,'新增一行 ')
PRINT (@循 环变 量 )
SET @循 环变 量 = @循 环变 量 + 1
SET @循 环计数 = @循 环计数 + 1
END
GO
【 例 10-12】
现 在 执 行起始 值为 1 的存 储过 程，如下所示：
插 入行 1
GO
执 行后 会 打印三 个 @循 环变 量 值,0,1和 2。使用下面的 语 句 从测试 局部 变 量表中 选择 所有的列,select * from 测试 局部 变 量表在 执 行 SELECT 语 句后,会 出 现 如下的 输 出：
1 列 2
-----------------------------
1 新增一行
2 新增一行
3 新增一行
（所影 响 的行 数为 3 行）
当结 束存 储过 程后,@循 环计数 和 @循 环变 量就无法再被存取。先用下面的 T-SQL
语 句 尝试 打印,PRINT @循 环变 量,就 会 得到 这样 的 错误讯 息：
服 务 器,消息 137,级别 15,状态 2，行 1
必 须声 明 变 量 '@循 环变 量 '。
执 行批 处 理 时,此 变 量范 围 的 规则 也适用。一旦 声 明 关键词 GO（表示批 处 理结 束），批 处 理所 声 明的局部 变 量 将 不能再被使用，局部 变 量的范 围仅 限 该 批 处 理使用。以下的存 储过 程 调 用能 让 用 户 了解 这个规则,
DECLARE @成 绩 numeric
EXECUTE 根据 学号 返回高等 数学 成 绩 '040201',@成 绩 _1 = @成 绩 OUTPUT
PRINT CONVERT(varchar(6),@成 绩 ) /GO
PRINT CONVERT(varchar(6),@成 绩 ) /GO
第一 个 PRINT 语 句打印了批次操作中的局部 变 量 @成 绩 ；第二 个 PRINT 语 句企 图在批次操作 结 束后，打印此局部 变 量 。
（ 1）使用 RETURN回 传值如前文所述，在任何 时 刻使用 RETURN 关键词 都可以无 条 件退出存 储过 程以回到 调 用程序，也可用于退出批 处 理。存 储过 程 执 行到 RETURN语 句即停止 执 行,并 回到 调 用程序中的下一 个语句,RETURN 也可 传 回整 数值 。
【 例 10-13】 修改根据性 别显 示 学 生信息程序 来检查 是否提供了 输 入 值 。
说 明：如果 没 有,PRINT信息 并 回到 调 用程序。 将输 入 参数 默 认值 定 义为 NULL，然后 检查 程序中的 值 是否 为 NULL，如果是表示 没 有 输 入 值 。以下是 这个 程序的程序代 码,
USE 信息管理
GO
ALTER PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)=NULL
AS
IF @性 别 _1=NULL
BEGIN
PRINT '请输 入一 个 性 别 作 为 存 储过 程的 参数 '
RETURN
END
ELSE
BEGIN
SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
END
GO
执 行不 带参数 的存 储过 程：
exec 根据性 别显 示 学 生信息输 出 结 果如下：
请输 入一 个 性 别 作 为 存 储过 程的 参数
4,值 的回 传
【 例 10-14】 使用 RETURN 传 回 值 到 调 用程序中。
*传 回的 值 必 须 是一 个 整 数,常 数 或 变 量皆可。 变 量必 须 透 过声 明才可在 调 用程序中使用。 输 入 参数为 高等 数学 成 绩优 秀的 学号时,传 回 值 1，否 则传 回 99。
CREATE PROC 根据 学号检查 高等 数学 成 绩 是否 优 秀 @学号 _1 char(6)
AS
IF (SELECT 成 绩 FROM 成 绩 WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号
_1)<85
RETURN 0
ELSE
RETURN 1
GO
为 了 调 用存 储过 程 并 能使用 传 回 值,在 调 用程序中 声 明 变 量 并将该变 量 设为 存 储过 程的 传 回 值,
DECLARE @返回 值 int
EXECUTE @返回 值 = 根据 学号检查 高等 数学 成 绩 是否 优 秀 '040201'
IF (@返回 值 = 1) PRINT '恭喜 你,高等 数学 成 绩优 秀！ '
GO
由于指定的 产 品 单 价 为 $17，因此 传 回 值为 1,并 打印 Unit price is less than
$100。 当变 量用于保存 传 回 值时,记 住其 变 量 类 型必 须 被 声 明 为 整 数,这 是
RETURN 语 句的要求。
（ 2）使用 SELECT 回 传值在存 储过 程中可以使用 SELECT 语 句 传 回 数 据，可以 从 SELECT 查询 或 传 回变 量 值 中 传 回 结 果集。
【 例 10-15】 建立一 个 名 为 ‘根据 学号 返回 学号 和高 数 成 绩 ’存 储过 程,该 存 储过 程 会传 回 输入 参数 所指定的 产 品 单 价（透 过 ProductID）。
CREATE PROCEDURE 根据 学号 返回 学号 和高 数 成 绩 @学号 _1 char(6)
AS SELECT 学号,成 绩 FROM 成 绩 WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号 _1
GO 注：要使用 SELECT 语 句 传 回 变 量 值,请 在 语 句后面接着 输 入 变 量名 称 。
【 例 10-16】 在此范例中，重建 ‘根据 学号检查 高等 数学 成 绩 是否 优 秀 ’存 储过 程,传 回一 变 量 值,
并 指定 输 出 标题 。
ALTER PROC 根据 学号检查 高等 数学 成 绩 是否 优 秀 @学号 _1 char(6)
AS
DECLARE @变 量 _1 int
IF (SELECT 成 绩 FROM 成 绩 WHERE 课 程名 ='高等 数学 ' AND 学号 =@学号 _1)<85
SET @变 量 _1 = 0
ELSE
SET @变 量 _1 = 1
SELECT '优 秀否 ' = @变 量 _1
PRINT '这 里可以添加其 它 TSQL语 句 '
GO
执 行存 储过 程,根据 学号检查 高等 数学 成 绩 是否 优 秀 '040201'
结 果如右,优 秀否
-----------
1
（ 所影 响 的行 数为 1 行）
这 里可以添加其 它 Trans这 里可以添加其 它 Transact-SQL语 句,用于强 调 使用 SELECT 传 回 值与 使用 RETURN 传 回 值 之 间 的差 别 。 当调 用 RETURN 时,存 储过 程跟着 结 束； 当调 用
SELECT 时,存 储过 程 则 在 SELECT 传 回 结 果集后,继续执 行。在前面的范例中，如果 没 有指定 输 出 标题,只使用 SELECT @varl,输 出 结 果 将没 有 标题,所示如右,-----------
1
（所影 响 的行 数为 1 行）
1．何时使用触发器触发器和条件约束相同，可用来维持资料的完整性和商业规则，但是触发器不能取代条件约束。用户还可以利用触发器执行比 CHECK 条件约束更复杂的资料检测。由于触发器可以引用其它表中的行，因此才有可能执行复杂的资料检测；反之,CHECK 条件约束只限于在其所定义的表上执行。
用户还可以建立多重触发器，当资料修改时即触动所有触发器。（请记住，
如果在表或检视表中为一个事件定义多重触发器，每个触发器都必须有一个自己的名称）。或者用户可以建立单一触发器，在资料修改时即被触动。也就是每一次当被定义的事件发生，触发器就被触动一次。因此，若是在表上定义 INSERT,UPDATE 和 DELETE 的触发器，每次定义的事件产生时，触发器就会触动。
2,deleted和 inserted表在建立触发器时,SQL Server 会为触发器建立两个暂时表，用户可以参考这两个表，用 Transact-SQL撰写触发过程定义。这两个表固定储存在与触发器一起的内存中，每个触发器只能存取自己的暂时表，暂时表即为触发器所在表的一个副本。用户可以使用这两个表比较资料修改前后状态。这两个表虽被称为表，其实并不同于一般的数据库表，它们储存在内存中，而非在磁盘上。这两个表被命名为 deleted和 inserted。
10-3-2 触发 器的 应 用两个表的结构（相同的列及同一类型的资料）类似于定义触发器的表。
deleted表会储存因 DELETE 及 UPDATE 语句而受影响的行副本。当行因触发器被删除或更新时，被删除或更新的行会传送到 delete表；在触发器中即可使用 deleted表。 inserted表会储存被 INSERT 及 UPDATE 语句影响的行副本，在插入或更新事务时，新的行会同时被加至触发器表与
inserted表。由于执行 UPDATE 语句时，会被视为插入或删除事务，旧的行值会保留一份副本在 deleted表中，而新的行值的副本则保留在触发器表与 inserted表。
inserted和 deleted表中的值只限于在触发器中使用。一旦触发器完成就无法再使用。
3,DELETE 触发器利用 DELETE 语句可激活表的串联更新。
【 例 10-17】 建立一个触发器，当从班级表中删除一行时，在学生、成绩表中对应的行也会被删除。当从 titles表中删除列时，在信息管理数据库内其它的表（如学生表，成绩表），与该行相关的行都会被一并删除。我们会利用 deleted 表指出哪些列该从相关表中删除。
（当从触发器表中删除行时，在 deleted表中会有删除数据的副本，所以可以从 deleted表查询所有被触发器删除的行）。
USE 信息管理
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = '删 除班 级 ' AND
type = 'TR')
DROP TRIGGER 删 除班 级
GO
CREATE TRIGGER 删 除班 级
ON 班 级
FOR DELETE
AS
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始 '
DELETE 学 生
FROM 学 生,deleted
WHERE 学 生,班 级编号 = deleted.班 级编号
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束 '
SELECT * FROM deleted
使用以下的 DELETE 语 句 测试触发 器：
SELECT * FROM 班 级
SELECT 学号,姓名,班 级编号 FROM 学 生
DELETE 班 级 WHERE 班 级编号 ='00000311'
GO
假 设 用 户 已 删 除稍早提及的外部索引 键,一旦 执 行 这个 DELETE 语 句,触发 器便 会触动,用 户会 在 title表上看到 资 料修改事件影 响 列的 讯 息，接着是 从触发 器中三行
PRINT 语 句 说 明的 讯 息,并 列出每 个 表中受影 响 的列 数,输 出 结 果如下：
班 级编号 班 级 名 称
-----------------------------
00000311 会计学
00000402 计 算机
00000411 管理 学
（所影 响 的行 数为 3 行）
学号 姓名 班 级编号
---------------------------------
040201 刘华 德 00000402
040202 许 慧 00000402
031101 陈顺发 00000311
031102 黎和生 00000311
（所影 响 的行 数为 4 行）
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始
（所影 响 的行 数为 2 行）
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束班 级编号 班 级 名 称
---------------------------------
00000311 会计学
（所影 响 的行 数为 1 行）
如果前面做 过备份,下列 语 句用于恢 复 班 级 和 学 生 数 据 库
DELETE 班 级
INSERT INTO 班 级 SELECT * FROM bj
DELETE 学 生
INSERT INTO 学 生 SELECT * FROM student
GO
deleted表的另一 个 用途，是 将 表中被 删 除的列 储 存到 备份 表中，以供日后的 资料分析。例如，用 户 可以利用以下的 语 法,从 成 绩 表 将 被 删 除的列存入一 个 命名为 成 绩备份 的表中。
USE 信息管理
GO
CREATE TABLE 成 绩备份
( 学号 char(6) NOT NULL,课 程名 char(12) NOT NULL,课 程 号 char(6) NOT
NULL,
成 绩 numeric(18,1) NULL,补 考成 绩 numeric(18,1) NULL )
GO
CREATE TRIGGER 成 绩备份触发 器
ON 成 绩
FOR DELETE
AS
INSERT INTO 成 绩备份 SELECT * FROM deleted
GO
SELECT * FROM 成 绩备份
GO
4,INSERT 触发 器
【 例 10-18】 在此 将 通 过 建立一 个 INSERT 触发 器（ 当 INSERT 语 句 执 行 时,会让此 触发 器 触发 ） 来 表述 应 用。 当 我 们 在成 绩 表中 插 入新 值,触发 器 会显 示相 关 信息
（在 触发 器 内 引用 SELECT 语 句以 显 示 inserted表中所含的 资 料）。
USE 信息管理
GO
CREATE TRIGGER 成 绩插 入 触发 器
ON 成 绩
FOR INSERT
AS
SELECT * FROM inserted
PRINT '可以在 这 里 插 入其 它 T-SQL语 句，可以使用 inserted表 '
Go
当 下列 INSERT 语 句 执 行 时,会触发触发 器：
INSERT INTO 成 绩 VALUES(040201,'工 业会计 ',030107,80.0,NULL)
GO
用 户将 看到如下的 运 行 结 果（ 触发 的 显 示信息）：
学号 课 程名 课 程 号 成 绩 补 考成 绩
------------------------------------------------------------
-----------
40201 工 业会计 30107 80.0 NULL
（所影 响 的行 数为 1 行）
（所影 响 的行 数为 1 行）
可以在 这 里 插 入其 它 T-SQL语 句，可以使用 inserted表
5,UPDATE 触发 器
【 例 10-19】 在此,将 建立一 个 UPDATE 触发 器,当 更新成 绩 表中的成 绩 列 时,该触发 器 会检查 成 绩 提高是否超 过 10%。如果超 过 了 10%,将 以 ROLLBACK 语 句 来复 原 触发 器和 调 用 触发器的 语 句。如果在一 个较 大的事 务 中激活 触发 器，整 笔 事 务将 被 复 原。在 这个 范例中，我 们 利用
deleted和 inserted表 来测试 成 绩 的 变 化。
USE 信息管理
GO
ALTER TRIGGER 成 绩 更新 触发 器
ON 成 绩
FOR UPDATE
AS
DECLARE @更改前成 绩 numeric,@更改后成 绩 numeric
SELECT @更改前成 绩 = 成 绩 from deleted
PRINT '更改前成 绩 ='
PRINT CONVERT(varchar(6),@更改前成 绩 )
SELECT @更改后成 绩 = 成 绩 from inserted
PRINT '更改后成 绩 ='
PRINT CONVERT(varchar(6),@更改后成 绩 )
IF(@更改后成 绩 > (@更改前成 绩 * 1.10))
BEGIN
PRINT '成 绩 更改升幅太大，更改失 败 '
ROLLBACK
END
ELSE
PRINT '成 绩 更改成功 '
GO
执 行以下的 语 句：
UPDATE 成 绩 SET 成 绩 =成 绩 * 1.2 WHERE 学号 ='040201' AND 课 程 号
='030107'
GO
用 户会 看到以下的 结 果：
更改前成 绩 =
80
更改后成 绩 =
96
成 绩 更改升幅太大，更改失 败当 建立一 个 UPDATE 触发 器 时,可以指定 触发 器 仅 在特定的列更新 时,才 执 行 语 句。
例如，用 户 可以 设 定只有在成 绩 行被更新 时,才 检查该 行，确 认触发 器正确 触发 。 请 使用下面的 IF UPDATE 子句：
USE 信息管理
GO
ALTER TRIGGER 成 绩 更新 触发 器
ON 成 绩
FOR UPDATE
AS
IF UPDATE(成 绩 )
BEGIN
DECLARE @更改前成 绩 numeric,@更改后成 绩 numeric
SELECT @更改前成 绩 = 成 绩 from deleted
PRINT '更改前成 绩 ='
PRINT CONVERT(varchar(6),@更改前成 绩 )
SELECT @更改后成 绩 = 成 绩 from inserted
PRINT '更改后成 绩 ='
PRINT CONVERT(varchar(6),@更改后成 绩 )
IF(@更改后成 绩 > (@更改前成 绩 * 1.10))
BEGIN
PRINT '成 绩 更改升幅太大，更改失 败 '
ROLLBACK
END
ELSE
PRINT '成 绩 更改成功 '
END
GO
说 明：如果成 绩 表的更新不包括成 绩 行（ 仅 更新其 它 行）,触发 器 会 跳 过 AS后的第一个 BEGIN 和 END 之 间 的 语 句，即跳 过该触发 器。要 测试 以上的 触发 器是否如 预 期般跳 过执 行 BEGIN 和 END 之 间 的 语 句，用 户 可以用下面的 Transact-SQL 语 句，更新一 个 非成 绩 行的 资 料。
UPDATE 成 绩
SET 学号 ='040202'
WHERE 学号 ='040201' AND 课 程 号 ='030107'
GO
用 户 可以看到行的更新不在成 绩 列，外部 IF 条 件回 传 FALSE，因此 输 出 结 果不激活触发 器的打印 语 句。利用 这种 方式可以避免 SQL Server 处 理不必要的 语 句。
6,触发 器嵌套触发 器嵌套（ nested trigger） 是由其 它触发 器所激活的 触发 器，和 递归触发 器不同的地方在于,触发 器嵌套 并 不自行激活，而是 当 修改事件 产 生时,才由激活其 它 的 触发 器所激活。 SQL Server 2000 和 SQL Server
7.0 相同，第一 个触发 器可以激活第二 个触发 器，第二 个触发 器接着激活第三 个触发 器，依此 类 推，可以高 达 32 个层级 的 触发 器。在 SQL
Server 2000 中,触发 器嵌套的 预设状态设为启 用,nested trigger服 务器 设 定 参数 可用 来 控制 触发 器能否巢 状触发 。要停止 触发 器嵌套，可 执 行以下的指令：
sp_configure "nested triggers",0
go
将 nested triggers设 成 0时,则 不激活 触发 器嵌套； 将 nested triggers设成 1时,则 可激活 触发 器嵌套。
例 10-20】 建立一 个 基于 ‘删 除 ’触发 的嵌套 触发 器。
IF EXISTS (SELECT name FROM sysobjects WHERE name = '删 除班 级 ' AND type = 'TR')
DROP TRIGGER 删 除班 级
GO
CREATE TRIGGER 删 除班 级
ON 班 级
FOR DELETE
AS
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始 '
DELETE 学 生
FROM 学 生,deleted
WHERE 学 生,班 级编号 = deleted.班 级编号
PRINT '使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束 '
GO
CREATE TRIGGER 删除学生
ON 学生
FOR DELETE
AS
PRINT '使用存储过程从成绩库中删除相关行 —— 开始 '
DELETE 成绩
FROM 成绩,deleted
WHERE 成绩,学号 = deleted.学号
PRINT '使用存储过程从成绩库中删除相关行 —— 结束 '
SELECT * FROM deleted
GO
执行以下的语句：
DELETE 班级 WHERE 班级编号 ='00000402'
GO
用 户 可以看到如下的 结 果：
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 开 始使用存 储过 程 从 成 绩库 中 删 除相 关 行 —— 开 始
（所影 响 的行 数为 4 行）
使用存 储过 程 从 成 绩库 中 删 除相 关 行 —— 结 束
（所影 响 的行 数为 2 行）
（所影 响 的行 数为 1 行）
使用 DELETE触发 器 从学 生 库 中 删 除相 关 行 —— 结 束当 一 组触发 器嵌套的任一 层 失 败 事 务会 被取消，且所有 资 料修改 将 被 复 原至整 组触发 器 触发 前的 状态 。
存储过程 是 SQL 语句和可选控制流语句的预编译集合,SQL Server 会将该集合中的语句编译成一个执行单位。存储过程可包含程序流、逻辑以及对数据库的查询。可以接受输入参数、输出参数、返回单个或多个结果集以及返回值。触发器是一种特殊的存储过程，它与表紧密相连，基于表而建立，可视作表的一部分。
用户创建触发器后，就能控制与触发器关联的表。当表中的数据发生插入、删除或修改时，触发器自动运行。触发器是一种维持数据引用完整性的极好方法。
本章介绍了存储过程的基本概念和如何使用企业管理器与 Transact-SQL语言在查询分析器中创建、管理、执行和修改存储过程；介绍了触发器的基本概念和使用企业管理器与 Transact-SQL语言在查询分析器中创建、管理、执行和修改触发器。并通过一系列实例来叙述参数、变量,RETURN,SELECT在存储过程中的使用和
INSERT,UPDATE,DELETE和嵌套触发器的应用。
习题十
1,何谓存储过程？简述其作用及分类。
2,何谓触发器？简述其作用及分类。
3,试说明 deleted和 inserted表。
4.试根据学号创建一个存储过程，用于显示学生学号和姓名。
5,修改书中存储过程例题：“根据学号检查高等数学成绩是否优秀”，要求根据不同学号有三种返回：用户没有选高等数学这门课、没有达到优秀和达到优秀。
10-4 本章小 结
6,在,成绩,表上创建一个触发器,成绩插入,。当用户插入记录时触发。
7,在,成绩,表上创建一个触发器,成绩删除,。当用户删除记录时触发。
8,在,成绩,表上创建一个触发器,成绩更新,。当用户更新记录时触发。
9,简 述下列程序的 运 行 结 果。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '根据性 别显 示 学 生信息 ' AND type = 'P')
DROP PROCEDURE 根据性 别显 示 学 生信息
GO
CREATE PROC 根据性 别显 示 学 生信息 @性 别 _1 char(2)
AS SELECT 学号,姓名 FROM 学 生 WHERE 性 别 =@性 别 _1
10,简 述下列程序的 运 行 结 果。
USE 信息管理
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'CJ_IU' AND type = 'TR')
DROP TRIGGER CJ_IU
GO
CREATE TRIGGER CJ_IU
ON cj
FOR INSERT,UPDATE
AS PRINT '插 入或更新了 CJ库 '
GO
