Oracle触发器详细讲解.ppt

《Oracle触发器详细讲解.ppt》由会员分享，可在线阅读，更多相关《Oracle触发器详细讲解.ppt（36页珍藏版）》请在三一办公上搜索。

1、触发器 触发器类似于过程、函数，因为它们都是拥有说明部分、语句执行部分和异常处理部分的有名的PL/SQL块。与包类似，触发器必须存储在数据库中，并且不能被块进行本地化说明。但是，对于过程而言，可以从另一个块中通过过程调用显式地执行一个过程，同时在调用时可以传递参数。对于触发器而言，当触发事件发生时就会显式地执行该触发器，并且触发器不接受参数。,触发事件是在数据库表上执行的DML（INSERT、UPDATE、DELETE）操作。使用触发器，可以做许多事情，包括：维护不可能在表创建时通过说明性约束进行的复杂 的完整性约束限制。通过记录所进行的修改以及谁进行了修改来审计表 中的信息。当表被修改的时候

2、，自动给需要执行操作的程序发信号。,1触发器概念及组成,制作一个数据库触发器，目的是在某个表上执行特定的数据维护操作时，隐含地执行一个PLSQL块。,例子：制作一个数据库触发器。记录下那些超过其工种工 资范围的工资值(但不禁止这种操作)CREATE TRIGGER check_sal BEFORE INSERT OR UPDATE OF sal，job ON emp FOR EACH ROW WHEN(new.job PRESIDENT)DECLAER v_minsal sal_guide.minsalTYPE；v_maxsal sal_guide.maxsalTYPE；e_sal_out_o

