主要内容
第第第第第第第第1111111122222222章章章章章章章章
存存储储过过程程和和触触发发器器
返回总目录返回总目录返回总目录返回总目录
↵ 存储过程概述
↵ 创建存储过程
↵ 触发器的概念和作用
↵ 创建触发器
↵ 触发器的应用
↵ 触发器的高级应用
↵ 查看 修改 删除触发器
在大型数据库系统中 存储过程和触发器具有很重要的作用 无
论是存储过程还是触发器 都是 SQL 语句和流程控制语句的集合 就
本质而言 触发器也是一种存储过程 存储过程在运算时生成执行方
式 所以 以后对其再运行时其执行速度很快 SQL Server 2000 不仅
提供了用户自定义存储过程的功能 而且也提供了许多可作为工具使
用的系统存储过程
1122..11 存储过程概述
12.1.1 存储过程的概念
存储过程 Stored Procedure 是一组为了完成特定功能的 SQL 语句集 经编译后存
储在数据库中 用户通过指定存储过程的名字并给出参数 如果该存储过程带有参数 来
执行它
在 SQL Server 的系列版本中存储过程分为两类 系统提供的存储过程和用户自定义
存储过程 系统过程主要存储在 master 数据库中并以 sp_为前缀 并且系统存储过程主
要是从系统表中获取信息 从而为系统管理员管理 SQL Server 提供支持 通过系统存储
过程 MS SQL Server 中的许多管理性或信息性的活动 如了解数据库对象 数据库信息
都可以被顺利有效地完成 尽管这些系统存储过程被放在 master 数据库中 但是仍可以
在其它数据库中对其进行调用 在调用时不必在存储过程名前加上数据库名 而且当创建
一个新数据库时 一些系统存储过程会在新数据库中被自动创建 用户自定义存储过程是
由用户创建并能完成某一特定功能 如查询用户所需数据信息 的存储过程 在本章中所
涉及到的存储过程主要是指用户自定义存储过程
12.1.2 存储过程的优点
当利用 MS SQL Server 创建一个应用程序时 Transaction-SQL 是一种主要的编程语
言 若运用 Transaction-SQL 来进行编程 有两种方法 其一是 在本地存储 Transaction-
SQL 程序 并创建应用程序向 SQL Server 发送命令来对结果进行处理 其二是 可以把
部分用 Transaction-SQL 编写的程序作为存储过程存储在 SQL Server 中 并创建应用程序
来调用存储过程 对数据结果进行处理 存储过程能够通过接收参数向调用者返回结果集
结果集的格式由调用者确定 返回状态值给调用者 指明调用是成功或是失败 包括针对
数据库的操作语句 并且可以在一个存储过程中调用另一存储过程
我们通常更偏爱于使用第二种方法 即在 SQL Server 中使用存储过程而不是在客户
计算机上调用 Transaction-SQL 编写的一段程序 原因在于存储过程具有以下优点
1 存储过程允许标准组件式编程
存储过程在被创建以后 可以在程序中被多次调用 而不必重新编写该存储过程的 SQL
语句 而且数据库专业人员可随时对存储过程进行修改 但对应用程序源代码毫无影响 因
为应用程序源代码只包含存储过程的调用语句 从而极大地提高了程序的可移植性
2 存储过程能够实现较快的执行速度
如果某一操作包含大量的 Transaction-SQL 代码或分别被多次执行 那么存储过程要
比批处理的执行速度快很多 因为存储过程是预编译的 在首次运行一个存储过程时 查
询优化器对其进行
优化 并给出最终被存在系统表中的执行
而批处理的 Tran-
saction-SQL 语句在每次运行时都要进行编译和优化 因此速度相对要慢一些
3 存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作 如查询 修改 如果这一操作所涉及到的
Transaction-SQL 语句被组织成一存储过程 那么当在客户计算机上调用该存储过程时
网络中传送的只是该调用语句 否则将是多条 SQL 语句 从而大大增加了网络流量 降
低网络负载
4 存储过程可被作为一种安全机制来充分利用
系统管理员通过对执行某一存储过程的权限进行限制 从而能够实现对相应的数据访
问权限的限制 避免非授权用户对数据的访问 保证数据的安全 我们将在 14 章 SQL
Server 的用户和安全性管理 中对存储过程的这一应用作更为清晰的介绍������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
存储过程虽然既有参数又有返回值 但是它与函数不同 存储过程的返回值只是指明执行������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������是否成功 并且它不能像函数那样被直接调用 也就是在调用存储过程时 在存储过程名������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������字前一定要有 EXEC保留字 如何执行存储过程见本章下一节
1122..22 创建存储过程
在 MS SQL Server 2000 中 创建一个存储过程有两种方法 一种是使用 Transaction-SQL
命令 Create Procedure 另一种是使用图形化管理工具 Enterprise Manager 用 Transaction-
SQL 创建存储过程是一种较为快速的方法 但对于初学者 使用 Enterprise Manager 更易
理解 更为简单
当创建存储过程时 需要确定存储过程的三个组成部分
所有的输入参数以及传给调用者的输出参数
被执行的针对数据库的操作语句 包括调用其它存储过程的语句
返回给调用者的状态值 以指明调用是成功还是失败
12.2.1 使用 Enterprise Manager创建存储过程
按照下述步骤用 Enterprise Manager 创建一个存储过程
1 启动 Enterprise Manager 登录到要使用的服务器
2 选择要创建存储过程的数据库 在左窗格中单击 Stored Procedure文件夹 此
时在右窗格中显示该数据库的所有存储过程 如图 12-1所示
3 右击 Stored Procedure文件夹 在弹出菜单中选择 New Stored Procedure, 此
时打开创建存储过程对话框 如图 12-2所示
图12-1 EM中显示的存储过程的详细信息
图12-2 创建存储过程对话框
4 输入存储过程正文
5 单击 Check Syntax 检查语法是否正确
6 单击 OK 保存
7 在右窗格中 右击该存储过程 在弹出菜单中选择 All task, 选择 Manage
Permissions 设置权限 如图 12-3所示
图12-3 EM中显示的设置存储过程权限的对话框
12.2.2 用 CREATE PROCEDURE命令创建存储过程
通过运用 Create Procedure 命令能够创建存储过程 在创建存储过程之前 应该考虑
到以下几个方面
在一个批处理中 Create Procedure语句不能与其它 SQL语句合并在一起
数据库所有者具有默认的创建存储过程的权限 它可把该权限传递给其它的用
户
存储过程作为数据库对象其命名必须符合命名规则
只能在当前数据库中创建属于当前数据库的存储过程
用 Create Procedure 创建存储过程的语法规则如下
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
各参数的含义如下
procedure_name
是要创建的存储过程的名字 它后面跟一个可选项 number 它是一个整数 用来区
别一组同名的存储过程 存储过程的命名必须符合命名规则 在一个数据库中或对其所有
者而言 存储过程的名字必须惟一
@parameter
是存储过程的参数 在 Create Procedure 语句中 可以声明一个或多个参数 当调用
该存储过程时 用户必须给出所有的参数值 除非定义了参数的缺省值 若参数的形式以
@parameter=value 出现 则参数的次序可以不同 否则用户给出的参数值必须与参数列表
中参数的顺序保持一致 若某一参数以@parameter=value 形式给出 那么其它参数也必须
以该形式给出 一个存储过程至多有 1024 个参数
Data_type
是参数的数据类型 在存储过程中 所有的数据类型包括 text 和 image 都可被用作参
数 但是 游标 cursor 数据类型只能被用作 OUTPUT 参数 当定义游标数据类型时 也
必须对 VARING 和 OUTPUT 关键字进行定义 对可能是游标型数据类型的 OUTPUT 参
数而言 参数的最大数目没有限制
VARYING
指定由 OUTPUT 参数支持的结果集 仅应用于游标型参数
Default
是指参数的缺省值 如果定义了缺省值 那么即使不给出参数值 则该存储过程仍能
被调用 缺省值必须是常数 或者是空值
OUTPUT
表明该参数是一个返回参数 用 OUTPUT 参数可以向调用者返回信息 Text 类型参
数不能用作 OUTPUT 参数
RECOMPILE
指明 SQL Server 并不保存该存储过程的执行计划 该存储过程每执行一次都又要重
新编译
ENCRYPTION
表明 SQL Server 加密了 syscomments 表 该表的 text 字段是包含有 Create procedure
语句的存储过程文本 使用该关键字无法通过查看 syscomments 表来查看存储过程内容
FOR REPLICATION
选项指明了为复制创建的存储过程不能在订购服务器上执行 只有在创建过滤存储过
程时 仅当进行数据复制时过滤存储过程才被执行 才使用该选项 FOR REPLICATION
与 WITH RECOMPILE 选项是互不兼容的
AS
指明该存储过程将要执行的动作
Sql_statement
是任何数量和类型的包含在存储过程中的 SQL 语句
另外应该指出 一个存储过程的最大尺寸为 128M 用户定义的存储过程必须创建在
当前数据库中
下面将给出几个例子 用来详细介绍如何创建包含有各种保留字的存储过程
例 12-1 该存储过程返回所有作者 以及他们的文章和出版者
use pubs
if exists select name from sysobjects
where name=’author_infor’ and type=’p’
drop procedure author_infor
go
create procedure author_infor as
select au_lname, au_fname, title, pub_name
from authors a inner join titleauthor ta
on a.au_id=ta.au_id inner join titles t
on t.title_id=ta.title_id inner join publishers p
on t.pub_id=p.pub_id
go
例 12-2 在该存储过程中使用了参数
use pubs
if exists select name from sysobjects
where name=’author_infor and type=’p’
drop procedure author_infor
go
use pubs
go
create procedure author_infor
@lastname varchar 40 , @firstname varchar 20
as
select au_lname,au_fname,title, pub_name
from authors a inner join titleauthor ta
on a.au_id=ta.au_id inner join ttitles t
on t.title_id=ta.title_id inner join publishers p
on t.pub_id=p.pub_id
where au_fname=@firstname and au_lname=@lastname
go
例 12-3 在该存储过程中使用了 OUTPUT 保留字
首先创建存储过程
use pubs
go
if exists select name from sysobjects
where name = 'titles_sum' and type = 'p'
drop procedure titles_sum
go
use pubs
go
create procedure salequa @stor_id char 4 ,@sum smallint output
as
select ord_num, ord_date, payterms, title_id, qty
from sales
where stor_id = @stor_id
select @sum = sum qty
from sales
where stor_id = @stor_id
go
然后在 Query Analyzer 中调用例 12-3 的存储过程
declare @totalqua smallint
execute salequa '7131',@totalqua output
if @totalqua<=50
select '销售信息'='销售等级为 3 销售量为'+rtrim cast @totalqua as varchar 20
if @totalqua>50 and @totalqua<=100
select '销售信息'='销售等级为 2 销售量为'+rtrim cast @totalqua as varchar 20
if @totalqua>100
select '销售信息'='销售等级为 1 销售量为'+rtrim cast @totalqua as varchar 20
运行结果为
ord_num ord_date payterms title_id qty
-------------------- --------------------------- ------------ -------- ------
N914008 1994-09-14 00:00:00.000 Net 30 PS2091 20
N914014 1994-09-14 00:00:00.000 Net 30 MC3021 25
P3087a 1993-05-29 00:00:00.000 Net 60 PS1372 20
P3087a 1993-05-29 00:00:00.000 Net 60 PS2106 25
P3087a 1993-05-29 00:00:00.000 Net 60 PS3333 15
P3087a 1993-05-29 00:00:00.000 Net 60 PS7777 25
6 row s affected
销售信息
-----------------------------------------
销售等级为 1 销售量为 130
1 row s affected
1122..33 管理存储过程
12.3.1 查看存储过程
存储过程被创建以后 它的名字存储在系统表 sysobjects 中 它的源代码存放在系统
表 syscomments 中 可以通过 MS SQL Server 提供的系统存储过程来查看关于用户创建的
存储过程信息
1 通过 Enterprise Manager管理工具同样可以查看存储过程的源代码
其操作如下
1 启动 Enterprise Manager 登录到要使用的服务器
2 选择要创建存储过程的数据库 在左窗格中单击 Stored Procedure文件夹 此
时在右窗格中显示该数据库的所有存储过程
3 在右窗格中 右击要查看源代码的存储过程 在弹出的菜单中选择 Properties
选项 此时便可看到存储过程的源代码 如图 12-4所示
图12-4 查看存储过程源代码对话框
2 使用 sp_helptext 存储过程查看存储过程的源代码
其语法格式如下
sp_helptext 存储过程名称存储过程名称存储过程名称存储过程名称
例如要查看数据库 pubs 是否是存储过程 reptq1 的源代码 则执行 sp_helptext reptq1
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
! 如果在创建存储过程时使用了 WITH ENCRYPTION 选项 那么无论是使用 Enterprise������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������Manager还是系统存储过程 sp_helptext都无法查看到存储过程的源代码
12.3.2 重新命名存储过程
修改存储过程的名字使用系统存储过程 sp_rename 其命令格式为
sp_rename 原存储过程名原存储过程名原存储过程名原存储过程名, 新存储过程名新存储过程名新存储过程名新存储过程名
例 12-4 将存储过程 reptq1 修改为 newproc 其语句为
sp_rename reptq1, newproc
另外 通过 Enterprise Manager 也可修改存储过程的名字 其操作过程与 WINDOWS
下修改文件名字的操作类似 即首先选中需修改名字的存储过程 然后右击鼠标 在弹出
菜单中选取 rename 选项 最后输入新存储过程的名字
12.3.3 删除存储过程
删除存储过程使用 drop 命令 drop 命令可将一个或多个存储过程或者存储过程组从
当前数据库中删除 其语法规则为
DROP PROCEDURE {procedure}} [,…n]
例 12-5 如将存储过程 reptq1 从数据库中删除 则执行
drop procedure reptq1
go
12.3.4 执行存储过程
执行已创建的存储过程使用 EXECUTE 命令 其语法如下
[EXECUTE]
{[@return_statur=]
{procedure_name[;number] | @procedure_name_var}
[[@parameter=] {value | @variable [OUTPUT] | [DEFAULT] [,…n]
[WITH RECOMPILE]
各参数的含义如下
@return_status
是可选的整型变量 用来存储存储过程向调用者返回的值
@procedure_name_var
是一变量名 用来代表存储过程的名字
其它参数据和保留字的含义与 CREATE PROCEDURE 中介绍的一样
例 12-6 该存储过程被用来将两个字符串连接成一个字符串 并将结果返回
创建存储过程
create procedure strconnect @str1 varchar 20 , @str2 varchar 20 , @connect varchar 40 output
as
select @connect=@str1 + @str2
如果我们提供三个字符串来执行这一存储过程 我们将看不到字符串相加的结果 虽
然 Select 语句用来对 result 变量赋值 但 result 结果并没有显示 再执行以下语句
declare @result varchar 40
execute strconnect 'I am', ' John', ' string'
select 'The result'=@result
则其运行结果为
The result
----------------------------------------
NULL
1 row s affected
若增加 OUTPUT 保留字到 EXECUTE 语句中便可显示返回参数 result 的值 OUTPUT
要求参数值被作为一个变量传送 而不是作为一个常量 下面的例子说明@result 变量来
存放由存储过程 strconnect 通过@connect 返回给调用者的结果值 从而使 SQL Server 能
够显示出存储过程的返回值
例 12-7
declare @result varchar 40
execute strconnect 'I am', 'John ', @result output
select 'The result'=@result
运行结果为
The result
-----------------------------------
I am John
1 row s affected
12.3.5 修改存储过程
修改以前用 CREATE PROCEDURE 命令创建的存储过程 并且不改变权限的授予情
况以及不影响任何其它的独立的存储过程或触发器常使用 ALTER PROCEDURE 命令 其
语法规则是
ALTER PROC[EDURE] procedure_name [;number]
[ {@parameter data_type } [VARYING] [= default] [OUTPUT]] [,...n]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION}]
[FOR REPLICATION]
AS
sql_statement [...n]
其中各参数和保留字的具体含义请参看 CREATE PROCEDURE 命令
下面将举一个例子 使读者对该命令有更为深刻的理解
例 12-8
use pubs
go
if exists select name from sysobjects where name = 'oakland_authors' and type = 'p'
drop procedure oakland_authors
go
/* 创建一个存储过程 该存储过程获取所有居住在加里福尼亚 奥克兰城的作者的信息 */
use pubs
go
create procedure oakland_authors
as
select au_fname, au_lname, address, city, zip
from pubs..authors
where city = 'oakland'
and state = 'ca'
order by au_lname, au_fname
go
/* 查看该存储过程的源代码 */
select o.id, c.text
from sysobjects o inner join syscomments c on o.id = c.id
where o.type = 'p' and o.name = 'oakland_authors'
/* 将执行该存储过程的权限授予 public 角色 */ 关于角色在 17 章介绍
grant execute on oakland_authors to public
go
/* 下面对该存储过程进行修改 使其能够显示出所有居住在加里福尼亚的作者 而不考虑其它地
区居住的作者 */
alter procedure oakland_authors
with encryption
as
select au_fname, au_lname, address, city, zip
from pubs..authors
where state = 'ca'
order by au_lname, au_fname
go
/* 查看该存储过程的源代码 */
select o.id, c.text
from sysobjects o inner join syscomments c on o.id = c.id
where o.type = 'p' and o.name = 'oakland_authors'
go
将该段代码输入到 SQL Server Query Analyzer 窗口中 运行后结果为
id text
--------------------------------------------------------------
1557580587 create procedure oakland_authors
as
select au_fname, au_lname, address, city, zip
from pubs..authors
where city = 'oakland'
and state = 'ca'
order by au_lname, au_fname
1 row s affected
--------------------------------------------------------------------------------------------------------
id text
1557580587 ???芹??????负?彣??????????蟸?? 泐摍?賯?鎨???????懡 ??跍旟?迨 輵?椿蚿?閗椌
洿?謋鈲?酄衎战胰嵨?襩??噍???秘???糡?媁???? ????妓??识墥 ? ??? 牙阾喯隶堠?潪?????????国卍
嫟??T 沁??????癦? ??傜貒???芦?簳?????窳遳?????诵? 宅?殴?鞧櫰??? ?漨?窦筘???飔??苜址?蛌??
????技???裗唘??紃��铀????楢? 凂??? ???澵???噉骆盲 ??喺尔??沷鸱??蘩??婨辷?卓??秥蛹??? ??
圱??
1 row s affected
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
由于在 ALERT PROCEDURE 中使用了 WITH ENCTYPTION 保留字 所以在查看修改后������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
的存储过程源代码时看到是一些乱码
1122..44 系统存储过程
系统存储过程就是系统创建的存储过程 目的在于能够方便地从系统表中查询信息或
完成与更新数据库表相关的管理任务或其它的系统管理任务 系统过程以 sp_ 为开头
在 Master 数据库中创建并保存在该数据库中 为数据库管理者所有 一些系统过程只能
由系统管理员使用 而有些系统过程通过授权可以被其它用户所使用
系统存储过程主要包括以下几类 这里主要给出每类系统过程中经常使用的系统过
程
目录存储过程
sp_column_privileges sp_special_columns
sp_columns sp_sproc_columns
sp_databases sp_statistics
sp_fkeys sp_stored_procedures
sp_pkeys sp_table_privileges
sp_Server_info sp_tables
复制类存储过程
sp_addarticle sp_adddistpublisher
sp_adddistributiondb sp_adddistributor
sp_addpublication sp_help_agent_profile
sp_addpublication_snapshot sp_help_publication_access
sp_addpublisher70 sp_helparticle
sp_addpullsubscription sp_addpullsubscription_agent
sp_helpdistpublisher sp_addsubscriber sp_addsubscription
sp_helpdistributiondb sp_addsubscriber_schedule
sp_helpdistributor sp_helppublication
sp_helppullsubscription sp_dropsubscriber
sp_helpreplicationdboption sp_changedistpublisher
sp_helpsubscription sp_changedistributiondb
sp_changedistributor_password sp_link_publication
sp_refreshsubscriptions sp_droparticle
sp_dropdistpublisher sp_dropdistributiondb
sp_dropdistributor sp_droppublication
sp_droppullsubscription
类存储过程
sp_addalias sp_droprole
sp_addapprole sp_droprolemember
sp_addgroup sp_dropServer
sp_addlinkedsrvlogin sp_dropsrvrolemember
sp_addlogin sp_dropuser
sp_addremotelogin sp_grantdbaccess
sp_addrole sp_grantlogin
sp_addrolemember sp_helpdbfixedrole
sp_addServer sp_helpgroup
sp_addsrvrolemember sp_helplinkedsrvlogin
sp_adduser sp_helplogins
sp_approlepassword sp_helpntgroup
sp_change_users_login sp_helpremotelogin
sp_changedbowner sp_helprole
sp_changegroup sp_helprolemember
sp_changeobjectowner sp_dbfixedrolepermission
sp_helpsrvrole sp_defaultdb
sp_helpsrvrolemember sp_dropremotelogin
sp_defaultlanguage sp_helpuser
sp_denylogin sp_password
sp_dropalias sp_remoteoption
sp_dropgroup sp_revokelogin
sp_droplinkedsrvlogin sp_droplogin
分布式查询存储过程
sp_addlinkedServer sp_indexes
sp_addlinkedsrvlogin sp_linkedServers
sp_catalogs sp_primarykeys
sp_foreignkeys
在以后的章节中 我们会使用或讲解这些存储过程
1122..55 触发器概述
在上面几节我们介绍了一般意义的存储过程 即用户自定义的存储过程和系统存储过
程 本节将介绍一种特殊的存储过程 即触发器 在余下各节中我们将对触发器的概念
作用以及对其的使用方法作详尽介绍 使读者了解如何定义触发器 创建和使用各种不同
复杂程度的触发器
12.5.1 触发器的概念及作用
触发器是一种特殊类型的存储过程 它不同于我们前面介绍过的存储过程 触发器主
要是通过事件进行触发而被执行的 而存储过程可以通过存储过程名字而被直接调用 当
对某一表进行诸如 UPDATE INSERT DELETE 这些操作时 SQL Server 就会自动执行
触发器所定义的 SQL 语句 从而确保对数据的处理必须符合由这些 SQL 语句所定义的规
则
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数
据的一致性 除此之外 触发器还有其它许多不同的功能
1 强化约束强化约束强化约束强化约束 Enforce restriction
触发器能够实现比 CHECK 语句更为复杂的约束
2 跟踪变化跟踪变化跟踪变化跟踪变化 Auditing changes
触发器可以侦测数据库内的操作 从而不允许数据库中未经许可的指定更新和变化
3 级联运行级联运行级联运行级联运行 Cascaded operation
触发器可以侦测数据库内的操作 并自动地级联影响整个数据库的各项内容 例如
某个表上的触发器中包含有对另外一个表的数据操作 如删除 更新 插入 而该操作
又导致该表上触发器被触发
4 存储过程的调用存储过程的调用存储过程的调用存储过程的调用 Stored procedure invocation
为了响应数据库更新 触发器可以调用一个或多个存储过程 甚至可以通过外部过程
的调用而在 DBMS 数据库管理系统 本身之外进行操作
由此可见 触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等
一些方面的问题 例如 触发器能够找出某一表在数据修改前后状态发生的差异 并根据
这种差异执行一定的处理 此外一个表的同一类型 INSERT UPDATE DELETE 的
多个触发器能够对同一种数据操作采取多种不同的处理
总体而言 触发器性能通常比较低 当运行触发器时 系统处理的大部分时间花费在
参照其它表的这一处理上 因为这些表既不在内存中也不在数据库设备上 而删除表和插
入表总是位于内存中 可见触发器所参照的其它表的位置决定了操作要花费的时间长短
12.5.2 触发器的种类
SQL Server 2000 支持两种类型的触发器 AFTER 触发器和 INSTEAD OF 触发器 其
中 AFTER 触发器即为 SQL Server 2000 版本以前所介绍的触发器 该类型触发器要求只
有执行某一操作 INSERT UPDATE DELETE 之后 触发器才被触发 且只能在表
上定义 可以为针对表的同一操作定义多个触发器 对于 AFTER 触发器 可以定义哪一
个触发器被最先触发 哪一个被最后触发 通常使用系统过程 sp_settriggerorder 来完成此
任务
INSTEAD OF 触发器表示并不执行其所定义的操作 INSERT UPDATE DELETE
而仅是执行触发器本身 既可在表上定义 INSTEAD OF 触发器 也可以在视图上定义
INSTEAD OF 触发器 但对同一操作只能定义一个 INSTEAD OF 触发器
1122..66 创建触发器
上面介绍了有关触发器的概念 作用和一些基本问题 下面我们将分别介绍在 MS SQL
Server 中如何用 SQL Server 管理工具 Enterprise Manager 和 Transaction_SQL 来创建触发
器
在创建触发器以前必须考虑到以下几个方面
CREATE TRIGGER语句必须是批处理的第一个语句
表的所有者具有创建触发器的缺省权限 表的所有者不能把该权限传给其它用
户
触发器是数据库对象 所以其命名必须符合命名规则
尽管在触发器的 SQL 语句中可以参照其它数据库中的对象 但是 触发器只能
创建在当前数据库中
虽然触发器可以参照视图或临时表 但不能在视图或临时表上创建触发器 而只
能在基表或在创建视图的表上创建触发器
一个触发器只能对应一个表 这是由触发器的机制决定的
尽管 TRUNCATE TABLE 语句如同没有 WHERE 从句的 DELETE 语句 但是由
于 TRUNCATE TABLE 语句没有被记入日志 所以该语句不能触发 DELETE 型
触发器
WRITETEXT语句不能触发 INSERT或 UPDATE型的触发器
当创建一个触发器时 必须指定触发器的名字 在哪一个表上定义触发器 激活触发
器的修改语句 如 INSERT DELETE UPDATE 当然两个或三个不同的修改语句也可
以都触发同一个触发器 如 INSERT 和 UPDATE 语句都能激活同一个触发器
12.6.1