第章创建数据库触发器ppt课件.ppt

第九章创建数据库触发器 东软IT人才实训中心 Copyright2008ByNeusoftGroup Allrightsreserved 学时 4学时教学方法 讲授ppt 上机练习 点评 目标 本章旨在向学员介绍Oracle触发器的应用 通过本课的学习 学员应该掌握如下知识 1 描述不同触发器类型的区别2 描述数据库触发器和它们的用途3 创建数据库触发器4 描述数据库触发器的触发 firing 规则5 删除触发器 数据库触发器 是特殊的PL SQL块或存储过程是基于表 视图 方案 数据库的是针对特定事件发生时触发的是隐式执行的 触发器的类型 应用程序触发器在应用程序内发生特定DML事件时 将隐式触发它数据库触发器在表 视图 方案或数据库上发生特定事件时 将隐式触发它 设计触发器的原则 设计触发器为了 保证当一个指定的操作被执行时 执行相关的动作 集中全局操作下面的情况不用触发器 已经内建于Oracle服务器中的功能 重复其它的触发器如果触发器的算法很长 将算法创建于存储过程中 再在触发器中调用它们 触发器的大小不能超过32K 过分地使用触发器可能导致复杂的依赖 这在大的应用程序中可能会产生维护困难 DML触发器 一个触发器语句包含 触发时间 对于表 BEFORE AFTER 对于视图 INSTEADOF触发事件 INSERT UPDATE或DELETE表名 Ontable view触发器类型 Row或statement 只执行一次 WHEN子句 限制条件 只能在行级触发器中使用触发器体 PL SQL块 DML触发器 续 在写触发器代码之前 确定触发器各部分的值 触发时机 触发事件和触发类型 如果在一个表上定义了多个触发器 要意识到相同类型的多个触发器的触发顺序是随机的 为了保证相同类型的多个触发器以特定的顺序触发 统一多个触发器到一个触发器中 再以想要的顺序分别调用过程 创建DML语句级别的触发器 CREATE ORREPLACE TRIGGERtrigger name BEFORE AFTER INSERT UPDATE ofcolumn DELETE ONtable nameTrigger body 创建DML语句级别的触发器 续 CREATEORREPLACETRIGGERsal empBEFOREINSERTONempBEGINIF to char sysdate DY in 星期六 星期日 thenraise application error 20001 只是在工作期间可以录入数据 ENDIF END INSERTINTOemp empno ename job mgr hiredate sal VALUES 1002 ljs clerk 7902 sysdate 2000 DML触发器的触发时间 触发时间 什么时候触发器应该触发 BEFORE 在表上触发DML事件之前执行触发器体 AFTER 在表上触发DML事件之后执行触发器体 INSTEADOF 修改视图时执行触发器体代替触发语句 DML触发器的触发时间 续 比较事件的BEFORE与AFTER之间差别 CREATEORREPLACETRIGGERcount tBEFORE AFTER INSERTONtestDECLAREv countnumber 2 BEGINselectcount intov countfromtest insertintotest1values v count END Insertintotestvalues aa DML触发器的触发事件 触发事件可以是表上的INSERT UPDATE或DELETE语句当触发事件是一个UPDATE语句时 可以用一个字段列表来确定那些必需触发触发器自来改变的列 不能为INSERT或DELETE语句指定字段列表 因为它们总是影响整个行 UPDATEOFsal 触发事件可以是一个 两个或全部DML语句的三个 INSERT或UPDATE或DELETE INSERT或UPDATEOFjob DML触发器的触发类型 触发器类型 触发器体将对语句影响的每行都执行 还是只执行一次 语句 触发器体对于触发事件执行一次 这是默认 一个语句触发器触发一次 即使根本没有行受影响 行 触发体对受触发事件影响的每行执行一次 如果触发事件没有受影响的行 行触发器不执行 触发顺序 在一个表中操纵一个单个的行时使用下面的触发顺序 INSERTINTOdept deptno dname loc VALUES 400 CONSULTING shenyang 触发动作 BEFORE语句触发器 BEFORE行触发器 AFTER行触发器 AFTER语句触发器 触发顺序 续 在一个表中操纵许多行时使用下面的触发顺序 UPDATEempSETsal sal 1 1WHEREdeptno 30 触发动作 BEFORE语句触发器 AFTER语句触发器 BEFORE行触发器 AFTER行触发器 BEFORE行触发器 AFTER行触发器 练习 在emp表上创建语句级别的触发器 当用户在8 00点至17 00点以外插入 修改 删除数据之后 系统提示 只是在工作期间可以录入数据 CREATEORREPLACETRIGGERsal empBEFOREINSERTORUPDATEORDELETEONempBEGINIF to char sysdate HH24 MI NOTBETWEEN 08 00 AND 17 00 THENraise application error 20001 只是在工作期间可以录入数据 ENDIF END INSERTINTOemp empno ename job mgr hiredate sal VALUES 1002 ljs clerk 7902 sysdate 2000 练习 只允许在正常的办公时间 即从周一到周五的上午8 45到下午5 30允许改变数据 创建一个存储过程SECURE DML 它防止DML语句在正常的办公时间之外执行 返回信息 你只能在正常的办公时间改变数据 在dept表上创建一个语句触发器调用上面的过程 临时修改过程中的时间并且尝试插入一条新纪录到dept表中以测试过程 合并多个事件的事例 CREATEORREPLACETRIGGERsal empBEFOREINSERTORUPDATEORDELETEONempBEGINIF to char sysdate HH24 MI notbetween 08 00 and 17 00 THENIFdeletingTHENraise application error 20001 只是在工作期间可以删除数据 ELSIFinsertingTHENraise application error 20002 只是在工作期间可以录入数据 ELSIFupdating sal THENraise application error 20003 只是在工作期间可以更新薪水数据 ELSEraise application error 20004 只是在工作期间可以更新数据 ENDIF ENDIF END 创建DML行触发器 CREATE ORREPLACE TRIGGERtrigger name BEFORE AFTER INSERT UPDATE ofcolumn DELETE ONtable name REFERENCINGOLDasold NEWasnew FOREACHROW WHEN condition Trigger body 创建DML行触发器 续 在一个行触发器中 在数据改变之前或之后 可以用OLDandNEW限定词作为前缀引用一个列的值 创建DML行触发器 续 CREATEORREPLACETRIGGERaudit emp valuesAFTERDELETEORINSERTORUPDATEONempFOREACHROWBEGININSERTINTOaudit emp table user name timestamp old empno new empno old ename new ename old job new job old sal new sal VALUES USER SYSDATE OLD empno NEW empno OLD ename NEW ename OLD job NEW job OLD sal NEW sal END OLD和NEW限定词只在行触发器中可用在每一个SQL和PL SQL语句中这些限定词用冒号 前缀 创建DML行触发器 续 INSERTINTOemp empno ename job sal VALUES 999 Temp CLERK 1000 UPDATEempSETsal 2000 ename Smith WHEREempno 999 SELECT FROMaudit emp table 创建DML行触发器 续 CREATEORREPLACETRIGGERsal empBEFOREUPDATEOFsalONempFOREACHROWBEGINIF new sal old sal THENraise application error 20001 薪水太高了 ENDIF END UPDATEempSETsal 5000 创建DML行触发器 续 1 创建一个表tmp table 包含empno ename sal old sal new job五列信息2 在emp表上创建一个update行触发器 当新的薪水小于原有的薪水时 在tmp table表中填加一行信息 行触发器的限制 CREATEORREPLACETRIGGERsal empBEFOREINSERTORUPDATEOFsalONempFOREACHROWWHEN new job MANAGER BEGINraise application error 20001 需要关注 END INSERTINTOemp empno ename job mgr hiredate sal VALUES 1003 ljs MANAGER 7902 sysdate 5500 创建视图 在emp dept表上创建视图my view 查询empno ename job sal deptno dname列信息 通过视图插入相应的信息 CREATEVIEWmy viewASSELECTemp empno emp ename emp job emp sal emp deptno dept dnameFROMemp deptWHEREemp deptno dept deptno INSERTINTOmy viewVALUES 1005 ljs clerk 2000 30 sales INSTEADOF触发器 用INSTEADOF触发器来修改DML语句不能更新的视图上的数据 INSTEADOF触发器被用于直接在基表上执行INSERT UPDATE或DELETE操作 可以对视图写INSERT UPDATE或DELETE语句 INSTEADOF触发器在后台不可见地工作 产生正确的动作 创建INSTEADOF触发器 CREATEORREPLACETRIGGERtrigger nameINSTEADOF INSERT UPDATE OFcolumn DELETE ONview name REFERENCINGOLDasold NEWasnew FOREACHROWTrigger body 创建INSTEADOF触发器事例 CREATEORREPLACETRIGGERsal empINSTEADOFINSERTONmy viewFOREACHROWBEGININSERTINTOdept deptno dname VALUES 90 neu INSERTINTOemp empno ename job sal deptno VALUES 1005 ljs clerk 2000 90 END INSERTINTOmy viewVALUES 1005 ljs clerk 2000 30 sales 创建INSTEADOF触发器事例 续 创建视图empv 根据部门分组显示部门编号 部门雇员的最大薪水 最少薪水 通过视图将部门编号为10的雇员最少薪水增加500 CREATEORREPLACEVIEWempvASSELECTdeptno max sal max s min sal min sFROMempGROUPBYdeptno updateempvsetmin s min s 500wheredeptno 10 CREATEORREPLACETRIGGERsal empINSTEADOFUPDATEONempvFOREACHROWBEGINupdateempsetsal sal 500wheresal selectmin sal fromempwheredeptno 10 anddeptno 10 END 数据库触发器和存储过程之间的区别 如果在一个触发器的编译期间发生错误 该触发器仍然会创建 小结 根据用途开发不同类型的数据库结构程序 维护触发器 禁用或重新启用数据库触发器 禁用或重新启用一个表中所有的数据库触发器 重新编译一个表中的触发器 ALTERTRIGGERtrigger nameDISABLE ENABLE ALTERTABLEtable nameDISABLE ENABLEALLTRIGGERS ALTERTRIGGERtrigger nameCOMPILE DROPTRIGGER语法 用DROPTRIGGER语法从数据库中删除一个触发器 当表被删除时 表上的所有触发器都被删除 DROPTRIGGERsal emp DROPTRIGGERtrigger name 触发器执行模式和约束检查 CREATEORREPLACETRIGGERsal empBEFOREUPDATEONempFOREACHROWBEGININSERTINTOdeptVALUES 99 dept99 neu END UPDATEempSETdeptno 99WHEREempno 7788 创建数据库触发器 触发用户事件CREATE ALTER DROPLogingon Logingoff触发数据库或系统事件数据库shutdown startup一个指定的错误 或任何错误 出现 在DDL语句上创建触发器 CREATE ORREPLACE TRIGGERtrigger nametiming ddl event1 ORddl event2OR ON database schema Trigger body 在系统事件上创建触发器 CREATE ORREPLACE TRIGGERtrigger nameTiming database event1 ORdatabase event2OR ON database schema Trigger body LOGON触发器事例 CREATEORREPLACETRIGGERlogon trigAFTERLOGONONSCHEMABEGININSERTINTOlog table user id log date action VALUES USER SYSDATE Loggingon END LOGOFF触发器练习 创建基于数据库的logoff触发器 当用户退出时 将信息记录在log table中 信息包括用户名 退出的系统时间 Loggingoff 信息 CREATEORREPLACETRIGGERlogoff trigBEFORELOGOFFONdatabaseBEGININSERTINTOlog table user id log date action VALUES USER SYSDATE Loggingoff END CALL语句 CREATE ORREPLACE TRIGGERtrigger nameTimingevent1 ORevent2ORevent3 ONtable name REFERENCINGOLDASold NEWASnew FOREACHROW WHENcondition CALLprocedure name CREATEORREPLACETRIGGERlog employeeBEFOREINSERTONEMPCALLlog execution CALL语句 续 CREATETRIGGERsalary checkBEFOREUPDATEOFsal jobONempFOREACHROWWHEN NEW job MANAGER CALLcheck sal NEW job NEW sal UPDATEempSETsal 5000 job CLERK WHEREdeptno 10 注 在CALL语句的末尾没有分号 在emp表上创建触发器check s