苏飞论坛

标题: Sql Server触发器的原理及应用技巧 [打印本页]

作者: 微笑 时间: 2013-2-28 15:53
标题: Sql Server触发器的原理及应用技巧
   触发器是Sql Server数据库应用中的一个重要工具，其实也可以说是一种特殊的存储过程，应用的也非常广发。一般存储过程主要通过存储过程名称来直接调用，而触发器比较特殊则是通过事件触发执行的。触发器是基于一个表来创建并和一个或是多个数据库操作（例如 insert ， update  或是 delete ）相关联，我们可以看做是表的一部分吧。触发器与数据库的表紧密相关，比如当表执行了上变的inser， update， delete 等操作的时候，触发器就会自动执行。这也就是我整体对数据库的触发器的一个原理认识吧，具体的应用技巧下边再看。
   首先这里我先说下触发器的语法吧，我认为不管是学习哪们技术，前提都必须是先学习语法，有了语法再结合语法和用例来写出属于自己的Demo，这样的话很快就会把握这个技术，所以就不多说啥，我们一起开始学习下触发器的语法。
   [code=sql]CREATE TRIGGER  Trigger_Name
ON Table_Name | View_Name
[WITH ENCRYPTION]
{
   { {FOR | AFTER | INSTEAD OF} {[Insert][,][Update]}
   [WITH APPEND]
   [NOT FOR REPLICATION]
   AS
   [ {IF Update(Column)
   [{AND | OR} Update(Column)]
   [...n]
   | IF (COLUMNS_UPDATED(){bitwise_operator} updated_bitmast)
   {comparison_operator}column_bitmask[...n] }]
   sql_statement[...n]
   }
}[/code]

  参数：
Trigger_Name
      触发器名称，触发器名称必须符合标示符规则，并且在数据库中必须唯一，可以选择是否指定触发器所有者名称。

Table_Name | View_Name
      是在表或视图上执行触发器的表名称和视图名称，有时候也叫做触发器表或触发器试图，可以选是否指定表是试图的所有者名称。

[WITH ENCRYPTION]
      加密syscomments表中包含create Trigger语句文本的条目。使用 [WITH ENCRYPTION]的好处是可以防止讲触发器作为sql server赋值的一部分发布。

AFTER
   指定触发器只有在触发sql语句中指定的所有操作都已成功执行后激发。所有的应用级联操作和约束检查也必须成功执行完毕后才能执行该触发器。
注意，如果只指定for关键字，则after是默认的设置；还有就是不能在视图上设置after触发器。

INSTEAD OF
      指定执行触发器而不是执行sql语句，从而替代出发语句的操作。
      在表或视图上，每个Insert，delete 或是update语句最多可以定义一个instead of触发器，但是可以在每个具有instead of触发器的视图上定义视图。
注意：instead of 触发器不能在with check option的可更新视图上定义，如果想指定了with check option选项的可更新视图添加instead of触发器，sql server 将会产生一个错误，用户必须在使用Alter view删除选项后才能定义instead of触发器。

{ [ Insert ] [ , ] [ Update ] }
      是指定在表或视图上执行那些数据修改语句时将激活触发器的关键字，必须至少指定一个选项，在触发器定义中允许使用以任意顺序组合的这些关键字，如果指定的选项多余一个需要使用逗号分隔这些项。

WITH APPEND
   指定应该添加现有类型的其它触发器。只有当兼容级别是 65或更低时，才需要使用该可选子句。如果兼容级别是 70或更高，则不必使用 WITH APPEND子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CreateTRIGGER 的默认行为）。
注意：WITH APPEND 不能与 INSTEAD OF触发器一起使用，或者，如果显式声明 AFTER触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持WITH APPEND 和 FOR（将被解释为 AFTER）。

NOT FOR REPLICATION
      表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

AS ：是触发器要执行的操作。

sql_statement
   是触发器的条件和操作。触发器条件指定其它准则，以确定Delete、Insert 或 Update 语句是否导致执行触发器操作。

