浅谈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 触发器 ------------------

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

/* 实现: 当清除'交易信息表'的数据时，

         自动备份被清除的数据到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 触发器 ------------------

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() 子句示例：

  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() 函数示例：

  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