new第10章触发器的创建和使用.ppt

2020 2 24 1 第10章触发器的创建和使用 2 第10章触发器的创建和使用 本章学习目标了解触发器和一般存储过程的主要区别了解使用触发器的优点掌握创建触发器的方法掌握查看触发器信息的方法掌握修改和删除触发器的方法 3 第10章触发器的创建和使用 10 1概述 1 10 2创建触发器 2 10 3查看 修改和删除触发器 2 3 3 4 10 1概述 10 1 1触发器的概念触发器是一种特殊类型的存储过程 它在指定的表中的数据发生变化时自动生效 一般的存储过程通过存储过程名称被直接调用 而触发器主要是通过当某个事件发生时自动被触发执行的 触发器可以用于SQLServer约束 默认值和规则的完整性检查 还可以完成难以用普通约束实现的复杂功能 5 10 1概述 触发器与存储过程的区别 一般的存储过程由用户根据需要通过存储过程名称被调用调用的 而触发器主要是通过事件 执行INSERT UPDATE或DELETE语句 进行触发而自动执行的 存储过程不依赖于某个表而单独存在 而触发器必须基于一个表或视图创建 6 10 1概述 10 1 2触发器的优点使用触发器有以下优点 触发器是自动执行的 触发器可以通过数据库中的相关表进行层叠更改 触发器可以强制限制 这些限制比用CHECK约束所定义的更复杂 与CHECK约束不同 触发器可以引用其它表中的列 触发器也可以评估数据修改前后的表状态 并根据其差异采取对策 7 10 1概述 10 1 3触发器的类型在SQLServer2005中 触发器可以分为DML触发器和DDL触发器两种 DDL触发器用于响应各种数据定义语言 DDL 事件 这些事件主要对应于Transact SQL中的CREATE ALTER和DROP语句 以及执行类似DDL操作的某些系统存储过程 它们用于执行管理任务 并强制影响数据库的业务规则 DML触发器是在用户使用数据操作语言 DML 事件编辑数据时发生 DML事件是针对表或视图的INSERT UPDATE或DELETE语句 DML触发器有助于在表或视图中修改数据时强制业务规则 扩展数据完整性 8 10 1概述 DML触发器又分为AFTER触发器和INSTEADOF触发器两种 AFTER触发器 这种类型的触发器将在数据变动 INSERT UPDATE和DELETE操作 完成以后才被触发 AFTER触发器只能在表上定义 INSTEADOF触发器 INSTEADOF触发器将在数据变动以前被触发 并取代变动数据的操作 而去执行触发器定义的操作 本章首页 9 10 2创建触发器 在创建触发器之前应该考虑以下几个问题 CREATETRIGGER必须是批处理中的第一条语句 触发器只能在当前的数据库中创建 TRUNCATETABLE语句不会引发DELETE触发器 WRITETEXT语句不会引发INSERT或UPDATE触发器 10 10 2创建触发器 当创建一个触发器时必须指定以下几项内容 触发器的名称 在其上定义触发器的表 触发器将何时激发 执行触发操作的编程语句 11 10 2 1使用SQLServer管理控制台创建DML触发器方法 打开相应表 右击 触发器 文件夹 新建触发器 10 2创建触发器 12 例10 1 创建一个INSERT触发器 TR Stu Insert 当在T STUDENT表中插入一条新记录时 触发该触发器 并给出 你插入了一条新记录 的提示信息 见P183 185 10 2创建触发器 13 10 2创建触发器 例10 2 创建了TR Stu Insert触发器后 查看向T STUDENT表中插入数据时 此触发器所完成的功能 USESTUDENTGOinsertintoT STUDENT S NUMBER S NAME SEX values 0554115 王小帆 女 GO 14 10 2创建触发器 10 2 2使用Transact SQL语句创建DML触发器可以使用CREATETRIGGER命令创建DML触发器 语法形式 CREATETRIGGERtrigger nameON table view WITH n FOR AFTER INSTEADOF INSERT UPDATE DELETE AS sql statement n ENCRYPTION EXECUTEASClause 15 10 2创建触发器 参数说明 WITHENCRYPTION 对CREATETRIGGER语句的文本进行加密处理 EXECUTEAS 指定用于执行该触发器的安全上下文 FOR AFTER AFTER指定触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发 不能对视图定义AFTER触发器 INSTEADOF 指定执行DML触发器而不是触发SQL语句 DELETE INSERT UPDATE 指定数据修改语句 必须至少指定一个选项 sql statement 触发条件和操作 16 10 2创建触发器 inserted表和deleted表 在创建触发器时可使用两个特殊的临时表 deleted表和inserted表 它们存在于内存中 inserted表和deleted表是逻辑 概念 表 它们在结构上类似于定义触发器的表 也就是用户在其中尝试操作的表 这些表用于保存用户操作可能更改的行的旧值或新值 inserted表存储被INSERT和UPDATE语句影响的新的数据行 deleted表存储被DELETE和UPDATE语句影响的旧的数据行 一个UPDATE操作相当于一个删除旧记录 插入新记录的组合操作 所以要用到INSERTED和DELETED表 17 inserted表和deleted表存放的信息 10 2创建触发器 18 1 inserted表 临时保存被INSERT和UPDATE语句影响的新的数据行 即在执行INSERT或UPDATE语句时 新的数据行被添加到数据表中 同时这些新的数据行也被复制到inserted临时表中 10 2创建触发器 inserted表工作示意图 19 10 2创建触发器 2 deleted表 临时保存了被DELETE和UPDATE语句影响旧的数据行 即在执行DELETE或UPDATE语句时 从数据表中删除旧的数据行 然后将这些旧的数据行存入deleted表中 deleted表工作示意图 20 10 2创建触发器 例10 3 创建一个AFTER触发器 要求实现以下功能 在T SCORE表上创建一个插入 更新类型的触发器TR ScoreCheck 当在SCORE字段中插入或修改考试分数后 触发该触发器 检查分数是否在0 100之间 21 10 2创建触发器 CREATETRIGGERTR ScoreCheckONT SCOREFORINSERT UPDATEASIFUPDATE SCORE PRINT AFTER触发器开始执行 BEGINDECLARE ScoreValuerealSELECT ScoreValue SELECTSCOREFROMinserted IF ScoreValue 100OR ScoreValue 0PRINT 输入的分数有误 请确认输入的考试分数 END 22 10 2创建触发器 接上页 验证TR ScoreCheck触发器的功能USEstudentGOPRINT 在t score中插入记录时触发器执行结果 PRINT INSERTINTOt scoreVALUES 0554112 10010218 35 INSERTINTOt scoreVALUES 0554113 100102183 152 GO 转下页 23 10 2创建触发器 接上页 PRINT 在t score中修改记录时触发器执行结果 PRINT UPDATEt scoreSETscore 115WHEREs number 0554112 UPDATEt scoreSETscore 65WHEREs number 0554113 GO 24 10 2创建触发器 例10 4 创建一个AFTER触发器 要求如下 在T STUDENT表上创建一个删除类型的触发器TR Stu Delete 当在T STUDENT表中删除某一条记录后 触发该触发器 在T SCORE表中删除与此学号对应的记录 设t student表与t score表之间无外键约束 25 10 2创建触发器 CREATETRIGGERTR Stu DeleteONT STUDENTFORDELETEASPRINT 删除触发器开始执行 DECLARE StuNumchar 10 PRINT 把在T STUDENT表中删除的记录的S NUMBER赋值给局部变量 StuNum SELECT StuNum S NUMBERFROMdeletedPRINT 开始查找并删除T SCORE表中的相关记录 DELETEFROMT SCOREWHERES NUMBER StuNumPRINT 删除了T SCORE表中的学号为 RTRIM StuNum 的记录 26 10 2创建触发器 验证Stu Delete触发器的功能USEstudentGOPRINT 在t score中插入记录时触发器执行结果 PRINT DELETEFROMt studentWHEREs number 0554112 DELETEFROMt studentWHEREs number 0554113 GO 27 10 2创建触发器 例10 5 创建一个INSTEADOF触发器 要求实现以下功能 在T COURSE表上创建一个删除类型的触发器TR NotAllowDelete 当在T COURSE表中删除记录时 触发该触发器 显示不允许删除表中数据的提示信息 28 10 2创建触发器 USESTUDENTGOCREATETRIGGERTR NotAllowDeleteONT COURSEINSTEADOFDELETEASPRINT INSTEADOF触发器开始执行 PRINT 本表中的数据不允许被删除 不能执行删除操作 GO 验证NotAllowDelete触发器的功能USEstudentGODELETEFROMt courseWHEREc number 20010203 GO 本章首页 29 10 3查看 修改和删除触发器 10 3 1查看并修改触发器1 使用SQLServer管理控制台查看并修改触发器 1 查看并修改触发器定义信息 30 10 3查看 修改和删除触发器 2 查看与触发器有依赖关系的其他数据库对象 31 10 3查看 修改和删除触发器 2 使用系统存储过程查看触发器sp help 用于查看触发器的一般信息 如触发器的名称 属性 类型和创建时间 sp help 触发器名称 sp helptext 用于查看触发器的正文信息 sp helptext 触发器名称 sp depends 用于查看指定触发器所引用的表 sp depends 触发器名称 32 10 3查看 修改和删除触发器 例10 6 使用系统存储过程查看TR Stu Insert触发器的一般信息 sp help TR Stu Insert 例10 7 使用系统存储过程查看T STUDENT表涉及到的所有触发器 sp depends TR Stu Delete 33 10 3查看 修改和删除触发器 10 3 2删除触发器1 使用SQLServer管理控制台删除触发器 34 10 3查看 修改和删除触发器 2 使用Transact SQL语句删除触发器使用系统命令DROPTRIGGER删除指定的触发器 语法形式 DROPTRIGGER trigger n 例10 8 使用系统命令删除TR Stu Insert触发器 DROPTRIGGERTR Stu Insert 35 10 3查看 修改和删除触发器 3 直接删除触发器所在的表删除触发器的另一个方法是直接删除触发器所在的表 删除表时 SQLServer将会自动删除与该表相关的所有触发器 本章首页 36 10 4思考与练习 1 触发器与一般的存储过程的主要区别是什么 2 简述触发器的分类及各自的特点 3 使用触发器有哪些优点 4 使用Transact SQL语句创建一个INSERT触发器 完成的功能是当在T SCORE表中插入或修改S NUMBER列的数据时 检测在T STUDENT表中是否存在相应的S NUMBER值 如果不存在 则给出相应的提示信息 5 删除触发器的方法有哪几种 并分别举例说明 2020 2 24 37 谢谢使用 再见