SQLServer2005数据库任务化教程 任务6 触发器的规划与设计.ppt

SQLServer2005数据库任务化教程 梁竞敏黄华林宋广科等编著 中国水利水电出版社 任务六触发器的规划与设计 一 任务目标 1掌握创建和使用触发器来实现数据有效性和完整性 2掌握创建和使用触发器来实现数据的业务逻辑 3掌握创建和使用触发器来实现数据的保护 二 教学任务 1介绍触发器的概念 2介绍触发器的触发机制 3创建带有提示信息的触发器 4创建限制取值范围约束的触发器 5创建实现用户逻辑上数据完整性触发器 6分析设计分销系统中需要的触发器 6 1 1触发器的概念 触发器是一种特殊的存储过程 其特殊性在于它不需要由用户调用执行 而是当用户对表中的数据进行UPDATE INSERT或DELETE操作时自动触发执行 触发器通常用于保证业务规则和数据完整性约束 其优点是用户可以用编程的方法来实现复杂的处理逻辑和业务规则 增强了数据完整性约束的功能 在SQLServer2005中 触发器有了更进一步的功能 在数据表 库 发生Create Alter和Drop操作时 也会自动激活执行 6 1 2触发器的分类 在SQLServer2005中 触发器可以分为两大类 DML触发器和DDL触发器 DML触发器 DML触发器是当数据库服务器中发生数据操作语言 DataManipulationLanguage 事件时执行的存储过程 DML事件包括在指定表或视图中修改数据的INSERT语句 UPDATE语句或DELETE语句 DML触发器又分为两类 After触发器和InsteadOf触发器 DML触发器可以查询其他表 还可以包含复杂的T SQL语句 系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待 如果检测到错误 例如 磁盘空间不足 则整个事务即自动回滚 DDL触发器 DDL触发器是当数据库服务器中发生数据定义语言 DataDefinitionLanguage 事件时执行的存储过程 这是SQLServer2005的新增功能 它与DML触发器不同的是 它不会为响应针对表或视图的UPDATE INSERT或DELETE语句而激发 相反 它会为响应多种数据定义语言 DDL 语句而激发 这些语句主要是以CREATE ALTER和DROP开头的语句 DDL触发器一般用于执行数据库中管理任务 如审核和规范数据库操作 防止数据库表结构被修改等 DML触发器的分类SQLServer2005的DML触发器分为两类 After触发器和InsteadOf触发器 After触发器 这类触发器是在记录已经改变完之后 after 才会被激活执行 它主要是用于记录变更后的处理或检查 一旦发现错误 也可以用RollbackTransaction语句来回滚本次的操作 InsteadOf触发器 这类触发器一般是用来取代原本的操作 在记录变更之前发生的 它并不去执行原来SQL语句里的操作 Insert Update Delete 而去执行触发器本身所定义的操作 6 1 2触发器的分类 DML触发器的工作原理在DML触发器的工作过程中 SQLServer建立和管理两个临时的虚拟表 一个是Inserted 插入 表 一个是Deleted 删除 表 这两个表是建在数据库服务器的内存中的 是由系统管理的逻辑表 而不是真正存储在数据库中的物理表 这两个特殊表可供用户读取 但是用户不能直接修改表中的数据 Inserted和Deleted两个表的结构与触发器所在数据表的结构是完全一致的 当触发器的工作完成之后 这两个表也将会从内存中删除 6 1 2触发器的分类 6 1 3触发器的创建 CREATETRIGGERtrigger nameONtable name WITHENCRYPTION FOR AFTER INSTEADOF INSERT DELETE UPDATE ASSQL statement n 6 1 3触发器的创建 参数含义说明如下 CREATETRIGGER 用来触发器 trigger name 触发器名称 触发器是对象 必须具有数据库中的唯一名称 ONtable name 用于指定触发执行触发器的表 WithEncryption 用来加密触发器 如果使用了这个参数 该触发器将会被加密 任何人都看不到触发器的内容了 AFTER 指定触发器只有在引发触发器执行的SQL语句指定的操作都已经成功执行 并且所有的约束检查也成功完成后 才执行此触发器 这种类型的触发器称为后触发型触发器 FOR 如果只是指定FOR关键字 则AFTER为默认值 INSTEADOF 指定执行触发器而不是执行引发触发器执行的SQL语句 从而替代触发语句的操作 这种触发器称为前触发型触发器 一个表只能定义一个INSTEADOF触发器 INSERT DELETE UPDATE 指定引发触发器执行的操作 若同时指定多个操作 则各操作之间用逗号分隔 6 1 4触发器的实例 任务6 2 创建带有提示信息的触发器 当用户在期初库存表中插入数据时 产生一条提示信息 在SQLQuery窗口中执行如下命令 CreateTRIGGERTRI insert qckcon期初库存FORINSERTASPRINT 在期初库存表中插入了数据 6 1 4触发器的实例 任务6 3 创建限制取值范围约束的触发器 限制期初库存表中的期初数量和期初单价必须是大于零的数字 在SQLQuery窗口中执行如下命令 CreateTRIGGERTRI check qckcON期初库存FORINSERT UPDATEASIfexists select frominsertedwhereisnull 期初数量 0 0orisnull 期初单价 0 0 BeginPRINT 期初数量或期初单价不能为负数 更改失败 RollbackEnd 6 1 4触发器的实例 任务6 4 为收款单创建一个insteadof触发器 使得新插入记录中收款金额小于应收总额时 备注内容自动填写为 未收讫 而收款金额等于应收总额时 备注内容自动填写为 已收讫 在SQLQuery窗口中执行如下命令 CreateTRIGGERTRI SKD BZON收款单INSTEADOFINSERTASbegininsertinto收款单select frominsertedupdate收款单set备注 未收讫 where收款金额 应收总额and 收款单号in select收款单号frominserted update收款单set备注 已收讫 where收款金额 应收总额and 收款单号in select收款单号frominserted end 6 1 5查看 修改和删除触发器 ALTERTRIGGERtrigger nameONtable name WITHENCRYPTION FOR AFTER INSTEADOF INSERT DELETE UPDATE ASSQL statement n 6 1 5查看 修改和删除触发器 任务6 8 修改触发器TRI check cgmx的定义 将采购订单明细表的数量字段的上限修改为1200 在SQLQuery窗口中执行如下命令 ALTERTRIGGERTRI check cgmxON采购订单明细表FORINSERT UPDATEASIf select数量frominserted 1200BeginPRINT 采购数量超出上限 操作失败 Rollbackend 6 1 5查看 修改和删除触发器 可以使用DropTrigger语句来删除触发器 任务6 9 删除触发器TRI insert qckc 在SQLQuery窗口中执行如下命令 droptriggerTRI insert qckc该语句执行后 触发器TRI insert qckc即被删除 6 1 6DDL触发器 创建DDL触发器的语法如下 CREATETRIGGERtrigger nameON ALLSERVER DATABASE WITH n FOR AFTER event type event group n AS sql statement n EXTERNALNAME 参数含义说明如下 trigger name 触发器名称 必须遵循标识符规则 ONAllServer 是将DDL触发器作用到整个当前的服务器上 如果指定了这个参数 在当前服务器上的任何一个数据库都能激活该触发器 ONDatabase 是将DDL触发器作用到当前数据库 只能在这个数据库上激活该触发器 For或After 是同一个意思 指定的是After触发器 DDL触发器无法作为InsteadOf触发器 event type 执行之后将导致激发DDL触发器的Transact SQL语言事件的名称 event group 预定义的Transact SQL语言事件分组的名称 6 1 6DDL触发器 任务6 10 创建用于保护 分销系统 数据库中的数据表不被删除的触发发器 在SQLQuery窗口中执行如下命令 createtriggerdisable table droppingondatabasefordrop tableasbeginraiserror 分销系统数据表不能被删除 16 10 rollbackend 6 2分销系统触发器的设计 6 2 1分销系统触发器规划 在分销系统的数据表中 销售订单明细表有字段 数量 单价 金额 根据业务逻辑 金额 单价 数量 所以对于销售订单明细表应该创建一个触发器 使得 金额 的值是由 单价 和 数量 的乘积得来的 保证该业务逻辑的准确性 同样地 根据业务逻辑 在某个销售订单中 销售订单明细表中字段 金额 和销售订单中字段 总金额 存在这样的关系 总金额等于该销售订单明细中的金额的总和 为此 还需要为销售订单明细表创建一个触发器来保证该业务逻辑 类似地 在采购订单与采购订单明细表之间 盘点表与盘点表明细表之间 入库单与入库单明细表之间 出库单与出库单明细表之间 期初库存 也有相同的业务逻辑 都需要建立保证相关业务逻辑准确性的触发器 在部分缺少外键约束的数据表中 比如入库单明细表 该表的 仓库编码 字段的值必须是仓库资料表中存在的仓库编码 因为仓库资料表中不存在的仓库编码就意味着这样的仓库编码所标识的仓库是不存在的 现实中是绝不可能把商品入库到一个不存在的仓库中的 同样地 该表的 仓位编码 字段的值必须是仓位资料表中存在的数据 要保证上述数据完整性要求 可以创建相应的触发器 在分销系统的数据表中 虽然不少表都定义了外键约束 但并没有定义级联删除和级联修改 为此 可以定义相应的触发器来达到相应的效果 比如 若某个销售订单被删除 则该销售订单对应的明细记录也能被自动删除 6 2 2分销系统触发器设计 任务6 11 为表期初库存创建一个触发器 保证插入新记录 更改了期初数量或更改了期初单价后期初金额都会随着变化而自动更新为期初数量和期初单价的乘积 对此触发器可以做如下分析 如果 期初单价 或者 期初数量 字段有更改 则将期初库存中序号跟inserted表中序号对应的记录的期初金额按公式 期初金额 期初单价 期初数量 计算后进行更新 6 2 2分销系统触发器设计 在SQLQuery窗口中执行如下命令 createtriggerTRI update qckcon期初库存forinsert updateasifupdate 期初单价 orupdate 期初数量 Update期初库存set期初金额 isnull 期初单价 0 isnull 期初数量 0 where序号in select序号frominserted 6 2 2分销系统触发器设计 任务6 12 为销售订单明细表创建一个触发器 无论该表的新增 修改或删除记录都能保证记录中的金额为数量和单价的乘积 同时其相应的销售订单主表中的总金额也能保持准确 对此触发器可以做如下分析 先在销售订单明细表中更新Inserted表中涉及的记录的金额字段 然后在销售订单表中更新Inserted表中涉及的销售订单号的总金额字段 还要注意兼顾删除记录的情况 在销售订单表中更新Deleted表中涉及的销售订单号的总金额字段 6 2 2分销系统触发器设计 在SQLQuery窗口中执行如下命令 createtriggerTRI xsddmxbon销售订单明细表forinsert update deleteasbeginUpdate销售订单明细表set金额 isnull 单价 0 isnull 数量 0 where销售订单号in select销售订单号frominserted and序号in select序号frominsertedwhere销售订单号 销售订单明细表 销售订单号 update销售订单set总金额 selectsum 金额 from销售订单明细表where销售订单号in select销售订单号frominserted where销售订单号in select销售订单号frominserted update销售订单set总金额 isnull selectsum 金额 from销售订单明细表where销售订单号in select销售订单号fromdeleted 0 where销售订单号in select销售订单号fromdeleted end 6 2 2分销系统触发器设计 任务6 13 为入库单明细表创建一个触发器 保证插入的新记录或修改的记录中 仓库编码和仓位编码字段内容都是已存在于仓位资料表的合法数据 对此触发器可以做如下分析 由于仓库编码和仓位编码在仓位资料表中具有组合唯一性 若在Inserted表中查询到的 仓库编码 仓位编码 在仓位资料表中并不存在 则要插入的或更新的记录中的仓库编码 仓位编码不是合法数据 这时候需要回滚处理 6 2 2分销系统触发器设计 在SQLQuery窗口中执行如下命令 createtriggerTRI rkdmxbon入库单明细表forinsert updateasbegindeclare iintset i selectcount 仓库编码 仓位编码 frominsertedwhere仓库编码 仓位编码notin selectdistinct仓库编码 仓位编码from仓位资料 if i 0beginprint 非法仓库仓位数据 ro