小议sql触发器的使用

触发器是SQL Server数据库应用中一个重要工具，是一种特殊类型的存储过程，应用非常广泛。一般存储过程主要通过存储过程名而被直接调用，触发器则是通过事件触发执行。触发器基于一个表来创建并和一个或多个数据修改操作（插入、更新或删除）相关联，可视作表的一部分。触发器与数据库中的表紧密相关，比如当对表执行INSERT、UPDATE或DELETE操作时，触发器就会自动执行。

SQL Server 包括两大类触发器：DML 触发器和 DDL 触发器。其中DDL 触发器是 SQL Server 2005 的新增功能，当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用该触发器；DML 触发器是指当数据库中发生数据操作语言 (DML) 事件时被调用。DML 事件包括在指定表或视图中修改数据的 INSERT、UPDATE或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。DML 触发器用于在数据被修改时强制执行业务规则，以及扩展 Microsoft SQL Server 2005 约束、默认值和规则的完整性检查逻辑。本文中所讲述的触发器主要是指DML 触发器。

DML触发器包括三种类型：AFTER 触发器、INSTEAD OF 触发器、CLR 触发器。在执行了 INSERT、UPDATE 或 DELETE 语句操作之后将会执行 AFTER 触发器，本文将以AFTER触发器为例来讲述触发器在保持数据完整性中的应用。

在创建触发器以前必须考虑到以下几个方面：
CREATE TRIGGER 语句必须是批处理的第一个语句;
表的所有者具有创建触发器的缺省权限,表的所有者不能把该权限传给其它用户;
触发器是数据库对象,所以其命名必须符合命名规则;
尽管在触发器的SQL 语句中可以参照其它数据库中的对象,但是,触发器只能创建在当前数据库中;
虽然触发器可以参照视图或临时表,但不能在视图或临时表上创建触发器,而只能在基表或在创建视图的表上创建触发器;
一个触发器只能对应一个表,这是由触发器的机制决定的;
尽管TRUNCATE TABLE 语句如同没有WHERE 从句的delete 语句,但是由于TRUNCATE TABLE 语句没有被记入日志,所以该语句不能触发delete 型触发器;
WRITETEXT 语句不能触发INSERT 或update 型的触发器。
当创建一个触发器时,必须指定触发器的名字,在哪一个表上定义触发器,激活触发 器的修改语句,如INSERT、 delete、 update。 当然两个或三个不同的修改语句也可 以都触发同一个触发器,如INSERT 和update 语句都能激活同一个触发器。

用管理工具Enterprise Manger 创建触发器
其操作步骤如下：
启动Enterprise Manger, 登录到指定的服务器上。
展开数据库,然后展开要在其上创建触发器的表所在的数据库,然后单击该表。
右击鼠标,在弹出菜单中选择ALL Tasks, 然后单击Manage Triggers…。
在名字框中选择新建, 在文本框中输入触发器文本.

单击“检查语法”检查语句是否正确。
单击应用, 在名称 下拉列表中会有新创建的触发器名字。
单击OK, 关闭窗口创建成功。

创建触发器语法如下：
CREATE TRIGGER [trigger_name] ON [dbo].[Table
]
FOR INSERT, update, delete
AS
Sql_statement

各参数的说明如下：

• trigger_name
  是用户要创建的触发器的名字触发器的名字,必须符合MS SQL Server 的命名规则,且其名字在当前数据库中必须是惟一的。
• Table
  是与用户创建的触发器相关联的表的名字,并且该表已经存在。
• WITH ENCRYPTION
  表示对包含有CREATE TRIGGER 文本的syscomments 表进行加密。
• AFTER
  表示只有在执行了指定的操作（INSERT、 delete、 update）之后触发器才被激活,执行触发器中的SQL 语句。若使用关键字FOR, 则表示为AFTER 触发器,且该类型触发器仅能在表上创建。
• INSTEAD OF
  请参看“12.8 INSTEAD OF 触发器”
• [delete] [,] [INSERT] [,] [update]
  关键字用来指明哪种数据操作将激活触发器。至少要指明一个选项,在触发器的定义中三者的顺序不受限制,且各选项要用逗号隔开。
• WITH APPEND
  表明增加另外一个已存在某一类型触发器。只有在兼容性水平（指某一数据库行为与以前版本的MS SQL Server 兼容程度）不大于65 时才使用该选项。
• NOT FOR REPLICATION
  表明当复制处理修改与触发器相关联的表时,触发器不能被执行。
• AS
  是触发器将要执行的动作。
• Sql_statement
  是包含在触发器中的条件语句或处理语句。触发器的条件语句定义了另外的标准来决定将被执行的INSERT、 delete、 update 语句是否激活触发器。
• IF update （column）
  用来测定对某一确定列是插入操作还是更新操作,但不与删除操作用在一起。
• IF （COLUMNS_updateD（））
  仅在INSERT 和update 类型的触发器中使用,用其来检查所涉及的列是被更新还是被插入。
• Bitwise_operatorj
  是在比较中使用的位逻辑运算符。
• Pdated_bitmask
  是那些被更新或插入的列的整形位掩码。例如,如果表T 包括C1, C2, C3, C4, C5五列。为了确定是否只有C2 列被修改,可用2 来做位掩码,如果想确定是否C1, C2, C3,C4 都被修改,可用14 来做位掩码。
• Comparison_operator
  是一比较操作符用“= ”表示检查在updated_bitmask 中定义的所有列是否都被更新,用“>” 表示检查是否在updated_bitmask 中定义的某些列被更新。

常见的触发器有三种：分别应用于Insert , Update , Delete 事件。(SQL Server 2000定义了新的触发器，这里不提）

   我为什么要使用触发器？
     比如，这么两个表：
   Create Table Student(          --创建学生表
     StudentID int primary key,      --学号，定义主键约束
     ....
     )

   Create Table BorrowRecord(          --创建借书记录表
     BorrowRecord int identity(1,1),      --自动增长流水号
     StudentID    int ,              --学号
     BorrowDate    datetime,            --借出时间
     ReturnDAte    Datetime,            --归还时间
     ...
   )

   用到的功能有:
     1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
     2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。
   等等。

   这时候可以用到触发器。对于1，创建一个Update触发器：

   Create Trigger truStudent
     On Student
     for Update    --触发器类型--Update
   -------------------------------------------------------
   --Name:truStudent
   --func:更新BorrowRecord 的StudentID，与Student同步。
   --Use :None
   --User:System
   --Author: wp_love
   --Date : 2003-4-16
   --Memo : 临时写写的，给大家作个Sample。没有调试阿。
   -------------------------------------------------------
   As
     if Update(StudentID)
     begin

     Update BorrowRecord
       Set br.StudentID=i.StudentID
       From BorrowRecord br , Deleted d ,Inserted i
       Where br.StudentID=d.StudentID

     end     
          
   理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
   一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。

   对于2，创建一个Delete触发器
   Create trigger trdStudent
       On Student
       for Delete
   -------------------------------------------------------
   --Name:trdStudent
   --func:同时删除 BorrowRecord 的数据
   --Use :None
   --User:System
   --Author:
   --Date : 2003-4-16
   --Memo : 临时写写的，给大家作个Sample。没有调试阿。
   -------------------------------------------------------
   As
     Delete BorrowRecord
     From BorrowRecord br , Deleted d
     Where br.StudentID=d.StudentID