Oracle 学习之触发器

时间：2023-03-10 01:22:19
Oracle 学习之触发器

1. 触发器简介

　　触发器是存储在数据库服务器中的程序单元，当一个表或一个视图被改变，或者数据库发生某些事件时，Oracle会自动触发触发器，并执行触发器中的代码。只有在触发器中定义的事件发生时，触发器才被触发。触发器是自动执行的代码块，和存储过程的区别在于，用户可以直接调用存储过程，而不能直接调用触发器。

　　触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

2. 触发器种类

　　能够触发触发器的事件有以下几种：

 DML操作（INSERT、UPDATE、DELETE）

 DDL操作（CREATE、ALTER、DROP）

 系统事件（数据库的关闭与启动等）

 用户事件（用户的登陆等）

　　上述的这些语句都可以触发触发器。如果你想在这些事件发生时干些别的事情，这个时候只需要定义对应的触发器即可，在触发器中完成你的工作。

触发器是基于表、视图、模式、数据库的，于此，我们可以把触发器分为下面的几类：

2.1 行级触发器和语句级触发器

　　行级触发器，即触发机制是基于行的，当表中数据改变时，将触发行级触发器，改变一行数据，触发一次；改变N行数据，就会触发N次；
    语句级触发器是基于语句级的，当一条SQL语句改变数据时，无论这条SQL语句影响多少条记录，语句级触发器都只触发一次。SQL语句每执行一次，语句级触发器就被触发一次。

2.2 BEFORE和AFTER触发器

　　BEFORE：表示在触发语句运行前先运行“触发动作”。

    AFTER表示触发语句运行之后才运行“触发动作”。

    BEFORE和AFTER适用于行级触发器和语句级触发器。

2.3 复合触发器

　　复合触发器是表上的触发器，它有4个时间点，可以让我们针对不同的时间点指定不同的处理动作。这四个时间点分别如下：

    • 在触发语句执行前（BEFORE STATEMENT）
    • 在触发语句执行后（AFTER STATEMENT）
    • 在每行记录被修改之前（BEFORE EACH ROW）
    • 在每行记录被修改之后（AFTER EACH ROW）

　　触发语句必须是DML，如果触发语句没有影响任何一行数据，并且也没有指定BEFORE STATEMENT和AFTER STATEMENT两个时间点，则触发器也不会被触发。

2.4 INSTEAD OF触发器

　　有的视图，我们不能直接对其进行更新操作，但是我们可以在这种视图上建立触发器，利用触发器对视图的基表进行更新操作，这种类型的触发器就叫做“INSTEAD OF触发器”。

2.5 系统级触发器

　　系统事件触发器是基于数据库系统的触发器，系统事件触发器与表、视图没有关系，系统事件包括数据库启动、关闭、服务器错误、数据库角色改变等。当这些事件发生时，就会触发系统事件触发器。可以通过系统事件触发器实现对数据库的审计。

3. 触发器的组成

　　触发器一般由以下几部分组成:

. 触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

. 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

. 触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

. 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

. 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

. 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；

行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

4. 创建触发器

　　创建触发器的一般语法是:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

其中：

　　BEFORE 和 AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

FOR EACH ROW选项说明触发器为行触发器，行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略 FOR EACH ROW 选项时，BEFORE 和 AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。

REFERENCING 子句说明相关名称，在行触发器的 PL/SQL 块和 WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

　　WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

4.1 创建DML触发器

DML触发器基本要点

（1）触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指　　 定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。

（2）触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用O　　 R逻辑组合，不能使用AND逻辑组合）。

（3）条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供　　的如下条件谓词。

（4）INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。

　　UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE  时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x　　是可选的。

（5）DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。

（6）触发对象：指定触发器是创建在哪个表、视图上。

（7）触发类型：是语句级还是行级触发器。

（8）触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。

　　:NEW 修饰符访问操作完成后列的值， :OLD 修饰符访问操作完成前列的值

示例1：建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE =;

CREATE OR REPLACE TRIGGER tr_del_emp

   BEFORE DELETE --指定触发时机为删除操作前触发

   ON scott.emp

   FOR EACH ROW   --说明创建的是行级触发器

BEGIN

   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。

   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

示例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表

