浅谈SQL Server 数据库之触发器

触发器1_概念

触发器的特征：

1、触发器是在对表进行增、删、改时， 自动执行的存储过程。触发器常用于 强制业务规则，它是一种 高级约束 ，通过 事件 进行触发而被执行。

2、触发器是一个特殊的事务单元，可以引用其他表中的列执行特殊的业务规则或数据逻辑关系。当出现错误时，可以执行rollback transaction操作将整个触发器以及触发它的T-SQL语句一并回滚（不需显示声明begin transaction）。

3、每个触发器将用到的两个临时表：

   deleted 临时表：用于临时存放被删除的记录行副本(包括delete和update语句所影响的数据行)；
                  注意：被删除的记录行，首先从原始表中删除，并保存到触发器表。然后从触发器表中删除，再保存到deleted表。

   inserted临时表：用于临时存放插入的记录行副本(包括insert和update语句所影响的数据行)；

    deleted表和inserted表的特征：
    > 这两个表的表结构与该触发器作用的表相同；
    > 这两个表是逻辑表，并且由系统管理；
    > 这两个表是动态驻留在内存中的(不是存储在数据库中)，当触发器工作完成后，它们也被删除；
    > 这两个表是只读的，即只能运用select语句查看(用户不能直接更改)；

4、所创建的 触发器(insert、delete、update)是在原表数据行已经修改完成后再触发。所以，触发器是在约束检查之后才执行。


什么时候使用触发器？

a、实现主外键关系所不能保证的 复杂参照完整性和 数据的一致性。
    不过，通过 “级联引用完整性约束”可以更有效地执行这些更改。

b、防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。
   > 与 CHECK 约束不同(check约束只能引用自身表中的列)，DML 触发器可以引用其他表中的列；
   > 触发器可以完成 所有约束的功能，但不一定是最佳方案；
   > 触发器能够使用 自定义信息和较为 复杂的错误处理；

c、DML 触发器可以 评估数据修改前后表的状态，并根据该差异采取措施。

d、一个表中的 同一个修改语句的DML触发器，允许被多个不同的操作（INSERT、UPDATE 或 DELETE）来 响应；


触发器的类型：

insert 触发器；(略)
delete 触发器；(略)
update 触发器：在修改表中记录行或某列数据时触发执行；
注意：update(列)函数：实现检测某列是否被修改。

update 更新操作分为两步：
首先， “删除”更改前原有数据行：删除的原有数据行将复制到deleted临时表中；
然后， “插入”更改后的新数据行：插入新数据行到原始表，同时将新数据行保存到inserted临时表和触发器表中；


创建触发器的注意点：
1、create trigger必须是批处理(go)的第一条语句；

2、一个触发器语句只能用到一个表或一个视图中；
   on 表名/ 视图名

3、一个触发器语句可以执行多个操作；
   for delete,insert,update -- 无先后顺序的任意组合

4、建议DML触发器不返回任何结果。这是因为对这些返回结果的特殊处理必须写入每个允许对触发器表进行修改的应用程序中。
     若要防止从 DML 触发器返回任何结果，请不要在触发器定义中包含select语句或变量赋值；
     如果必须在触发器中进行变量赋值，则应该在触发器被触发之前使用set nocount on语句以避免返回任何结果集；

     注意：未来版本的SQL Server 中，将会删除从触发器返回结果集的功能。

5、如果“触发器表”本身也存在约束，则在执行insert、delete、update触发器前，首先会检查“触发器表”上存在的约束。如果不满足约束，则不会执行其insert、delete、update触发器。

 

查看当前数据库中的所有触发器

select   *   from  sys.triggers

 

创建临时表 #tableName

create   table  #tableName

如何使用 SQL Server 触发器

 

触发器2_初始化环境SQL

初始化环境

-- ------------- 初始化环境 ---------------

create   database  TriggerDatabase
use  TriggerDatabase
go

if   exists ( select   *   from  sysobjects  where  name = ' bank ' )
    drop   table  bank

create   table  bank  --  账户信息表
(
   userName       varchar ( 10 )  not   null ,   -- 顾客名
   cardID         varchar ( 10 )  not   null ,   -- 卡号
   currentMoney   money         not   null     -- 当前余额
)

if   exists ( select   *   from  sysobjects  where  name = ' transInfo ' )
    drop   table  transInfo

