触发器PPT课件

触发器 内容 什么是触发器 触发器的作用触发器的分类触发器的创建触发器的应用触发器的注意问题 什么是触发器 引言 当表或视图中的某些重要数据发生变化时 为了保持数据的一致性和完整性 可以自动执行某段程序保证相关联其他数据也跟着进行相应的变化 能完成这种功能的程序就是触发器 触发器是特殊类型的存储过程 它能在任何试图改变表或视图中由触发器保护的数据时执行 触发器主要通过操作事件 INSERT UPDATE DELETE 进行触发而被自动执行 不能直接调用执行 也不能被传送和接受参数 触发器与表或视图是不能分开的 触发器定义在一个表或视图中 当在表或视图中执行插入 INSERT 修改 UPDATE 删除 DELETE 操作时触发器被触发自动执行 当表或视图被删除时与它关联的触发器也一同被删除 什么是触发器 Ret Emp表 Derek EMP表 退休 什么是触发器 EMP表 Ret Emp表 插入 删除 触发器触发 触发器的作用 比较不同版本的数据 读取其他数据库的表中的数据在数据库中所有的相关表中级联所作的修改或删除回滚无效的修改强制实现比由CHECK约束提供的限制更为复杂的限制 但是可以跨表 跨库 跨服务器执行本地和远程存储过程7 强制引用完整性 尽可能使用声明性引用完整性 主外键 但是对于有些情况下 例如跨数据库 跨服务器 复杂关系 DRI无法实现 还是需要使用触发器来实现强制的引用完整性 这种情况比较少 触发器的创建 创建触发器 CREATETRIGGERTrigger nameONtable WITHENCRYPTION FOR AFTER DELETE INSERT UPDATE ASSql statements注意 FOR和AFTER作用相同 我们建议使用AFTER 因为别的数据库也支持 触发器的分类 DML触发器 Insert触发器 插入操作时触发Update触发器 修改操作时触发Delete触发器 删除操作时触发Insteadof触发器 替代视图操作时触发级联触发器 实现级联操作 嵌套触发器 一个触发器触发下一个触发器 依此类推 DDL触发器 Create Alter Drop DDL操作时触发 触发器的应用 使用Inserted和Deleted表触发器可以访问两个逻辑表Inserted表Deleted表Inserted和Deleted表包含更新前和更新后的数据映像Inserted和Deleted表中不包含表中不受更新操作影响的数据Inserted表和Deleted表在内存的缓冲区里 触发器的应用 Insert触发器 当试图向表中插入数据时 将执行INSERT触发器INSERT触发器执行下列操作 向Inserted表中插入一个新行的副本 检查Inserted表中的新行 确定是否要阻止该插入操作 如果所插入的行中的值是有效的 则将该行插入到触发器表中 触发器的应用 Insert触发器 例 CREATETRIGGERCheckRoyaltyONroyschedFORINSERTASIF SELECTroyaltyFROMinserted 30BEGINPRINT RoyaltyTrigger 版权费不能超过30 PRINT 请将版权费修改为小于30的值 ROLLBACKTRANSACTIONEND 触发器的应用 UPDATE触发器 当试图更新表中的数据时 将执行UPDATE触发器UPDATE触发器执行下列操作 将原始数据行移到逻辑Deleted表中将一个新行插入Inserted表中 然后插入触发器表中计算Deleted表和Inserted表中的值以确定是否需要进行干预可以创建Update触发器以验证对单个列或整个表的更新 触发器的应用 UPDATE触发器 例 列级UPDATE触发器CREATETRIGGERNoUpdatePaytermsONsalesFORUPDATEASIFUPDATE payterms 如果修改了某列BEGINPRINT 不能修改订单的付费条款 ROLLBACKTRANSACTIONEND 触发器的应用 UPDATE触发器 例 表级UPDATE触发器 CREATETRIGGERNoUpdateDiscountONdiscountsFORUPDATEASIF SELECTdiscountFROMinserted 12BEGINPRINT 不能指定大于12 的折扣 ROLLBACKTRANSACTIONEND 触发器的应用 DELETE触发器 当试图从表中删除数据时 将执行DELETE触发器 DELETE触发器执行下列操作 从触发器表中删除行 将删除的行插入到Deleted表中 检查Deleted表中的行 以确定是否需要或应如何执行触发器操作 触发器的应用 DELETE触发器 例 CREATETRIGGERNoDelete9901ONpub infoFORDELETEASIF SELECTpub idFROMdeleted 9901BEGINPRINT 不能删除出版商9901的详细信息 ROLLBACKTRANSACTIONEND 触发器的应用 级联触发器 了解单独的DELETE或UPDATE语句可启动一系列级联引用操作 例如 假设有一个数据库包含三个表 TableA TableB和TableC 针对TableA中的主键 用ONDELETECASCADE定义TableB中的外键 针对TableB中的主键 用ONDELETECASCADE定义TableC中的外键 如果DELETE语句删除TableA中的行 则该操作也将删除TableB中具有与TableA中所删除的主键匹配的任何外键中的所有行 然后删除TableC中具有与TableB中所删除的主键匹配的任何外键中的所有行 由单个DELETE或UPDATE触发的一系列级联引用操作必须构成不包含循环引用的树 在DELETE或UPDATE所产生的所有级联引用操作的列表中 每个表只能出现一次 级联引用操作树到任何给定表的路径必须只有一个 树的任何分支在遇到指定了NOACTION或默认为NOACTION的表时终止 触发器的应用 嵌套触发器 如果一个触发器在执行操作时会启动另一个触发器 而第二个触发器又接着启动下一个触发器 以次类推 些触发器称为嵌套触发器 触发器最多可以嵌套32层 要启用触发器嵌套 请以以下方式执行sp configure系统存储过程 sp configure nestedtrigger 1要禁用触发器嵌套 请执行下面的语句 sp configure nestedtrigger 0 触发器的应用 INSTEADOF触发器 包含代替原始数据操作语句的代码INSTEADOF触发器的主要优点是可以使不能更新的视图支持更新INSTEADOF触发器的另一个优点是使得以编写这样的逻辑代码 可以拒绝批处理中的某些部分同时允许批处理的其它部分成功 触发器的应用 INSTEADOF触发器 视图定义示例 CREATEVIEWEmp pubASSELECTemp id lname job id pub nameFROMemployeee publisherspWHEREe pub id p pub id触发器定义示例 CREATETRIGGERdel empONEmp pubINSTEADOFDELETEASDELETEemployeeWHEREemp idIN SELECTemp idFROMDELETED 其它示例 触发器的应用 DDL触发器 一 MicrosoftSQLServer2005在数据库引擎中引入了多项能提高程序员的开发能力和工作效率的改进和新功能 其中DDL触发器就是其中之一 除了对修改数据的数据操作语言语句 INSERT UPDATE和DELETE 所激发的触发器提供支持外 数据库引擎还包括一类新的触发器 由修改数据库对象的DDL语句 如以CREATE ALTER或DROP开头的语句 激发 DDL触发器可用于审核或控制对数据库架构的更改 像常规触发器一样 DDL触发器将激发存储过程以响应事件 这些语句主要是以CREATE ALTER和DROP开头的语句 DDL触发器可用于管理任务 例如审核和控制数据库操作 仅在运行触发DDL触发器的DDL语句后 DDL触发器才会激发 DDL触发器无法作为INSTEADOF触发器使用 二 在响应当前数据库或服务器中处理的Transact SQL事件时 可以激发DDL触发器 触发器的作用域取决于事件 例如 每当数据库中发生CREATETABLE事件时 都会触发为响应CREATETABLE事件创建的DDL触发器 每当服务器中发生CREATELOGIN事件时 都会触发为响应CREATELOGIN事件创建的DDL触发器 触发器的应用 DDL触发器 在下面的示例中 每当数据库中发生DROPTABLE事件或ALTERTABLE事件 都将触发DDL触发器safety CREATETRIGGERsafetyONDATABASEFORDROP TABLE ALTER TABLEASPRINT YoumustdisableTrigger safety todroporaltertables ROLLBACK 数据库范围内的DDL触发器都作为对象存储在创建它们的数据库中 可以在master数据库中创建DDL触发器 这些DDL触发器的行为与在用户设计的数据库中创建的DDL触发器一样 可以从创建DDL触发器的数据库上下文中的sys triggers目录视图中 或通过指定数据库名称作为标识符 例如master sys triggers 来获取有关这些DDL触发器的信息 服务器范围内的DDL触发器作为对象存储在master数据库中 不同的是 可以从任何数据库上下文中的sys server triggers目录视图中获取有关数据库范围内的DDL触发器的信息 使用EVENTDATA函数 可以捕获有关激发DDL触发器的事件的信息 此函数返回xml值 触发器的应用 DDL触发器 三 以下示例创建用于存储所有数据库级事件的相关信息的表 并在表中填充DDL触发器 通过对EVENTDATA生成的XML数据使用XQuery 可以捕获事件类型和Transact SQL语句 USEAdventureWorks GOCREATETABLEddl log PostTimedatetime DB Usernvarchar 100 Eventnvarchar 100 TSQLnvarchar 2000 GOCREATETRIGGERlogONDATABASEFORDDL DATABASE LEVEL EVENTSASDECLARE dataXMLSET data EVENTDATA INSERTddl log PostTime DB User Event TSQL VALUES GETDATE CONVERT nvarchar 100 CURRENT USER data value EVENT INSTANCE EventType 1 nvarchar 100 data value EVENT INSTANCE TSQLCommand 1 nvarchar 2000 GO Testthetrigger CREATETABLETestTable aint DROPTABLETestTable GOSELECT FROMddl log GO Dropthetrigger DROPTRIGGERlogONDATABASEGO Droptableddl log DROPTABLEddl logGO SQLServer2005中的DDL触发器的实现 触发器的应用 触发器执行顺序 触发器的应用 禁用触发器 1 如果是大批量的导入数据 而且确认数据的正确性没有问题 那么我们在数据导入的过程中可以禁用触发器提高数据导入的速度 2 一定要确认在数据导入的过程中 没有用户在操作数据 因此最好是进入单用户模式或者DBO ONLY模式 altertabledbo memberdisabletriggertestTrigger 触发器的应用 触发器激发顺序 了解1 在7 0以前 在一个表上的一个操作上 INSERT DELETE UPDATE 只能有一个触发器 例如在test表上的insert操作只能有一个触发器 2 只能为AFTER提供触发器3 只能为表提供触发器4 可以指定触发器为FIRST LAST 剩余的就是未定义5 保证FIRST首先执行 LAST最后执行 NONE在两者之间执行 但是不能保证NONE之间的执行顺序 触发器的注意问题 性能考虑 1 触发器一般比起CHECK约束的性能会差一些2 触发器和激发他的进程之间属于同一个事务 激发进程所锁定的所有资源对于触发器来说都是可以访问的 因为都属于同一个事务3 ROLLBACK回滚的是整个事务 而不是触发器4 即使是在触发器中显式的开始一个事务 回滚时也是回滚整个事务 5 保持短小精悍 因为触发器和激发进程在同一个事务中 触