CREATE OR REPLACE TRIGGER tr_dept_time

BEFORE INSERT OR DELETE OR UPDATE

ON departments

BEGIN

 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN

     RAISE_APPLICATION_ERROR(-, '不是上班时间，不能修改departments表');

 END IF;

END;

示例3：限定只对部门号为80的记录进行行触发器操作

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

BEFORE UPDATE OF salary, commission_pct

       OR DELETE

ON HR.employees

FOR EACH ROW

WHEN (old.department_id = )

BEGIN

 CASE

     WHEN UPDATING ('salary') THEN

        IF :NEW.salary < :old.salary THEN

           RAISE_APPLICATION_ERROR(-, '部门80的人员的工资不能降');

        END IF;

     WHEN UPDATING ('commission_pct') THEN

        IF :NEW.commission_pct < :old.commission_pct THEN

           RAISE_APPLICATION_ERROR(-, '部门80的人员的奖金不能降');

        END IF;

     WHEN DELETING THEN

          RAISE_APPLICATION_ERROR(-, '不能删除部门80的人员记录');

     END CASE;

END;

示例4 利用行触发器实现级联更新，在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id

CREATE OR REPLACE TRIGGER tr_reg_cou

AFTER update OF region_id

ON regions

FOR EACH ROW

BEGIN

 DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id

                  ||'、新的region_id值是'||:new.region_id);

 UPDATE countries SET region_id = :new.region_id

 WHERE region_id = :old.region_id;

END;

示例5 在触发器中调用过程

CREATE OR REPLACE PROCEDURE add_job_history

 ( p_emp_id          job_history.employee_id%type

   , p_start_date      job_history.start_date%type

  , p_end_date        job_history.end_date%type

   , p_job_id          job_history.job_id%type

   , p_department_id   job_history.department_id%type

   )

IS

BEGIN

 INSERT INTO job_history (employee_id, start_date, end_date,

                           job_id, department_id)

  VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);

END add_job_history;

--创建触发器调用存储过程...

CREATE OR REPLACE TRIGGER update_job_history

 AFTER UPDATE OF job_id, department_id ON employees

 FOR EACH ROW

BEGIN

 add_job_history(:old.employee_id, :old.hire_date, sysdate,

                  :old.job_id, :old.department_id);

END

4.2 创建替代(INSTEAD OF)触发器

创建触发器的一般语法是:

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.] view_name --只能定义在视图上

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定

[WHEN condition]

PL/SQL_block | CALL procedure_name;

其中：

INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF触发器。

FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器

REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

　　WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

　　创建INSTEAD OF触发器需要注意以下几点：

  1. 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
  2. 不能指定BEFORE 或 AFTER选项。
  3. FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
  4. 没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

4.3 创建系统事件触发器

　　ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：

CREATE OR REPLACE TRIGGER [sachema.]trigger_name

{BEFORE|AFTER}

{ddl_event_list | database_event_list}

ON { DATABASE | [schema.]SCHEMA }

[WHEN condition]

PL/SQL_block | CALL procedure_name;

其中: ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；

database_event_list：一个或多个数据库事件，事件间用 OR 分开；

系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

示例：创建登录、退出触发器

CREATE TABLE log_event

(user_name VARCHAR2(),

 address VARCHAR2(),

 logon_date timestamp,

 logoff_date timestamp);

--创建登录触发器

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

BEGIN

   INSERT INTO log_event (user_name, address, logon_date)

   VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

--创建退出触发器

CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DATABASE

BEGIN

   INSERT INTO log_event (user_name, address, logoff_date)

   VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logoff;

4.4 使用触发器谓词

INSERTING

如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE

UPDATING

如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE

DELETING

如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

4.5 数据库触发器的应用举例

　　1. 创建一个DML语句级触发器，当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新 dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。

CREATE TABLE dept_summary(

 Deptno NUMBER(),

 Sal_sum NUMBER(, ),

 Emp_count NUMBER);

INSERT INTO dept_summary(deptno, sal_sum, emp_count)

 SELECT deptno, SUM(sal), COUNT(*)

FROM emp

GROUP BY deptno;

--创建一个PL/SQL过程disp_dept_summary

--在触发器中调用该过程显示dept_summary标中的数据。

