数据库系列_6触发器.ppt

第七章 触发器 2 回顾2 1 代码阅读 下列系统存储过程的功能是 EXECsp helpconstraintstuInfoEXECsp helpindexstuMarksEXECsp renamedb stuDB studentDB EXECxp cmdshell mkdird project 存储过程的参数分为哪两类 根据您的理解 print语句和RAISERROR语句有什么区别 3 回顾2 2 代码阅读 指出下列语句的错误CREATEPROCEDUREproc stuInfo 定义存储过程 stuNameVARCHAR 10 mINTOUTPUT stuAge 18INTAS 代码略GO 调用存储过程DECLARE sINTEXECproc stuInfo 张三 s 30EXECproc stuInfo 张三 sEXECproc stuInfo stuAge 22 stuName 李四 m sOUTPUT stuAgeINT 18 EXECproc stuInfo 张三 sOUTPUT 30 EXECproc stuInfo 张三 sOUTPUT 正确 4 本章目标 了解为什么需要触发器理解触发器的工作原理掌握如何使用inserted表和deleted表掌握如何创建 INSERT触发器UPDATE触发器DELETE触发器 5 为什么需要触发器 TRIGGER 呢 典型的应用就是银行的取款机系统 为什么需要触发器 演示 为什么需要触发器 sql 帐户信息表bank 交易信息表transInfo 张三取钱200问题 没有自动修改张三的余额 最优的解决方案就是采用触发器 它是一种特殊的存储过程也具备事务的功能它能在多表之间执行特殊的业务规则 张三开户1000元 李四开户1元 6 赵二 插入 什么是触发器3 1 删除 触发器触发 赵二退休 赵二 员工表 退休员工表 7 触发器是在对表进行插入 更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器是一种高级约束 可以定义比用CHECK约束更为复杂的约束可执行复杂的SQL语句 if while case 可引用其它表中的列 什么是触发器3 2 8 触发器定义在特定的表上 与表相关自动触发执行不能直接调用是一个事务 可回滚 什么是触发器3 3 9 触发器的类型 DELETE触发器INSERT触发器UPDATE触发器 10 触发器触发时 系统自动在内存中创建deleted表或inserted表只读 不允许修改 触发器执行完成后 自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚插入操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚插入操作 inserted和deleted表2 1 11 inserted和deleted表2 2 inserted表和deleted表存放的信息 12 创建触发器的语法 如何创建触发器 CREATETRIGGERtrigger nameONtable name WITHENCRYPTION FOR DELETE INSERT UPDATE AST SQL语句GO WITHENCRYPTION表示加密触发器定义的SQL文本DELETE INSERT UPDATE指定触发器的类型 13 INSERT触发器 插入记录行 触发insert触发器 向inserted表中插入新行的副本 触发器检查inserted表中插入的新行数据 确定是否需要回滚或执行其他操作 INSERT触发器的工作原理 14 INSERT触发器示例3 1 问题 解决上述的银行取款问题 当向交易信息表 transInfo 中插入一条交易信息时 我们应自动更新对应帐户的余额 分析 在交易信息表上创建INSERT触发器从inserted临时表中获取插入的数据行根据交易类型 transType 字段的值是存入 支取 增加 减少对应帐户的余额 15 关键代码 CREATETRIGGERtrig transInfoONtransInfoFORINSERTASDECLARE typechar 4 outMoneyMONEYDECLARE myCardIDchar 10 balanceMONEYSELECT type transType outMoney transMoney myCardID cardIDFROMinsertedIF type 支取 UPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardIDELSEUPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardID GO INSERT触发器示例3 2 从inserted表中获取交易类型 教员金额等 根据交易类型 减少或增加对应卡号的余额 16 INSERT触发器示例3 3 17 DELETE触发器 删除记录行 触发delete触发器向deleted表中插入被删除的副本 触发器检查deleted表中被删除的数据 决定是否需要回滚或执行其他操作 DELETE触发器的工作原理 18 问题 当删除交易信息表时 要求自动备份被删除的数据到表backupTable中 分析 在交易信息表上创建DELETE触发器被删除的数据可以从deleted表中获取 DELETE触发器示例3 1 19 关键代码 CREATETRIGGERtrig delete transInfoONtransInfoFORDELETEASprint 开始备份数据 请稍后 IFNOTEXISTS SELECT FROMsysobjectsWHEREname backupTable SELECT INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT FROMdeletedprint 备份数据成功 备份表中的数据为 SELECT FROMbackupTableGO 从deleted表中获取被删除的交易记录 DELETE触发器示例3 2 20 DELETE触发器示例3 3 21 UPDATE触发器 删除记录行 向deleted表中插入被删除的副本 检查deleted和inserted表中的数据 确定是否需要回滚或执行其他操作 UPDATE触发器的工作原理 向inserted表中插入被添加的副本 插入记录行 22 问题 跟踪用户的交易 交易金额超过20000元 则取消交易 并给出错误提示 分析 在bank表上创建UPDATE触发器修改前的数据可以从deleted表中获取修改后的数据可以从inserted表中获取 UPDATE触发器示例3 1 23 关键代码 CREATETRIGGERtrig update bankONbankFORUPDATEASDECLARE beforeMoneyMONEY afterMoneyMONEYSELECT beforeMoney currentMoneyFROMdeletedSELECT afterMoney currentMoneyFROMinsertedIFABS afterMoney beforeMoney 20000BEGINprint 交易金额 convert varchar 8 ABS afterMoney beforeMoney RAISERROR 每笔交易不能超过2万元 交易失败 16 1 ROLLBACKTRANSACTIONENDGO 从deleted表中获取交易前的余额 从inserted表中获取交易后的余额 UPDATE触发器3 2 交易金额是否 2万 回滚事务 撤销交易 24 UPDATE触发器3 3 25 列级UPDATE触发器3 1 UPDATE触发器除了跟踪数据的变化 修改 外 还可以检查是否修改了某列的数据使用UPDATE 列 函数检测是否修改了某列 问题 交易日期一般由系统自动产生 默认为当前日期 为了安全起见 一般禁止修改 以防舞弊 分析 UPDATE 列名 函数可以检测是否修改了某列 26 关键代码 CREATETRIGGERtrig update transInfoONtransInfoFORUPDATEASIFUPDATE transDate BEGINprint 交易失败 RAISERROR 安全警告 交易日期不能修改 由系统自动产生 16 1 ROLLBACKTRANSACTIONENDGO 检查是否修改了交易日期列transDate 回滚事务 撤销交易 列级UPDATE触发器3 2 27 列级UPDA