13-Oracle触发器(定义，管理及测试）

本章内容

1.触发器概述

2. DML 触发器

3. INSTEAD-OF 触发器

4. 系统触发器

5. 变异表触发器

6. 触发器的管理

触发器概述

• 触发器的概念与作用

• 触发器的类型

• 触发器组成

（1）触发器的概念与作用

• 触发器是一种特殊类型的存储过程，编译后存储在数据库服务器中。

• 当特定事件发生时，由系统自动调用执行，而不能由应用程序显式地调用执行。

• 触发器不接受任何参数。

• 触发器主要用于维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束，并对数据库中特定事件进行监控和响应。

（2）触发器的类型

•DML触发器

  建立在基本表上的触发器，响应基本表的 INSERT ， UPDATE ， DELETE 操作。

•INSTEAD OF触发器

  建立在视图上的触发器，响应视图上的 INSERT ， UPDATE ， DELETE 操作。

•系统触发器

          建立在系统或模式上的触发器，响应系统事件和DDL（CREATE，ALTER，DROP）操作。

（3）触发器组成

• 触发器由触发器头部和触发器体两个部分组成，主要包括：

  作用对象：触发器作用的对象包括表、视图、数据库和模式。

  触发事件：激发触发器执行的事件。如 DML 、 DDL 、数据库系统事件等。

  触发时间：用于指定触发器在触发事件完成之前还是之后执行。如果指定为 AFTER ，则表示先执行触发事件，然后再执行触发器；如果指定为 BEFORE ，则表示先执行触发器，然后再执行触发事件。

  触发级别：触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器，即触发事件发生后，触发器只执行一次。如果指定为 FOR EACH ROW ，即为行级触发器，则触发事件每作用于一个记录，触发器就会执行一次。

  触发条件：由 WHEN 子句指定一个逻辑表达式，当触发事件发生，而且 WHEN 条件为 TRUE 时，触发器才会执行。

  触发操作：触发器执行时所进行的操作。

DML触发器

• DML 触发器的种类及执行顺序

• 创建 DML 触发器

（1）DML触发器的种类及执行顺序

•DML触发器的种类

  语句级前触发器

  语句级后触发器

  行级前触发器

  行级后触发器

•DML触发器的执行顺序

  如果存在，则执行语句级前触发器。

  对于受触发事件影响的每一个记录：

• 如果存在，则执行行级前触发器；

• 执行当前记录的 DML 操作（触发事件）；

• 如果存在，则执行行级后触发器。

  如果存在，则执行语句级后触发器。

（2）创建DML触发器

•语法

  CREATE [OR REPLACE] TRIGGER trigger_name

  BEFORE|AFTER triggering_event [OF column_name]

  ON table_name]

  [FOR EACH ROW]

  [WHEN trigger_condition]

  DECLARE

       /*Declarative section is here */

  BEGIN

       /*Exccutable section si here*/

  EXCEPTION

       /*Exception section is here*/

  END [trigger_name];

•语句级触发器

  在默认情况下创建的 DML 触发器为语句级触发器，即触发事件发生后，触发器只执行一次。

•创建一个触发器，禁止在休息日改变雇员信息

CREATE OR REPLACE TRIGGER trg_emp_weekend
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
   IF TO_CHAR(SYSDATE, 'DY', 'nls_date_language= 
                         american') IN('SAT', 'SUN')
  THEN
      raise_application_error(-20000, 'Can''t operate in weekend. ');
  END IF;
END trg_emp_weekend;

•如果触发器响应多个DML事件，而且需要根据事件的不同进行不同的操作，则可以在触发器体中使用3个条件谓词。

谓词	行为
INSERTING	如果触发语句是INSERT，则为TRUE； 否则为FALSE
UPDATING	如果触发语句是UPDATE，则为TRUE； 否则为FALSE
DELETING	如果触发语句是DELETE，则为TRUE； 否则为FALSE

为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。

CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
  v_count NUMBER;
  v_sal   NUMBER(6,2);
BEGIN
  IF INSERTING THEN 
    SELECT count(*) INTO v_count FROM emp;
    DBMS_OUTPUT.PUT_LINE(v_count);
  ELSIF UPDATING THEN
    SELECT avg(sal) INTO v_sal FROM emp;
    DBMS_OUTPUT.PUT_LINE(v_sal);
  ELSE
    FOR v_dept IN (SELECT deptno,count(*) num FROM emp 
                                                   GROUP BY deptno) LOOP
      DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);
    END LOOP;
  END IF;
