SQL Server 2005开发与管理第11章.pptVIP

第11章 触发器 SQL Server2005开发与管理 本章目标 理解触发器的作用 掌握创建触发器的方法 掌握修改和删除触发器的方法 思考：以银行的存取款为例,当我们向交易信息表中插入一条交易信息时，自动更新对应账户的余额，如何处理？ 为什么要使用触发器 触发器是一种特殊类型的存储过程 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程。 触发器可以查询其他表，还可以包含复杂的Transact-SQL语句。 什么是触发器 在指定的表中数据发生变化时被调用以响应INSERT、UPDATE或DELETE事件 强制执行业务规则 触发器的作用 使用Management Studio创建触发器 使用Transact-SQL语句创建触发器 CREATE TRIGGER trigger_name On table [WITH ENCRYPTION] {FOR|AFTER| INSTEAD OF} {[INSERT] [，][UPDATE][，] [DELETE]} AS sql_statement 创建触发器 系统为每个触发器都自动创建了INSERTED表和DELETED表 INSERTED表用于存储INSERT和UPDATE语句所影响的新的数据行； DELETED表用于存储被DELETE和UPDATE语句所影响的旧数据行 这两个表的结构和被触发器作用的表的结构相同 用户不能对这两个表进行修改，但可以引用表中的数据。 INSERTED表和DELETED表 【问题1】创建一个INSERT触发器，要求实现以 下功能：当我们向交易信息表transinfo中插入一条交易信息时，自动更新对应账户的余额。 创建触发器 ……关键语句讲解……… create trigger tri_transinfo on transinfo for insert as declare @cardid varchar(18),@money money,@type char(4),@balance money select @cardid=cardid,@type=transtype,@money=transMoney from inserted if(@type=存入) update userinfo set currentmoney=currentmoney+@money where cardid=@cardid else update userinfo set currentmoney=currentmoney-@money where cardid=@cardid print 交易成功！交易金额：+convert(varchar(20),@money) select @balance=currentmoney from userinfo where cardid=@cardid print 卡号+@cardid+余额：+ convert(varchar(20),@balance) go 关键代码 【问题2】创建一个DELETE触发器，要求实现以下功能：当我们删除交易信息表时,自动备份被删除的数据到表backupTable中。 创建触发器 ……关键语句讲解……… Create trigger del_transinfo on transinfo for delete As Print ‘开始备份数据,请稍猴….’ if exists(select * from sysobjects where name=backuptable) insert into backuptable select * from deleted else select * into backuptable from deleted … go 关键代码 【问题3】创建一个UPDATE触发器，要求实现以下功能：交易日期一般由系统自动产生，默认为当前日期。为了安全起见，一般禁止修改，以防舞弊。 特点：只有当IF UPDATE指明的列发生修改时，才触发执行触发器。 IF UPDATE的应用 ……关键语句讲解……… BEGIN TRANSACTION /*--定义变量，用于累计事务执行过程中的错误--*/ DECLARE @errorSum INT SET @errorSum=0 --初始化为0，即无错误 /*--转账：张三的账户少1000元，李四的账户多1000元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 SET @errorSum=@errorSum+@@error UPDATE bank SET currentMoney=currentMoney+1000