sqlserver2000触发器开发文档_数据库_ASP编程网

来源:百度文库 编辑:神马文学网 时间:2024/03/28 17:58:33

sqlserver2000触发器开发文档

http://www.aspprogram.cn 19:36:13  asp学习网触发器是一种特殊的存储过程,类似于其它编程语言中的事件函数,SQL Server™ 允许为 INSERT、UPDATE、DELETE 创建触发器,当在表(视图)中插入、更新、删除记录时,触发一个或一系列 T-SQL 语句。 

本专题的目的并非是向您详尽地介绍触发器,而是系统地向您介绍触发器的常用知识与相关应用,并与您一起探讨应用经验,如果您需要了解触发器不常用的知识点,请参见 SQL Server™ 联机丛书(安装 SQL Server™ 时默认是安装的)。 

在视图上创建普通触发器可能会出现“对象无效”的错误,实际上,我们不能在视图上创建 FOR 触发器,而应该创建 INSTEAD OF 触发器。在 SQL Server™ 联机丛书中,是没有说触发器不能在视图上创建的, 并且在语法解释中表明:在 CREATE TRIGGER 的 ON 之后可以是视图。 然而,事实似乎并不是如此,很多专家也说触发器不能在视图上创建。 我也专门作了测试,的确如此,不管是普通视图还是索引视图,都无法在上面创建触发器. 但是无可厚非的是:当在临时表或系统表上创建触发器时会遭到拒绝。


FOR CREATE TRIGGER 语句

FOR 关键字之后可以跟 INSERT、UPDATE、DELETE 中的一个或多个,也就是说在其它情况下是不会触发触发器的, 包括 SELECT、TRUNCATE、WRITETEXT、UPDATETEXT。

TRUNCATE TABLE 和不带 WHERE 的 DELETE 功能是一样的,都是删除表中的所有数据,不过 TRUNCATE TABLE 速度更快,占用的日志更少,这是因为 TRUNCATE TABLE 直接释放数据页并且在事务日志中也只记录数据页的释放,而 DELETE 是一行一行地删除,在事务日志中要记录每一条记录的删除。 

那么可不可以用 TRUNCATE TABLE 代替不带 WHERE 的 DELETE 呢?在以下情况是不行的: 
1、要保留标识的情况下不能用 TRUNCATE TABLE,因为 TRUNCATE TABLE 会重置标识。 
2、需要使用触发器的情况下不能使用 TRUNCATE TABLE ,它不会激发触发器。 
3、对于由 FOREIGN KEY 约束引用的表(即主键所在的表,不是外键所在的表)不能使用 TRUNCATE TABLE。 
4、对于参与了索引视图的表不能使用 TRUNCATE TABLE ,注意指索引视图,并非普通视图。 

触发器内部语句出错时,前面对数据更改操作将会无效。 举个例子,在表中插入数据时触发触发器,而触发器内部此时发生了运行时错误,那么将返回一个错误值,并且拒绝刚才的数据插入。 

一个有趣的触发器应用 触发器回滚

我们看到许多注册系统在注册后都不能更改用户名,但这多半是由应用程序决定的, 如果直接打开数据库表进行更改,同样可以更改其用户名,在触发器中利用回滚就可以巧妙地实现无法更改用户名。 

use 数据库名 
go 
create trigger tr 
on 表名 
for update 
as 
if update(userName) 
rollback tran 
关键在最后两句,其解释为:如果更新了 userName 列,就回滚事务。 

不能在触发器中使用的语句 

触发器中可以使用大多数 T-SQL 语句,但如下一些语句是不能在触发器中使用的。 
CREATE 语句,如:CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。 
ALTER 语句,如:ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。 
DROP 语句,如:DROP DATABASE、DROP TABLE、DROP INDEX 等。 
DISK 语句,如:DISK INIT、DISK RESIZE。 
LOAD 语句,如:LOAD DATABASE、LOAD LOG。 
RESTORE 语句,如:RESTORE DATABASE、RESTORE LOG。 
RECONFIGURE 
说明:有人说不能用 TRUNCATE TABLE 语句,其实是可以的。 

递归、嵌套触发器 


递归触发器 

递归分两种,间接递归和直接递归。我们举例解释如下,假如有表1、表2名称分别为 T1、T2,在 T1、T2 上分别有触发器 G1、G2。 

间接递归:对 T1 操作从而触发 G1,G1 对 T2 操作从而触发 G2,G2 对 T1 操作从而再次触发 G1... 
直接递归:对 T1 操作从而触发 G1,G1 对 T1 操作从而再次触发 G1... 
嵌套触发器 

类似于间接递归,间接递归必然要形成一个环,而嵌套触发器不一定要形成一个环,它可以 T1->T2->T3...这样一直触发下去,最多允许嵌套 32 层。 

设置直接递归 

默认情况下是禁止直接递归的,要设置为允许有两种方法: 

T-SQL:exec sp_dboption 'dbName', 'recursive triggers', true 
EM:数据库上点右键->属性->选项。 
设置间接递归、嵌套 

默认情况下是允许间接递归、嵌套的,要设置为禁止有两种方法: 

T-SQL:exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许 
EM:注册上点右键->属性->服务器设置。 


