SQL Server：触发器品读 -- Insert、 Delete、 Update（“updated ”是不存在的，不能想当然）

 

         对于触发器，好长时间没怎么用；知道要用到几个特殊的表，比如：deleted 表和 inserted 表。我还一直以为里面应该还有个"updated "表。事实不是这样的。让大家见笑了。说实话，很少用。如今用到了，用起来还真没那么顺手。写好触发器（里面用了updated），点击“Execute” - 竟然提示成功完成了！以为是正确的。也没怎么测，就提交发布了。QA就是QA，人家给测出来，发了bug。调试，抛出异常说什么“无效xx对象”；看不出程序错误；猜了一下应该是数据库方面出了问题，我们用了不少存储过程和触发器，可能就是这儿的问题了。为了更好更准的定位错误。我进行了跟踪，把对应的执行存储过程及参数值的sql语句拷贝出来，在查询分析器上执行了一下。果不其然是对应表触发器出了问题。里面用了一个“updated ”导致的。有点纳闷，为什么当时“成功完成了”，怎么程序运行时现在有出错了呢。难道创建和修改时只检查语法，还是怎地。不检查对象是否存在和有效吗？但有一点可以肯定的是“updated ”确实是不存在的，不能想当然啊。

        搜了一下，补充了一下自己的相对盲区（见笑了）。现把搜索结果整理一下，以便记忆和温故学习；也希望给同仁有所收获及重新认识。当然大牛们就不必看了，该干嘛干嘛去吧。O(∩_∩)O~

 

补习一：

触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

1.插入操作（Insert）
Inserted表有数据，Deleted表无数据

2.删除操作（Delete）
Inserted表无数据，Deleted表有数据

3.更新操作（Update）
Inserted表有数据（新数据），Deleted表有数据（旧数据）

链接地址：http://www.cnblogs.com/dikongpulu/archive/2007/04/21/722222.html

 

补习二（有点儿跑题）：

对行记录的判断应该用到Inserted表和Deleted表

ps：对列更新操作的判断的两个函数COLUMNS_UPDATED()和UPDATE(列名)

UPDATE(列名)是为了判断单一列是否被INSERT或是UPDATE语句修改过，返回true或false

COLUMNS_UPDATED()是对多个列是否被INSERT或是UPDATE语句修改的判断，返回的是位掩码，读取的顺序是从左到右，和一般的二进制读取顺序正好相反

eg：

对Order_Details表中的Quantity和UnitPrice做检测是否被修改

CREATE TRIGGER tr_OrderDetails ON [Order_Details] AFTER UPDATE 
AS 
IF (COLUMNS_UPDATED() = 12) 
BEGIN 
RAISERROR (''''Cannot change both UnitPrice and Quantity at the 
same time'''', 16, 1) 
ROLLBACK TRAN 
END 
GO

拿Order_Details表来说，COLUMN_UPDATED功能返回代表Order_Details表中字段的五个字节。只要第三和第四个字段被修改，上面这种情况就会发生，它检测这些位是不是已赋值为1.当第三和第四位都修改的话，它就如：00110。因这个位掩码代表2次幂，第一位表示1，第二位表示2，第三位表示4，第四位表示8，第五位表示16（是的，这是和正常二进制数相反的顺序）；因此只表示UnitPrice和Quantity字段被修改位掩码的值为00110，这个值为12（4+8）。

ps，这个Order_Details表共有5个字段，Quantity和UnitPrice分别在第三位和第四位

链接地址：http://www.cnblogs.com/zqstc/archive/2009/11/19/1605919.html

 

完全补习（完全跑题）：

一、触发器定义和创建

　　触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由个事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。　

　　触发器可以在查询分析器里创建，也可以在表名上点右键->“所有任务”->“管理触发器”来创建，不过都是要写 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.'

二、删除触发器

　　1.用查询分析器删除
　　　　在查询分析器中使用 drop trigger 触发器名称 来删除触发器。
　　　　也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称...
　　　　注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：

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

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

三、重命名触发器

　　1.用查询分析器重命名

　　　exec sp_rename 原名称, 新名称

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

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

四、INSERT、UPDATE、DELETE 详解

　   1、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 列

　　2、inserted、deleted
　　这是两个虚拟表，在进行INSERT、UPDATE或DELETE操作的时候自行创建的，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。

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

　　1、在查询分析器中运行：

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

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

六、sp_helptext 查看触发器内容

　　1、用查询分析器查看

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

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

　　2、用企业管理器查看

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

七、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 层。

　　设置直接递归

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

• 1、T-SQL语句：

  exec sp_dboption 'dbName', 'recursive triggers', true

• 2、用企业管理器：数据库上点右键->属性->选项。

设置间接递归、嵌套

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

• 1、T-SQL语句：

  exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许

• 2、用企业管理器：注册上点右键->属性->服务器设置。

九、触发器回滚

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

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

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

十、禁用、启用触发器

　　1、禁用触发器：

　　　　alter table 表名 disable trigger 触发器名称

　　2、启用触发器：

　　　　alter table 表名 enable trigger 触发器名称

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

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

十一、如何判断触发器被哪种状态触发

　　

　　　　　　　 if   not   exists(select   *   from   deleted)    
                    /*Insert*/  
            if   not   exists(select   *   from   Inserted)    
                    /*Delete*/  
            if   exists(select   *   from   Inserted)   and   exists(select   *   from   Deleted)  
                    /*Update*/   

 

望客友读了，有所收获。。。新年伊始，愿大家工作顺利，心想事成！！