系
统
第九章 存储过程与触发器
?
前 台
后台数据库
<调用 >
包含用户的操作界面。
例如：登陆界面、商品
入库界面、卖出商品界
面等
第九章 存储过程与触发器
?
前
台
后台数据库 执行 select … 命令，并将结果返回
<传递 >
商品查询界面
根据关键词写出 select … 语句
<返回 >
第九章 存储过程与触发器
?
前
台
后台数据库 自动执行已编写好的命令，将结果返回
<调用 >
商品查询界面
根据关键词写出调用语句
<返回 >
第九章 存储过程与触发器
?存储过程综述
?存储过程的基本操作
?创建和执行带有参数的存储过程
?存储过程重新编译
?系统存储过程和扩展存储过程
第九章 存储过程与触发器
?存储过程综述
1、存储过程概念
存储过程是一种数据库对象，是为了实现某个特定任务，将一组
预编译的 SQL语句以一个存储单元的形式存储在服务器上，供用户调
用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在
高速缓存中以便以后调用，这样可以提高代码的执行效率。
存储过程同其他编程语言中的过程相似，有如下 特点,
1）接受输入参数并以输出参数的形式将多个值返回至调用过程或
批处理。
2）包含执行数据库操作（包括调用其它过程）的编程语句。
3）向调用过程或批处理返回状态值，以表明成功或失败（以及失
败原因）。
第九章 存储过程与触发器
?存储过程综述
优点：
1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表
和视图的权限。
2、改良了执行性能：在第一次执行后，会在 SQL server的缓冲区中
创建查询树，以后执行无需编译。
3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执
行存储过程的执行命令和返回结果。
4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。
第九章 存储过程与触发器
?存储过程综述
2、存储过程类型
系统存储过程,系统存储过程存储在 master数据库中，并以 sp_为前缀，主
要用来从系统表中获取信息，为系统管理员管理 SQL Server提供帮助，为用
户查看数据库对象提供方便。
本地存储过程,本地存储过程是用户根据需要，在自己的普通数据库中创
建的存储过程。
临时存储过程,临时存储过程通常分为局部临时存储过程和全局临时存储
过程。创建局部临时存储过程时，要以,#”作为过程名称的第一个字符。创
建全局临时存储过程时，要以,##”作为过程名称的前两个字符。
远程存储过程,远程存储过程是 SQL Server 2000的一个传统功能，是指非
本地服务器上的存储过程。
扩展存储过程,扩展存储过程以 xp_为前缀，它是关系数据库引擎的开放
式数据服务层的一部分，其可以使用户在动态链接库 (DLL)文件所包含的函
数中实现逻辑，从而扩展了 Transact-SQL的功能，并且可以象调用 Transact-
SQL过程那样从 Transact-SQL语句调用这些函数。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
创建存储过程时，需要注意下列事项：
1、只能在当前数据库中创建存储过程。
2、数据库的所有者可以创建存储过程，也可以授权其他用户创建存
储过程。
3、存储过程是数据库对象，其名称必须遵守标识符命名规则。
4、不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单
个批处理中。
5、创建存储过程时，应指定所有输入参数和向调用过程或批处理返
回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处
理以表明成功或失败的状态值。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
使用 SQL语句创建不带参数的存储过程语法格式如下：
CREATE PROC [ EDURE ] procedure_name
[ WITH
{ RECOMPILE
| ENCRYPTION
} ]
AS sql_statement [,..n ]
procedure_name,新存储过程的名称。过程名必须符合标识符规则，且对于
数据库及其所有者必须唯一。
RECOMPILE, SQL 不会缓存该过程的计划，该过程将在运行时重新编译。
ENCRYPTION, SQL Server 加密 使用 ENCRYPTION 可防止将过程作为
SQL Server 复制的一部分发布。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
例、在 STUDENT数据库中，创建一个查询存储过程 ST_PROC_BJ，
该存储过程将返回计算机系的班级名称。其程序清单如下：
USE STUDENT
GO
CREATE PROC DBO.ST_PROC_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 = 班级,系部代码 and 系部,系部名称 =
'计算机系 '
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---执行
执行存储过程
对存储在服务器上的存储过程，可以使用 EXECUTE命令或其名称执行它，其
语法格式如下：
[ EXEC [ UTE ] ]
procedure_name [number ] }
如果存储过程是批处理中的第一条语句,EXECUTE命令可以省略，可以使
用存储过程的名字执行该存储过程。
例：在查询分析器中执行存储过程 ST_PROC_BJ，其代码清单如下：
USE STUDENT
EXECUTE ST_PROC_BJ
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
使用 SQL语句创建带参数的存储过程语法格式如下：
CREATE PROC [ EDURE ] procedure_name [ number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION
}
]
AS sql_statement [,..n ]
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
其中：
procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，
且对于数据库及其所有者必须唯一。
number：该参数是可选的整数，用来对同名的过程分组，以便用一条 DROP
PROCEDURE 语句即可将同组的过程一起删除。例如，名为 orders的应用程
序使用的过程可以命名为 orderproc1,orderproc2等。 DROP PROCEDURE
orderproc 语句将删除整个组。
parameter：存储过程中的输入和输出参数。
data_type：参数的数据类型。
Default：指参数的默认值，必须是常量或 NULL。如果定义了默认值，不必
指定该参数的值也可执行过程。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
其中：
OUTPUT：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用
OUTPUT参数可将信息返回给调用过程。
RECOMPILE：表明 SQL Server不保存存储过程的计划，该过程将在运行时
重新编译。
ENCRYPTION：表示 SQL Server加密 syscomments 表中包含 CREATE
PROCEDURE语句文本的条目。
sql_statement：指存储过程中的任意数目和类型的 Transact-SQL语句。
在存储过程中使用参数，可以扩展存储过程的功能。使用输入参
数，可以将外部信息传入到存储过程；使用输出参数，可以将存储过
程内的信息传出到外部 。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
例,在 STUDENT数据库中，建立一个名为 XIBU_INFOR的存储过程
，它带有一个参数，用于接受系部代码，显示该系部名称和系主任信
息。其程序清单如下：
USE STUDENT
GO
CREATE PROCEDURE XIBU_INFOR
@系部代码 CHAR (2)
AS
SELECT 系部名称,系主任
FROM 系部
WHERE 系部代码 =@系部代码
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---执行
执行待参数的存储过程
对存储在服务器上的存储过程，可以使用 EXECUTE命令或其名称执行它，其
语法格式如下：
[ EXEC [ UTE ] ]
{ [ @return_status = ] procedure_name [number ] }
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ]
] [,...n ]
其中：
如果存储过程是批处理中的第一条语句,EXECUTE命令可以省略，可以使
用存储过程的名字执行该存储过程。
@return_status：是一个可选的整型变量，用来保存存储过程的返回状态。
@parameter：存储过程的参数。
例如：执行存储过程：
EXEC XIBU_INFOR ?01?
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
例：
use pubs
go
create procedure title_sum @title varchar(40)=?%?,@sum money
output
as
select ?title name?=title
from titles
where title like @title
select @sum=sum(price)
from titles
where title like @title
go
第九章 存储过程与触发器
?存储过程的基本操作 ---执行
执行：
declare @totalcost money
execute title_sum ?the%?,@totalcost output
if @totalcost>=200
begin
select ?the total cost of these title is $?+
rtrim ( cast ( @totalcost as varchar(20) ) )
注意：
必须在 create procedure 和 execute 语句中都指定 output关键字。
如果 output关键字在存储过程执行过程中被忽略了，存储过程仍然执
行，但是他将会产生一个错误状态。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
使用企业管理器
例：在 STUDENT数据库中，创建一个名称为 ST_CHAXUN_01的存储过程，
该存储过程返回计算机系学生的姓名、性别和年龄信息。其操作步骤如下：
1）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库
节点。
2）单击相应的数据库（这里我们选择 student数据库），在其右边的详细窗格
中右击“存储过程”图标，在弹出的快捷菜单中选择“新建存储过程”命令。
3）执行“新建存储过程”命令，打开“存储过程属性”对话框。在其文本框
中首先输入所有者和存储过程名称，例如用所有者 DBO替换 OWNER，用过程
名 ST_CHAXUN_01替换 PROCEDURE NAME。
4）在文本框的第二行输入存储过程文本，根据题意输入如下语句：
SELECT 姓名,性别,YEAR(GETDATE())-YEAR(出生日期 ) AS 年龄
FROM 学生
WHERE 系部代码 ='01'
5）输入完成后，单击“语法检查”按钮，检查语法是否正确，然后单击“确
定”。
第九章 存储过程与触发器
?存储过程的基本操作 ---查看
1、使用企业管理器查看存储过程的步骤
为：
1）打开企业管理器，展开控制台目录，依
次展开服务器组、服务器、数据库节点。
2）单击相应的数据库（这里我们选择
student数据库），选择“存储过程”节点，
在右边的列表中显示出当前数据库中所有的
存储过程。
3）选择需要查看的存储过程右击，例如
,ST_PROC_BJ”，在弹出的快捷菜单中选
择“属性”命令，打开“存储过程属性”对
话框。
4）在“属性”对话框中，既可以查看过程
定义信息，又可以在文本框中对存储过程的
定义进行修改。修改后，可以单击“应用”
或“确定”按钮，保存修改。
第九章 存储过程与触发器
?存储过程的基本操作 ---查看
2、使用系统存储过程查看存储过程信息
在 SQL Server中，根据不同需要，可以使用 sp_helptext,sp_depends,sp_help
等系统存储过程来查看存储过程的不同信息。具体作用和语法如下：
sp_helptext查看存储过程的文本信息，其语法格式为,sp_helptext 存储过程名
sp_depends查看存储过程的相关性，其语法格式为,sp_depends 存储过程名
sp_help查看存储过程的一般信息，其语法格式为,sp_help 存储过程名
例：使用有关系统过程查看 STUDENT数据库中名为 ST_PROC_BJ的存储过程
的定义、相关性以及一般信息。其程序清单如下：
USE STUDENT
GO
EXEC SP_HELPTEXT ST_PROC_BJ
EXEC SP_DEPENDS ST_PROC_BJ
EXEC SP_HELP ST_PROC_BJ
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---修改
下面介绍 SQL语句，其语法格式为：
ALTER PROC [ EDURE ] procedure_name [number ]
[ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}
] [ FOR REPLICATION ]
AS sql_statement [,..n ]
例：修改存储过程 ST_PROC_BJ，使该存储过程返回经济管理系的班级名称。
其程序清单如下：
ALTER PROC DBO.ST_PROC_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 = 班级,系部代码 and 系部,系部名称 ='经济管理系
'
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---删除
1、使用企业管理器删除存储过程
在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选
择“删除”命令，将弹出“移除对象”对话框。在此对话框中，单击
“全部除去”按钮，删除该存储过程。
2、使用 DROP PROCEDURE语句删除存储过程
DROP PROCEDURE语句可以一次从当前数据库中将一个或多个
存储过程或过程组删除，其语法格式如下：
DROP PROCEDURE 存储过程名称 [,n]
例：删除存储过程 ST_CHAXUN_01，其程序清单如下：
USE STUDENT
GO
DROP PROCEDURE ST_CHAXUN_01
GO
第九章 存储过程与触发器
?存储过程的重新编译
存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，
当用户再次执行该存储过程时,SQL Server将其从缓存中调出执行。
有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向
表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结
构。这时，如果调用缓存中的存储过程，需要对它进行重新编译，使
存储过程能够得到优化。 SQL Server提供三种重新编译存储过程的方
法：
1、在建立存储过程时设定重新编译
创建存储过程时，在其定义中指定 WITH RECOMPILE选项，使 SQL
Server在每次执行存储过程时都要重新编译。
第九章 存储过程与触发器
?存储过程的重新编译
2、在执行存储过程时设定重编译
在执行存储过程时指定 WITH RECOMPILE选项，可强制对存储过程
进行重新编译。其语法格式如下：
EXECUTE procedure_name WITH RECOMPILE
3、通过使用系统存储过程设定重编译
系统存储过程 sp_recompile强制在下次运行存储过程时进行重新编译
。其语法格式为：
EXEC sp_recompile OBJECT
其中 OBJECT是当前数据库中的存储过程、触发器、表或视图的名称
。如果 object是存储过程或触发器的名称，那么该存储过程或触发器
将在下次运行时重新编译。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
在 SQL Server中有两类重要的存储过程：系统存储过程和扩展存
储过程。这些存储过程为用户管理数据库、获取系统信息、查看系统
对象提供了很大的帮助。
系统存储过程
在 SQL Server中存在两百多个系统存储过程，这些系统存储过程的使用
，使用户很容易的管理 SQL Server的数据库。在安装 SQL Server数据库系统
时，系统存储过程被系统安装在 master数据库中，并且初始状态只有系统管
理员拥有使用权。所有的系统存储过程名称都是以 sp_开头。
在使用以 sp_开头的系统存储过程时,SQL Server首先在当前数据库中寻
找，如果没有找到，则再到 master数据库中查找并执行。虽然存储在 master
数据库中，但是绝大部分系统存储过程可以在任何数据库中执行，而且在使
用时不用在名称前加数据库名。当系统存储过程的参数是保留字或对象名时
，在使用存储过程时候，作为参数的“对象名或保留字”必须用单引号括起
来。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
例：利用 sp_addgroup命令在当前数据库中建立一个角色 user_group：
USE master
GO
EXEC sp_addgroup user_group
例：利用 sp_addlogin命令建立一个登录用户名为 user01：
USE master
GO
EXEC sp_addlogin user01
运行后提示以创建。需要注意的是，在没有指定用户密码和默认数据库的时候，创建的用
户默认数据库是 master，默认的密码是 NULL。
例：利用 sp_addtype创建新的用户自定义数据类型 user_date，该类型为 datetime数据类型：
EXEC sp_addtype user_date, datetime
运行结果为类型已添加。
例：使用 sp_monitor显示 CPU,I/O的使用信息：
USE MASTER
EXEC sp_monitor
GO
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
扩展存储过程
扩展存储过程是允许用户使用一种编程语言（例如 C语言）创建的
应用程序，程序中使用 SQL Server开放数据服务的 API函数，它们直接
可以在 SQL Server地址空间中运行。用户可以象使用普通的存储过程一
样使用它，同样也可以将参数传给它并返回结果和状态值。扩展存储过
程编写好后，可以由系统管理员在 SQL Server中注册登记，然后将其执
行权限授予其他用户。扩展存储过程只能存储在 master数据库中。下面
通过几个例子，介绍扩展存储过程的创建和应用实例。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
扩展存储过程
例：使用 sp_addextendedproc存储过程将一个编写好的扩展存储过程 xp_userprint.dll注册
到 SQL Server中。
执行代码如下：
EXEC sp_addextendedproc xp_userprint,?xp_userprint.dll?
其中：
sp_addextendedproc：为系统存储过程。
xp_userprint：为扩展存储过程在 SQL Server中的注册名。
‘ xp_userprint.dll?：为用某种语言编写的扩展存储过程动态链接库。
例：使用扩展存储过程 xp_dirtree返回本地操作系统的系统目录’ c:\winnt?目录树。
执行代码如下：
EXEC xp_dirtree "c:\winnt"
GO
执行结果返回 c:\winnt目录树。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
扩展存储过程
例：利用扩展存储过程 xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返
回任何输出。
执行代码：
EXEC master xp_cmdshell,dir *.exe”
GO
执行结果返回系统目录下的文件内容文本信息。
例：利用扩展存储过程实现远程备份数据库。假设 Windows 2000 Sever服务器计算机名为
yang，本地域为 Domain域,SQL Server数据库名称为 yang，系统管理员账号为 sa，密码
为 888，需要备份的数据库为 student。实现此操作的代码如下：
EXEC xp_cmdshell,et share baktest=e:\baktest”
GO
master..xp_cmdshell,net use \\yang\baktest 888 /user:domain\sa”
GO
backup database student to disk=,\\yang\baktest\student.bak”
GO
EXEC xp_cmmdshell,net share baktest /delete”
GO
第九章 存储过程与触发器
?触发器综述
?触发器的基本操作
?创建触发器
?查看触发器
?修改触发器
?禁止和启用触发器
?删除触发器
?嵌套触发器
第九章 存储过程与触发器
?触发器综述
1、触发器概念
触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包
含复杂的 SQL语句。它们主要用于强制复杂的业务规则或要求。触发
器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保
留表之间已定义的关系 。 可以完成存储过程能完成的功能，但是它具
有自己显著的的特点：
1）它与表紧密相连，可以看作表定义的一部分；
2）它不能通过名称被直接调用，更不允许带参数，而是当用户对
表中的数据进行修改时，自动执行；
3）它可以用于 SQL Server约束、默认值和规则的完整性检查，实
施更为复杂的数据完整性约束。
第九章 存储过程与触发器
?触发器综述
2、触发器的优点
触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。
同其他约束相比，它主要有以下优点：
1）触发器自动执行
在对表的数据作了任何修改（比如手工输入或者应用程序采取的
操作）之后立即被激活。
2）触发器能够对数据库中的相关表实现级联更改
触发器是基于一个表创建的，但是可以针对多个表进行操作，实
现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品
表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，
在产品销售表的产品编号上自动插入编号值。
第九章 存储过程与触发器
?触发器综述
2、触发器的优点
3）触发器可以实现比 CHECK约束更为复杂的数据完整性约束
在数据库中为了实现数据完整性约束，可以使用 CHECK约束或触发
器。 CHECK约束不允许引用其它表中的列来完成检查工作，而触发
器可以引用其它表中的列。例如，在 STUDENT数据库中，向学生表
中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该
系。这只能通过触发器实现，而不能通过 CHECK约束完成。
4）触发器可以评估数据修改前后的表状态，并根据其差异采取对策
。
5）一个表中可以存在多个同类触发器（ INSERT,UPDATE或
DELETE），对于同一个修改语句可以有多个不同的对策以响应。
第九章 存储过程与触发器
?触发器综述
3、触发器的种类
SQL Server 2000按触发被被激活的时机可以分为两种类型：
AFTER 触发器和 INSTEAD OF触发器。
AFTER触发器 又称为后触发器，该类触发器是在引起触发器执行
的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或
语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上
，不能创建在视图上。可以为每个触发操作（ INSERT,UPDATE或
DELETE）创建多个 AFTER触发器。
INSTEAD OF触发器 又称为替代触发器，当引起触发器执行的修
改语句停止执行时，该类触发器代替触发操作执行。该类触发器既可
在表上定义，也可在视图上定义。对于每个触发操作（ INSERT、
UPDATE和 DELETE）只能定义一个 INSTEAD OF触发器。
第九章 存储过程与触发器
?触发器
在进行触发器的基本操作之前，介绍两张特殊的临时表，分别是
inserted表和 deleted表。这两张表都存在于高速缓存中。用户可以使
用这两张临时表来检测某些修改操作所产生的效果。例如，可以使
用 SELECT 语句来检查 INSERT和 UPDATE语句执行的插入操作是
否成功，触发器是否被这些语句触发等。但是不允许用户直接修改
inserted表和 deleted表中数据。
第九章 存储过程与触发器
?触发器
deleted表中存储着被 DELETE和 UPDATE语句影响的旧数据行。在
执行 DELETE和 UPDATE语句过程中，指定的数据行被用户从基本表
中删除，然后转移到了 delete表中。一般来说，在基本表中 delete表中
不会存在有相同的数据行。
inserted表中存储着被 INSERT 和 UPDATE语句影响的新的数据行
。当用户执行 INSERT 和 UPDATE语句时，新的数据行被添加到
insert表中，同时这些数据行的备份被复制到 inserted临时表中。
一个典型的 UPDATE事务实际上是由两个操作组成。首先，旧的数
据行从基本表中转移到 delete表中，前提是这个过程没有出错；紧接
着将新的数据行同时插入基本表和 insert表。
第九章 存储过程与触发器
?触发器的基本操作 __创建
在创建触发器前，必须注意以下几点：
1,CREATE TRIGGER 语句必须是批处理中的第一条语句。
2、只能在当前数据库中创建触发器，名称必须遵循标识符的命名规则。
3、表的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。
4、不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但
是不能引用系统表。
5、尽管 TRUNCATE TABLE 语句类似于没有 WHERE子句（用于删除行）
的 DELETE语句，但由于该语句不被记入日志，所以它不会引发 DELETE触
发器。
6,WRITETEXT语句不会引发 INSERT或 UPDATE触发器。
在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名
称,激发时机、激活触发器的修改语句（ INSERT,UPDATE或 DELETE）
。
第九章 存储过程与触发器
?触发器的基本操作 __创建
使用 SQL语句创建触发器语法格式为：
CREATE TRIGGER trigger_name ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [,] [DELETE][,][ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[,..n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [,..n ]
} ]
sql_statement [,..n ]
}
}
第九章 存储过程与触发器
?触发器的基本操作 __创建
其中：
trigger_name：触发器名称，其必须符合命名标识规则，并且在当前数据库
中唯一。
table | view：被定义触发器的表或视图。
WITH ENCRYPTION：对 syscomments 表中含 CREATE TRIGGER 语句
文本进行加密。
AFTER,默认的触发器类型，后触发器。此类型触发器不能在视图上定义
。
INSTEAD OF：表示建立替代类型的触发器。
NOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不
应执行该触发器。
IF UPDATE：指定对表中字段进行增加或修改内容时起作用，不能用于删
除操作。
sql_statement：定义触发器被触发后，将执行的 SQL语句。
第九章 存储过程与触发器
?触发器的基本操作 __创建
例：在 STUDENT数据库中，为产品表中产品编号建立一个名为
del_xiaoshao的 DELETE触发器，其作用是当删除产品表中的记录时
，同时删除产品销售表中与产品表相关的记录。其程序清单如下：
USE STUDENT
GO
CREATE TRIGGER del_xiaoshou
ON [dbo].[产品 ]
FOR DELETE
AS
DELETE 产品销售 WHERE 产品编号 IN (SELECT 产品编号
FROM DELETED)
GO
第九章 存储过程与触发器
?触发器
例,USE NorthWind
GO
CREATE TRIGGER TR_DEL
ON suppliers
FOR DELETE
AS IF @@rowcount=0
RETURN
DELETE PRODUCTS
FROM deleted d,supplliers s
WHERE d.supplierid=s.supplierid
IF @@ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
RETURN
GO
这个例子首先
检测数据行的数量,
如果数量为 0,则触
发器不执行任何操
作 。 如果删除数据
行的数量大于 0,则
表示删除操作成功,
触发器根据 deleted
表中的数据, 将
suppliers 表中的相
关数据也删除掉 。
第九章 存储过程与触发器
?触发器的基本操作 __创建
使用企业管理器创建触发器具体创建步
骤：
1）在企业管理器，单击相应的数据
库选择“表”节点，在其右边的详细窗
格中显示出当前数据库中所有的表。
2）选择需要创建触发器的表右击，
在弹出的快捷菜单中选择“所有任务”
，再选择“管理触发器”命令，打开“
触发器属性”对话框。
3）在其名称框中选择“新建”，在
文本框中输入触发器文本，然后单击“
检查语法”按钮检查语句是否正确。
4）单击“应用”按钮，完成触发器
的创建。
第九章 存储过程与触发器
?触发器的基本操作 __查看
1、使用系统存储过程查看触发器信息
使用 sp_help查看触发器的一般信息
使用 sp_helptext查看未加密的触发器的定义信息
使用 sp_depends查看触发器的依赖关系
专门查看触发器信息的系统存储过程 ——sp_helptrigger,语法如下：
sp_helptrigger 表名,[ INSERT ] [,] [DELETE][,][ UPDATE ]
例：查看产品表上存在的触发器的信息。其程序清单如下：
EXEC sp_helptrigger 产品
GO
2、使用企业管理器查看触发器信息
在企业管理器中，选择需要的数据库，然后选择表右击，在弹出的快捷菜
单中选择“所有任务”，再选择“管理触发器”命令，打开“触发器属性”
对话框，在该对话框中，可以从名称列表框中查看当前表的所有触发器名称
及其所有者，当需要查看触发器定义信息时，只要选择该触发器名称，其文
本内容就显示在“触发器属性”对话框的文本框中。
第九章 存储过程与触发器
?触发器的基本操作 __修改
1、使用系统存储过程修改触发器名称
对触发器进行重命名，可以使用系统存储过程 sp_rename来完成，
语法格式如下：
[EXECUTE] sp_rename 触发器原名,触发器新名
2、使用企业管理器修改触发器文本
使用企业管理器修改触发器的操作步骤与创建触发器相似，只不
过在打开“触发器属性”对话框后，从名称对话框中选择需要修改的
触发器，然后对文本中的 SQL语句进行修改即可。修改完后，使用“
检查语法”选项来验证语法是否正确。最后，单击“确定”按钮，完
成触发其的修改。
第九章 存储过程与触发器
?触发器的基本操作 __修改
3、使用 SQL语句修改触发器，语法格式如下：
ALTER TRIGGER trigger_name ON ( table | view )
[ WITH ENCRYPTION ]
{ { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [,] [ INSERT ] [,] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [,..n ]
}
{ IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ] [,..n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [,..n ]
}
sql_statement [,..n ]
} }
其中的参数与创建触发器语句中的参数相同。
第九章 存储过程与触发器
?触发器的基本操作 __修改
例：在 STUDENT数据库中，修改产品表的触发器 del_xiaoshao，使其在实现
级联删除时，显示一条语句“产品销售表中相应记录也被删除”。程序如下
：
USE STUDENT
GO
ALTER TRIGGER del_xiaoshou ON [dbo].[产品 ] FOR DELETE
AS
BEGIN
DELETE 产品销售 WHERE 产品编号 IN (SELECT 产品编号
FROM DELETED)
PRINT '产品销售表中相应记录也被删除 '
END
GO
例：删除产品表中产品编号为,0001”的记录，观察触发器 del_xiaoshao的作
用。其程序清单如下：
DELETE FROM 产品 WHERE 产品编号 =?0001?
GO
第九章 存储过程与触发器
?触发器的基本操作 __禁止和启动
针对某个表创建的触发器，可以根据需要，禁止或启用其执行。
禁止触发器或启用触发器执行只能在查询分析器中进行，其语法格式
为：
ALTER TABLE 表名
{ENABLE |DISABLE } 触发器名称
其中：
ENABLE：该选项为启用触发器
DISABLE：该选项为禁用触发器
第九章 存储过程与触发器
?触发器的基本操作 __删除
当不再需要某个触发器时，可以将其删除。只有触发器的所有者
才有权删除触发器。可以使用下面的方法将触发器删除：
1、使用企业管理删除触发器，其步骤为：
在企业管理器中，展开控制台目录，依次展开服务器组、服务器
、数据库节点，选择数据库，选择表右击，在弹出的快捷菜单中选择
“所有任务”，再选择“管理触发器”命令，打开“触发器属性”对
话框，从名称下拉列表框中选择要删除的触发器，单击“删除”按钮
，将触发器删除。
2、使用 SQL语句删除触发器
删除一个或多个触发器，可以使用 DROP TRIGGER语句，语法如下
,DROP TRIGGER { 触发器名称 } [,...n ]
3、删除表同时删除触发器
当某个表被删除后，该表上的所有触发器将同时被删除，但是删
除触发器不会对表中数据有影响。
第九章 存储过程与触发器
?嵌套触发器
在触发器中可以包含影响另外一个表的 INSERT,UPDATE或者
DELETE语句 。 这就是嵌套触发器, 具体来说就是, 如果表 A上的触
发器在执行时引发了表 B上的触发器, 而表 B上的触发器又激活了表 C
上的触发器, 表 C上的触发器又激活了表 D上的触发器 ……, 所有触
发器依次触发 。 这些触发器不会形成无限循环, SQL Server规定触发
器最多可嵌套至 32 层 。 如果允许使用嵌套触发器, 且链中的一个触
发器开始一个无限循环, 如果超出嵌套级, 触发器将被终止执行 。 正
确的使用嵌套触发器, 可以执行一些有用的日常工作, 但是嵌套触发
器比较复杂, 使用时要注意技巧, 比如, 由于触发器在事务中执行,
如果在一系列嵌套触发器的任意层中发生错误, 则整个事务都将取消,
且所有的数据修改都将回滚 。 一般情况下, 在触发器中包含 PRINT语
句, 用以确定错误发生的位置 。
第九章 存储过程与触发器
?嵌套触发器
设置是否嵌套的方法：
1,使用系统存储过程改变嵌套
使用 sp_config系统存储过程设置是否允许嵌套的语法格式如下：
EXEC sp_configure 'nested trigger',0|1
其中，如果设置为 0，则允许嵌套，设置为 1，禁止嵌套
2,使用企业管理器设置嵌套
1）在企业管理器中，展开控制台目录，展开服务器组，选择服务
器。
2）选择需要修改的服务器右击，在弹出的快捷菜单中选择“属性
”，打开,SQL Server属性配置”对话框。
3）在对话框中选择“服务器设置”标签卡，如图 9.2所示。在“服
务器行为”选项中通过勾选复选框来设置是否允许使用嵌套触发器。
第九章 存储过程与触发器
?触发器案例
例：在 STUDENT数据库中建立一个名为 insert_xibu的 INSERT触发器，存储
在专业表中。当用户向专业表中插入记录时，如果插入了在系部表中没有的
系部代码，则提示用户不能插入记录，否则提示记录插入成功。
CREATE TRIGGER insert_xibu ON [dbo].[专业 ]
FOR INSERT
AS
DECLARE @XIBU CHAR(2)
SELECT @XIBU=系部,系部代码
FROM 系部,inserted
WHERE 系部,系部代码 =inserted.系部代码
IF @XIBU<>''
PRINT('记录插入成功 ')
ELSE
BEGIN
PRINT ('系部代码不存在系部表中，不能插入记录，插入将终止！ ')
ROLLBACK TRANSACTION
END
GO
第九章 存储过程与触发器
?触发器案例
例：在 STUDENT数据库中建立一个名为 delete_zhye的 DELETE触发器，存
储在专业表中。当用户删除专业表中的记录时，如果班级表引用了此记录的
专业代码，则提示用户不能删除记录，否则提示记录已删除。
CREATE TRIGGER delete_zhye ON 专业
FOR DELETE
AS
IF(SELECT COUNT(*) FROM 班级 INNER JOIN DELETED
ON 班级,专业代码 =DELETED.专业代码 )>0
BEGIN
PRINT ('该专业被班级表所引用，你不可以删除此条记录，删除将终止 ')
ROLLBACK TRANSACTION
END
ELSE
PRINT '记录已删除 '
GO
第九章 存储过程与触发器
?触发器案例
例：在 STUDENT数据库中建立一个名为 update_zymc的 UPDATE触发器，存
储在专业表中。当用户更新专业表中的专业名称时，提示用户不能修改专业
名称。
USE STUDENT
GO
CREATE TRIGGER update_zymc ON [dbo].[专业 ]
FOR UPDATE
AS
IF UPDATE(专业名称 )
BEGIN
PRINT '不能修改系部名称 '
ROLLBACK TRANSACTION
END
GO
统
第九章 存储过程与触发器
?
前 台
后台数据库
<调用 >
包含用户的操作界面。
例如：登陆界面、商品
入库界面、卖出商品界
面等
第九章 存储过程与触发器
?
前
台
后台数据库 执行 select … 命令，并将结果返回
<传递 >
商品查询界面
根据关键词写出 select … 语句
<返回 >
第九章 存储过程与触发器
?
前
台
后台数据库 自动执行已编写好的命令，将结果返回
<调用 >
商品查询界面
根据关键词写出调用语句
<返回 >
第九章 存储过程与触发器
?存储过程综述
?存储过程的基本操作
?创建和执行带有参数的存储过程
?存储过程重新编译
?系统存储过程和扩展存储过程
第九章 存储过程与触发器
?存储过程综述
1、存储过程概念
存储过程是一种数据库对象，是为了实现某个特定任务，将一组
预编译的 SQL语句以一个存储单元的形式存储在服务器上，供用户调
用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在
高速缓存中以便以后调用，这样可以提高代码的执行效率。
存储过程同其他编程语言中的过程相似，有如下 特点,
1）接受输入参数并以输出参数的形式将多个值返回至调用过程或
批处理。
2）包含执行数据库操作（包括调用其它过程）的编程语句。
3）向调用过程或批处理返回状态值，以表明成功或失败（以及失
败原因）。
第九章 存储过程与触发器
?存储过程综述
优点：
1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表
和视图的权限。
2、改良了执行性能：在第一次执行后，会在 SQL server的缓冲区中
创建查询树，以后执行无需编译。
3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执
行存储过程的执行命令和返回结果。
4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。
第九章 存储过程与触发器
?存储过程综述
2、存储过程类型
系统存储过程,系统存储过程存储在 master数据库中，并以 sp_为前缀，主
要用来从系统表中获取信息，为系统管理员管理 SQL Server提供帮助，为用
户查看数据库对象提供方便。
本地存储过程,本地存储过程是用户根据需要，在自己的普通数据库中创
建的存储过程。
临时存储过程,临时存储过程通常分为局部临时存储过程和全局临时存储
过程。创建局部临时存储过程时，要以,#”作为过程名称的第一个字符。创
建全局临时存储过程时，要以,##”作为过程名称的前两个字符。
远程存储过程,远程存储过程是 SQL Server 2000的一个传统功能，是指非
本地服务器上的存储过程。
扩展存储过程,扩展存储过程以 xp_为前缀，它是关系数据库引擎的开放
式数据服务层的一部分，其可以使用户在动态链接库 (DLL)文件所包含的函
数中实现逻辑，从而扩展了 Transact-SQL的功能，并且可以象调用 Transact-
SQL过程那样从 Transact-SQL语句调用这些函数。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
创建存储过程时，需要注意下列事项：
1、只能在当前数据库中创建存储过程。
2、数据库的所有者可以创建存储过程，也可以授权其他用户创建存
储过程。
3、存储过程是数据库对象，其名称必须遵守标识符命名规则。
4、不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单
个批处理中。
5、创建存储过程时，应指定所有输入参数和向调用过程或批处理返
回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处
理以表明成功或失败的状态值。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
使用 SQL语句创建不带参数的存储过程语法格式如下：
CREATE PROC [ EDURE ] procedure_name
[ WITH
{ RECOMPILE
| ENCRYPTION
} ]
AS sql_statement [,..n ]
procedure_name,新存储过程的名称。过程名必须符合标识符规则，且对于
数据库及其所有者必须唯一。
RECOMPILE, SQL 不会缓存该过程的计划，该过程将在运行时重新编译。
ENCRYPTION, SQL Server 加密 使用 ENCRYPTION 可防止将过程作为
SQL Server 复制的一部分发布。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
例、在 STUDENT数据库中，创建一个查询存储过程 ST_PROC_BJ，
该存储过程将返回计算机系的班级名称。其程序清单如下：
USE STUDENT
GO
CREATE PROC DBO.ST_PROC_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 = 班级,系部代码 and 系部,系部名称 =
'计算机系 '
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---执行
执行存储过程
对存储在服务器上的存储过程，可以使用 EXECUTE命令或其名称执行它，其
语法格式如下：
[ EXEC [ UTE ] ]
procedure_name [number ] }
如果存储过程是批处理中的第一条语句,EXECUTE命令可以省略，可以使
用存储过程的名字执行该存储过程。
例：在查询分析器中执行存储过程 ST_PROC_BJ，其代码清单如下：
USE STUDENT
EXECUTE ST_PROC_BJ
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
使用 SQL语句创建带参数的存储过程语法格式如下：
CREATE PROC [ EDURE ] procedure_name [ number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION
}
]
AS sql_statement [,..n ]
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
其中：
procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，
且对于数据库及其所有者必须唯一。
number：该参数是可选的整数，用来对同名的过程分组，以便用一条 DROP
PROCEDURE 语句即可将同组的过程一起删除。例如，名为 orders的应用程
序使用的过程可以命名为 orderproc1,orderproc2等。 DROP PROCEDURE
orderproc 语句将删除整个组。
parameter：存储过程中的输入和输出参数。
data_type：参数的数据类型。
Default：指参数的默认值，必须是常量或 NULL。如果定义了默认值，不必
指定该参数的值也可执行过程。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
其中：
OUTPUT：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用
OUTPUT参数可将信息返回给调用过程。
RECOMPILE：表明 SQL Server不保存存储过程的计划，该过程将在运行时
重新编译。
ENCRYPTION：表示 SQL Server加密 syscomments 表中包含 CREATE
PROCEDURE语句文本的条目。
sql_statement：指存储过程中的任意数目和类型的 Transact-SQL语句。
在存储过程中使用参数，可以扩展存储过程的功能。使用输入参
数，可以将外部信息传入到存储过程；使用输出参数，可以将存储过
程内的信息传出到外部 。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
例,在 STUDENT数据库中，建立一个名为 XIBU_INFOR的存储过程
，它带有一个参数，用于接受系部代码，显示该系部名称和系主任信
息。其程序清单如下：
USE STUDENT
GO
CREATE PROCEDURE XIBU_INFOR
@系部代码 CHAR (2)
AS
SELECT 系部名称,系主任
FROM 系部
WHERE 系部代码 =@系部代码
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---执行
执行待参数的存储过程
对存储在服务器上的存储过程，可以使用 EXECUTE命令或其名称执行它，其
语法格式如下：
[ EXEC [ UTE ] ]
{ [ @return_status = ] procedure_name [number ] }
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ]
] [,...n ]
其中：
如果存储过程是批处理中的第一条语句,EXECUTE命令可以省略，可以使
用存储过程的名字执行该存储过程。
@return_status：是一个可选的整型变量，用来保存存储过程的返回状态。
@parameter：存储过程的参数。
例如：执行存储过程：
EXEC XIBU_INFOR ?01?
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
例：
use pubs
go
create procedure title_sum @title varchar(40)=?%?,@sum money
output
as
select ?title name?=title
from titles
where title like @title
select @sum=sum(price)
from titles
where title like @title
go
第九章 存储过程与触发器
?存储过程的基本操作 ---执行
执行：
declare @totalcost money
execute title_sum ?the%?,@totalcost output
if @totalcost>=200
begin
select ?the total cost of these title is $?+
rtrim ( cast ( @totalcost as varchar(20) ) )
注意：
必须在 create procedure 和 execute 语句中都指定 output关键字。
如果 output关键字在存储过程执行过程中被忽略了，存储过程仍然执
行，但是他将会产生一个错误状态。
第九章 存储过程与触发器
?存储过程的基本操作 ---创建
使用企业管理器
例：在 STUDENT数据库中，创建一个名称为 ST_CHAXUN_01的存储过程，
该存储过程返回计算机系学生的姓名、性别和年龄信息。其操作步骤如下：
1）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库
节点。
2）单击相应的数据库（这里我们选择 student数据库），在其右边的详细窗格
中右击“存储过程”图标，在弹出的快捷菜单中选择“新建存储过程”命令。
3）执行“新建存储过程”命令，打开“存储过程属性”对话框。在其文本框
中首先输入所有者和存储过程名称，例如用所有者 DBO替换 OWNER，用过程
名 ST_CHAXUN_01替换 PROCEDURE NAME。
4）在文本框的第二行输入存储过程文本，根据题意输入如下语句：
SELECT 姓名,性别,YEAR(GETDATE())-YEAR(出生日期 ) AS 年龄
FROM 学生
WHERE 系部代码 ='01'
5）输入完成后，单击“语法检查”按钮，检查语法是否正确，然后单击“确
定”。
第九章 存储过程与触发器
?存储过程的基本操作 ---查看
1、使用企业管理器查看存储过程的步骤
为：
1）打开企业管理器，展开控制台目录，依
次展开服务器组、服务器、数据库节点。
2）单击相应的数据库（这里我们选择
student数据库），选择“存储过程”节点，
在右边的列表中显示出当前数据库中所有的
存储过程。
3）选择需要查看的存储过程右击，例如
,ST_PROC_BJ”，在弹出的快捷菜单中选
择“属性”命令，打开“存储过程属性”对
话框。
4）在“属性”对话框中，既可以查看过程
定义信息，又可以在文本框中对存储过程的
定义进行修改。修改后，可以单击“应用”
或“确定”按钮，保存修改。
第九章 存储过程与触发器
?存储过程的基本操作 ---查看
2、使用系统存储过程查看存储过程信息
在 SQL Server中，根据不同需要，可以使用 sp_helptext,sp_depends,sp_help
等系统存储过程来查看存储过程的不同信息。具体作用和语法如下：
sp_helptext查看存储过程的文本信息，其语法格式为,sp_helptext 存储过程名
sp_depends查看存储过程的相关性，其语法格式为,sp_depends 存储过程名
sp_help查看存储过程的一般信息，其语法格式为,sp_help 存储过程名
例：使用有关系统过程查看 STUDENT数据库中名为 ST_PROC_BJ的存储过程
的定义、相关性以及一般信息。其程序清单如下：
USE STUDENT
GO
EXEC SP_HELPTEXT ST_PROC_BJ
EXEC SP_DEPENDS ST_PROC_BJ
EXEC SP_HELP ST_PROC_BJ
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---修改
下面介绍 SQL语句，其语法格式为：
ALTER PROC [ EDURE ] procedure_name [number ]
[ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}
] [ FOR REPLICATION ]
AS sql_statement [,..n ]
例：修改存储过程 ST_PROC_BJ，使该存储过程返回经济管理系的班级名称。
其程序清单如下：
ALTER PROC DBO.ST_PROC_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 = 班级,系部代码 and 系部,系部名称 ='经济管理系
'
GO
第九章 存储过程与触发器
?存储过程的基本操作 ---删除
1、使用企业管理器删除存储过程
在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选
择“删除”命令，将弹出“移除对象”对话框。在此对话框中，单击
“全部除去”按钮，删除该存储过程。
2、使用 DROP PROCEDURE语句删除存储过程
DROP PROCEDURE语句可以一次从当前数据库中将一个或多个
存储过程或过程组删除，其语法格式如下：
DROP PROCEDURE 存储过程名称 [,n]
例：删除存储过程 ST_CHAXUN_01，其程序清单如下：
USE STUDENT
GO
DROP PROCEDURE ST_CHAXUN_01
GO
第九章 存储过程与触发器
?存储过程的重新编译
存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，
当用户再次执行该存储过程时,SQL Server将其从缓存中调出执行。
有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向
表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结
构。这时，如果调用缓存中的存储过程，需要对它进行重新编译，使
存储过程能够得到优化。 SQL Server提供三种重新编译存储过程的方
法：
1、在建立存储过程时设定重新编译
创建存储过程时，在其定义中指定 WITH RECOMPILE选项，使 SQL
Server在每次执行存储过程时都要重新编译。
第九章 存储过程与触发器
?存储过程的重新编译
2、在执行存储过程时设定重编译
在执行存储过程时指定 WITH RECOMPILE选项，可强制对存储过程
进行重新编译。其语法格式如下：
EXECUTE procedure_name WITH RECOMPILE
3、通过使用系统存储过程设定重编译
系统存储过程 sp_recompile强制在下次运行存储过程时进行重新编译
。其语法格式为：
EXEC sp_recompile OBJECT
其中 OBJECT是当前数据库中的存储过程、触发器、表或视图的名称
。如果 object是存储过程或触发器的名称，那么该存储过程或触发器
将在下次运行时重新编译。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
在 SQL Server中有两类重要的存储过程：系统存储过程和扩展存
储过程。这些存储过程为用户管理数据库、获取系统信息、查看系统
对象提供了很大的帮助。
系统存储过程
在 SQL Server中存在两百多个系统存储过程，这些系统存储过程的使用
，使用户很容易的管理 SQL Server的数据库。在安装 SQL Server数据库系统
时，系统存储过程被系统安装在 master数据库中，并且初始状态只有系统管
理员拥有使用权。所有的系统存储过程名称都是以 sp_开头。
在使用以 sp_开头的系统存储过程时,SQL Server首先在当前数据库中寻
找，如果没有找到，则再到 master数据库中查找并执行。虽然存储在 master
数据库中，但是绝大部分系统存储过程可以在任何数据库中执行，而且在使
用时不用在名称前加数据库名。当系统存储过程的参数是保留字或对象名时
，在使用存储过程时候，作为参数的“对象名或保留字”必须用单引号括起
来。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
例：利用 sp_addgroup命令在当前数据库中建立一个角色 user_group：
USE master
GO
EXEC sp_addgroup user_group
例：利用 sp_addlogin命令建立一个登录用户名为 user01：
USE master
GO
EXEC sp_addlogin user01
运行后提示以创建。需要注意的是，在没有指定用户密码和默认数据库的时候，创建的用
户默认数据库是 master，默认的密码是 NULL。
例：利用 sp_addtype创建新的用户自定义数据类型 user_date，该类型为 datetime数据类型：
EXEC sp_addtype user_date, datetime
运行结果为类型已添加。
例：使用 sp_monitor显示 CPU,I/O的使用信息：
USE MASTER
EXEC sp_monitor
GO
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
扩展存储过程
扩展存储过程是允许用户使用一种编程语言（例如 C语言）创建的
应用程序，程序中使用 SQL Server开放数据服务的 API函数，它们直接
可以在 SQL Server地址空间中运行。用户可以象使用普通的存储过程一
样使用它，同样也可以将参数传给它并返回结果和状态值。扩展存储过
程编写好后，可以由系统管理员在 SQL Server中注册登记，然后将其执
行权限授予其他用户。扩展存储过程只能存储在 master数据库中。下面
通过几个例子，介绍扩展存储过程的创建和应用实例。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
扩展存储过程
例：使用 sp_addextendedproc存储过程将一个编写好的扩展存储过程 xp_userprint.dll注册
到 SQL Server中。
执行代码如下：
EXEC sp_addextendedproc xp_userprint,?xp_userprint.dll?
其中：
sp_addextendedproc：为系统存储过程。
xp_userprint：为扩展存储过程在 SQL Server中的注册名。
‘ xp_userprint.dll?：为用某种语言编写的扩展存储过程动态链接库。
例：使用扩展存储过程 xp_dirtree返回本地操作系统的系统目录’ c:\winnt?目录树。
执行代码如下：
EXEC xp_dirtree "c:\winnt"
GO
执行结果返回 c:\winnt目录树。
第九章 存储过程与触发器
?系统存储过程和扩展存储过程
扩展存储过程
例：利用扩展存储过程 xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返
回任何输出。
执行代码：
EXEC master xp_cmdshell,dir *.exe”
GO
执行结果返回系统目录下的文件内容文本信息。
例：利用扩展存储过程实现远程备份数据库。假设 Windows 2000 Sever服务器计算机名为
yang，本地域为 Domain域,SQL Server数据库名称为 yang，系统管理员账号为 sa，密码
为 888，需要备份的数据库为 student。实现此操作的代码如下：
EXEC xp_cmdshell,et share baktest=e:\baktest”
GO
master..xp_cmdshell,net use \\yang\baktest 888 /user:domain\sa”
GO
backup database student to disk=,\\yang\baktest\student.bak”
GO
EXEC xp_cmmdshell,net share baktest /delete”
GO
第九章 存储过程与触发器
?触发器综述
?触发器的基本操作
?创建触发器
?查看触发器
?修改触发器
?禁止和启用触发器
?删除触发器
?嵌套触发器
第九章 存储过程与触发器
?触发器综述
1、触发器概念
触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包
含复杂的 SQL语句。它们主要用于强制复杂的业务规则或要求。触发
器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保
留表之间已定义的关系 。 可以完成存储过程能完成的功能，但是它具
有自己显著的的特点：
1）它与表紧密相连，可以看作表定义的一部分；
2）它不能通过名称被直接调用，更不允许带参数，而是当用户对
表中的数据进行修改时，自动执行；
3）它可以用于 SQL Server约束、默认值和规则的完整性检查，实
施更为复杂的数据完整性约束。
第九章 存储过程与触发器
?触发器综述
2、触发器的优点
触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。
同其他约束相比，它主要有以下优点：
1）触发器自动执行
在对表的数据作了任何修改（比如手工输入或者应用程序采取的
操作）之后立即被激活。
2）触发器能够对数据库中的相关表实现级联更改
触发器是基于一个表创建的，但是可以针对多个表进行操作，实
现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品
表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，
在产品销售表的产品编号上自动插入编号值。
第九章 存储过程与触发器
?触发器综述
2、触发器的优点
3）触发器可以实现比 CHECK约束更为复杂的数据完整性约束
在数据库中为了实现数据完整性约束，可以使用 CHECK约束或触发
器。 CHECK约束不允许引用其它表中的列来完成检查工作，而触发
器可以引用其它表中的列。例如，在 STUDENT数据库中，向学生表
中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该
系。这只能通过触发器实现，而不能通过 CHECK约束完成。
4）触发器可以评估数据修改前后的表状态，并根据其差异采取对策
。
5）一个表中可以存在多个同类触发器（ INSERT,UPDATE或
DELETE），对于同一个修改语句可以有多个不同的对策以响应。
第九章 存储过程与触发器
?触发器综述
3、触发器的种类
SQL Server 2000按触发被被激活的时机可以分为两种类型：
AFTER 触发器和 INSTEAD OF触发器。
AFTER触发器 又称为后触发器，该类触发器是在引起触发器执行
的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或
语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上
，不能创建在视图上。可以为每个触发操作（ INSERT,UPDATE或
DELETE）创建多个 AFTER触发器。
INSTEAD OF触发器 又称为替代触发器，当引起触发器执行的修
改语句停止执行时，该类触发器代替触发操作执行。该类触发器既可
在表上定义，也可在视图上定义。对于每个触发操作（ INSERT、
UPDATE和 DELETE）只能定义一个 INSTEAD OF触发器。
第九章 存储过程与触发器
?触发器
在进行触发器的基本操作之前，介绍两张特殊的临时表，分别是
inserted表和 deleted表。这两张表都存在于高速缓存中。用户可以使
用这两张临时表来检测某些修改操作所产生的效果。例如，可以使
用 SELECT 语句来检查 INSERT和 UPDATE语句执行的插入操作是
否成功，触发器是否被这些语句触发等。但是不允许用户直接修改
inserted表和 deleted表中数据。
第九章 存储过程与触发器
?触发器
deleted表中存储着被 DELETE和 UPDATE语句影响的旧数据行。在
执行 DELETE和 UPDATE语句过程中，指定的数据行被用户从基本表
中删除，然后转移到了 delete表中。一般来说，在基本表中 delete表中
不会存在有相同的数据行。
inserted表中存储着被 INSERT 和 UPDATE语句影响的新的数据行
。当用户执行 INSERT 和 UPDATE语句时，新的数据行被添加到
insert表中，同时这些数据行的备份被复制到 inserted临时表中。
一个典型的 UPDATE事务实际上是由两个操作组成。首先，旧的数
据行从基本表中转移到 delete表中，前提是这个过程没有出错；紧接
着将新的数据行同时插入基本表和 insert表。
第九章 存储过程与触发器
?触发器的基本操作 __创建
在创建触发器前，必须注意以下几点：
1,CREATE TRIGGER 语句必须是批处理中的第一条语句。
2、只能在当前数据库中创建触发器，名称必须遵循标识符的命名规则。
3、表的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。
4、不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但
是不能引用系统表。
5、尽管 TRUNCATE TABLE 语句类似于没有 WHERE子句（用于删除行）
的 DELETE语句，但由于该语句不被记入日志，所以它不会引发 DELETE触
发器。
6,WRITETEXT语句不会引发 INSERT或 UPDATE触发器。
在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名
称,激发时机、激活触发器的修改语句（ INSERT,UPDATE或 DELETE）
。
第九章 存储过程与触发器
?触发器的基本操作 __创建
使用 SQL语句创建触发器语法格式为：
CREATE TRIGGER trigger_name ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [,] [DELETE][,][ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[,..n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [,..n ]
} ]
sql_statement [,..n ]
}
}
第九章 存储过程与触发器
?触发器的基本操作 __创建
其中：
trigger_name：触发器名称，其必须符合命名标识规则，并且在当前数据库
中唯一。
table | view：被定义触发器的表或视图。
WITH ENCRYPTION：对 syscomments 表中含 CREATE TRIGGER 语句
文本进行加密。
AFTER,默认的触发器类型，后触发器。此类型触发器不能在视图上定义
。
INSTEAD OF：表示建立替代类型的触发器。
NOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不
应执行该触发器。
IF UPDATE：指定对表中字段进行增加或修改内容时起作用，不能用于删
除操作。
sql_statement：定义触发器被触发后，将执行的 SQL语句。
第九章 存储过程与触发器
?触发器的基本操作 __创建
例：在 STUDENT数据库中，为产品表中产品编号建立一个名为
del_xiaoshao的 DELETE触发器，其作用是当删除产品表中的记录时
，同时删除产品销售表中与产品表相关的记录。其程序清单如下：
USE STUDENT
GO
CREATE TRIGGER del_xiaoshou
ON [dbo].[产品 ]
FOR DELETE
AS
DELETE 产品销售 WHERE 产品编号 IN (SELECT 产品编号
FROM DELETED)
GO
第九章 存储过程与触发器
?触发器
例,USE NorthWind
GO
CREATE TRIGGER TR_DEL
ON suppliers
FOR DELETE
AS IF @@rowcount=0
RETURN
DELETE PRODUCTS
FROM deleted d,supplliers s
WHERE d.supplierid=s.supplierid
IF @@ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
RETURN
GO
这个例子首先
检测数据行的数量,
如果数量为 0,则触
发器不执行任何操
作 。 如果删除数据
行的数量大于 0,则
表示删除操作成功,
触发器根据 deleted
表中的数据, 将
suppliers 表中的相
关数据也删除掉 。
第九章 存储过程与触发器
?触发器的基本操作 __创建
使用企业管理器创建触发器具体创建步
骤：
1）在企业管理器，单击相应的数据
库选择“表”节点，在其右边的详细窗
格中显示出当前数据库中所有的表。
2）选择需要创建触发器的表右击，
在弹出的快捷菜单中选择“所有任务”
，再选择“管理触发器”命令，打开“
触发器属性”对话框。
3）在其名称框中选择“新建”，在
文本框中输入触发器文本，然后单击“
检查语法”按钮检查语句是否正确。
4）单击“应用”按钮，完成触发器
的创建。
第九章 存储过程与触发器
?触发器的基本操作 __查看
1、使用系统存储过程查看触发器信息
使用 sp_help查看触发器的一般信息
使用 sp_helptext查看未加密的触发器的定义信息
使用 sp_depends查看触发器的依赖关系
专门查看触发器信息的系统存储过程 ——sp_helptrigger,语法如下：
sp_helptrigger 表名,[ INSERT ] [,] [DELETE][,][ UPDATE ]
例：查看产品表上存在的触发器的信息。其程序清单如下：
EXEC sp_helptrigger 产品
GO
2、使用企业管理器查看触发器信息
在企业管理器中，选择需要的数据库，然后选择表右击，在弹出的快捷菜
单中选择“所有任务”，再选择“管理触发器”命令，打开“触发器属性”
对话框，在该对话框中，可以从名称列表框中查看当前表的所有触发器名称
及其所有者，当需要查看触发器定义信息时，只要选择该触发器名称，其文
本内容就显示在“触发器属性”对话框的文本框中。
第九章 存储过程与触发器
?触发器的基本操作 __修改
1、使用系统存储过程修改触发器名称
对触发器进行重命名，可以使用系统存储过程 sp_rename来完成，
语法格式如下：
[EXECUTE] sp_rename 触发器原名,触发器新名
2、使用企业管理器修改触发器文本
使用企业管理器修改触发器的操作步骤与创建触发器相似，只不
过在打开“触发器属性”对话框后，从名称对话框中选择需要修改的
触发器，然后对文本中的 SQL语句进行修改即可。修改完后，使用“
检查语法”选项来验证语法是否正确。最后，单击“确定”按钮，完
成触发其的修改。
第九章 存储过程与触发器
?触发器的基本操作 __修改
3、使用 SQL语句修改触发器，语法格式如下：
ALTER TRIGGER trigger_name ON ( table | view )
[ WITH ENCRYPTION ]
{ { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [,] [ INSERT ] [,] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [,..n ]
}
{ IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ] [,..n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [,..n ]
}
sql_statement [,..n ]
} }
其中的参数与创建触发器语句中的参数相同。
第九章 存储过程与触发器
?触发器的基本操作 __修改
例：在 STUDENT数据库中，修改产品表的触发器 del_xiaoshao，使其在实现
级联删除时，显示一条语句“产品销售表中相应记录也被删除”。程序如下
：
USE STUDENT
GO
ALTER TRIGGER del_xiaoshou ON [dbo].[产品 ] FOR DELETE
AS
BEGIN
DELETE 产品销售 WHERE 产品编号 IN (SELECT 产品编号
FROM DELETED)
PRINT '产品销售表中相应记录也被删除 '
END
GO
例：删除产品表中产品编号为,0001”的记录，观察触发器 del_xiaoshao的作
用。其程序清单如下：
DELETE FROM 产品 WHERE 产品编号 =?0001?
GO
第九章 存储过程与触发器
?触发器的基本操作 __禁止和启动
针对某个表创建的触发器，可以根据需要，禁止或启用其执行。
禁止触发器或启用触发器执行只能在查询分析器中进行，其语法格式
为：
ALTER TABLE 表名
{ENABLE |DISABLE } 触发器名称
其中：
ENABLE：该选项为启用触发器
DISABLE：该选项为禁用触发器
第九章 存储过程与触发器
?触发器的基本操作 __删除
当不再需要某个触发器时，可以将其删除。只有触发器的所有者
才有权删除触发器。可以使用下面的方法将触发器删除：
1、使用企业管理删除触发器，其步骤为：
在企业管理器中，展开控制台目录，依次展开服务器组、服务器
、数据库节点，选择数据库，选择表右击，在弹出的快捷菜单中选择
“所有任务”，再选择“管理触发器”命令，打开“触发器属性”对
话框，从名称下拉列表框中选择要删除的触发器，单击“删除”按钮
，将触发器删除。
2、使用 SQL语句删除触发器
删除一个或多个触发器，可以使用 DROP TRIGGER语句，语法如下
,DROP TRIGGER { 触发器名称 } [,...n ]
3、删除表同时删除触发器
当某个表被删除后，该表上的所有触发器将同时被删除，但是删
除触发器不会对表中数据有影响。
第九章 存储过程与触发器
?嵌套触发器
在触发器中可以包含影响另外一个表的 INSERT,UPDATE或者
DELETE语句 。 这就是嵌套触发器, 具体来说就是, 如果表 A上的触
发器在执行时引发了表 B上的触发器, 而表 B上的触发器又激活了表 C
上的触发器, 表 C上的触发器又激活了表 D上的触发器 ……, 所有触
发器依次触发 。 这些触发器不会形成无限循环, SQL Server规定触发
器最多可嵌套至 32 层 。 如果允许使用嵌套触发器, 且链中的一个触
发器开始一个无限循环, 如果超出嵌套级, 触发器将被终止执行 。 正
确的使用嵌套触发器, 可以执行一些有用的日常工作, 但是嵌套触发
器比较复杂, 使用时要注意技巧, 比如, 由于触发器在事务中执行,
如果在一系列嵌套触发器的任意层中发生错误, 则整个事务都将取消,
且所有的数据修改都将回滚 。 一般情况下, 在触发器中包含 PRINT语
句, 用以确定错误发生的位置 。
第九章 存储过程与触发器
?嵌套触发器
设置是否嵌套的方法：
1,使用系统存储过程改变嵌套
使用 sp_config系统存储过程设置是否允许嵌套的语法格式如下：
EXEC sp_configure 'nested trigger',0|1
其中，如果设置为 0，则允许嵌套，设置为 1，禁止嵌套
2,使用企业管理器设置嵌套
1）在企业管理器中，展开控制台目录，展开服务器组，选择服务
器。
2）选择需要修改的服务器右击，在弹出的快捷菜单中选择“属性
”，打开,SQL Server属性配置”对话框。
3）在对话框中选择“服务器设置”标签卡，如图 9.2所示。在“服
务器行为”选项中通过勾选复选框来设置是否允许使用嵌套触发器。
第九章 存储过程与触发器
?触发器案例
例：在 STUDENT数据库中建立一个名为 insert_xibu的 INSERT触发器，存储
在专业表中。当用户向专业表中插入记录时，如果插入了在系部表中没有的
系部代码，则提示用户不能插入记录，否则提示记录插入成功。
CREATE TRIGGER insert_xibu ON [dbo].[专业 ]
FOR INSERT
AS
DECLARE @XIBU CHAR(2)
SELECT @XIBU=系部,系部代码
FROM 系部,inserted
WHERE 系部,系部代码 =inserted.系部代码
IF @XIBU<>''
PRINT('记录插入成功 ')
ELSE
BEGIN
PRINT ('系部代码不存在系部表中，不能插入记录，插入将终止！ ')
ROLLBACK TRANSACTION
END
GO
第九章 存储过程与触发器
?触发器案例
例：在 STUDENT数据库中建立一个名为 delete_zhye的 DELETE触发器，存
储在专业表中。当用户删除专业表中的记录时，如果班级表引用了此记录的
专业代码，则提示用户不能删除记录，否则提示记录已删除。
CREATE TRIGGER delete_zhye ON 专业
FOR DELETE
AS
IF(SELECT COUNT(*) FROM 班级 INNER JOIN DELETED
ON 班级,专业代码 =DELETED.专业代码 )>0
BEGIN
PRINT ('该专业被班级表所引用，你不可以删除此条记录，删除将终止 ')
ROLLBACK TRANSACTION
END
ELSE
PRINT '记录已删除 '
GO
第九章 存储过程与触发器
?触发器案例
例：在 STUDENT数据库中建立一个名为 update_zymc的 UPDATE触发器，存
储在专业表中。当用户更新专业表中的专业名称时，提示用户不能修改专业
名称。
USE STUDENT
GO
CREATE TRIGGER update_zymc ON [dbo].[专业 ]
FOR UPDATE
AS
IF UPDATE(专业名称 )
BEGIN
PRINT '不能修改系部名称 '
ROLLBACK TRANSACTION
END
GO