CREATE OR REPLACE PROCEDURE disp_dept_summary

IS

 Rec dept_summary%ROWTYPE;

 CURSOR c1 IS SELECT * FROM dept_summary;

BEGIN

 OPEN c1;

 FETCH c1 INTO REC;

 DBMS_OUTPUT.PUT_LINE('deptno    sal_sum    emp_count');

 DBMS_OUTPUT.PUT_LINE('-------------------------------------');

 WHILE c1%FOUND LOOP

    DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, )||

      To_char(rec.sal_sum, '$999,999.99')||

      LPAD(rec.emp_count, ));

    FETCH c1 INTO rec;

 END LOOP;

 CLOSE c1;

END;

BEGIN

 DBMS_OUTPUT.PUT_LINE('插入前');

 Disp_dept_summary();

 DBMS_UTILITY.EXEC_DDL_STATEMENT('

    CREATE OR REPLACE TRIGGER trig1

      AFTER INSERT OR DELETE OR UPDATE OF sal ON emp

    BEGIN

      DBMS_OUTPUT.PUT_LINE(''正在执行trig1 触发器…'');

      DELETE FROM dept_summary;

      INSERT INTO dept_summary(deptno, sal_sum, emp_count)

      SELECT deptno, SUM(sal), COUNT(*)

      FROM emp GROUP BY deptno;

    END;

 ');

 INSERT INTO dept(deptno, dname, loc)

 VALUES(, ‘demo_dept’, ‘none_loc’);

 INSERT INTO emp(ename, deptno, empno, sal)

 VALUES(USER, , , );

 DBMS_OUTPUT.PUT_LINE('插入后');

 Disp_dept_summary();

 UPDATE emp SET sal= WHERE empno=;

 DBMS_OUTPUT.PUT_LINE('修改后');

 Disp_dept_summary();

 DELETE FROM emp WHERE empno=;

 DELETE FROM dept WHERE deptno=;

 DBMS_OUTPUT.PUT_LINE('删除后');

 Disp_dept_summary();

 DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’);

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END;

例2：创建DML语句行级触发器。当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。

BEGIN

  DBMS_OUTPUT.PUT_LINE('插入前');

  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT(

    'CREATE OR REPLACE TRIGGER trig2_update

      AFTER UPDATE OF sal ON emp

      REFERENCING OLD AS old_emp NEW AS new_emp

      FOR EACH ROW

      WHEN (old_emp.sal != new_emp.sal)

    BEGIN

      DBMS_OUTPUT.PUT_LINE(''正在执行trig2_update 触发器…'');

      DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal);

      DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);

      UPDATE dept_summary

        SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal

        WHERE deptno = :new_emp.deptno;

    END;'

  );

  DBMS_UTILITY.EXEC_DDL_STATEMENT(

    'CREATE OR REPLACE TRIGGER trig2_insert

      AFTER INSERT ON emp

      REFERENCING NEW AS new_emp

      FOR EACH ROW

    DECLARE

      I NUMBER;

    BEGIN

      DBMS_OUTPUT.PUT_LINE(''正在执行trig2_insert 触发器…'');

      SELECT COUNT(*) INTO I

      FROM dept_summary WHERE deptno = :new_emp.deptno;

      IF I >  THEN

        UPDATE dept_summary

        SET sal_sum=sal_sum+:new_emp.sal,

        Emp_count=emp_count+

        WHERE deptno = :new_emp.deptno;

      ELSE

        INSERT INTO dept_summary

        VALUES (:new_emp.deptno, :new_emp.sal, );

      END IF;

    END;'

  );

  DBMS_UTILITY.EXEC_DDL_STATEMENT(

    'CREATE OR REPLACE TRIGGER trig2_delete

      AFTER DELETE ON emp

      REFERENCING OLD AS old_emp

      FOR EACH ROW

    DECLARE

      I NUMBER;

    BEGIN

      DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…'');

      SELECT emp_count INTO I

      FROM dept_summary WHERE deptno = :old_emp.deptno;

      IF I > THEN

        UPDATE dept_summary

        SET sal_sum=sal_sum - :old_emp.sal,

        Emp_count=emp_count -

        WHERE deptno = :old_emp.deptno;

      ELSE

        DELETE FROM dept_summary WHERE deptno = :old_emp.deptno;

      END IF;

    END;'

  );

  INSERT INTO dept(deptno, dname, loc)

    VALUES(, 'demo_dept', 'none_loc');

  INSERT INTO emp(ename, deptno, empno, sal)

    VALUES(USER, , , );

  INSERT INTO emp(ename, deptno, empno, sal)

    VALUES(USER, , , );

  DBMS_OUTPUT.PUT_LINE('插入后');

  Disp_dept_summary();

  UPDATE emp SET sal = sal*1.1 WHERE deptno=;

  DBMS_OUTPUT.PUT_LINE('修改后');

  Disp_dept_summary();

  DELETE FROM emp WHERE deptno=;

  DELETE FROM dept WHERE deptno=;

  DBMS_OUTPUT.PUT_LINE('删除后');

  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update');

  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert');

  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete');

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END;