3、f_range EXCEPTION；,BEGIN SELECT minsal，maxsal INTO v_minsal，v_maxsal FROM sal_guide WHERE job：new.job；IF:new.salv_minsal OR:new:salv_maxsal THEN RAISE e_sal_out_of_range;END IF；EXCEPTION WHEN e_sal_out_of_range THEN INSERT INTO audit_message(line_nr，line)VALUES(l,Salary|TO_CHAR(:new.sal)|is out of r

4、ange for employee|TO_CHAR(:new.empno)；END；,关联触发器 把一个数据库触发器的动作与另一个触发器联系起来，使之触发这另一个触发器。,例子制作相关联的触发器。在一个全局变量中统计 审计信息的总数,注释：参数MAXOPENCURSORS限制在一个数据库中可用 的关联触发器的个数，其缺省值为32。,写触发器的源代码之前，先确定好其触发时间、触发事件及触发器的类型。内容 描述 可能值触发时间 与触发事件的时间次序 BEFORE AFTER触发事件 触发触发器的数据操作 INSERT 事件类型 UPDATE DELETE触发器类型 触发器体被执行的次数 State

5、ment ROW触发器体 该触发器将要执行的动作 完整的 PL SQL块,行级触发器与语句级触发器的区别 根据进行一个操作时触发器的触发次数，来决定是创建一个语句级触发器，还是创建一个行级触发器。注意当某操作只影响到表中的一行数据时，语句级触发器与行级触发器的效果相同。,例子：下面的语句对语句级触发器和行级触发器效果一样,当触发事件影响数据库的多行时，语句级触发器只触发一次，而行级触发器则每一行被触发一次。,例子-下列语句的行级触发器与语句级触发器效果不同,2创建触发器 开发触发器的步骤 用系统编辑器或字处理软件写一个含有 CREATE TRIGGER语句的脚本文件。在写 CREATE TRI

6、GGER语句时，充分考虑运行时出 错的处理。在 SQL*Plus或 SVRMGR中运行脚本文件，将触发器的 源代码编译成编译代码p_code，并把源代码存储到数据 库中。编译代码p_code不存储在数据库中。调试编译错误。在 ORACLE环境中测试开发的触发器（用INSERT、UPDATE、DELETE语句对表进行操作）。在运行期间调试触发器的逻辑错误。,语句级触发器 用 CREATE TRIGGBR语句创建一个语句级触发器，该触发器在一个数据操作语句发生时只触发一次。语法创建一个语句级触发器 CREATE OR REPLACE TRIGGER trigger_name BEFORE|AFTE

7、R event1 OR event2.ON table_name PL/SQL block,其中:trigger name 触发器名 timing 指明触发事件触发的时间顺序：BEFORE AFTER event 指明触发事件的数据操纵类型 INSERT UPDATE DELETE table name 标明与该触发器相关联的表名 plsql block 触发器体，指明该触发器将执行的操 作,例子1创建一个 BEFORE型语句级触发器。限制一周内往 EMP表插入数据的时间,例子：创建一个包含多个触发事件的触发器。在触发器体中使用条件语句，指明 INSERTING，UPDATING及 DELET

8、ING，从而把多种触发事件组成一个触发器。对前面的例子进行扩展，使其不但限制某周内插入数据的时间，还限制进行数据修改和删除的时间。,CREATE TRIGGER secure_empBEFORE DELETE OR INSERT OR UPDATE ON empDECIARE v_dummy VARCHAR2(1)；BEGIN IF(TO_CHAR(sysdate，DY IN(SAT，SHN)OR(TO_NUMBER(sysdate，HH24)NOT BETWEEN 8 AND18)THEN IF DELETING THEN RAISE_APPLICATION_ERROR(一20502，You

9、 may only delete from EMP during normal hours.)；ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(一20500，You may only insert into EMP during mormal hours)；,ELSIF UPDATING(SAL)THEN RAISE_APPLICATION_ERROR(一20503，You may only update SAL column during normal hours)；ELSE RAISE_APPLICATION_ERROR(一20504，You ma

10、y only update EMP table during normal hours)；END IF；END IF；END；,行级触发器 通过在 CREATE TRIGGER语句中指定 FOR EACH ROW子句创建一个行级触发器，使其在受到触发事件影响的每一行上都被触发。语法：创建一个行级触发器CREATE OR REPLACE TRIGGER trigger_nameBEFORE|AFTER trigger_event1 OR trigger_event2.ON table_nameFOR EACH ROW WHEN restricting_conditionPL/SQL block;

11、其中:restricting condition为指定的条件,以确定触发器体是否被执行。,例子：创建一个行级触发器。记录下每个用户对数据库的表进行数据操纵的次数 CREATE TRIGGER audit_emp AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN IF DELETING THEN UPDATE audit_table SET del del+l WHERE user_name=user AND table_nameEMP AND colun_name IS NULL；,ELSIF INSERTING THEN U

12、PDATE audit_table SET ins=ins+l WHERE user_name=user AND table_name=EMP AND column_name IS NULL；ELSIF UPDATING(sal)THEN UPDATE audit_table SET upd=upd+l WHERE user_nameuser AND table_name=EMP AND column name=SAL；,EISE UPDATE audit_table SET upd=upd+1 WHERE user_name=user AND table_nameEMP AND column

13、_name IS NULL；END IF；END；,注释:如果一个触发器不必在触发事件前触发，请创建 AFTER型触发器(不要创建 BEFORE型),行级触发器的标识符 在行级触发器中，在列名前加上 OLD标识符标识该列变化前的值，加上 NEW标识符标识变化后的值。数据操作 旧值 新值 Insert NULL 插入的值 Update 修改前的值 修改后的值 Delete 删除前的值 NULL,例子：在行级触发器中获取某列的新值和旧值。为 EMP表中的所有数据保留一个历史档案（审计）,详细的用户审计表,用 WHEN子句来保证，当某些行满足一定条件时，在该行上行级触发器才被触发。,例子：在行级触发

14、器限制其触发。自动计算销售员的奖金,注释:在 WHEN子句中，标识符前不加冒号(：)在语句级触发器中不要使用 WHEN子句。,3触发器的管理,管理触发器的命令与管理过程的命令相似。任务 命令_ 创建一个新的触发器 CREATE TRIGGER 修改一个已有的触发器 CREATE OR REPLACE TRIGGER 删除触发器 DROP TRIGGER 与过程不同的是，当一个触发器不再适宜被触发时，可以使其处于无效状态。,语法使一个数据库触发器无效,语法：使一个数据库触发器重新变为有效,注释：当触发器刚创建时，它被自动置为有效。为了改善性能，以及在大量装载数据时(例如使用 SQL*Loader

15、)避 免进行数据的完整性校验，可以使 数据库触发器暂时无效。当发生网络连接故障、磁盘损坏，数据文件脱机或 表空间脱机，从而导致触发器中涉及到的实体不能 再被访问时，应使触发器失效。,管理触发器的命令与管理过程的命令稍有差别。,4触发器的应用,在应用程序中创建数据库触发器，可以大大增强 ORACLE系统的性能，完成一些 ORACLE系统服务不能完成的功能。,5.触发器的优点除了具有存储过程的优点外，数据库触发器还有许多其它的优点：提高数据的安全性 为数据操作提供了基于值的安全性检验机制 为数据操作提供了基于值的审计,提高数据的完整性 在数据库一级增强了数据完整性的动态限制。增强了复杂的参考完整性约束。确保一些相关的操作隐式地一起进行。,