sqlserver触发器

一、创建一个简单的触发器

触发器是一种特殊的存储过程，类似于事件函数，SQL?Server??允许为?INSERT、UPDATE、DELETE?创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列?T-SQL语句。

触发器可以在查询分析器里创建，也可以在表名上点右键->“所有任务”->“管理触发器”来创建，不过都是要写?T-SQL?语句的，只是在查询分析器里要先确定当前操作的数据库。

创建触发器用?CREATE?TRIGGER

CREATE?TRIGGER?触发器名称
ON?表名
FOR?INSERT、UPDATE?或?DELETE
AS
????T-SQL?语句
注意：触发器名称是不加引号的。

如下是联机丛书上的一个示例，当在?titles?表上更改记录时，发送邮件通知?MaryM。
CREATE?TRIGGER?reminder
ON?titles
FOR?INSERT,?UPDATE,?DELETE?
AS
???EXEC?master..xp_sendmail?'MaryM',?
??????'Don''t?forget?to?print?a?report?for?the?distributors.'

二、删除触发器

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

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

三、重命名触发器

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

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

四、more....

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。

五、查看数据库中所有的触发器

在查询分析器中运行：?

use?数据库名
go
select?*?from?sysobjects?where?xtype='TR'

sysobjects?保存着数据库的对象，其中?xtype?为?TR?的记录即为触发器对象。在?name?一列，我们可以看到触发器名称。

六、sp_helptext?查看触发器内容

用查询分析器查看

use?数据库名
go
exec?sp_helptext?'触发器名称'

将会以表的样式显示触发器内容。?
除了触发器外，sp_helptext?还可以显示?规则、默认值、未加密的存储过程、用户定义函数、视图的文本?

用企业管理器查看

在表上点右键->“所有任务”->“管理触发器”，选择所要查看的触发器存储过程?

七、sp_helptrigger?用于查看触发器的属性

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

例：

use?数据库名
go
exec?sp_helptrigger?tbl

?

八、递归、嵌套触发器

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

九、触发器回滚

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

use?数据库名
go
create?trigger?tr
on?表名
for?update
as
????if?update(userName)
????????rollback?tran

关键在最后两句，其解释为：如果更新了?userName?列，就回滚事务。

十、禁用、启用触发器

禁用：alter?table?表名?disable?trigger?触发器名称
启用：alter?table?表名?enable?trigger?触发器名称

如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!