例3：利用ORACLE提供的条件谓词INSERTING、UPDATING和DELETING创建与例2具有相同功能的触发器。

BEGIN

    DBMS_OUTPUT.PUT_LINE('插入前');

    Disp_dept_summary();

    DBMS_UTILITY.EXEC_DDL_STATEMENT(

        'CREATE OR REPLACE TRIGGER trig2

            AFTER INSERT OR DELETE OR UPDATE OF sal

ON emp

            REFERENCING OLD AS old_emp NEW AS new_emp

            FOR EACH ROW

        DECLARE

            I NUMBER;

        BEGIN

            IF UPDATING AND :old_emp.sal != :new_emp.sal THEN

            DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…'');

                DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal);

                DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);

                UPDATE dept_summary

                    SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal

                WHERE deptno = :new_emp.deptno;

            ELSIF INSERTING THEN

                DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…'');

                SELECT COUNT(*) INTO I

        FROM dept_summary

        WHERE deptno = :new_emp.deptno;

                IF I >  THEN

                    UPDATE dept_summary

          SET sal_sum=sal_sum+:new_emp.sal,

              Emp_count=emp_count+

          WHERE deptno = :new_emp.deptno;

            ELSE

          INSERT INTO dept_summary

            VALUES (:new_emp.deptno, :new_emp.sal, );

        END IF;

      ELSE

        DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…'');

        SELECT emp_count INTO I

        FROM dept_summary WHERE deptno = :old_emp.deptno;

      IF I >  THEN

        UPDATE dept_summary

        SET sal_sum=sal_sum - :old_emp.sal,

        Emp_count=emp_count -

        WHERE deptno = :old_emp.deptno;

      ELSE

          DELETE FROM dept_summary

          WHERE deptno = :old_emp.deptno;

      END IF;

    END IF;

    END;'

  );

  INSERT INTO dept(deptno, dname, loc)

    VALUES(, 'demo_dept', 'none_loc');

  INSERT INTO emp(ename, deptno, empno, sal)

    VALUES(USER, , , );

  INSERT INTO emp(ename, deptno, empno, sal)

    VALUES(USER, , , );

  DBMS_OUTPUT.PUT_LINE('插入后');

  Disp_dept_summary();

  UPDATE emp SET sal = sal*1.1 WHERE deptno=;

  DBMS_OUTPUT.PUT_LINE('修改后');

  Disp_dept_summary();

  DELETE FROM emp WHERE deptno=;

  DELETE FROM dept WHERE deptno=;

  DBMS_OUTPUT.PUT_LINE('删除后');

  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2');

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END;

例4：创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。

DECLARE

    No NUMBER;

    Name VARCHAR2();

