关闭

sql 触发器 专题文档

926人阅读 评论(0) 收藏 举报

前面已经有一篇关于触发器的文档。触发器是关于sql server的重要内容。

sql server:触发器 基础文档整理

SQL SERVER数据库开发之触发器的应用

触发器是一种特殊的存储过程,类似于其它编程语言中的事件函数,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 ,注意指索引视图,并非普通视图。

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

[NextPage]

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

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

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 语句,其实是可以的。

[NextPage]

递归、嵌套触发器


递归触发器

递归分两种,间接递归和直接递归。我们举例解释如下,假如有表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:注册上点右键->属性->服务器设置。

[NextPage]

多个触发器

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

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

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

查看某个触发器的内容

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

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

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

在查询分析器中运行:

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

[NextPage]

删除触发器

用查询分析器删除
在查询分析器中使用 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

[NextPage]

触发器更多语法

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。

[NextPage]

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

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

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

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

 

 

 

http://www.86oo.com/html/176/5275.html

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:30951次
    • 积分:85
    • 等级:
    • 排名:千里之外
    • 原创:30篇
    • 转载:2篇
    • 译文:0篇
    • 评论:16条
    最新评论
    高手中的高手