第 9 章存储过程和触发器
• 9.1 存储过程
9.1.1 存储过程的概念
u 存储过程是指封装了可重用代码的, 存储在服务器上的程序模块或例程. 存储过程是数据库对象之一, 它类似于其他高级编程语言中的过程或子程序, 编译成可执行代码后保存在服务器上, 可多次调用.
u 其特点体现在:
Ø 可以接受多个输入参数, 能够以多输出参数的格式返回多个值.
Ø 在服务器端运行, 使用 EXECUTE(简写为 EXEC)语句来执行.
Ø 可以调用其他存储过程, 也可以被其他语句或存储过程调用, 但不能直接在表达式中使用.
Ø 具有返回状态值, 表明被调用是成功还是失败. 但不返回取代其名称的值, 这是它与函数的不同之处.
Ø 存储过程已在服务器注册.
存储过程的优点主要体现在:
Ø 提高程序的执行效率. 存储过程执行在第一次被执行以后, 其执行规划就驻留在高速缓冲存储器中. 在以后的每次操作中, 只需从高速缓冲存储器中调用已编译好的二进制代码执行即可, 而不必重新编译再执行, 从而提高了执行效率.
Ø 具有较高的安全特性. 作为一种数据库对象, 存储过程要求拥有相应权限的用户才能执行它. 同时, 它也提供了一种更为灵活的安全性管理机制: 用户可以被授予权限来执行存储过程, 而不必对存储过程中引用的对象拥有访问权限.
Ø 如果一个存储过程是用于更新某一个数据表的, 那么只要用户拥有执行该存储过程的权限, 他就可以通过执行该存储过程的方法来实现对指定数据表的更新操作, 而不必直接拥有对该数据表操作的权限.
Ø 减少网络通信流量. 由于存储过程在服务器端执行, 用户每次只需发出一条执行命令, 而不必发出存储过程所有的冗长代码, 因而减少了网络的数据流量.
Ø 允许模块化程序设计, 提高代码的可重用性. 存储过程一旦被创建, 以后就可以在所有程序中多次调用. 这有利于程序的结构化设计, 提高程序的可维护性和代码的可重用性.
9.1.2 存储过程的类型
Ø 在 SQL Server 2008 中, 存储过程可以分为两种类型: SQL 存储过程和 CLR 存储过程. SQL 存储过程是指出由 SQL 语言编写而形成的存储过程, 它是 SQL 语句的集合. CLR(Common Language Runtime)存储过程是指引用 Microsoft.NET Framework 公共语言运行时 (CLR) 方法的存储过程, 它在. NET Framework 程序集中是作为类的公共静态方法实现的.
Ø 目前常使用的是 SQL 存储过程, 所以本书要介绍的也就是这类存储过程.
Ø 根据来源和应用目的的不同, 又可以将存储过程分为用户存储过程, 系统存储过程和扩展存储过程.
1. 系统存储过程
ü 系统存储过程是 SQL Server 2008 本身定义的, 当作命令来执行的一类存储过程. 它主要用于管理 SQL Server 数据库和显示有关数据库及用户的信息, 通常前缀 "sp_".
Ø sp_addrolemember 就是一个用于为数据库角色添加成员的系统存储过程. 从逻辑结构看, 系统存储过程出现在每个系统定义数据库和用户定义数据库的 sys 构架中. 读者最好能够熟悉一些常用的系统存储过程, 以免重复开发.
2. 用户存储过程
ü 用户存储过程是指由用户通过利用 SQL 语言编写的, 具有特定功能的一类存储过程. 由于系统存储过程以 "sp_" 为前缀, 扩展存储过程以 "xp_", 所以用户存储过程在定义时最好不要使用 "sp_" 或 "xp_" 为前缀. 如果需要, 用户存储过程应以 "up_" 为前缀,"u" 是单词 user 的头字母.
ü 本章将主要介绍用户存储过程的定义, 修改和删除等基本管理操作.
3. 扩展存储过程
ü 扩展存储过程是指 SQL Server 的实例可以动态加载和运行的动态链接库 (DLL). 通过扩展存储过程, 可以使用其他编程语言(如 C 语句) 创建自己的外部程序, 实现了 SQL 程序与其他语言程序的连接与融合.
ü 扩展存储过程直接在 SQL Server 的实例地址空间中运行, 可以使用 SQL Server 扩展存储过程 API 完成编程. 但由于后续的 SQL Server 版本中将不支持扩展存储过程, 所以在新的工程开发项目中应尽量少用或不用这种功能.
9.1.3 存储过程的创建和调用
存储过程是由 CREATE PROCEDURE 语句来创建, 其语法如下:
- CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
- [ { @parameter [ type_schema_name. ] data_type }
- [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
- [ WITH <procedure_option> [ ,...n ]
- [ FOR REPLICATION ]
- AS { <sql_statement> [;][ ...n ] | <method_specifier> }[;]
- <procedure_option> ::=
- [ ENCRYPTION ]
- [ RECOMPILE ]
- [ EXECUTE_AS_Clause ]
- <sql_statement> ::= { [ BEGIN ] statements [ END ] }
- <method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name
对涉及的参数说明如下:
ü schema_name: 设定存储过程所属架构的名称.
ü procedure_name: 存储过程的名称. 它是一个合法的标识符, 在架构中是唯一的. 存储过程名一般不能使用前缀 "sp_", 此前缀由系统存储过程使用. 如果过程名以井号 "#" 开头, 则表示创建的过程将局部临时过程, 这种过程名的长度不超 116 个字符(含 #); 如果以双井号 "##" 开头, 则表示是全局临时过程, 这种过程名的长度不超 128 个字符(含 ##).
ü number: 用于对同名的存储过程进行分组的整数. 例如, myPro;1,myPro;2 等.
ü @parameter: 存储过程带的参数, data_type 为参数所属架构的数据类型. 参数可以是一个或者多个, 最多为 2,100 个参数. 在定义时参数可以设置默认值, 而对于没有设置默认值的参数, 在调用时必须为其提供值. 在默认情况下, 参数只能代表常量表达式, 而不能用于代表表名, 列名或其他数据库对象的名称. 如果指定了 FOR REPLICATION, 则无法声明参数.
ü OUTPUT(或 OUT): 如果指定了 OUTPUT(或 OUT), 则表示该参数为输出参数. 输出参数用于将存储过程处理后的某些结果返回给调用它的语句. 游标 (cursor) 数据类型参数必须指定 OUTPUT, 同时还必须指定关键字 VARYING. 一般情况下, text,ntext 和 image 类型参数不能用作 OUTPUT 参数.
ü VARYING: 指定输出参数支持的结果集. 仅适用于游标类型参数.
ü default: 设定参数的默认值. 如果定义了 default 值, 则在调用存储过程时无需为此参数指定值, 否则必须指定参数值才能调用. 默认值必须是常量或 NULL.
ü RECOMPILE: 该选项用于指示 SQL Server 不要将存储过程的执行规划保存在高速缓冲存储器中, 因为该过程在执行时要重新编译, 然后才运行. 如果指定了 FOR REPLICATION, 则不能使用此选项.
ü ENCRYPTION: 指示 SQL Server 对 CREATE PROCEDURE 语句的原始文本进行加密, 加密后的代码的输出在 SQL Server 2008 的任何目录视图中都不能直接显示.
ü EXECUTE AS: 该子句用于指定在其中执行存储过程的安全上下文.
ü FOR REPLICATION: 如果选择该选项, 则表示创建的存储过程只能在复制过程中执行. 该类过程不能声明参数, 忽略 RECOMPILE 选项.
ü <sql_statement>: 表示包含在过程中的一个或多个 SQL 语句.
ü <method_specifier>:CLR 存储过程的标识. assembly_name.class_name.method_name 用于指定. NET Framework 程序集的方法, 以便 CLR 存储过程引用.
[例 9.1] (简单的存储过程)创建一个存储过程, 它可以输出学生的学号, 姓名, 平均成绩以及所在系别.
该过程名为 "myPro1", 所使用的 SQL 语句如下:
USE MyDatabase; -- 设置当前数据库
GO
IF OBJECT_ID('myPro1','P') IS NOT NULL -- 判断是否已存在名为 "myPro1" 存储过程
DROP PROCEDURE myPro1; -- 如果存在则删除, 否则无法创建(不是必备代码)
GO
CREATE PROCEDURE myPro1 -- 定义存储过程 myPro1
- AS
- SELECT s_no, s_name, s_avgrade, s_dept
- FROM student;
- GO
在 SQL Server Management Studio 中编写上述代码, 然后运行此代码即可在服务器端生成存储过程 myPro1, 此后就可以调用此存储过程.
Ø 调用一个存储过程, 一般是用 EXECUTE(或 EXEC)语句来完成. 但也可以直接将过程名当作一个条命令来执行.
[例子] 对于上面定义的过程 myPro1, 以下三种执行方式都是有效且等价的:
• myPro1; -- 这种没有 EXECUTE 或 EXEC 的执行方式必须位于批处理中的第一条语句
• EXEC myPro1; -- 这种格式通常用于嵌入到其他语言中
• EXECUTE myPro1; -- 这种格式通常用于嵌入到其他语言中
[例 9.2] (带参数的存储过程)对于例 9.1, 进一步要求能够按照成绩段来查询学生的相关信息. 满足本例要求的存储过程需要带参数, 用于界定成绩段. 该存储过程定义的代码如下:
- USE MyDatabase;
- GO
CREATE PROCEDURE myPro2 -- 定义带两个参数的存储过程
@mingrade numeric(3,1) = 60, -- 参数 @mingrade 的默认值为 60
@maxgrade numeric(3,1) -- 参数 @maxgrade 没有设置默认值
AS
-- 查询平均成绩在 @mingrade 到 @maxgrade 之间的学生信息
- SELECT s_no, s_name, s_avgrade, s_dept
- FROM student
- WHERE s_avgrade>= @mingrade AND s_avgrade <= @maxgrade;
- GO
Ø 上述存储过程带有两个参数, 所以调用该过程时必须为之指定相应的参数值. 对于有默认值的参数, 如果不指定参数值, 则使用默认值, 但调用格式要正确. 例如, 对于存储过程 myPro2, 可通过执行下列语句来查询平均成绩在 60 到 90 分之间的学生信息(它们都是等价的):
- EXEC myPro2 60, 90;
- EXEC myPro2 @mingrade = 60, @maxgrade = 90;
- EXEC myPro2 @maxgrade = 90, @mingrade = 60;
EXEC myPro2 @maxgrade = 90; -- 参数 @mingrade 使用默认值 60
但如果试图使用下列方式来执行过程 myPro2, 则是错误的或与题意相背:
EXEC myPro2 90; -- 错误的调用格式, 少了一个参数
EXEC myPro2 90, 60; -- 能成功调用, 但与题意相背
[例 9.3] (带通配符参数的存储过程)创建一个存储过程, 使之能够按照姓名模糊查询并列出学生的学号, 姓名和平均成绩; 如果在调用时不带参数, 则列出所有学生的相关信息.
该存储过程使用带通配符的方法来实现, 其代码如下:
- USE MyDatabase;
- GO
- CREATE PROCEDURE myPro3
- @s_name varchar(8) = '%'
- AS
- SELECT s_no, s_name, s_avgrade, s_dept
- FROM student
- WHERE s_name LIKE @s_name;
- GO
Ø 调用该过程时, 如果带参数值则按姓名进行模糊查询, 如果不带参数值则列出所有学生的相关信息.
[例子] 下列语句将列出所有的姓 "王" 的学生信息:
EXEC myPro3 '王 %';
Ø 而执行下列语句后则列出所有学生的学号, 姓名和平均成绩:
EXEC myPro3;
[例 9.4] (带 OUTPUT 参数的存储过程)创建一个存储过程, 使之能够求出所有学生成绩的总和以及女学生成绩的总和.
OUTPUT 参数可以从存储过程中 "带回" 返回值, 因此利用 OUTPUT 参数可以让存储过程具有返回值功能.
本例中的存储过程要求有两个返回结果, 因此在定义存储过程时需要声明带两个 OUTPUT 参数.
定义该过程的代码如下:
- USE MyDatabase;
- GO
- CREATE PROCEDURE myPro4
@ s_total real OUTPUT, -- 声明 OUTPUT 参数
@ s_total _female real OUTPUT, -- 声明 OUTPUT 参数
AS
SELECT @ s_total =SUM(s_avgrade) -- 求所有学生成绩总和
FROM student;
SELECT @ s_total _female =SUM(s_avgrade) -- 求女学生成绩总和
- FROM student
- WHERE s_sex='女'
- GO
Ø 对于带 OUTPUT 参数的存储过程, 其调用方法与其他的存储过程的调用方法有所不同. 首先要声明相应的变量来存放返回结果, 然后在调用过程的时候要带关键字 OUTPUT, 否则无法将返回结果保存下来.
[例子] 要获取存储过程 myPro4 返回的结果并打印出来, 相应的代码如下:
DECLARE @total real, @total_female real;
EXEC myPro4 @total OUTPUT, @total_female OUTPUT; -- 调用时要带关键字 OUTPUT
- print @total;
- print @total_female;
[例 9.5] (加密存储过程)创建一个加密的存储过程.
Ø 加密存储过程是指在存储过程被创建后对保存在服务器端的过程文本代码进行加密, 从而无法使用文本编辑器来查看代码.
Ø 加密存储过程的方法很简单, 只要在定义时使用 WITH ENCRYPTION 子句即可. 以下是一个加密存储过程的定义代码:
- USE MyDatabase;
- GO
- CREATE PRO CEDURE myPro5 WITH ENCRYPTION
- AS
- SELECT s_no, s_name, s_avgrade, s_dept
- FROM student;
- GO
Ø 一个存储过程的定义文本可以用系统存储过程 sp_helptext 来查看. 但执行下列语句后, 会显示对象已加密的信息, 这表示 myPro5 的定义文本已经被加密:
EXEC sp_helptext myPro5;
9.1.4 存储过程的修改和删除
1. 修改存储过程
Ø 存储过程的修改可用 ALTER PROCEDURE 语句来实现, 修改后用户对该存储过程拥有的权限并没有发生改变.
Ø ALTER PROCEDURE 语句的语法如下:
- ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
- [ { @parameter [ type_schema_name. ] data_type }
- [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
- [ WITH <procedure_option> [ ,...n ] ]
- [ FOR REPLICATION ]
- AS
- { <sql_statement> [ ...n ] | <method_specifier> }
- <procedure_option> ::=
- [ ENCRYPTION ]
- [ RECOMPILE ]
- [ EXECUTE_AS_Clause ]
- <sql_statement> ::= { [ BEGIN ] statements [ END ] }
- <method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name
注意: 如果原来的存储过程在定义时使用了 WITH ENCRYPTION 或 WITH RECOMPILE 选项, 那么只有在 ALTER PROCEDURE 语句中也选择了这些选项, 这些选项才有效; 另外, 使用 ALTER PROCEDURE 修改后, 原过程的权限和属性将保持不变.
Ø [例 9.6] 对例 9.3 创建的存储过程 myPro3 进行修改, 使之能够按照姓名 (s_name) 或系别 (s_dept) 进行查询.
该修改操作可用下列的 ALTER PROCEDURE 来实现.
- ALTER PROCEDURE myPro3
- @s_name varchar(8) = '赵 %',
- @s_dept varchar(50) = '%'
- AS
- SELECT s_no, s_name, s_avgrade, s_dept
- FROM student
- WHERE s_name LIKE @s_name OR s_dept LIKE @s_dept;
- GO
修改后的过程与原来过程的权限完全一样. 不同的是它除了可以按姓名查询外, 还可以按系别查询.
Ø 在 SSMS 中修改存储过程的方法是, 在对象资源管理中右击要修改的存储过程所对应的节点, 并在弹出的菜单中选择 "修改" 命令, 然后在打开的查询编辑器窗口中修改过程的定义代码即可. 但对加密存储过程, 则无法用这种方法修改.
2. 删除存储过程
Ø 当一个存储过程不再使用时, 就应该将它从数据库中删除. 删除一个存储过程的 SQL 语句是 DROP PROCEDURE. 实际上, 在前面介绍的例子中已经多次用到. 其语法如下:
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]
从该语法中可以看出, 一条 DROP PROCEDURE 语句可以同时删除一个或多个存储过程.
[例子] 同时删除过程 myPro1, myPro2, myPro3, 可使用下列语句来完成:
DROP PROCEDURE myPro1, myPro2, myPro3;
Ø 也可以在 SSMS 中删除一个存储过程. 方法是: 在对象资源管理器中右击要删除的存储过程所对应的节点, 然后在弹出的菜单中选择 "删除" 命令, 最后根据提示删除存储过程.
Ø 注意, 当一个存储过程被删除以后, 所有用户对其拥有的操作权限也将全部被删除.
• 9.2 触发器
9.2.1 关于触发器
Ø 触发器是数据库服务器中发生事件时自动执行的一种特殊的存储过程. 与一般存储过程不同的是, 触发器不是被调用执行, 而是在相应的事件发生时激发执行的, 并且不能传递参数和接受参数. 它与数据表关系密切, 一般用于实现比较复杂的数据完整性规则, 检查数据的有效性, 实现对用户操作和数据状态的实时监控, 实现数据库的一些管理任务和其他的一些附加功能等.
Ø 触发器执行的前提是要有相应事件的发生, 这些事件主要是针对数据表. 在 SQL 语言中, 引发事件的语句主要是 DML 和 DDL 语句, 因此又有 DML 事件和 DDL 事件, 以及 DML 触发器和 DDL 触发器之称. 另外, 自从 SQL Server 2005 开始, SQL Server 增加了一类新的触发器 --LOGON 触发器(登录触发器). 利用登录触发器可以实现对登录用户的锁定, 限制和跟踪等.
1. DML 触发器
Ø 数据库操纵语言 (DML) 主要包含 INSERT,UPDATE,DELETE 等语句. 这些语句作用于数据表或视图的时候, 将产生相应的事件 --DML 事件. 此类事件一旦发生可引起相关触发器的执行, 因此这类事件通常称为 DML 事件, 相应的触发器称为 DML 触发器. 也可以这样理解, DML 触发器是在运行 DML 语句时由于产生 DML 事件而被执行的一类触发器.
Ø 根据触发器的执行与触发事件发生的先后关系, 又可以将 DML 触发器分为 AFTER 触发器和 INSTEAD OF 触发器.
(1)AFTER 触发器: 在 DML 触发事件发生后才激发执行的触发器, 也就是说, 先执行 INSERT,UPDATE,DELETE 语句然后才执行 AFTER 触发器. 这类触发器只适用于数据表, 不适用于视图. AFTER 触发器一般用于检查数据的变动情况, 以便采取相应的措施. 例如, 如发现错误, 将拒绝或回滚更改的数据;
(2)INSTEAD OF 触发器:"INSTEAD OF" 的中文意思就是 "代替" 之意, 由此不难理解: INSTEAD OF 触发器是在 DML 触发事件发生之前 (即数据被更新之前) 执行的, 这种执行将代替 DML 语句的执行. 也就是说, INSTEAD OF 触发器是在 DML 触发事件发生之前执行, 并且取代相应的 DML 语句(INSERT,UPDATE 或 DELETE 语句), 转而去执行 INSTEAD OF 触发器定义的操作(此后不再执行此 DML 语句).INSTEAD OF 触发器既适用于数据表, 也适用于视图. 但对同一个操作只能定义一个 INSTEAD OF 触发器.
如果根据触发事件的类型划分, DML 触发器通常又可以分为 INSERT 触发器, UPDATE 触发器和 DELETE 触发器:
Ø INSERT 触发器: 执行 INSERT 语句而激发执行的触发器;
Ø UPDATE 触发器: 执行 UPDATE 语句而激发执行的触发器;
Ø DELETE 触发器: 执行 DELETE 语句而激发执行的触发器.
2. DDL 触发器
Ø DDL 触发器是一种由执行 DDL 语句产生触发事件而触发执行的触发器. DDL 语句包括 CREATE,ALTER,DROP,GRANT,DENY,REVOKE 和 UPDATE STATISTICS 等语句.
Ø 与 DML 触发器不同的是, DDL 触发器的触发事件是执行 DDL 语句而引起的事件, 这种触发器是在触发事件发生后执行的; 而 DML 触发器的触发事件则是由执行 DML 语句引起的, 可在事件发生前或发生后执行. 另外, DDL 触发器的作用域不是架构, 因而不能使用 OBJECT_ID 来查询有关 DDL 触发器的元数据. DDL 触发器可用于执行数据库级的管理任务, 如审核和规范数据库操作等.
Ø DML 触发器的触发事件类型比较简单, 主要包括 INSERT,DELETE 和 UPDATE 等三种事件. 但 DDL 触发器的触发事件就比较多, 表 9.1 列了出常用的几种事件. 记住这几种事件对以后的触发器编程很有帮助.
3. LOGON 触发器(登录触发器)
Ø 登录触发器是 SQL Server 2005 开始新增加的一类为响应 LOGON 事件 (登录) 而激发执行的触发器. 也就是说, 只要有用户登录, 登录触发器即可激发执行. 因此, 通过登录触发器可以知道谁登录了服务器以及何时登录的, 并可以实现如何跟踪用户的活动, 还可以限制特定用户只能在特定时间段登录等.
Ø 触发事件对触发器来说是关键的, 所以许多时候又用引发触发事件的 SQL 语句来对触发器进行分类和命名.
[例子] INSERT 触发器, DELETE 触发器, UPDATE 触发器等. 但这种分类不是严格的, 只是为阐明问题之便.
9.2.2 创建触发器
1. 创建 DML 触发器
创建 DML 触发器的 SQL 语法如下:
- CREATE TRIGGER [ schema_name. ]trigger_name
- ON {
- table | view
- }
- [ WITH <dml_trigger_option> [ ,...n ] ]
- {
- FOR | AFTER | INSTEAD OF
- }
- {
- [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]
- }
- [ WITH APPEND ]
- [ NOT FOR REPLICATION ]
- AS {
- sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier [ ; ]>
- }
- <dml_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
- <method_specifier> ::= assembly_name.class_name.method_name
参数说明如下:
Ø trigger_name: 设置触发器的名称, 但不能以 #或## 开头.
Ø schema_name: 设置触发器所属架构的名称.
Ø table | view: 执行 DML 触发器的表或视图, 也分别称为触发器表或触发器视图.
Ø WITH ENCRYPTION: 选择该子句, 则表示对触发器文本进行加密.
Ø EXECUTE AS: 指定用于执行该触发器的安全上下文, 即设置操作权限.
Ø AFTER: 表示定义 AFTER 触发器, 即 DML 触发器在触发事件发生后执行. 如果仅指定 FOR 关键字, 则默认使用 AFTER.
Ø INSTEAD OF: 表示定义 INSTEAD OF 触发器, 即 DML 触发器在触发事件发生之前执行.
Ø { [DELETE] [,] [INSERT] [,] [UPDATE] }: 指定触发事件, 如果选择了 DELETE, 则表示创建 DELETE 触发器, 其他类推.
Ø WITH APPEND: 指定添加一个与当前触发器类型相同的另外一个触发器. 该子句不适用于 INSTEAD OF 触发器. 该功能在未来中将被删除, 建议不要使用.
Ø NOT FOR REPLICATION: 该选项用于指示复制代理修改到触发器表时不执行触发器.
Ø sql_statement:SQL 语句.
Ø <method_specifier>: 只适用于 CLR 触发器, 指定程序集与触发器绑定的方法.
Ø 如果在 CREATE TRIGGER 语句中选择了 INSERT ,UPDATE 或 DELETE 选项, 则表示创建 INSERT,UPDATE 或 DELETE 触发器. 对于这类触发器(DML 触发器), 有两种临时表与它们有着密切的联系, 它们是表 DELETED 和表 INSERTED. 这两种临时表都是在触发器执行时被创建, 执行完毕后被删除. 对它们的维护和管理是由 SQL Server 自动完成, 用户不能对这两个表进行直接操作.
Ø 具体地, 在执行 INSERT 触发器时创建表 INSERTED, 执行 DELETE 触发器时创建表 DELETED, 执行 UPDATE 触发器时则同时创建表 INSERTED 和表 DELETED, 其中表 INSERTED 保存了更新的数据记录, 表 DELETED 则保存更新前的数据记录(不受到更新影响的记录不含在其中).
Ø SQL Server 对这两个表的操作过程如下:
ü 表 INSERTED: 在执行 INSERT 或 UPDATE 语句时, 对用于插入或用于更新的数据记录拷贝一个副本, 并将该副本保存到表 INSERTED 中. 可见, 表 INSERTED 是触发器表被插入或被更新后的一个子集.
ü 表 DELETED: 在执行 DELETE 和 UPDATE 语句时, 将触发器表中被删除或被更新的数据记录拷贝到表 DELETED 中. 可见, 表 DELETED 和触发器表是不相交的(不会含有相同的记录).
[例 9.7] 创建一个触发器, 使之拒绝执行 UPDATE 操作, 并输出 "对不起, 您无权修改数据!".
Ø 这是一个 INSTEAD OF 触发器, 其实现代码如下:
- USE MyDatabase
- GO
- CREATE TRIGGER myTrigger1
- ON student
- INSTEAD OF UPDATE
- AS
- BEGIN
- PRINT '对不起, 您无权修改数据!';
- END
- GO
Ø 该触发器的作用是, 执行对表 student 的 UPDATE 操转为执行触发器 myTrigger1(输出 "对不起, 您无权修改数据!"), 而不再执行此 UPDATE 操作, 因此表 student 中的数据并未受到该 UPDATE 操作的任何影响.
[例 9.8] 假设表 student 是记录学生的一些注册登记信息, 表 SC 则是记录注册后的学生的选课信息. 有的学生试图不注册而直接选课(显然, 在实际中是不允许出现这种情况的), 因此一个管理系必须能够杜绝这一点. 这就涉及到两个表之间的约束问题, 以下定义一个 AFTER 触发器来实现这种约束.
- USE MyDatabase
- GO
- CREATE TRIGGER myTrigger2 ON SC
- AFTER INSERT
- AS
- BEGIN
- DECLARE @s_no char(8), @n int;
SELECT @s_no = P.s_no -- 将正在插入的记录的 s_no 字段值保存在 @s_no 中
- FROM SC AS P INNER JOIN INSERTED AS I
- ON P.s_no = I.s_no;
SELECT @n = COUNT(*) -- 在表 student 中查找是否有 s_no 字段值等于 @s_no 的学生
- FROM student
- WHERE s_no = @s_no;
- IF @n <> 1
- BEGIN
- RAISERROR ('该学生没有注册, 选课无效.', 16, 1);
ROLLBACK TRANSACTION; -- 回滚(撤销前面的插入操作)
- END
- ELSE PRINT '成功插入数据';
- END
- GO
Ø 上述触发器通过使用内查询来找出当前插入记录的 s_no 字段值, 并将该值保存在变量 @s_no 中. 其中, 使用了临时表 INSERTED, 该表包含了 INSERT 语句中已插入的记录. 然后根据变量 @s_no 的值在表 student 中进行查询, 如果存在这样的学生则函数 COUNT(*)的值为 1, 否则为 0. 最后根据函数 COUNT(*)的值来决定插入的选课信息是否有效.
[例 9.9] 出于某种原因(如学生因退学, 出国而取消学籍), 有时候需要将表 student 中的记录删除, 这时也应该将表 SC 中对应学生的选课信息删除, 以保持数据库的完整性. 这种完整性的保持可以通过定义如下的 AFTER 触发器来实现.
- USE MyDatabase
- GO
- CREATE TRIGGER myTrigger3 ON student
- AFTER DELETE
- AS
- BEGIN
- DECLARE @s_no char(8);
- SELECT @s_no = I.s_no
- FROM DELETED AS I;
- DELETE FROM SC
- WHERE s_no = @s_no;
- END
- GO
该触发器的作用就是, 当在表 student 中删除某一条件记录时, 表 SC 中与该记录对应的记录 (字段 s_no 值相同的记录) 将自动被删除, 以保持两个表中数据的参照完整性.
2. 创建 DDL 触发器
Ø 创建 DDL 触发器的语法如下:
- CREATE TRIGGER trigger_name
- ON {
- ALL SERVER | DATABASE
- }
- [ WITH <ddl_trigger_option> [ ,...n ] ]
- {
- FOR | AFTER
- } {
- event_type | event_group
- } [ ,...n ]
- AS {
- sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier> [ ; ]
- }
- <ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
- <method_specifier> ::= assembly_name.class_name.method_name
Ø 其参数意义基本同 DML 触发器. 此外, 其特有的参数包含以下几种:
ü DATABASE: 将触发器的作用域指定为当前数据库, 这时只要数据库中出现 event_type 或 event_group, 就会激发该触发器.
ü ALL SERVER: 将触发器的作用域指定为当前服务器, 这时只要在服务器上出现 event_type 或 event_group 即可激发该触发器.
ü event_type | event_group: 分别为 SQL 语言事件的名称和事件分组的名称.
[例 9.10] 创建一个触发器, 用于禁止在当前数据库中删除任何的数据表.
Ø 该触发器是一个 DDL 触发器, 其作用范围是整个数据库. 其实现代码如下:
- USE MyDatabase
- IF EXISTS (SELECT * FROM sys.triggers
- WHERE parent_class = 0 AND name = 'myTrigger4')
DROP TRIGGER myTrigger4 ON DATABASE -- 删除已存在的同名触发器
- GO
- CREATE TRIGGER myTrigger4
- ON DATABASE
- FOR DROP_TABLE, ALTER_TABLE
- AS
- PRINT '禁止删除或修改数据库中的任何数据表!';
- ROLLBACK;
- GO
Ø 由于 DDL 触发器不能使用 OBJECT_ID 来查询有关 DDL 触发器的元数据, 所以只能通过查询系统数据表 sys.triggers 的方法来判断触发器是否存在.
Ø 该触发器创建以后, 发出删除或修改数据表的任何命令都是被禁止的.
[例 9.11] 创建一个触发器, 使之能够禁止在服务器上创建任何服务器登录.
Ø 该触发器的作用范围是整个服务器, 其实现代码如下:
- IF EXISTS (SELECT * FROM sys.server_triggers
- WHERE name = 'myTrigger5')
DROP TRIGGER myTrigger5 -- 删除已存在的同名触发器
- ON ALL SERVER
- GO
- CREATE TRIGGER myTrigger5
- ON ALL SERVER
- FOR CREATE_LOGIN
- AS
- PRINT '禁止创建服务器登录.'
- ROLLBACK;
- GO
9.2.3 修改触发器
Ø 触发器的修改是由 ALTER TRIGGER 语句来完成. 但修改不同类型的触发器, ALTER TRIGGER 语句的语法是不相同的.
Ø 以下分别是修改 DML 触发器和 DDL 触发器的 SQL 语句:
ALTER TRIGGER schema_name.trigger_name -- 修改 DML 触发器
- ON ( table | view )
- [ WITH <dml_trigger_option> [ ,...n ] ]
- ( FOR | AFTER | INSTEAD OF )
- {
- [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ]
- }
- [ NOT FOR REPLICATION ]
- AS {
- sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier> [ ; ]
- }
- <dml_trigger_option> ::= [ ENCRYPTION ] [ <EXECUTE AS Clause> ]
- <method_specifier> ::= assembly_name.class_name.method_name
ALTER TRIGGER trigger_name -- 修改 DDL 触发器
- ON {
- DATABASE | ALL SERVER
- }
- [ WITH <ddl_trigger_option> [ ,...n ] ]
- {
- FOR | AFTER
- } {
- event_type [ ,...n ] | event_group
- }
- AS {
- sql_statement [ ; ] | EXTERNAL NAME <method specifier> [ ; ]
- }
- }
- <ddl_trigger_option> ::= [ ENCRYPTION ] [ <EXECUTE AS Clause> ]
- <method_specifier> ::= assembly_name.class_name.method_name
Ø 其中涉及的参数与触发器定义语法中的参数一样. 注意, 不能为 DDL 触发器指定架构 schema_name.
Ø 修改触发器的优点:
主要是用户拥有对它的操作权限不会因为对触发器的修改而发生改变. 另外, 如果原来的触发器定义时使用 WITH ENCRYPTION 或 WITH RECOMPILE 选项创建的, 只有在 ALTER TRIGGER 中也包含这些选项时, 这些选项才有效.
[例 9.12] 希望修改例 9.11 中创建的触发器 myTrigger5, 使之由 "禁止创建服务器登录" 改为 "禁止创建数据库".
对于这个修改操作, 我们可以用下面的 ALTER TRIGGER 语句来完成:
- ALTER TRIGGER myTrigger5
- ON ALL SERVER
- FOR CREATE_DATABASE
- AS
- PRINT '禁止在服务器上创建数据库.'
- ROLLBACK;
- GO
9.2.4 禁用和删除触发器
1. 禁用和启用触发器
Ø 有时候 (特别是在调试阶段) 我们并不希望频繁地触发执行一些触发器, 但又不能将之删除, 这时最好先禁用这些触发器.
Ø 禁用一段时间以后, 一般还需重新启用它, 这又要涉及到触发器启用的概念.
Ø 以下分别是禁用和启用触发器的 SQL 语法:
- DISABLE TRIGGER {
- [ schema. ] trigger_name [ ,...n ] | ALL
- }
- ON {
- object_name | DATABASE | ALL SERVER
- } [ ; ]
- ENABLE TRIGGER {
- [ schema_name . ] trigger_name [ ,...n ] | ALL
- }
- ON {
- object_name | DATABASE | ALL SERVER
- } [ ; ]
u 参数说明如下:
Ø trigger_name: 要禁用的触发器的名称.
Ø schema_name: 触发器所属架构的名称. 但 DDL 触发器没有架构.
Ø ALL: 如果选择该选项, 则表示对定义在 ON 子句作用域中的所有触发器全部禁用.
Ø object_name: 触发器表或视图的名称.
Ø DATABASE: 将作用域设置为整个数据库.
Ø ALL SERVER: 将作用域设置为整个服务器.
[例 9.13] 对例 9.12 修改后得到的用于禁止在当前服务器中创建数据库的 DDL 触发器 myTrigger5, 我们可以使用列的 SQL 语句来禁用它.
DISABLE TRIGGER myTrigger5 ON ALL SERVER;
如果将上述语句中的 "myTrigger5" 改为 "ALL", 则表示禁用所有定义在服务器作用域中的触发器. 这样就可以避免一个一个地去执行禁用操作.
Ø 以下是启用触发器 myTrigger5 的语句:
ENABLE TRIGGER myTrigger5 ON ALL SERVER;
Ø 如果要启用所有定义在服务器作用域中的触发器, 则可以使用下列的语句来完成:
ENABLE TRIGGER ALL ON ALL SERVER;
[例 9.14] 以下定义了一个 DML 触发器, 它不允许对表 student 进行更新操作:
- USE MyDatabase
- GO
- CREATE TRIGGER myTrigger6
- ON student
- INSTEAD OF UPDATE
- AS
- BEGIN
- RAISERROR ('对表 student 进行更新!', 16, 10)
- ROLLBACK;
- END
- GO
Ø 如果要禁用该 DML 触发器, 则可以用下列的 DISABLE TRIGGER 语句来完成:
DISABLE TRIGGER myTrigger6 ON student;
Ø 重新启用 myTrigger6 则用下列语句完成:
ENABLE TRIGGER myTrigger6 ON student;
Ø 如果将上面语句中的 "myTrigger6" 改为 "ALL", 则表示启用所有作用在表 student 上的触发器.
2. 删除触发器
Ø 当确信一个触发器不再使用时, 应当将之删除. DML 触发器和 DDL 触发器的删除方法有所不同, 以下分别是删除这两种触发器的 SQL 语法:
DROP TRIGGER schema_name.trigger_name [ ,...n ] [ ; ] -- 删除 DML 触发器
DROP TRIGGER trigger_name [ ,...n ] ON { DATABASE | ALL SERVER } [ ; ] -- 删除 DDL 触发器
Ø 在删除 DDL 触发器时需要指定触发器名称和作用域(即, 是 DATABASE 还是 ALL SERVER), 而删除 DML 触发器时则只需指定其名称.
来源: http://www.bubuko.com/infodetail-3098939.html