递归触发器 递归分两种,间接递归和直接递归。我们举例解释如下,假如有表1、表2名称分别为 T1、T2,在 T1、T2 上分别有触发器 G1、G2。 间接递归:对 T1 操作从而触发 G1,G1 对 T2 操作从而触发 G2,G2 对 T1 操作从而再次触发 G1... 直接递归:对 T1 操作从而触发 G1,G1 对 T1 操作从而再次触发 G1... 嵌套触发器 类似于间接递归,间接递归必然要形成一个环,而嵌套触发器不一定要形成一个环,它可以 T1->T2->T3...这样一直触发下去,最多允许嵌套 32 层。 -------------------------------------------------------------------------------- 设置直接递归 默认情况下是禁止直接递归的,要设置为允许有两种方法: T-SQL:exec sp_dboption 'dbName', 'recursive triggers', true EM:数据库上点右键->属性->选项。 设置间接递归、嵌套 默认情况下是允许间接递归、嵌套的,要设置为禁止有两种方法: T-SQL:exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许 EM:注册上点右键->属性->服务器设置。 

多个触发器 

触发器的名称不同,触发事件相同(INSERT、UPDATE、DELETE),我们称为多个触发器。 

多个触发器之间的执行顺序并不确定。 

我个人认为应该避免使用多个触发器,因为它不利于维护。 

查看某个触发器的内容 


use 数据库名 
go 
exec sp_helptext '触发器名称' 
将会以表的样式显示触发器内容。 
除了触发器外,sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本 
用企业管理器查看 

在表上点右键->“所有任务”->“管理触发器”,选择所要查看的触发器 

查看当前数据库中有哪些触发器 

在查询分析器中运行: 

use 数据库名 
go 
select * from sysobjects where xtype='TR' 
sysobjects 保存着数据库的对象,其中 xtype 为 TR 的记录即为触发器对象。在 name 一列,我们可以看到触发器名称。 

删除触发器 


用查询分析器删除 
在查询分析器中使用 drop trigger 触发器名称 来删除触发器。 
也可以同时删除多个触发器:drop trigger 触发器名称,触发器名称... 
注意:触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在: 
if Exists(select name from sysobjects where name=触发器名称 and xtype='TR') 

用企业管理器删除 
在企业管理器中,在表上点右键->“所有任务”->“管理触发器”,选中所要删除的触发器,然后点击“删除”。 


重命名触发器 
用查询分析器重命名 
exec sp_rename 原名称, 新名称 
sp_rename 是 SQL Server™ 自带的一个存储过程,用于更改当前数据库中用户创建的对象的名称,如表名、列表、索引名等。 

用企业管理器重命名 
在表上点右键->“所有任务”->“管理触发器”,选中所要重命名的触发器,修改触发器语句中的触发器名称,点击“确定”。 

查看触发器的属性

存储过程 sp_helptrigger 用于查看触发器的属性。 

sp_helptrigger 有两个参数:第一个参数为表名;第二个为触发器类型,为 char(6) 类型,可以是 INSERT、UPDATE、DELETE,如果省略则显示指定表中所有类型触发器的属性。 

例: 

use 数据库名 
go 
exec sp_helptrigger tbl 

触发器更多语法 

INSTEAD OF 
执行触发器语句,但不执行触发触发器的 SQL 语句,比如试图删除一条记录时,将执行触发器指定的语句,此时不再执行 delete 语句。例: 
create trigger f 
on tbl 
instead of delete 
as 
insert into Logs... 

IF UPDATE(列名) 
检查是否更新了某一列,用于 insert 或 update,不能用于 delete。例: 
create trigger f 
on tbl 
for update 
as 
if update(status) or update(title) 
sql_statement --更新了 status 或 title 列 

inserted、deleted 
这是两个虚拟表,inserted 保存的是 insert 或 update 之后所影响的记录形成的表,deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例: 
create trigger tbl_delete 
on tbl 
for delete 
as 
declare @title varchar(200) 
select @title=title from deleted 
insert into Logs(logContent) values('删除了 title 为:' + title + '的记录') 
说明:如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时,所取得的值将会是 null。 

慎用触发器
触发器功能强大,轻松可靠地实现许多复杂的功能,为什么又要慎用呢。 触发器本身没有过错,但由于我们的滥用会造成数据库及应用程序的维护困难。 

在数据库操作中,我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作,比如删除 T1 表记录时期望删除 T2 表相关的记录,此时可以建立级联删除的关系,也可以为 T1 表建立触发器使同时删除 T2 表相关记录,也可以自定义存储过程删除 T1 和 T2 表的记录,也可以在应用程序中使用两个 SQL 语句来删除……到底用哪一种好呢,应该说我们建立通过建立关系来实现级联删除是最好的,除非更有高的需求。 

触发器还有一个用途可以用来保障数据的完整性,但同时规则、约束、默认值也可以保障数据完整性,到底哪一个好呢,一般说来,较为简单的完整性要求我们不应该使用触发器。两者在运行机制上也是有区别的,像规则、约束、默认值这些是在数据更改之前进行数据验证,而触发器是在数据更改之后进行验证(如果事务回滚,该表将不会产生变化)。 

总之,如果我们对触发器过分的依赖,就会造成遍地是程序的情况,因为触发器本身就需要别的程序给它一个触发条件,也就是说至少在两个地方存在着程序,同时我们抛弃了约束、默认值等而选用触发器,势必影响数据库的结构