END trg_emp_dml; 

•行级触发器

  行级触发器是指执行 DML 操作时，每操作一个记录，触发器就执行一次，一个 DML 操作涉及多少个记录，触发器就执行多少次。

  在行级触发器中可以使用 WHEN 条件，进一步控制触发器的执行。

  在行级触发器中引入了 :old 和 :new 两个标识符，来访问和操作当前被处理记录中的数据。

•标识符

  :old 和 :new 作为 triggering_table%ROWTYPE 类型的两个变量

  在不同触发事件中， :old 和 :new 的意义不同

触发事件	:old	:new
INSERT	未定义，所有字段都为NULL	当语句完成时，被插入的记录
UPDATE	更新前原始记录	当语句完成时，更新后的记录
DELETE	记录被删除前的原始值	未定义，所有字段都为NULL

•引用方式：

      :old.field和:new.field （执行部分）

       old.field 和new.field   (WHEN条件中)

•注意事项：

  是伪记录，不能作为整个记录进行赋值或引用

  不能传递给带 triggering_table%ROWTYPE 参数的过程和函数

  如果触发器是建立在嵌套表上， ;old 和 ;new 都执行嵌套表的行， : parent 指向父表中的当前行。

为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。

CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
   IF INSERTING THEN 
         DBMS_OUTPUT.PUT_LINE(:new.empno||' '|| :new.ename);
   ELSIF UPDATING THEN
         DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);
   ELSE
         DBMS_OUTPUT.PUT_LINE(:old.empno||' '|| :old.ename);
   END IF;
END trg_emp_dml_row;

•在行级触发器中，可以使用WHEN子句进一步控制触发器的执行。

例如，修改员工工资时，保证修改后的工资高于修改前的工资。

CREATE OR REPLACE TRIGGER trg_emp_update_row
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.sal<=old.sal)
BEGIN
    RAISE_APPLICATION_ERROR(-20001,'The salary is lower!');
END trg_emp_update_row; 

INSTEAD OF触发器

•特点

  只能定义在视图上

  Instead-of 触发器是行级触发器

  Instead-of 触发器由 DML 操作激发，而 DML 操作本身并不执行

•作用

  修改一个本来不可以修改的视图

  修改视图中某嵌套表列的列

• 如果视图中包含下列任何一项，则该视图不可修改

  集合操作符 （ UNION ， UNION ALL ， MINUS ， INTERSECT ）；

  聚集函数（ SUM ， AVG 等）；

  GROUP BY ， CONNECT BY 或 START WITH 子句 ；

  DISTINCT 操作符；

  涉及多个表的连接操作。

•创建INSTEAD OF触发器的基本语法

  CREATE [OR REPLACE] TRIGGER trigger_name

  INSTEAD OF triggering_event [OF column_name]

  ON view_name

  FOR EACH ROW

  [WHEN trigger_condition]

  DECLARE

        /*Declarative section is here */

  BEGIN

        /*Exccutable section si here*/

  EXCEPTION

        /*Exception section is here*/

  END [trigger_name];

创建一个包括员工及其所在部门信息的视图empdept，然后向视图中插入一条记录（2345,’TOM’,3000,’SALES’）。

CREATE OR REPLACE VIEW empdept 
AS
SELECT empno,ename,sal,dname 
FROM emp,dept WHERE emp.deptno=dept.deptno  
WITH CHECK OPTION;

INSERT INTO empdept
VALUES(2345, 'TOM',3000, 'SALES');
*
ERROR 位于第 1 行:
ORA-01733: 此处不允许虚拟列 

