《精编》SQL,Server,2000编程之触发器

SQLServer2000编程 触发器 一 了解触发器 创建帐户信息表bank和交易表transInfoCREATETABLEbank 帐户信息表 customerNameCHAR 8 NOTNULL 顾客姓名cardIDCHAR 10 NOTNULL 卡号currentMoneyMONEYNOTNULL 当前余额 CREATETABLEtransInfo 交易信息表 cardIDCHAR 10 NOTNULL 卡号transTypeCHAR 4 NOTNULL 交易类型 存入 支取 transMoneyMONEYNOTNULL 交易金额transDateDATETIMENOTNULL 交易日期 一个典型的应用 银行的取款系统 添加约束 帐户余额不能少于元 交易日期默认为当天日期 ALTERTABLEbankADDCONSTRAINTCK currentMoneyCHECK currentMoney 1 ALTERTABLEtransInfoADDCONSTRAINTDF transDateDEFAULT getDate FORtransDateGO 插入测试数据 张三开户 开户金额为 李四开户 开户金额 INSERTINTObank customerName cardID currentMoney VALUES 张三 10010001 1000 INSERTINTObank customerName cardID currentMoney VALUES 李四 10010002 1 插入测试数据 张三取钱 INSERTINTOtransInfo cardID transType transMoney VALUES 10010001 支取 200 查看结果SELECT FROMbankSELECT FROMtransInfo 一个典型的应用 银行的取款系统 帐户信息表bank 交易信息表transInfo 张三取钱200问题 没有自动修改张三的余额 最优的解决方案就是采用触发器 它是一种特殊的存储过程也具备事务的功能它能在多表之间执行特殊的业务规则 张三开户1000元 李四开户1元 赵二 插入 1 什么是触发器 删除 触发器触发 赵二退休 赵二 员工表 退休员工表 触发器是在对表进行插入 更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器是一种高级约束 可以定义比用CHECK约束更为复杂的约束可执行复杂的SQL语句 if while case 可引用其它表中的列 什么是触发器 2 触发器主要提供的功能 在数据库中的相关表上实现级联更改事务功能 撤销或回滚违反引用完整性的操作 防止非法修改操作执行比CHECK约束更复杂的约束操作比较数据修改前后的状态大部分触发器提供了引用被修改数据的能力 这样就允许用户在触发器中引用正被修改语句所影响的行在一张表的同一类型操作 update insert delete 上设置多个触发器 从而可以对同样的修改语句执行不同的多种操作 3 使用触发器的说明 和特定表关联 自动调用 当试图在某个表插入 更新或删除数据 而在那个表上定义了针对所做动作的触发器 那么触发器会自动执行 只有表的拥有者才可以在表上创建或删除触发器 这种权限不许转授 不能再在视图或临时表上创建触发器 但可以在触发器中引用视图或临时表 是一个事务的部分 如果触发器执行不成功 则整个修改事务回滚 不像普通的存储过程 触发器不能被直接调用 也不传递或接受参数当使用约束 规则 默认值就可以实现预定的数据完整性时 应该优先使用前3种措施 4 触发器的类型 DELETE触发器INSERT触发器UPDATE触发器 触发器触发时 系统自动在内存中创建deleted表或inserted表只读 不允许修改 触发器执行完成后 自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚操作 5 inserted和deleted inserted和deleted表 inserted表和deleted表存放的信息 创建触发器的语法 二 创建触发器 CREATETRIGGERtrigger nameONtable name WITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE AST SQL语句GO WITHENCRYPTION表示加密触发器定义的SQL文本DELETE INSERT UPDATE指定触发器的类型 创建触发器 FOR和AFTER是完全相等的 创建相同类型的触发器 在INSERT UPDATE或DELETE语句执行后触发INSTEADOF触发器取消触发动作 执行替代操作创建触发器后 其信息插入sysobjects和syscomments系统表中SQLServer不允许在触发器中使用下列语句 ALTERDATABASE CREATEDATABASE DISKINIT DISKRESIZE DROPDATABASE LOADDATABASE LOADLOG RECONFIGURE RESTOREDATABASE RESTORELOG 1 INSERT触发器 插入记录行 触发insert触发器 向inserted表中插入新行的副本 触发器检查inserted表中插入的新行数据 确定是否需要回滚或执行其他操作 INSERT触发器的工作原理 INSERT触发器示例 问题 解决上述的银行取款问题 当向交易信息表 transInfo 中插入一条交易信息时 我们应自动更新对应帐户的余额 帐户信息表bank 交易信息表transInfo 张三取钱200问题 没有自动修改张三的余额 张三开户1000元 李四开户1元 分析 在交易信息表上创建INSERT触发器从inserted临时表中获取插入的数据行根据交易类型 transType 字段的值是存入 支取 增加 减少对应帐户的余额 INSERT触发器示例 关键代码 CREATETRIGGERtrig transInfoONtransInfoFORINSERTASDECLARE typechar 4 outMoneyMONEYDECLARE myCardIDchar 10 balanceMONEYSELECT type transType outMoney transMoney myCardID cardIDFROMinsertedIF type 支取 UPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardIDELSEUPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardID GO 从inserted表中获取交易类型 交易金额等 根据交易类型 减少或增加对应卡号的余额 INSERT触发器示例 2 DELETE触发器 删除记录行 触发delete触发器向deleted表中插入被删除的副本 触发器检查deleted表中被删除的数据 决定是否需要回滚或执行其他操作 DELETE触发器的工作原理 问题 当删除交易信息表时 要求自动备份被删除的数据到表backupTable中 分析 在交易信息表上创建DELETE触发器被删除的数据可以从deleted表中获取 DELETE触发器示例 从deleted表中获取被删除的交易记录 DELETE触发器示例 关键代码 CREATETRIGGERtrig delete transInfoONtransInfoFORDELETEASprint 开始备份数据 请稍后 IFNOTEXISTS SELECT FROMsysobjectsWHEREname backupTable SELECT INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT FROMdeletedprint 备份数据成功 备份表中的数据为 SELECT FROMbackupTableGO DELETE触发器示例 3 UPDATE触发器 删除记录行 向deleted表中插入被删除的副本 检查deleted和inserted表中的数据 确定是否需要回滚或执行其他操作 UPDATE触发器的工作原理 向inserted表中插入被添加的副本 插入记录行 问题 跟踪用户的交易 交易金额超过20000元 则取消交易 并给出错误提示 分析 在bank表上创建UPDATE触发器修改前的数据可以从deleted表中获取修改后的数据可以从inserted表中获取 UPDATE触发器示例 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表中获取交易后的余额 交易金额是否 2万 回滚事务 撤销交易 UPDATE触发器 关键代码 UPDATE触发器 列级UPDATE触发器 UPDATE触发器除了跟踪数据的变化 修改 外 还可以检查是否修改了某列的数据使用UPDATE 列 函数检测是否修改了某列 问题 交易日期一般由系统自动产生 默认为当前日期 为了安全起见 一般禁止修改 以防舞弊 分析 UPDATE 列名 函数可以检测是否修改了某列 关键代码 CREATETRIGGERtrig update transInfoONtransInfoFORUPDATEASIFUPDATE transDate BEGINprint 交易失败 RAISERROR 安全警告 交易日期不能修改 由系统自动产生 16 1 ROLLBACKTRANSACTIONENDGO 检查是否修改了交易日期列transDate 回滚事务 撤销交易 列级UPDATE触发器 列级UPDATE触发器 4 INSTEADOF触发器的工作过程 INSTEADOF触发器可以在表和视图上定义INSTEADOF触发器 INSTEADOF触发器代替原触发动作执行 增加了视图上所能进行的更新的种类每个表上对每个触发动作 INSERT UPDATE或DELETE 只能定义一个INSTEADOF触发器不能在具有WITHCHECKOPTION选项的视图上创建INSTEADOF触发器INSTEADOF触发器可使一般不支持更新的视图可以被更新截获对视图的操作 将其重导向底层表在INSTEADOFDELETE触发器中 通过deleted表访问欲删除的行 在INSTEADOFUPDATE或INSTEADOFINSERT触发器中 通过inserted表访问新增加的行 INSTEADOF触发器的工作过程 续 创建结合两个或多个表的视图 CREATEVIEWvw CustomersASSELECT FROMCustomersMexUNIONSELECT FROMCustomersGer 创建触发器 将对视图的插入引导到对基本表的插入 CREATETRIGGERtr CustomersONvw CustomersINSTEADOFinsertASBEGINinsert