sql 触发器

先说一下为什么要写这一篇文章，是因为今天我到世界500强的IBM公司去面试，他们问了我这一个问题我没有回答上来，郁闷死我了，你们可不要像我这样呀。我来给大家总节一下吧。我在说的时候说的很模糊。感到不是很满意呀。所以现在给大家总节一下吧。呵呵赐教了。

先说一下
 1.了解为什么需要触发器。
2.理解触发器的工作原理。
3.掌握如何使用inserted和deleted表

4掌握如何创建

——insert触发器
——update触发器
——delete触发器

为什么需要触发器（TRIGGER)呢？典型的应用就是银行的取款机系统

最优的解决方案就是采用触发器：

它是一种特殊的存储过程 
也具备事务的功能 
它能在多表之间执行特殊的业务规则 

2.什么是触发器

现在有两张表，一张是员式表，一张是退休员工表

比如赵二现在要退休，要从员工表里面删除，但是退休表里要添加上赵二，这就是触发器的作用，当你删除员式表的成员时，相应的触发了，添加到退休员工表的功能。

再来解释一下什么是触发器。

触发器是在对表进行插入，更新或删除操作时自动执行的存储过程。

触发器通常用于强制业务规则。

触发器是一种高级约束，可以定义比用check约束更为复杂的约束

1.可执行复杂的sql语句（if/while/case)

2引用其它表中的列。

触发器定义在特定的表上，与表相关。

自动触发执行

不能直接调用

是一个事务（可回滚）

这个就是我没有回答上来的地方了，我左想右想，也没有想到。

触发器的类型。

他有三个类型。1.delete触发器2.insert触发器3.update触发器

有人会问为什么没有查询的触发器，大家好好想想为什么没有。

inserted 和deleted

触发器触发时：系统自动在内存中创建deleted表和inserted表

只读，不请允许修改，触发器执行完成后，自动删除

inserted表

临时保存入插入或更新后的记录行

可以从inserted表中检查插入的数据是否满足业务需求

如果不满足，由向用户报告错误消息，并回滚插入操作

deleted表

临时保存了删除或更新前的记录行

可以从deleted表中检查被删除的数据是否满足业务需求

如果不满足，则向用户报告错误消息，并回滚插入操作

inserted和deleted表

如何创建触发器

创建触发器的语法

 

CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO

WITH ENCRYTION 表示加密触发器定义的sql文本

DELETE ,INSERT UPDATE指定触发器的类型

insert  触发器的工作原理

insert 触发器示例

问题：

解决上术的银行取款问题，当向交易信息表（transinfo）中插入一条交易信息时，我们应自动更新对应帐户的余额。

分析：

在交易信息表上创建insert触发器

从inserted临时表中获取插入的数据行

根据交易类型（transType)字段的值是存入/支取

增加/减少对应帐户的余额

————————关键代码————————

CREATE TRIGGER trig_transInfo 
 ON transInfo 
  FOR INSERT 
   AS
   DECLARE @type char(4),@outMoney MONEY
   DECLARE @myCardID char(10),@balance MONEY
   SELECT @type=transType,@outMoney=transMoney，
         @myCardID=cardID FROM inserted----------从insert表中获取交易类型，交易金额等
     IF (@type='支取') -------------------根据交易类型减少或增加对应卡号的余额
        UPDATE bank SET currentMoney=currentMoney-@outMoney
             WHERE cardID=@myCardID
    ELSE
        UPDATE bank SET currentMoney=currentMoney+@outMoney
             WHERE cardID=@myCardID
   delete触发器的工作原理：

 

问题：

当删除交易信息表中，要 自动备份被删除的数据到表backupTable中。

分析：

在交易信息表上创建delete触发器

被删除的数据可以从deleted表中获取

————————关键代码————————

CREATE TRIGGER trig_delete_transInfo
 ON transInfo
  FOR DELETE 
   AS
      print '开始备份数据，请稍后......'
      IF NOT EXISTS(SELECT * FROM sysobjects 
           WHERE name='backupTable')
         SELECT * INTO backupTable FROM deleted---------从deleted表中获取被删除的交易记录
     ELSE
         INSERT INTO backupTable SELECT * FROM deleted
      print '备份数据成功，备份表中的数据为:'
      SELECT * FROM backupTable 
GO 

update触发器的工作原理：

问题：

跟踪用户的交易，交易金额超过20000元，取消交易，并给出错误提示

分析：

在bank 表上创建update触发器

修改前的数据可以从deleted 表中获取

修改后的数据可以从inserted表中获取

————————关键代码————————

CREATE TRIGGER trig_update_bank
 ON bank
  FOR UPDATE
   AS
      DECLARE @beforeMoney MONEY,@afterMoney MONEY  
      SELECT @beforeMoney=currentMoney FROM deleted   ------------从deleted 表中获取交易前的余额
      SELECT @afterMoney=currentMoney FROM inserted    -------------从inserted表中获取交易后的余额
      IF ABS(@afterMoney-@beforeMoney)>20000 --------交易金额是否>2万
        BEGIN
            print '交易金额:'+convert(varchar(8),
                ABS(@afterMoney-@beforeMoney))
            RAISERROR ('每笔交易不能超过2万元，交易失败',16,1)---------------回滚事务，撤销交易
            ROLLBACK TRANSACTION
         END
GO 

列级update触发器

update 触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列数据

使用update(列）函数检测是否修改了某列

问题：

交易日期一般由系统自动产生，默认为当前日期，为了安全起见，一般禁止修改，以防作弊。

分析：

update(列名）函数可以检测是否修改了某列

————————关键代码————————

CREATE TRIGGER trig_update_transInfo
 ON transInfo
  FOR UPDATE
   AS
      IF UPDATE(transDate)
         BEGIN
            print '交易失败.....'
            RAISERROR (‘安全警告：交易日期不能修改，
                           由系统自动产生',16,1)
            ROLLBACK TRANSACTION    
         END
GO 

好了总结完了希望你们会喜欢呵呵 ！