触发器_触发器 银行

转载自：http://hi.baidu.com/zhu07ying29/blog/item/3956c436fed344340a55a9d8.html

什么是触发器
银行存取款问题——最优的解决方案就是采用触发器：
。它是一种特殊的存储过程
。也具备事务的功能
。它能在多表之间执行特殊的业务规则

触发器：
是在对表进行插入、更新或删除操作时自动执行的存储过程
。触发器通常用于强制业务规则
。触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束
。可执行复杂的SQL语句（if/while/case）
。可引用其它表中的列
。触发器定义在特定的表上，与表相关
。自动触发执行
。不能直接调用
。是一个事务（可回滚）

◆触发器的类型
DELETE 触发器
INSERT 触发器
UPDATE 触发器

◆inserted 和deleted 表
1）触发器触发时：
系统自动在内存中创建deleted表或inserted表
只读，不允许修改；触发器执行完成后，自动删除
2）inserted 表
临时保存了插入或更新后的记录行
可以从inserted表中检查插入的数据是否满足业务需求
如果不满足，则向用户报告错误消息，并回滚插入操作
3）deleted 表
临时保存了删除或更新前的记录行
可以从deleted表中检查被删除的数据是否满足业务需求
如果不满足，则向用户报告错误消息，并回滚插入操作

◆inserted表和deleted表存放的信息：
修改操作         inserted表         deleted表
增加(INSERT)记录 存放新增的记录         ------
删除(DELETE)记录 -----                 存放被删除的记录
修改(UPDATE)记录 存放更新后的记录 存放更新前的记录

◆如何创建触发器：
创建的语法：
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]----------------------WITH ENCRYPTION表示加密触发器定义的SQL文本
FOR [DELETE, INSERT, UPDATE]----------DELETE, INSERT, UPDATE指定触发器的类型
AS
T-SQL语句
GO

◆INSERT触发器的工作原理：
插入记录行——触发insert触发器。向inserted表中插入新行的副本——触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作

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
     IF (@type='支取')
        UPDATE bank SET currentMoney=currentMoney-@outMoney
             WHERE cardID=@myCardID
    ELSE
        UPDATE bank SET currentMoney=currentMoney+@outMoney
             WHERE cardID=@myCardID
    …..
GO

◆DELETE触发器的工作原理：
删除记录行——触发delete触发器向deleted表中插入被删除的副本——触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作

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
     ELSE
         INSERT INTO backupTable SELECT * FROM deleted
      print '备份数据成功，备份表中的数据为:'
      SELECT * FROM backupTable
GO

◆UPDATE触发器的工作原理：
插入记录行——向deleted表中插入被删除的副本——向inserted表中插入被添加的副本——检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作

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  
      SELECT @afterMoney=currentMoney FROM inserted   
      IF ABS(@afterMoney-@beforeMoney)>20000
        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