触发器

触发器

1. 触发器的概念

    触发器是一种特殊类型的存储过程。当表中数据被修改时，SQL Server将自动执行触发器。使用触发器可以实施较为复杂的数据完整性约束。

    在SQL Server中，数据库的表之间可以声明参照完整性约束，即主键(PRIMARYKEY)和外键(FOREIGNKEY)约束。在同一数据库中的简单参照完整性通常由主键和外键约束来实现。但是，由于声明参照完整性约束不能参照其他数据库中的对象，而对于数据库之间的参照完整性约束就只有通过触发器来实现。引用完整性约束与触发器，对数据操作构成了双重的有效性约束。

    当对一个同时具有约束和触发器的表进行数据修改操作时，SQL Server将先进行约束检查，然后再执行触发器检查。如果这些语句的操作符合约束条件，系统将完成数据操作，然后再激活触发器；如果该语句未能通过约束检查，将不执行该数据操作语句，也就不可能激活触发器。如果某语句通过了约束检查而未能通过触发器检查，触发器也将取消该语句已经执行完毕的操作。

    触发器的主要优点是：

    任何一种对触发器表中数据进行修改的操作（包括操作人员录入数据或其他应用程序的修改）都能自动激活触发器，从而触发对这些操作进行的完整性检查。

    (1)触发器能够实施的检查和操作比主键和外键约束、CHECK约束和规则对象等更为复杂。例如，CHECK约束只能根据一个逻辑表达式或同一个表中其他列值来检查指定列值的有效性，而使用触发器时则可以参照其他表中的列值。

    (2)触发器建立在表一级，它与特定的数据修改事件相对应。而INSERT、UPDATE和DELETE三种操作都可能导致数据的修改，所以SQL Server中的触发器可分为 INSERT触发器、UPDATE触发器和DELETE触发器三种。而且SQL Server 2000版本中允许对同一数据对象修改事件、同一个数据操作定义多个触发器，这在SQLServer7.0以前的版本中是被禁止的。

    触发器只能由数据库所有者创建。因为当为某一个表或表的列、行创建触发器时，表的访问方式及其对象之间的关系也随之改变了。也就是说，触发器的创建变动了数据库模式，所以创建触发器的权利应该保留给数据库的所有者，以防止普通用户无意间修改了系统格局。

2. 触发器的创建

触发器(TRIGGER)由Transact-SQL中的CREATE TRIGGER语句创建，语句中应该包含所定义触发器基于的表、激活触发器的数据操作事件名称以及触发器所要执行的相应操作。CREATE TRIGGER语句不允许带参数，也不允许被直接调用，而只能由系统自动激活。CREATE TRIGGER定义触发器有两种格式。第一种格式定义触发器的触发事件为INSERT、UPDATE、DELETE等操作。其语法格式如下：

    CREATE TRIGGER[owner,]trigger_name

    On[owner,]table_name

    [WITH ENCRYPTION]

    {

    FOR{[INSERT][,UPDATE][,DELETE]}

    [NOT FORREPLICATION]

    AS sql_statements

    }

    在上述语句中：

    ① trigger_name：为创建的触发器名称，它必须遵守SQL Server的命名规则，而且同一个数据库中不允许出现触发器名称相同的情况。

    ② table_name：为该语句中定义的触发器所基于的表，也称为触发表。

    ⑧ WITH ENCRYPTION选项：SQL Server将触发器的定义文本保存在系统分类表 syscomments中，选用此项要求SQL Server对该触发器定义文本进行加密存储，以防止第三方用户从syscomments表中读取该触发器定义文本的内容。

    ④ NOT FOR REPLICATION选项：说明在复制过程中出现修改触发表数据时，触发器不被激活。

    ⑤ FOR{[INSERT][,UPDATE][,DELETE]}：定义触发器的触发事件。当某一个触发器由表中的多个事件触发时，可以在INSERT、UPDATE、DELETE之间使用“，”符号作为间隔符来组合表示。

    ⑥sql_statements：定义触发器在触发事件发生时(被激活时)所要执行的操作。

    创建触发器的第二种定义格式中的触发事件仅限于INSERT和UPDATE。其语法格式为：

    CREATE TRIGGER[owner,]trigger_name

    On[owner,]table_name

        [WITH ENCRYPTION]

        {

          FOR{[INSERT][,UPDATE]}

    [NOT FOR REPLICATION]

          AS

            {IF UPDATE(column)

            [{AND|OR}UPDATE(column)]

            |

            IF(COLUMNS UPDATED(){bitwise_operator}updated_bitmask

    {comparison_operator column_bitmask}

             sql_statements

            }

        }

    在第二种格式中，需要用IF子句进一步说明触发器的触发条件仅限于IF语句中指定的列值被修改。IF子句指定的被修改列有以下两种格式：

        IF UPDATE(column)[{AND|OR}UPDATE(column)]

    和

        IF(COLUMNS_UPDATED(){位运算符)updated_bitmask(L[较运算符 column_bitmask)其中：

    ①column：指明激活触发器的触发条件中其数据将被INSERT或UPDATE操作修改的列。

    ②updated bitmask：定义了各位的位屏蔽值。

    ⑧column bitmask：定义了各待检测列的位屏蔽值的值。各列的屏蔽位为该列在触发表中的序号。

④COLUMNS_UPDATED()：用来检测指定列的列值是否被插入或修改。需要用位运算符、比较运算符和updated_bitmask(各位的位屏蔽值)、column__bitmask(各待检测列的位屏蔽值的值)等几个参数一起说明待检测列。假如一个触发表中有5个待检测列，则各列对应的屏蔽位为1～5，对应的位屏蔽值为20～24。例如，要在触发器中检测列1和列3的列值是否被修改的IF子句为：IF(COLUMNS UPDATED()&(1+4))<>0

注意：在使用WITH ENCRYPTION选项时需要注意两点：一是原始触发器的文件丢失，将不能从syscomments表中重新保存加密文本；二是文本加密后，在数据库升级为新版本时不能修改，也不能重新存入新版本中。也就是说，如果触发器文本有可能需要修改，就不要随便将其加密。

3. 触发器的使用、限制及嵌套

    当触发器执行时，将生成两个特殊的临时表：inserted和deleted。它们与触发表的结构相同，用于测试触发条件，用户无法直接更改其中的内容。

    SQL Server在执行INSERT语句时，将要插入触发表的新记录行同时插入inserted表中：执行DELETE语句时，将触发表中将要被删除的行放入deleted表中。在执行 UPDATE语句时，先从表中删除旧行，并将删除的行插入deleted表中。然后插入新行，并将新行插入inserted表中。

　　提示：在使用触发器前，有时需要检查一下全局变量@@rowcount，以确定使用信息前的状况。因为大多数触发器都要影响到多行甚至多个表中的数据。

向某一列插入NULL值或为列赋值时使用了DEFAULT关键字，都将激活触发器。在使用INSERT语句时如果没有为列提供列值，但向列中插入了隐含的NULL值或默认值时，也将激活触发器。

提示：对某一个数据操作语句，就算影响到多行数据，同一个触发器也只能被激活一次。

  3.1  创建触发器

1. 在“企业管理器”中创建。

2. 在“查询分析器”中执行SQL语句创建。

  3.2  INSERT触发器

    INSERT及UPDATE触发器经常用于检测触发器所监控表的列及其数据是否符合所定义的规则。它们可以在数据输入表之前，对其进行在定义引用完整性时无法完成的约束检验。

 

该文章转载自网络大本营：http://show.haoad.net/Info/2278.Html