12009-7-28 Information College · ChangJun
第 9章 存储过程与触发器
22009-7-28 Information College · ChangJun
[本章概要 ]
9.1 存储过程
9.2 触发器
32009-7-28 Information College · ChangJun
大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是 SQL Server的数据库对象，都是 SQL 语句和流程控制语句的集合。
42009-7-28 Information College · ChangJun
9.1 存储过程
9.1.1 基本概念
1、什么是存储过程存储过程（ Stored Procedure）是一组为了完成特定功能的 SQL 语句和可选控制流语句的预编译集合，存储在数据库内，以一个名称存储并作为一个单元处理。
特点：
a) 存储过程在创建时即在服务器上进行编译，
所以执行起来比单个 SQL 语句快。
b) 存储过程的名字存放在当前数据库的
sysobjects系统表中，文本存放在 syscomments系统表中。
52009-7-28 Information College · ChangJun
c) 用户通过指定存储过程的名字并给出参数
（如果该存储过程带有参数）来执行它。
d) 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
e) 可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
存储过程的优点：
a) 执行速度快
b) 模块化程序设计
c) 减少网络通信量
d) 保证系统的安全性
62009-7-28 Information College · ChangJun
2、分类在 SQL Server 2000中存储过程分为五类：
1) 系统存储过程主要存储在 master 数据库中并以 sp_为前缀，
系统存储过程主要是从系统表中获取信息，从而为系统管理员管理 SQL Server 提供支持。可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，
一些系统存储过程会在新数据库中被自动创建。
2) 本地存储过程在独立的用户数据库中被创建。
3) 局部临时存储过程存储在 tempdb 内，名字以单个数字符号（ #）
开始，在单个用户任务中有效。
72009-7-28 Information College · ChangJun
4) 全局临时存储过程存储在 tempdb 内，名字以两个数字符号
（ ##）开始，在所有用户任务中有效。
5) 扩展存储过程作为动态链接库（ dll）在 SQL Server环境中执行的方法，以 xp_为前缀。
82009-7-28 Information College · ChangJun
9.1.2 创建存储过程当创建存储过程时，需要确定存储过程的三个组成部分：
a) 所有的输入参数以及传给调用者的输出参数
b) 被执行的针对数据库的操作语句包括调用其它存储过程的语句
c)返回给调用者的状态值以指明调用是成功还是失败
1、利用,向导,
2、使用企业管理器
[服务器 ]—— [数据库 ]—— [存储过程 ]— 右键 — [新建 ]—— [编辑文本 ]
92009-7-28 Information College · ChangJun
3、使用 Transact-SQL语句可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。
语法：
CREATE PROC [ EDURE ] procedure_name [ ;
number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION |
RECOMPILE,ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [,..n ]
102009-7-28 Information College · ChangJun
例 1：要求存储过程从三个表的联接中返回所有学生（提供了姓名）、选修的课程名称和成绩。
USE school
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘student_info_all' AND type
= 'P')
DROP PROCEDURE student_info_all
GO
CREATE PROCEDURE student_info_all
AS
SELECT sname,cname,grade
FROM s INNER JOIN sc ON s.sno=sc.sno
INNER JOIN c ON sc.cno=c.cno
GO
112009-7-28 Information College · ChangJun
4、执行存储过程语法：
[ [ EXEC [ UTE ] ]
{ [ @return_status = ]
{ procedure_name [ ;number ] |
@procedure_name_var}
[ [ @parameter = ] { value | @variable
[ OUTPUT ] | [ DEFAULT ] ]
[,...n ]
[ WITH RECOMPILE ]
122009-7-28 Information College · ChangJun
例 1的执行语句：
EXECUTE student_info_all
-- Or
EXEC student_info_all
如果该过程是批处理中的第一条语句，则可使用：
student_info_all
132009-7-28 Information College · ChangJun
例 2：输入参数：对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。
USE PUBS
CREATE PROCEDURE student_info2
@name varchar(30) = ‘%’,
AS
SELECT sname,cname,grade
FROM s INNER JOIN sc ON s.sno=sc.sno
INNER JOIN c ON sc.cno=c.cno
WHERE sname LIKE @name
执行：
exec student_info2
exec student_info ‘%[小大 ]%’
142009-7-28 Information College · ChangJun
例 3,输出参数：创建一个存储过程
(student_wavg)，模糊输入学生姓名，输出他的加权平均分。
USE pubs
CREATE PROCEDURE student_wavg
@name varchar(40) = ‘%’,
@wavg numeric(5,1) OUTPUT
AS
SELECT 姓名 =sname,加权平均分 =wavg from s,
(select
sno,wavg=sum(grade*ccredit)/sum(ccredit) from
c join sc om c.cno=s.cno group by sno) as t
where s.sno=t.sno and sname like @name
152009-7-28 Information College · ChangJun
上面创建的存储过程 student_wavg的使用。
DECLARE @avg numeric(5,1)
EXECUTE student_wavg ‘张 %’,@avg OUTPUT
IF @avg >=90
BEGIN
PRINT ‘ ’
PRINT ‘智育成绩为 ‘ + @avg + ’,已达到一等奖学金的要求。 ‘
END
162009-7-28 Information College · ChangJun
9.1.3 管理存储过程
1,使用企业管理器
（ 1）查看存储过程
[数据库 ]—— [存储过程 ]— 右键 — [属性 ]
（ 2）删除存储过程
[数据库 ]—— [存储过程 ]— 右键 — [删除 ]
（ 3）修改存储过程
[数据库 ]—— [存储过程 ]— 右键 — [属性 ]
172009-7-28 Information College · ChangJun
2、使用系统存储过程或 Transact-SQL语句
（ 1）查看存储过程
1） sp_helptext,查看存储过程的文本信息。
2） sp_depends,查看存储过程的引用表情况。
3） sp_rename,更改存储过程的名字。
（ 2）执行存储过程语法：
[ [ EXEC [ UTE ] ]
{ [ @return_status = ]
{ procedure_name [ ;number ] |
@procedure_name_var}
[ [ @parameter = ] { value | @variable [ OUTPUT ]
| [ DEFAULT ] ]
[,...n ]
[ WITH RECOMPILE ]
182009-7-28 Information College · ChangJun
（ 3）删除存储过程语法：
DROP PROCEDURE {procedure}} [,… n]
例：
DROP PROCEDURE strconnect
（ 4）修改存储过程语法：
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION } ]
[ FOR REPLICATION ]
AS
sql_statement [,..n ]
192009-7-28 Information College · ChangJun
9.2 触发器
9.2.1 基本概念
1、什么是触发器
SQL Server触发器是一类特殊的存储过程，
被定义为在对表或视图发出 UPDATE,INSERT 或
DELETE 语句时自动执行触发器所定义的 SQL 语句。
特点：
1） 自动激活触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。不能直接调用
2）与一张表相连一张表可以有多个触发器。用户可以对 UPDATE、
INSERT 或 DELETE 语句分别设置触发器，也可以对一张表上的特定操作设置多个触发器。
202009-7-28 Information College · ChangJun
3） 是一个事务触发器作为独立的单元被执行，被看作是一个事务，如果执行中发生了错误。则整个事务将会自动回滚。
优点：
1） 触发器可以通过数据库中的相关表进行层叠
（级联）更改。
例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其它表中的各匹配行采取删除操作。该触发器用 title_id 列作为唯一键，在 titleauthor,sales 及 roysched 表中对各匹配行进行定位。
212009-7-28 Information College · ChangJun
2） 触发器可以强制限制，这些限制比用 CHECK
约束所定义的更复杂。与 CHECK 约束不同的是，
触发器可以引用其它表中的列。
例如，触发器可以回滚试图对价格低于 10
美元的书（存储在 titles 表中）应用折扣（存储在 discounts 表中）的更新。
3） 触发器可以比较数据修改前后的表状态，并根据差异采取对策。
4） 通过使用触发器，当触发器执行时，根据某些条件，可以激活预先定义的或动态的自定义错误信息。
222009-7-28 Information College · ChangJun
2、触发器的类型
（ 1） AFTER 型
a) 触发器在触发它们的语句完成后执行。如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。
b) 不能为视图指定 AFTER 触发器，只能为表指定该触发器。
c) 可以为每个触发操作（ INSERT,UPDATE 或
DELETE）指定多个 AFTER 触发器。
d) 如果表有多个 AFTER 触发器，可使用
sp_settriggerorder 定义哪个 AFTER 触发器最先激发，
哪个最后激发。除第一个和最后一个触发器外，所有其它的 AFTER 触发器的激发顺序不确定，并且无法控制。
e) 在 SQL Server 2000 中 AFTER 是默认触发器。
232009-7-28 Information College · ChangJun
（ 2） INSTEAD OF 型
a) 该触发器代替触发语句的操作。
b) 可在表和视图上指定 INSTEAD OF 触发器。
c) 只能为每个触发操作（ INSERT,UPDATE
和 DELETE）定义一个 INSTEAD OF 触发器。
d) INSTEAD OF 触发器可用于对 INSERT 和
UPDATE 语句中提供的数据值执行增强的完整性检查。
e) INSTEAD OF 触发器还允许指定某些操作，
使一般不支持更新的视图可以被更新。
242009-7-28 Information College · ChangJun
（ 3） 嵌套触发器
a) 更改表的触发器可以激活第二个触发器，
它又激活第三个触发器，依次类推。
b) 用 sp_configure系统存储过程禁止和重新启用嵌套触发器。 (可用企业管理器 )
c) 触发器可以嵌套到 32级深。
（ 4） 递归触发器
a) 更改表的触发器可以激活第二个触发器，
第二个触发器通过更改原始表中的数据来激活调用它的触发器。
b) 可以调用它自己。
c) 用 sp_dboption table_name,’recursive
triggers’,ON | OFF 系统存储过程禁止和重新启用递归触发器。 (可用企业管理器 )
252009-7-28 Information College · ChangJun
（ 4）使用触发器
SQL Server使用到了两张特殊的临时表：
inserted表和 deleted表。用户可用 Select语句检查。
a) deleted表中存储着被 DELETE和 UPDATE语句影响的旧数据行。在执行 DELETE或 UPDATE语句过程中，指定的数据行被用户从基本表中删除，
然后转移到 deleted表中。
b) inserted表中存储着被 INSERT和 UPDATE
语句影响的新数据行。在执行 INSERT或 UPDATE语句过程中，新的数据行被添加到 inserted表。
262009-7-28 Information College · ChangJun
9.2.2 创建触发器
1、使用企业管理器
[服务器 ]—— [数据库 ]—— [表 ]— 右键 —
[所有任务 ]—— [管理触发器 ]—— [名称新建 ]
272009-7-28 Information College · ChangJun
2、使用 Transact-SQL语句语法：
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [,] [ DELETE ] [,] [ UPDATE ] }
[ WITH APPEND ]
[ 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 ]
}
282009-7-28 Information College · ChangJun
例 1：设计一触发器，实现禁止修改 s表的性别列。
Use school
go
Create trigger tr_sex on s
for update
as
if update(ssex)
update s set ssex=
(select ssex from deleted)
where sno=(select sno from deleted)
激活 update触发器
update s set ssex='女 ' where sno='9512101'
292009-7-28 Information College · ChangJun
例 2：现有一表 t,用 sno列和 zxf列（总学分）
记录每个学生的学分情况。设计一触发器，
实现 sc表和 t表的级联修改的功能（所选课程的成绩不低于 60分才给予相应课程的学分）。
302009-7-28 Information College · ChangJun
Create trigger tr_sc_zxf on sc
for insert,delete,update
as
declare @sid varchar(10),@cid varchar(10),@grade
numeric(5,1)
--select '1',@sid,@cid,@grade
select @sid=sno,@cid=cno,@grade=grade from inserted
--select '2',@sid,@cid,@grade
if (@grade>=60)
update t set zxf=zxf+
(select ccredit from c where cno=@cid ) where sno=@sid
set @sid=null
set @cid=null
set @grade=null
--select '3',@sid,@cid,@grade
select @sid=sno,@cid=cno,@grade=grade from deleted
--select '4',@sid,@cid,@grade
if (@grade>=60)
update t set zxf=zxf-
(select ccredit from c where cno=@cid ) where sno=@sid
go
激活 update触发器
312009-7-28 Information College · ChangJun
激活 update触发器,
insert into sc values('9512101','c03',90,'test') --
ccredit(c03)=4
delete sc where sno='9512101' and cno='c03'
update sc set grade=40 where sno='9512101' and
cno='c03'
322009-7-28 Information College · ChangJun
9.2.3 管理触发器
1,使用企业管理器
[服务器 ]—— [数据库 ]—— [表 ]— 右键 — [所有任务 ]—— [管理触发器 ]—— [编辑、删除 ]
332009-7-28 Information College · ChangJun
2、使用 Transact-SQL语句
（ 1）查看触发器
sp_help
查看触发器的一般信息，如触发器的名字、
属性、类型、创建时间。
sp_helptext
查看触发器的正文信息。
sp_depends
查看指定触发器所引用的表或指定的表涉及到的所有触发器。
sp_rename
修改触发器的名字
342009-7-28 Information College · ChangJun
（ 2）删除触发器语法：
DROP TRIGGER { trigger } [,...n ]
例：
DROP TRIGGER employee_insupd
352009-7-28 Information College · ChangJun
（ 3）修改触发器语法：
ALTER TRIGGER trigger_name
ON ( table | view )
[ WITH ENCRYPTION ]
{{ ( FOR | AFTER | INSTEAD OF )
{ [ DELETE ] [,] [ INSERT ] [,]
[ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [,..n ]
}
第 9章 存储过程与触发器
22009-7-28 Information College · ChangJun
[本章概要 ]
9.1 存储过程
9.2 触发器
32009-7-28 Information College · ChangJun
大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是 SQL Server的数据库对象，都是 SQL 语句和流程控制语句的集合。
42009-7-28 Information College · ChangJun
9.1 存储过程
9.1.1 基本概念
1、什么是存储过程存储过程（ Stored Procedure）是一组为了完成特定功能的 SQL 语句和可选控制流语句的预编译集合，存储在数据库内，以一个名称存储并作为一个单元处理。
特点：
a) 存储过程在创建时即在服务器上进行编译，
所以执行起来比单个 SQL 语句快。
b) 存储过程的名字存放在当前数据库的
sysobjects系统表中，文本存放在 syscomments系统表中。
52009-7-28 Information College · ChangJun
c) 用户通过指定存储过程的名字并给出参数
（如果该存储过程带有参数）来执行它。
d) 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
e) 可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
存储过程的优点：
a) 执行速度快
b) 模块化程序设计
c) 减少网络通信量
d) 保证系统的安全性
62009-7-28 Information College · ChangJun
2、分类在 SQL Server 2000中存储过程分为五类：
1) 系统存储过程主要存储在 master 数据库中并以 sp_为前缀，
系统存储过程主要是从系统表中获取信息，从而为系统管理员管理 SQL Server 提供支持。可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，
一些系统存储过程会在新数据库中被自动创建。
2) 本地存储过程在独立的用户数据库中被创建。
3) 局部临时存储过程存储在 tempdb 内，名字以单个数字符号（ #）
开始，在单个用户任务中有效。
72009-7-28 Information College · ChangJun
4) 全局临时存储过程存储在 tempdb 内，名字以两个数字符号
（ ##）开始，在所有用户任务中有效。
5) 扩展存储过程作为动态链接库（ dll）在 SQL Server环境中执行的方法，以 xp_为前缀。
82009-7-28 Information College · ChangJun
9.1.2 创建存储过程当创建存储过程时，需要确定存储过程的三个组成部分：
a) 所有的输入参数以及传给调用者的输出参数
b) 被执行的针对数据库的操作语句包括调用其它存储过程的语句
c)返回给调用者的状态值以指明调用是成功还是失败
1、利用,向导,
2、使用企业管理器
[服务器 ]—— [数据库 ]—— [存储过程 ]— 右键 — [新建 ]—— [编辑文本 ]
92009-7-28 Information College · ChangJun
3、使用 Transact-SQL语句可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。
语法：
CREATE PROC [ EDURE ] procedure_name [ ;
number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION |
RECOMPILE,ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [,..n ]
102009-7-28 Information College · ChangJun
例 1：要求存储过程从三个表的联接中返回所有学生（提供了姓名）、选修的课程名称和成绩。
USE school
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘student_info_all' AND type
= 'P')
DROP PROCEDURE student_info_all
GO
CREATE PROCEDURE student_info_all
AS
SELECT sname,cname,grade
FROM s INNER JOIN sc ON s.sno=sc.sno
INNER JOIN c ON sc.cno=c.cno
GO
112009-7-28 Information College · ChangJun
4、执行存储过程语法：
[ [ EXEC [ UTE ] ]
{ [ @return_status = ]
{ procedure_name [ ;number ] |
@procedure_name_var}
[ [ @parameter = ] { value | @variable
[ OUTPUT ] | [ DEFAULT ] ]
[,...n ]
[ WITH RECOMPILE ]
122009-7-28 Information College · ChangJun
例 1的执行语句：
EXECUTE student_info_all
-- Or
EXEC student_info_all
如果该过程是批处理中的第一条语句，则可使用：
student_info_all
132009-7-28 Information College · ChangJun
例 2：输入参数：对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。
USE PUBS
CREATE PROCEDURE student_info2
@name varchar(30) = ‘%’,
AS
SELECT sname,cname,grade
FROM s INNER JOIN sc ON s.sno=sc.sno
INNER JOIN c ON sc.cno=c.cno
WHERE sname LIKE @name
执行：
exec student_info2
exec student_info ‘%[小大 ]%’
142009-7-28 Information College · ChangJun
例 3,输出参数：创建一个存储过程
(student_wavg)，模糊输入学生姓名，输出他的加权平均分。
USE pubs
CREATE PROCEDURE student_wavg
@name varchar(40) = ‘%’,
@wavg numeric(5,1) OUTPUT
AS
SELECT 姓名 =sname,加权平均分 =wavg from s,
(select
sno,wavg=sum(grade*ccredit)/sum(ccredit) from
c join sc om c.cno=s.cno group by sno) as t
where s.sno=t.sno and sname like @name
152009-7-28 Information College · ChangJun
上面创建的存储过程 student_wavg的使用。
DECLARE @avg numeric(5,1)
EXECUTE student_wavg ‘张 %’,@avg OUTPUT
IF @avg >=90
BEGIN
PRINT ‘ ’
PRINT ‘智育成绩为 ‘ + @avg + ’,已达到一等奖学金的要求。 ‘
END
162009-7-28 Information College · ChangJun
9.1.3 管理存储过程
1,使用企业管理器
（ 1）查看存储过程
[数据库 ]—— [存储过程 ]— 右键 — [属性 ]
（ 2）删除存储过程
[数据库 ]—— [存储过程 ]— 右键 — [删除 ]
（ 3）修改存储过程
[数据库 ]—— [存储过程 ]— 右键 — [属性 ]
172009-7-28 Information College · ChangJun
2、使用系统存储过程或 Transact-SQL语句
（ 1）查看存储过程
1） sp_helptext,查看存储过程的文本信息。
2） sp_depends,查看存储过程的引用表情况。
3） sp_rename,更改存储过程的名字。
（ 2）执行存储过程语法：
[ [ EXEC [ UTE ] ]
{ [ @return_status = ]
{ procedure_name [ ;number ] |
@procedure_name_var}
[ [ @parameter = ] { value | @variable [ OUTPUT ]
| [ DEFAULT ] ]
[,...n ]
[ WITH RECOMPILE ]
182009-7-28 Information College · ChangJun
（ 3）删除存储过程语法：
DROP PROCEDURE {procedure}} [,… n]
例：
DROP PROCEDURE strconnect
（ 4）修改存储过程语法：
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION } ]
[ FOR REPLICATION ]
AS
sql_statement [,..n ]
192009-7-28 Information College · ChangJun
9.2 触发器
9.2.1 基本概念
1、什么是触发器
SQL Server触发器是一类特殊的存储过程，
被定义为在对表或视图发出 UPDATE,INSERT 或
DELETE 语句时自动执行触发器所定义的 SQL 语句。
特点：
1） 自动激活触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。不能直接调用
2）与一张表相连一张表可以有多个触发器。用户可以对 UPDATE、
INSERT 或 DELETE 语句分别设置触发器，也可以对一张表上的特定操作设置多个触发器。
202009-7-28 Information College · ChangJun
3） 是一个事务触发器作为独立的单元被执行，被看作是一个事务，如果执行中发生了错误。则整个事务将会自动回滚。
优点：
1） 触发器可以通过数据库中的相关表进行层叠
（级联）更改。
例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其它表中的各匹配行采取删除操作。该触发器用 title_id 列作为唯一键，在 titleauthor,sales 及 roysched 表中对各匹配行进行定位。
212009-7-28 Information College · ChangJun
2） 触发器可以强制限制，这些限制比用 CHECK
约束所定义的更复杂。与 CHECK 约束不同的是，
触发器可以引用其它表中的列。
例如，触发器可以回滚试图对价格低于 10
美元的书（存储在 titles 表中）应用折扣（存储在 discounts 表中）的更新。
3） 触发器可以比较数据修改前后的表状态，并根据差异采取对策。
4） 通过使用触发器，当触发器执行时，根据某些条件，可以激活预先定义的或动态的自定义错误信息。
222009-7-28 Information College · ChangJun
2、触发器的类型
（ 1） AFTER 型
a) 触发器在触发它们的语句完成后执行。如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。
b) 不能为视图指定 AFTER 触发器，只能为表指定该触发器。
c) 可以为每个触发操作（ INSERT,UPDATE 或
DELETE）指定多个 AFTER 触发器。
d) 如果表有多个 AFTER 触发器，可使用
sp_settriggerorder 定义哪个 AFTER 触发器最先激发，
哪个最后激发。除第一个和最后一个触发器外，所有其它的 AFTER 触发器的激发顺序不确定，并且无法控制。
e) 在 SQL Server 2000 中 AFTER 是默认触发器。
232009-7-28 Information College · ChangJun
（ 2） INSTEAD OF 型
a) 该触发器代替触发语句的操作。
b) 可在表和视图上指定 INSTEAD OF 触发器。
c) 只能为每个触发操作（ INSERT,UPDATE
和 DELETE）定义一个 INSTEAD OF 触发器。
d) INSTEAD OF 触发器可用于对 INSERT 和
UPDATE 语句中提供的数据值执行增强的完整性检查。
e) INSTEAD OF 触发器还允许指定某些操作，
使一般不支持更新的视图可以被更新。
242009-7-28 Information College · ChangJun
（ 3） 嵌套触发器
a) 更改表的触发器可以激活第二个触发器，
它又激活第三个触发器，依次类推。
b) 用 sp_configure系统存储过程禁止和重新启用嵌套触发器。 (可用企业管理器 )
c) 触发器可以嵌套到 32级深。
（ 4） 递归触发器
a) 更改表的触发器可以激活第二个触发器，
第二个触发器通过更改原始表中的数据来激活调用它的触发器。
b) 可以调用它自己。
c) 用 sp_dboption table_name,’recursive
triggers’,ON | OFF 系统存储过程禁止和重新启用递归触发器。 (可用企业管理器 )
252009-7-28 Information College · ChangJun
（ 4）使用触发器
SQL Server使用到了两张特殊的临时表：
inserted表和 deleted表。用户可用 Select语句检查。
a) deleted表中存储着被 DELETE和 UPDATE语句影响的旧数据行。在执行 DELETE或 UPDATE语句过程中，指定的数据行被用户从基本表中删除，
然后转移到 deleted表中。
b) inserted表中存储着被 INSERT和 UPDATE
语句影响的新数据行。在执行 INSERT或 UPDATE语句过程中，新的数据行被添加到 inserted表。
262009-7-28 Information College · ChangJun
9.2.2 创建触发器
1、使用企业管理器
[服务器 ]—— [数据库 ]—— [表 ]— 右键 —
[所有任务 ]—— [管理触发器 ]—— [名称新建 ]
272009-7-28 Information College · ChangJun
2、使用 Transact-SQL语句语法：
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [,] [ DELETE ] [,] [ UPDATE ] }
[ WITH APPEND ]
[ 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 ]
}
282009-7-28 Information College · ChangJun
例 1：设计一触发器，实现禁止修改 s表的性别列。
Use school
go
Create trigger tr_sex on s
for update
as
if update(ssex)
update s set ssex=
(select ssex from deleted)
where sno=(select sno from deleted)
激活 update触发器
update s set ssex='女 ' where sno='9512101'
292009-7-28 Information College · ChangJun
例 2：现有一表 t,用 sno列和 zxf列（总学分）
记录每个学生的学分情况。设计一触发器，
实现 sc表和 t表的级联修改的功能（所选课程的成绩不低于 60分才给予相应课程的学分）。
302009-7-28 Information College · ChangJun
Create trigger tr_sc_zxf on sc
for insert,delete,update
as
declare @sid varchar(10),@cid varchar(10),@grade
numeric(5,1)
--select '1',@sid,@cid,@grade
select @sid=sno,@cid=cno,@grade=grade from inserted
--select '2',@sid,@cid,@grade
if (@grade>=60)
update t set zxf=zxf+
(select ccredit from c where cno=@cid ) where sno=@sid
set @sid=null
set @cid=null
set @grade=null
--select '3',@sid,@cid,@grade
select @sid=sno,@cid=cno,@grade=grade from deleted
--select '4',@sid,@cid,@grade
if (@grade>=60)
update t set zxf=zxf-
(select ccredit from c where cno=@cid ) where sno=@sid
go
激活 update触发器
312009-7-28 Information College · ChangJun
激活 update触发器,
insert into sc values('9512101','c03',90,'test') --
ccredit(c03)=4
delete sc where sno='9512101' and cno='c03'
update sc set grade=40 where sno='9512101' and
cno='c03'
322009-7-28 Information College · ChangJun
9.2.3 管理触发器
1,使用企业管理器
[服务器 ]—— [数据库 ]—— [表 ]— 右键 — [所有任务 ]—— [管理触发器 ]—— [编辑、删除 ]
332009-7-28 Information College · ChangJun
2、使用 Transact-SQL语句
（ 1）查看触发器
sp_help
查看触发器的一般信息，如触发器的名字、
属性、类型、创建时间。
sp_helptext
查看触发器的正文信息。
sp_depends
查看指定触发器所引用的表或指定的表涉及到的所有触发器。
sp_rename
修改触发器的名字
342009-7-28 Information College · ChangJun
（ 2）删除触发器语法：
DROP TRIGGER { trigger } [,...n ]
例：
DROP TRIGGER employee_insupd
352009-7-28 Information College · ChangJun
（ 3）修改触发器语法：
ALTER TRIGGER trigger_name
ON ( table | view )
[ WITH ENCRYPTION ]
{{ ( FOR | AFTER | INSTEAD OF )
{ [ DELETE ] [,] [ INSERT ] [,]
[ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [,..n ]
}