create   table  transInfo  -- 交易信息表
(
   cardID      varchar ( 10 )  not   null ,   -- 卡号
   transType   char ( 4 )      not   null ,   -- 交易类型(存入/支取)
   transMoney  money         not   null ,   -- 交易金额
   transDate   datetime      not   null     -- 交易日期
)
go

-- ------------- 添加约束 ---------------
alter   table  bank
add   constraint  CK_currentMoney  check (currentMoney >= 1 );

alter   table  transInfo
add   constraint  DF_transDate  default ( getdate ())  for  transDate;

alter   table  transInfo
add   constraint  CK_transType  check (transType  in ( ' 支取 ' , ' 存入 ' ));

-- ------------- 添加测试数据 ---------------
/*  张三 1000元  */
insert   into  bank(userName,cardID,currentMoney)
         values ( ' 张三 ' , ' 1001 0001 ' , 1000 );
/*  李四 1元  */
insert   into  bank(userName,cardID,currentMoney)
         values ( ' 李四 ' , ' 1001 0002 ' , 1 );
/*  张三 支取 200元  */
insert   into  transInfo(cardID,transType,transMoney)
         values ( ' 1001 0001 ' , ' 支取 ' , 200 );

-- ------------- 查看结果 ---------------
select   *   from  bank;
select   *   from  transInfo;
go

触发器3_定义触发器的格式

定义触发器的格式

--  =============================================
--  Author:        xugang
--  Create date: 2010-2-14
--  Description:    定义触发器的精简格式
--   [ ]：可选     { }必选
--  =============================================

create   trigger   [  schema_name.  ]   --  触发器所属架构
               trigger_name      --  触发器名称
on  {  table   |   view  }        --  触发器的表或视图
    [  with encryption  ]      --  加密dml触发器定义(后面详解)
{  for   |  after } 
    /*  after：只有在触发它的SQL语句执行成功后才能激发。
             (只能对“表”定义after)  */
    {  insert , update , delete  } 
as
     /*  SQL语句...  */
go



-- 查看当前数据库中的所有触发器
select   *   from  sys.triggers

触发器4_insert 触发器SQL

insert 触发器

-- ---------------- insert 触发器 ------------------
use  TriggerDatabase
go
if   exists ( select   *   from  sysobjects 
            where  name = ' trig_insert_transInfo ' )
drop   trigger  trig_insert_transInfo
go

--  create trigger必须是批处理(go)的第一句

create   trigger  trig_insert_transInfo
on  transInfo  for   insert
as
     declare   @_transType     char ( 4 ),   -- 定义变量
             @_transMoney    money ,
             @_cardID        char ( 10 ),
             @balance        money       -- 所剩余额

     --  从inserted临时表中获取记录值
     select   @_transType   =  transType,
            @_transMoney   =  transMoney,
            @_cardID   =  cardID 
            from  inserted

     if ( @_transType   =   ' 支取 ' )
        update  bank  set  currentMoney = currentMoney - @_transMoney
               where  cardID  =   @_cardID ;
     else  
        update  bank  set  currentMoney = currentMoney + @_transMoney
               where  cardID  =   @_cardID ;

     -- 显示交易金额
     print   ' 交易成功！ 交易金额： '
           +   convert ( varchar ( 20 ), @_transMoney )

     -- 显示所剩余额
     select   @balance   =  currentMoney  from  bank 
            where  cardId  =   @_cardID

     print   ' 卡号： ' + @_cardID  
           +   '  余额： ' + convert ( varchar ( 20 ), @balance );
go



-- ---------------- 测试触发器 ------------------

--  delete from transInfo
set  nocount  on   -- 不显示T-SQL影响的记录行数

insert   into  transInfo(cardID,transType,transMoney)
              values ( ' 1001 0001 ' , ' 支取 ' , 200 );
insert   into  transInfo(cardID,transType,transMoney)
              values ( ' 1001 0001 ' , ' 存入 ' , 10000 );
-- 查看结果
select   *   from  bank
select   *   from  transInfo

触发器5_delete 触发器SQL

delete 触发器

/*  实现: 当清除'交易信息表'的数据时，
         自动备份被清除的数据到backupTable表中
*/

-- ---------------- delete 触发器 ------------------
use  TriggerDatabase
go