CREATE OR REPLACE TRIGGER trig_view
INSTEAD OF INSERT ON empdept
FOR EACH ROW
DECLARE
    v_deptno dept.deptno%type;
BEGIN
    SELECT deptno INTO v_deptno 
    FROM dept WHERE dname=:new.dname;
    INSERT INTO emp(empno,ename,sal,deptno)   
    VALUES(:new.empno,:new.ename,v_deptno,:new.sal);
END trig_view; 

系统触发器

• 触发器事件

• 创建系统触发器

（1）触发事件

DDL事件

  CREATE ， ALTER ， DROP ， RENAME ，

   GRANT，REVOKE，AUDIT，NOAUDIT，

   COMMENT，TRUNCATE，ANALYZE，

   ASSOCIATE STATISTICS，DISASSOCIATE

   STATISTICS等。

  触发时间可以是 BEFORE ，也可以是 AFTER 。

数据库事件

  STARTUP ， SHUTDOWN ， SERVERERROR ，

   LOGON，LOGOFF等。

  触发时间由具体事件决定，

数据库事件的触发时间

事件	允许计时	描述
STARTUP	AFTER	当实例开始时激发
SHUTDOWN	BEFORE	当实例关闭时激发
SERVERERROR	AFTER	只要错误发生就激发
LOGON	AFTER	在一个用户成功连接数据库时触发
LOGOFF	BEFORE	在用户注销时开始激发

（2）创建系统触发器

语法

  CREATE [OR REPLACE] TRIGGER trigger_name

  BEFORE|AFTER ddl_event_list|database_event_list

  ON DATABASE|SCHEMA

  [WHEN trigger_condition]

  DECLARE

       /*Declarative section is here */

  BEGIN

      /*Executable section is here*/

  EXCEPTION

      /*Exception section is here*/

  END [trigger_name];

• 说明

  基于数据库（ DATABASE ）的触发器，只要系统中该触发事件发生，且满足触发条件，则触发器执行；

  对于基于模式（ SCHEMAN ）的触发器，只有当特定模式中的触发事件发生时，触发器才执行。

• 注意

  STARTUP 和 SHUTDOWN 事件只能激发基于数据库的触发器。

•将每个用户的登录信息写入temp_table表中。

CREATE OR REPLACE TRIGGER log_user_connection
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO scott.temp_table 
  VALUES (user,sysdate);
END log_user_connection; 

• 在系统触发器内部可以使用一些事件属性函数以获得触发事件的信息。

• 由于系统没有为这些事件属性函数指定同义词，因此在调用时必须在其前加上“ SYS.” 前缀。

•

示例

  当数据库中执行 CREATE 操作时，将创建的对象信息记录到 ddl_creations 表中。

CREATE TABLE ddl_creations (
     user_id       VARCHAR2(30),
     object_type    VARCHAR2(20),
     object_name   VARCHAR2(30),
     object_owner  VARCHAR2(30),
     creation_date  DATE);

CREATE OR REPLACE TRIGGER log_creations
AFTER CREATE ON DATABASE
BEGIN
    INSERT INTO ddl_creations
    VALUES(ora_login_user, ora_dict_obj_type, 
         ora_dict_obj_name, ora_dict_obj_owner, sysdate);
END log_creations; 

变异表触发器

概念

  变异表是指激发触发器的 DML 语句所操作的表，即触发器为之定义的表，或者由于 DELETE CASCADE 操作而需要修改的表，即当前表的子表。

  约束表是指由于引用完整性约束而需要从中读取或修改数据的表，即当前表的父表。

• 当对一个表创建行级触发器，或创建由 DELETE CASCADE 操作而激发的语句级触发器时，有下列两条限制：

  不能读取或修改任何触发语句的变异表；

  不能读取或修改触发表的一个约束表的 PRIMARY

   KEY，UNIQUE 或FOREIGN KEY关键字的列, 但

  可以修改其他列。

