triggerPPT课件

1 5 6触发器 触发器是用户定义在关系表上的一类由事件驱动的特殊过程一旦定义 任何用户对表的增 删 改操作均由服务器自动激活相应的触发器 在DBMS核心层进行集中的完整性控制 类似于约束 但比约束更加灵活 可以实施比FOREIGNKEY约束 CHECK约束更为复杂的检查和操作 具有更精细和更强大的数据控制能力 2 5 6 1触发器的类型5 6 2触发器中的inserted表和deleted表5 6 3创建触发器5 6 4使用触发器实施特殊业务规则5 6 5管理触发器 5 6应用触发器 3 5 6 1触发器的类型 触发器 TRIGGER 是一种实施复杂数据完整性的特殊存储过程 如使用外键约束所不能实现的复杂参照完整性 它在数据操作语句执行时自动生效 通过触发器可实现跨数据库的参照完整性 SQLServer2005包括两大类触发器 DML触发器和DDL触发器 4 1 DDL触发器DDL触发器是SQLServer2005的新增功能 当服务器或数据库中发生数据定义语言 DDL 事件时触发 2 DML触发器当数据库中发生数据操作语言 DML 事件时 如在指定的表或视图中执行INSERT UPDATE或DELETE语句时 触发DML触发器 5 6 1触发器的类型 5 DML触发器又分为以下几种类型 1 AFTER触发器 在执行了INSERT UPDATE或DELETE语句之后触发AFTER触发器 其只能在表上指定 被INSERT语句触发的触发器称为INSERT型AFTER触发器 被DELETE语句触发的触发器称为DELETE型AFTER触发器 被UPDATE语句触发的触发器称为UPDATE型AFTER触发器 2 INSTEADOF触发器 执行INSTEADOF触发器代替触发动作进行激活 但同一种操作只能定义一种触发器 INSTEADOF触发器可以定义在表或视图上 3 CLR触发器 CLR触发器可以是AFTER触发器或INSTEADOF触发器 还可以是DDL触发器 6 3 DML触发器的特点DML触发器的特点如下 1 强制比CHECK约束更为复杂的数据完整性 2 实现数据库中多个表的级联更改 例如 表A的触发器A中包含对表B的数据操作A 而该操作A 插入 删除或修改 会导致表B中的触发器B被触发 触发器B指定的操作B又会影响表C的数据 3 一个表中可包含多个同类DML触发器 并允许采取不同的操作响应同一个修改语句 4 维护非规范化数据 实现特殊业务 5 使用自定义的错误消息 捕获破坏数据完整性的操作并返回自定义的错误提示信息 7 当触发器被触发 SQLServer为每个触发器在服务器内存中创建两个临时表 inserted表和deleted表 这是两个逻辑表 不会存储在数据库文件中 由系统自动维护而不允许用户直接对它进行修改 触发器工作完成后 这两个表也随之被删除 它们的结构总是与对应的触发器表的结构相同 5 6 2触发器中的inserted表和deleted表 8 1 inserted表用于存储触发器表中INSERT和UPDATE语句所影响的记录的副本 在一个插入或更新的操作事件处理中 新插入的记录被同时添加到触发器表和inserted表中 2 deleted表deleted表用于存储触发器表中DELETE和UPDATE语句所影响的记录的副本 在触发器表中执行DELETE或UPDATE操作事件时 记录从触发器表中删除 同时传输 复制 到deleted表中暂存起来 因此 deleted表和触发器表通常没有相同的数据行 因为此时记录已经从触发器表中被删除 提示 一个UPDATE操作可以分解为一个DELETE操作加一个INSERT操作 因此 UPDATE型触发器被触发时会同时创建deleted表和inserted表 9 触发器的定义包含以下四个要素 1 名称 有一个符合标志符命名规则的触发器名称 2 定义的目标 触发器必须定义在表或视图上 而AFTER触发器只能定义在表上 触发器表 3 触发条件 分别由INSERT UPDATE或DELETE操作事件语句触发 4 触发逻辑 被触发后会执行哪些操作 处理逻辑 5 6 3创建触发器 10 DBMS的完整性控制机制 续 例2 在 教授工资不得低于1000元 的约束中D约束作用的对象为工资Sal属性O插入或修改职工元组时ASal不能小于1000C职称 教授 A仅作用于职称 教授 的记录 P拒绝执行该操作 11 1 CREATETRIGGER语句的语法使用CREATETRIGGER语句创建触发器 基本语法如下 CREATETRIGGERtrigger nameONtable name view name WITHENCRYPTION FOR AFTER INSTEADOF INSERT DELETE UPDATE AS sql statement 5 6 3创建触发器 其中 trigger name 触发器名称 必须符合标识符的命名规则 且在当前数据库中唯一 table name view name 指定创建触发器的表或视图 即触发器表或视图 INSERT DELETE UPDATE 指明在触发器表中执行哪些操作时会激活对应类型的触发器 必须指定一个选项 选项顺序任意 选项之间用逗号分隔 AS 用来指定触发器要执行的操作序列 sql statement 触发器要执行的操作序列 12 创建UPDATE触发器在数据库JXGL的表student info中创建触发器student info update 实现当一名学生由一个班调到另一个班后 自动更新表class info中相应班级的学生数 stud num 代码如下 13 USEJXGLCREATETRIGGERstudent info update 指定触发器名称ONstudent info 指定触发器表FORUPDATE 指定触发条件ASDECLARE oldclass idvarchar 8 newclass idvarchar 8 SELECT oldclass id class idFROMdeletedSELECT newclass id class idFROMinsertedIF oldclass id newclass id BEGINUPDATEclass infoSETstud num stud num 1WHEREclass id oldclass idUPDATEclass infoSETstud num stud num 1WHEREclass id newclass idEND 14 USEJXGLCREATETRIGGERstudent info update 指定触发器名称ONstudent info 指定触发器表FORUPDATE 指定触发条件ASIF UPDATE class id BEGINUPDATEclass infoSETstud num stud num 1FROMclass info deletedWHEREclass info class id deleted class idUPDATEclass infoSETstud num stud num 1FROMclass info insertedWHEREclass info class id inserted class idEND 15 例题 1 设置一个针对student表的插入触发器 当插入某个学生记录 系别这项为空时 设定为CS 2 对sc创建一个一次只删除一条记录的触发器 否则报错 此删除操作可能会删除多次选课数据 3 级联更新 当修改course表中cno列的值时 同时修改sc表中的cno列的值 并且一次只能更新一行 否则报错 此删除操作可能会删除多门课程数据 16 例题 4 创建触发器 当输入某个同学选课成绩时 如果是课程学分超过3 其成绩自动提升10 5 为SC表建立一个插入触发器insertSC 当添加一门课程的成绩时自动输出此门课程的最高分 17 1 设置一个针对student表的插入触发器 当插入某个学生记录 系别这项为空时 设定为CS CREATETRIGGERINS STUDENTONSTUDENTFORINSERTASDECLARE SNOCHAR 5 SDEPTCHAR 2 SELECT SNO SNO SDEPT SDEPTFROMINSERTEDIF SDEPTISNULL UPDATESTUDENTSETSDEPT CS WHERESNO SNO 触发操作 INSERTINTOSTUDENTVALUES 98021 钱横 20 男 NULL 验证结果 SELECT FROMSTUDENTWHERESNO 98021 18 2 对sc创建一个一次只删除一条记录的触发器 否则报错 此删除操作可能会删除多次选课数据 createtriggerdel sconscfordeleteasdeclare row cntintselect row cnt count fromdeletedif row cnt 1beginprint 此删除操作可能会删除多次选课数据 rollbacktransactionend 触发操作 deletefromscwheresno 98001 SELECT RAISERROR 此删除操作可能会删除多次选课数据 16 1 19 3 级联更新 当修改course表中cno列的值时 同时修改sc表中的cno列的值 并且一次只能更新一行 否则报错 此删除操作可能会删除多门课程数据 createtriggercourse updateoncourseForupdateAsdeclare row cntintselect row cnt count fromdeletedIf row cnt 1beginprint 此删除操作可能会修改多门课程数据 rollbacktransactionendelse 20 beginifupdate cno beginDECLARE OLDCNOCHAR 2 DECLARE NEWCNOCHAR 2 SELECT OLDCNO CNOFROMDELETEDSELECT NEWCNO CNOFROMINSERTEDUpdatescsetcno NEWCNOWherecno OLDCNOendend 触发操作 第一种更新多行 updatecoursesetccredit 1whereccredit 3 第二种更新单行 完成级联更新updatecoursesetcno 02 wherecno 2 验证结果 SELECT FROMCOURSE SELECT FROMSCWHERECNO 02 21 4 创建触发器 当输入某个同学选课成绩时 如果是课程学分超过3 其成绩自动提升10 CREATETRIGGERsc insertONscFORINSERTASDECLARE SNOCHAR 5 CNOCHAR 2 CCREDITINTSELECT SNO SNO CNO CNOFROMINSERTEDSELECT CCREDIT CCREDITFROMCOURSEWHERECNO CNOIF CCREDIT 3 UPDATESCSETGRADE GRADE 1 1WHERESNO SNOANDCNO CNO 触发操作 INSERTINTOSCVALUES 98002 5 80 验证结果 SELECT FROMSCWHERESNO 98002 ANDCNO 5 22 5 为SC表建立一个插入触发器insertSC 当添加一门课程的成绩时自动输出此门课程的最高分 CREATETRIGGERinsertSCONSCFORINSERTASDECLARE MAXGRADEINTSELECT MAXGRADE MAX SC GRADE FROMSC INSERTEDWHERESC CNO INSERTED CNOPRINT MAXGRADE SELECT SELECT FROMSCWHERECNO 2 SELECTMAX GRADE FROMSCWHERECNO 2 触发操作 INSERTINTOSCVALUES 98004 2 72 23 1 设置一个触发器 当对student表某位学生进行更新时 限制学生年龄必须超过18 否则报错 学生年龄应该大于18 2 设置一个针对course表的插入触发器 当插入课程记录 学分Ccredit这项为空时 设定为1 3 对course创建一个一次只删除一条记录的触发器 否则报错 此删除操作可能会删除多门课程数据 4 级联更新 当修改student表中sno列的值时 同时修改sc表中的sno列的值 并且一次只能更新一行 第十一周实验内容 一 24 第十一周实验内容 一 5 创建触发器 当插入某个同学选课成绩时 如果其所在系是CS 其成绩自动加5分 6 为SC表建立一个更新触发器updateSC 当修改SC中某门课程的成绩时自动输出此门课程的平均成绩 7 为SC表建立一个插入触发器insertSC 当向SC表插入一条选课记录后自动统计输出该学生所选课程的门数 25 5 6 5管理触发器 2 使用T SQL语句查看触发器 1 使用系统存储过程sp helptrigger可以查看指定表中的触发器及其类型信息 如查看表student info中的触发器信息 代码如下 sp helptriggerstudent infoGO 2 使用系统存储过程sp