当尝试 Delete、Insert 或 Update操作时，Transact-SQL语句中指定的触发器操作将生效。
触发器可以包含任意数量和种类的 Transact-SQL语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的Transact-SQL 语句常常包含控制流语言。

Create TRIGGER语句中使用几个特殊的表：
   deleted 和 inserted是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要检索deleted 表中的所有值，请使用：
   Select *FROM deleted

n
是表示触发器中可以包含多条 Transact-SQL语句的占位符。对于 IF Update (column) 语句，可以通过重复 Update(column) 子句包含多列。

IF Update (column)
   测试在指定的列上进行的 Insert 或 Update操作，不能用于 Delete 操作。可以指定多列。因为在 ON子句中指定了表名，所以在 IF Update子句中的列名前不要包含表名。若要测试在多个列上进行的 Insert 或Update 操作，请在第一个操作后指定单独的 Update(column) 子句。在Insert 操作中 IF Update 将返回 TRUE值，因为这些列插入了显式值或隐性 (NULL) 值。

注意： IF Update (column) 子句的功能等同于IF、IF...ELSE 或 WHILE 语句，并且可以使用 BEGIN...END语句块。有关更多信息，请参见控制流语言。

可以在触发器主体中的任意位置使用 Update (column)。

column
是要测试 Insert 或 Update 操作的列名。该列可以是 SQL Server支持的任何数据类型。但是，计算列不能用于该环境中。有关更多信息，请参见数据类型。

IF (COLUMNS_UpdateD())
测试是否插入或更新了提及的列，仅用于 Insert 或 Update触发器中。COLUMNS_UpdateD 返回 varbinary位模式，表示插入或更新了表中的哪些列。

COLUMNS_UpdateD函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含8 列以上，则 COLUMNS_UpdateD返回多个字节，最左边的为最不重要的字节。在 Insert 操作中COLUMNS_UpdateD 将对所有列返回 TRUE值，因为这些列插入了显式值或隐性 (NULL) 值。

可以在触发器主体中的任意位置使用 COLUMNS_UpdateD。

好吧，我就先介绍这么多，具体的我也是很了解，只是根据自己的经验讲了一点点，不足之处大牛们提出，很感谢大家和我分享。接下来我会再总结下触发器使用的好处或是不足之处，，然后再分享出来，给大家提供更清晰的认识思路。

作者: 站长苏飞 时间: 2013-2-28 16:18
路过支持一下，另外建议楼主注意使用代码标签会更好看些
使用方法如下
http://www.sufeinet.com/thread-1942-1-1.html

作者: 微笑 时间: 2013-2-28 17:46

admin 发表于 2013-2-28 16:18
路过支持一下，另外建议楼主注意使用代码标签会更好看些
使用方法如下
http://www.sufeinet.com/thread-1 ...

就是没找到怎么用，自己在管理器写的copy过来的

作者: 微笑 时间: 2013-2-28 17:51

admin 发表于 2013-2-28 16:18
路过支持一下，另外建议楼主注意使用代码标签会更好看些
使用方法如下
http://www.sufeinet.com/thread-1 ...

跟本不行的，好不好，我刚编辑修改试了下，行不通，开始我也那么操作的，没效果然后才那样自己写的

作者: 微笑 时间: 2013-2-28 17:51

admin 发表于 2013-2-28 16:18
路过支持一下，另外建议楼主注意使用代码标签会更好看些
使用方法如下
http://www.sufeinet.com/thread-1 ...

跟本不行的，好不好，我刚编辑修改试了下，行不通，开始我也那么操作的，没效果然后才那样自己写的

作者: 站长苏飞 时间: 2013-2-28 18:04

微笑发表于 2013-2-28 17:51
跟本不行的，好不好，我刚编辑修改试了下，行不通，开始我也那么操作的，没效果然后才那样自己写的

你联系我QQ我帮你看看

欢迎光临苏飞论坛 (http://www.sufeinet.com/)