if   exists  ( select   *   from  sysobjects 
            where  name = ' trig_delete_transInfo ' )
drop   trigger  trig_delete_transInfo
go

create   trigger  trig_delete_transInfo
on  transInfo after  delete    --   for | after
as
    print   ' 开始备份数据，请稍后...... '
    --  如果数据库中，不存在 backupTable 表
    if   not   exists ( select   *   from  sysobjects 
                   where  name = ' backupTable ' )
       select   *   into  backupTable  from  deleted  -- deleted临时表
    else
       insert   into  backupTable  select   *   from  deleted
  
    print   ' 备份成功，备份表 backupTable 中的数据为： '
         select   *   from  backupTable;
go


-- ---------------- 测试触发器 ------------------
set  nocount  on

delete   from  transInfo;  -- 测试

-- 查看结果
select   *   from  transInfo
select   *   from  backupTable

触发器6_update 触发器SQL

update 触发器

-- ---------------- update 触发器 ------------------
use  TriggerDatabase
go

if   exists  ( select   *   from  sysobjects 
            where  name = ' trig_update_bank ' )
drop   trigger  trig_update_bank
go

create   trigger  trig_update_bank
on  bank  for   update    -- 在bank表上创建update触发器
as
    declare   @beforeMoney   money ,
            @afterMoney    money ,
            @currentTrans   money   -- 当前交易金额

    -- 从deleted临时表，获取交易前的余额
    select   @beforeMoney   =  currentMoney  from  deleted;
    -- 从inserted临时表，获取交易后的余额
    select   @afterMoney   =  currentMoney  from  inserted;
   
    if   abs ( @afterMoney - @beforeMoney )  >   2000
       begin
         print   ' 当前交易金额为： '   +
               convert ( varchar ( 20 ), abs ( @afterMoney - @beforeMoney ))
         --  自定义错误消息
         raiserror ( ' 每次交易金额不能超过2000元，交易失败！ ' , 16 , 1 )
       
         rollback   transaction   -- 回滚事务，撤销交易！
         /*  注意：
           触发器是一个特殊的事务单元
           不需显示声明begin transaction
         */
       end
go


-- ---------------- 测试触发器 ------------------
set  nocount  on

-- 测试1: 在 bank表触发 update触发器
update  bank  set  currentMoney  =  currentMoney  +   25000
        where  cardID  =   ' 1001 0001 '

-- 测试2: 通过 transInfo表的 trig_insert_transInfo触发器
--              间接触发 bank表的 trig_update_bank触发器

insert   into  transInfo(cardID,transType,transMoney)
              values ( ' 1001 0001 ' , ' 存入 ' , 10000 );

-- 查看结果
select   *   from  bank
select   *   from  transInfo

 

触发器7_MSDN参考

加密 dml触发器定义
      若要确保其他用户不能查看触发器定义，可以使用with encryption子句加密 dml 触发器。
      使用with encryption子句后，触发器定义即以无法读取的格式进行存储。
      触发器定义加密后，无法进行解密。且任何人都无法进行查看，包括触发器的所有者和系统管理员。

update() 函数：
可用于确定 insert或 update语句是否影响表中的特定列。
无论何时为列赋值，该函数都将返回 true。

使用if update() 子句示例：

if update()子句示例

   create   table  testTable(a  int   null , b  int   null )
   go

   create   trigger  my_trig
   on  testTable  for   insert
   as
      if   update (b)
      print   ' 列b已被修改！ '
   go
 
   insert   into  testTable(b)  values ( 123 );

   --  drop table testTable

注意：  
      由于 delete 语句无法只对某列进行删除，
      因此不能将if update()子句应用于delete 语句。

 

columns_updated() 函数：
也可用于检查 insert或 update语句更新了表中的哪些列。
此函数使用整数位掩码指定要测试的列。

使用columns_updated() 函数示例：

columns_updated()函数示例

   create   table  testTable2(a  int   null , b  int   null )
   go

   create   trigger  my_trig2
   on  testTable2  for   insert
   as
      if  ( columns_updated()  &   2   =   2  )
      print   ' 列b已被修改！ '
   go

   insert   into  testTable2(b)  values ( 123 );
 
   --  drop table testTable2

MSDN参考来源：
      对 DML 触发器进行编程 ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/b2b52258-642b-462e-8e0f-18c09d2eccf4.htm