sqlserver 触发器

概述SQLServer 触发器详解(10条) 一.不能在触发器中使用的语句 触发器中可以使用大多数 T-SQL 语句，但如下一些语句是不能在触发器中使用的。 CREATE 语句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。 ALTER 语句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。 DROP 语句，如：DRO

sqlServer 触发器详解(10条)

一.不能在触发器中使用的语句 触发器中可以使用大多数 T-sql 语句，但如下一些语句是不能在触发器中使用的。

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

二.创建触发器用 CREATE TRIGGER

CREATE TRIGGER 触发器名称

ON 表名

FOR INSERT、UPDATE 或 DELETE

AS

    T-sql 语句

注意：触发器名称是不加引号的。

如下是联机丛书上的一个示例，当在 Titles 表上更改记录时，发送邮件通知 MaryM。

CREATE TRIGGER reminder

ON Titles

FOR INSERT,UPDATE,DELETE

   EXEC master..xp_sendmail 'MaryM',

      'Don''t forget to print a report for the distributors.'

三.删除触发器

drop trigger 触发器名称,触发器名称...

注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：

if Exists(select name from sysobjects where name=触发器名称 and xtype='TR')

用企业管理器删除

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

四.重命名触发器

exec sp_rename 原名称,新名称

sp_rename 是 sql Server? 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。

用企业管理器重命名

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

五.复杂触发器

INSTEAD OF

执行触发器语句，但不执行触发触发器的 sql 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例：

create trigger f

on tbl

instead of delete

as

    insert into Logs...

IF UPDATE(列名)

检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：

on tbl

for update

    if update(status) or update(Title)

        sql_statement --更新了 status 或 Title 列

inserted、deleted

这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：

create trigger tbl_delete

for delete

    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 一列，我们可以看到触发器名称。

七. 查看触发器内容

exec sp_helptext '触发器名称'

将会以表的样式显示触发器内容。

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

用企业管理器查看

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

八. 触发器的扩展

1.存储过程 sp_helptrigger

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

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

例：

exec sp_helptrigger tbl

2.多个触发器

触发器的名称不同，触发事件相同（INSERT、UPDATE、DELETE），我们称为多个触发器。

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

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

3.递归和嵌套触发器

递归触发器

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

@H_403_12@间接递归：对 T1 *** 作从而触发 G1，G1 对 T2 *** 作从而触发 G2，G2 对 T1 *** 作从而再次触发 G1... 直接递归：对 T1 *** 作从而触发 G1，G1 对 T1 *** 作从而再次触发 G1...

嵌套触发器

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

设置直接递归

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

@H_403_12@T-sql：exec sp_dboption 'dbname','recursive triggers',true EM：数据库上点右键->属性->选项。

设置间接递归、嵌套

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

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

九.禁用启用触发器

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

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

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

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

十.触发器回滚

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

create trigger tr

on 表名

for update

    if update(username)

        rollback tran

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

补充：

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

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

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

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

总结

以上是内存溢出为你收集整理的sqlserver 触发器全部内容，希望文章能够帮你解决sqlserver 触发器所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。