注意

  如果 INSERT … VALUES 语句只影响一行，那么该语句的行级前触发器不会把触发表当做变异表对待。

  INSERT INTO table SELECT … 等语句总是把触发表当做变异表，即使子查询仅仅返回一条记录。

• 如果既想更新变异表，同时又需要查询变异表，那么如何处理呢 ?

  将行级触发器与语句级触发器结合起来，在行级触发器中获取要修改的记录的信息，存放到一个软件包的全局变量中，然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询，并根据查询的结果进行业务处理。

为了实现在更新员工所在部门或向部门插入新员工时，部门中员工人数不超过 8 人，可以在 emp 表上创建两个触发器，同时创建一个共享信息的包。

CREATE OR REPLACE PACKAGE share_pkg
AS
 v_deptno NUMBER(2); 
END;

CREATE OR REPLACE TRIGGER  rmutate_trigger
BEFORE INSERT OR UPDATE OF deptno ON EMP 
FOR EACH ROW
BEGIN
  share_pkg.v_deptno:=:new.deptno;  
END; 

CREATE OR REPLACE TRIGGER smutate_trigger
AFTER INSERT OR UPDATE OF deptno ON EMP
DECLARE
  v_num number(3);
BEGIN
  SELECT count(*) INTO v_num FROM emp 
  WHERE deptno=share_pkg.v_deptno;
  IF v_num>8 THEN
       RAISE_APPLICATION_ERROR(-20003,
        'TOO MANY EMPLOYEES IN DEPARTMENT '||
        share_pkg.v_deptno);
  END IF;
END; 

测试

INSERT INTO emp(empno,ename,sal,deptno) 
    VALUES(2,'WANG',2000,10);
UPDATE emp SET deptno=10 WHERE empno=7844;
UPDATE emp SET deptno=10 WHERE empno=7369;
UPDATE emp SET deptno=10 WHERE empno=7369
        *
第 1 行出现错误:
ORA-20003: TOO MANY EMPLOYEES IN DEPARTMENT 10
ORA-06512: 在 "SCOTT.SMUTATE_TRIGGER", line 6
ORA-04088: 触发器 'SCOTT.SMUTATE_TRIGGER' 执行过程中出错 

触发器的管理

• 触发器名称

   触发器存在于单独的名字空间中，在一个模式中可以与其他对象同名

• 触发器的限制

  不能出现任何事务控制语句。因为触发器作为触发语句执行的一部分，处于同一个事务中。

  触发器体所调用的过程或函数都不能发出任何事务控制语句（自治事务子程序除外）。

  触发器体中不能声明 LONG 或 LONG RAW 变量，而且 :new 和 :old 不能引用 LONG 或 LONG RAW 类型的列。

  触发器体中可以引用 LOB 和 OBJECT 列，但不能修改该列的值。

  触发器的大小不能超过 32K 。

• 激活或禁用触发器

  可以激活或禁用某个触发器。语法为 :

ALTER TRIGGER triggername ENABLE|DISABLE;

  激活或禁用某个表对象上的所有触发器。语法为 :

ALTER TABLE table_name ENABLE|DISABLE ALL

  TRIGGERS;

• 修改触发器

  CREATE OR REPLACE TRIGGER trigger_name

• 重新编译触发器

  ALTER TRIGGER trigger_name COMPILE;

• 查看触发器及其源代码

  查询数据字典视图 USER_TRIGGERS

  SELECT trigger_name,trigger_type,

  table_name,trigger_body FROM user_triggers;

• 删除触发器

  当触发器不再需要时，可以使用 DROP TRIGGER 语句删除触发器。

  DROP TRIGGER trigger_name;

本人从事软件项目开发20多年，2005年开始从事Java工程师系列课程的教学工作，录制50多门精品视频课程，包含java基础，jspweb开发，SSH,SSM，SpringBoot，SpringCloud,人工智能，在线支付等众多商业项目，每门课程都包含有项目实战，上课PPT，及完整的源代码下载，有兴趣的朋友可以看看我的在线课堂

讲师课堂链接：https://edu.csdn.net/lecturer/893