BEGIN

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE OR REPLACE VIEW myview AS

            SELECT empno, ename, ''E'' type FROM emp

            UNION

            SELECT dept.deptno, dname, ''D'' FROM dept

    ');

    -- 创建INSTEAD OF 触发器trigger3;

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE OR REPLACE TRIGGER trig3

            INSTEAD OF INSERT ON myview

            REFERENCING NEW n

            FOR EACH ROW

        DECLARE

            Rows INTEGER;

        BEGIN

            DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…'');

            IF :n.type = ''D'' THEN

                SELECT COUNT(*) INTO rows

                    FROM dept WHERE deptno = :n.empno;

                IF rows =  THEN

                    DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…'');

                    INSERT INTO dept(deptno, dname, loc)

                        VALUES (:n.empno, :n.ename, ''none’’);

                ELSE

                    DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||

                     ''的部门已存在，插入操作失败！'');

                 END IF;

            ELSE

                SELECT COUNT(*) INTO rows

                    FROM emp WHERE empno = :n.empno;

                IF rows =  THEN

                    DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’);

                    INSERT INTO emp(empno, ename)

                        VALUES(:n.empno, :n.ename);

                ELSE

                    DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||

                      ''的人员已存在，插入操作失败!'');

                END IF;

            END IF;

        END;

    ');

    INSERT INTO myview VALUES (, 'demo', 'D');

    INSERT INTO myview VALUES (, USER, 'E');

    SELECT deptno, dname INTO no, name FROM dept WHERE deptno=;

    DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name);

    SELECT empno, ename INTO no, name FROM emp WHERE empno=;

    DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name);

  DELETE FROM emp WHERE empno=;

  DELETE FROM dept WHERE deptno=;

    DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3');

END;

例5：利用ORACLE事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig4_ddl、trig4_before和trig4_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。

BEGIN

    -- 创建用于记录事件日志的数据表

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE TABLE eventlog(

            Eventname VARCHAR2() NOT NULL,

            Eventdate date default sysdate,

            Inst_num NUMBER NULL,

            Db_name VARCHAR2() NULL,

            Srv_error NUMBER NULL,

            Username VARCHAR2() NULL,

            Obj_type VARCHAR2() NULL,

            Obj_name VARCHAR2() NULL,

            Obj_owner VARCHAR2() NULL

        )

    ');

    -- 创建DDL触发器trig4_ddl

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE OR REPLACE TRIGGER trig4_ddl

            AFTER CREATE OR ALTER OR DROP

