Oracle触发器讲解解析课件

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

2、信息。 当表被修改的时候，自动给需要执行操作的程序发信号。第2页，共36页。1触发器概念及组成 制作一个数据库触发器，目的是在某个表上执行特定的数据维护操作时，隐含地执行一个PLSQL块。第3页，共36页。第4页，共36页。例子：制作一个数据库触发器。记录下那些超过其工种工 资范围的工资值(但不禁止这种操作) 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

3、_maxsal sal_guide.maxsalTYPE； e_sal_out_of_range EXCEPTION；第5页，共36页。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(li

4、ne_nr，line) VALUES(l,Salary| TO_CHAR(:new.sal)| is out of range for employee|TO_CHAR(:new.empno)； END；第6页，共36页。 关联触发器 把一个数据库触发器的动作与另一个触发器联系起来，使之触发这另一个触发器。第7页，共36页。例子制作相关联的触发器。在一个全局变量中统计 审计信息的总数 CREATE TRIGGER count_audit_message BEFORE INSERT ON audit_message BEGIN emp_package.v_total_audit:=emp_pac

5、kage.v_total_audit+1； END；注释： 参数MAXOPENCURSORS限制在一个数据库中可用 的关联触发器的个数，其缺省值为32。第8页，共36页。第9页，共36页。 写触发器的源代码之前，先确定好其触发时间、触发事件及触发器的类型。内容 描述 可能值触发时间 与触发事件的时间次序 BEFORE AFTER触发事件 触发触发器的数据操作 INSERT 事件类型 UPDATE DELETE触发器类型 触发器体被执行的次数 Statement ROW触发器体 该触发器将要执行的动作 完整的 PL SQL块第10页，共36页。 行级触发器与语句级触发器的区别 根据进行一个操作时

6、触发器的触发次数，来决定是创建一个语句级触发器，还是创建一个行级触发器。 注意当某操作只影响到表中的一行数据时，语句级触发器与行级触发器的效果相同。第11页，共36页。例子：下面的语句对语句级触发器和行级触发器效果一样SQL INSERT INTO DEPT(deptno，dname) VALUES(50，EDUCATION)； SQL UPDATE DEPT SET LOCMAUI WHERE DEPTNO50；SQL DELETE FROM DEPT WHERE DEPTNO50；第12页，共36页。第13页，共36页。 当触发事件影响数据库的多行时，语句级触发器只触发一次，而行级触发器则

7、每一行被触发一次。第14页，共36页。例子-下列语句的行级触发器与语句级触发器效果不同 SQLINSERT INTO EMP(empno，ename) 2 SELECT empno，ename 3 FROM EMP_BACKUP；SQL UPDATE DEPT 2 SET SAL SAL*1.1 3 WHERE DEPTNO10；SQL DELETE FROM EMP 2 WHERE DEPTNO10；第15页，共36页。2创建触发器 开发触发器的步骤 用系统编辑器或字处理软件写一个含有 CREATE TRIGGER语句的脚本文件。 在写 CREATE TRIGGER语句时，充分考虑运行时出

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

9、 event1 OR event2. ON table_name PL/SQL block第17页，共36页。其中: trigger name 触发器名 timing 指明触发事件触发的时间顺序： BEFORE AFTER event 指明触发事件的数据操纵类型 INSERT UPDATE DELETE table name 标明与该触发器相关联的表名 plsql block 触发器体，指明该触发器将执行的操 作第18页，共36页。例子1创建一个 BEFORE型语句级触发器。限制一周内往 EMP表插入数据的时间CREATE OR REPLACE TRIGGER secure_emp BEFOR

10、E INSERT ON empBEGIN IF(TO_CHAR(sysdate,DY) IN(SAT，SUN) OR(TO_CHAR(sysdate,HH24) NOT BETWEEN 8 AND 18) THEN RAISE_APPLICATION_ERROR(-20500， YOU may only insert into EMP during normal hours); END IF； END；第19页，共36页。例子：创建一个包含多个触发事件的触发器。在触发器体中使用条件语句，指明 INSERTING，UPDATING及 DELETING，从而把多种触发事件组成一个触发器。 对前面的

11、例子进行扩展，使其不但限制某周内插入数据的时间，还限制进行数据修改和删除的时间。第20页，共36页。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 may only

12、delete from EMP during normal hours.) ； ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(一20500， You may only insert into EMP during mormal hours) ； 第21页，共36页。 ELSIF UPDATING(SAL) THEN RAISE_APPLICATION_ERROR(一20503， You may only update SAL column during normal hours) ； ELSE RAISE_APPLICATION_ERROR(一205

13、04， You may only update EMP table during normal hours) ； END IF； END IF； END； 第22页，共36页。 行级触发器 通过在 CREATE TRIGGER语句中指定 FOR EACH ROW子句创建一个行级触发器，使其在受到触发事件影响的每一行上都被触发。 语法：创建一个行级触发器CREATE OR REPLACE TRIGGER trigger_nameBEFORE|AFTER trigger_event1 OR trigger_event2. ON table_nameFOR EACH ROW WHEN restric

14、ting_conditionPL/SQL block;其中:restricting condition为指定的条件,以确定触发器体是否被执行。第23页，共36页。例子：创建一个行级触发器。记录下每个用户对数据库的表进行数据操纵的次数 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_

15、name IS NULL；第24页，共36页。 ELSIF INSERTING THEN UPDATE 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；第25页，共36页。 EISE UPDATE audit_table SET u

16、pd=upd+1 WHERE user_name= user AND table_nameEMP AND column_name IS NULL； END IF；END；注释: 如果一个触发器不必在触发事件前触发，请创建 AFTER型触发器(不要创建 BEFORE型)第26页，共36页。 行级触发器的标识符 在行级触发器中，在列名前加上 OLD标识符标识该列变化前的值，加上 NEW标识符标识变化后的值。 数据操作 旧值 新值 Insert NULL 插入的值 Update 修改前的值 修改后的值 Delete 删除前的值 NULL第27页，共36页。例子：在行级触发器中获取某列的新值和旧值。为

17、 EMP表中的所有数据保留一个历史档案（审计） CREATE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN INSERT INTO audit_emp_values(user_name，timestamp， empno，old_ename，new_ename，old_job，new_job， old_mgr, new_mgr， old_sal，new_sal) VALUES(USER，SYSDATE，：old.empno，：old.ename， ： new.ename，：o

18、ld.job，：new.job，：old.mgr， ：new.mgr，： old.sal，：new sal)； END；第28页，共36页。详细的用户审计表 用 WHEN子句来保证，当某些行满足一定条件时，在该行上行级触发器才被触发。第29页，共36页。例子：在行级触发器限制其触发。自动计算销售员的奖金CREATE TRIGGER derive_comm BEFORE INSERT OR UPDATE OF sal ON emp FOR EACH ROW WHEN （new.jobSALESMAN) BEGIN :m ：= ：old. comm * (：new. sal ：old. sal)； END; 注释: 在 WHEN子句中，标识符前不加冒号(：) 在语句级触发器中不要使用 WHEN子句。第30页，共36页。3触发器的管理 管理触发器的命令与管理过程的命令相似。 任务 命令_ 创建一个新的触发器 CREATE TRIGGER 修改一个已有的触发器