触发器

转载自：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