ON DATABASE

        DECLARE

            Event VARCHAR2();

            Typ VARCHAR2();

            Name VARCHAR2();

            Owner VARCHAR2();

        BEGIN

            -- 读取DDL事件属性

            Event := SYSEVENT;

            Typ := DICTIONARY_OBJ_TYPE;

            Name := DICTIONARY_OBJ_NAME;

            Owner := DICTIONARY_OBJ_OWNER;

            --将事件属性插入到事件日志表中

            INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner)

                VALUES(event, typ, name, owner);

        END;

    ');

    -- 创建LOGON、STARTUP和SERVERERROR 事件触发器

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE OR REPLACE TRIGGER trig4_after

            AFTER LOGON OR STARTUP OR SERVERERROR

      ON DATABASE

        DECLARE

            Event VARCHAR2();

            Instance NUMBER;

            Err_num NUMBER;

            Dbname VARCHAR2();

            User VARCHAR2();

        BEGIN

            Event := SYSEVENT;

            IF event = ''LOGON'' THEN

                User := LOGIN_USER;

                INSERT INTO eventlog(eventname, username)

                    VALUES(event, user);

            ELSIF event = ''SERVERERROR'' THEN

                Err_num := SERVER_ERROR();

                INSERT INTO eventlog(eventname, srv_error)

                    VALUES(event, err_num);

            ELSE

                Instance := INSTANCE_NUM;

                Dbname := DATABASE_NAME;

                INSERT INTO eventlog(eventname, inst_num, db_name)

                    VALUES(event, instance, dbname);

      END IF;

    END;

  ');

  -- 创建LOGOFF和SHUTDOWN 事件触发器

  DBMS_UTILITY.EXEC_DDL_STATEMENT('

    CREATE OR REPLACE TRIGGER trig4_before

      BEFORE LOGOFF OR SHUTDOWN

      ON DATABASE

    DECLARE

      Event VARCHAR2();

      Instance NUMBER;

      Dbname VARCHAR2();

      User VARCHAR2();

    BEGIN

      Event := SYSEVENT;

      IF event = ''LOGOFF'' THEN

        User := LOGIN_USER;

        INSERT INTO eventlog(eventname, username)

          VALUES(event, user);

      ELSE

        Instance := INSTANCE_NUM;

        Dbname := DATABASE_NAME;

        INSERT INTO eventlog(eventname, inst_num, db_name)

          VALUES(event, instance, dbname);

      END IF;

    END;

  ');

END;

CREATE TABLE mydata(mydate NUMBER);

CONNECT SCOTT/TIGER

COL eventname FORMAT A10

COL eventdate FORMAT A12

COL username FORMAT A10

COL obj_type FORMAT A15

COL obj_name FORMAT A15

COL obj_owner FORMAT A10

SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error

  FROM eventlog;

DROP TRIGGER trig4_ddl;

DROP TRIGGER trig4_before;

DROP TRIGGER trig4_after;

DROP TABLE eventlog;

DROP TABLE mydata;

4.6 数据库触发器的应用实例

用户可以使用数据库触发器实现各种功能：复杂的审计功能；例：将EMP 表的变化情况记录到AUDIT_TABLE和AUDIT_TABLE_VALUES中。

CREATE TABLE audit_table(

    Audit_id     NUMBER,

    User_name VARCHAR2(),

    Now_time DATE,

    Terminal_name VARCHAR2(),

    Table_name VARCHAR2(),

    Action_name VARCHAR2(),

    Emp_id NUMBER());

CREATE TABLE audit_table_val(

    Audit_id NUMBER,

    Column_name VARCHAR2(),

    Old_val NUMBER(,),

    New_val NUMBER(,));

CREATE SEQUENCE audit_seq

    START WITH

    INCREMENT BY

    NOMAXVALUE

    NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER audit_emp

    AFTER INSERT OR UPDATE OR DELETE ON emp

    FOR EACH ROW

DECLARE

    Time_now DATE;

    Terminal CHAR();

BEGIN

    Time_now:=sysdate;

    Terminal:=USERENV('TERMINAL');

    IF INSERTING THEN

        INSERT INTO audit_table

    VALUES(audit_seq.NEXTVAL, user, time_now,

           terminal, 'EMP', 'INSERT', :new.empno);

    ELSIF DELETING THEN

        INSERT INTO audit_table

    VALUES(audit_seq.NEXTVAL, user, time_now,

           terminal, 'EMP', 'DELETE', :old.empno);

    ELSE

        INSERT INTO audit_table

    VALUES(audit_seq.NEXTVAL, user, time_now,

           terminal, 'EMP', 'UPDATE', :old.empno);

        IF UPDATING('SAL') THEN

            INSERT INTO audit_table_val

                VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal);

        ELSE UPDATING('DEPTNO')

            INSERT INTO audit_table_val

                VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno);

        END IF;

    END IF;

END;

示例2 增强数据的完整性管理

例：修改DEPT表的DEPTNO列时，同时把EMP表中相应的DEPTNO也作相应的修改；

CREATE SEQUENCE update_sequence

    INCREMENT BY

    START WITH

    MAXVALUE  CYCLE;

ALTER TABLE emp

    ADD update_id NUMBER;

CREATE OR REPLACE PACKAGE integritypackage AS

    Updateseq NUMBER;

END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS

END integritypackage;

CREATE OR REPLACE TRIGGER dept_cascade1

    BEFORE UPDATE OF deptno ON dept

DECLARE

    Dummy NUMBER;

BEGIN

    SELECT update_sequence.NEXTVAL INTO dummy FROM dual;

    Integritypackage.updateseq:=dummy;

END;

CREATE OR REPLACE TRIGGER dept_cascade2

    AFTER DELETE OR UPDATE OF deptno ON dept

    FOR EACH ROW

BEGIN

    IF UPDATING THEN

        UPDATE emp SET deptno=:new.deptno,

     update_id=integritypackage.updateseq

        WHERE emp.deptno=:old.deptno AND update_id IS NULL;

    END IF;

    IF DELETING THEN

        DELETE FROM emp

            WHERE emp.deptno=:old.deptno;

    END IF;

END;

CREATE OR REPLACE TRIGGER dept_cascade3

    AFTER UPDATE OF deptno ON dept

BEGIN

    UPDATE emp SET update_id=NULL

        WHERE update_id=integritypackage.updateseq;

END;

SELECT * FROM EMP ORDER BY DEPTNO;

UPDATE dept SET deptno= WHERE deptno=;

转自：http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html#undefined 该博主特别无私，分享了许多有用的东西，希望大家可以多去看看

相关文章