PL/SQL-->DML 触发器

    何谓触发器？简言之，是一段命名的PL/SQL代码块，只不过该代码块在特定的条件下被触发并且执行。对于这样的代码我们称之为触发器
。触发器根据触发类型的不同又分为不同级别的触发器，下面将给出触发器的分类，定义，以及使用的示例。
 
一、触发器的相关概念

    1.触发器的分类

        通常根据触发条件以及触发级别的不同分为DML触发器，INSTEAD OF 触发器，系统事件触发器。

        DML触发器
            通过ML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

        INSTEAD OF 触发器
            在ORACLE里，对于简单视图，可以直接使用DML进行操作，而复杂视图则不能直接使用DML，因此INSTEAD OF 触发器应运而生。
            INSTEAD OF 触发器主要是为解决复杂视图不能执行DML而创建。

        系统事件触发器  
            通过ORACLE数据库系统事件进行触发,如ORACLE系统的启动与关闭等.使用系统触发器,便于系统跟踪,监测数据库变化情况等。


    2.触发器的组成(一段PL/SQL代码块，可以由PL/SQL,Java,C进行开发,特定事件发生将被触发)

        a.触发事件
            Oracle 启动、关闭
            Oracle 错误消息
            用户登录与断开会话
            特定的表、视图上的DML操作
            基于schema的DDL操作

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

        c.触发器本身
            指实际的触发代码，当触发事件发生后，触发器代码决定将做何种操作。
            触发器代码大小不能超过32k，对于超长的代码可以将其置于单独的存储过程，然后在触发器中使用call 调用该过程。
            触发器代码只能包含DQL，DML，而不能包含DDL以及事务控制语言(COMMIT,ROLLBACK,SAVEPOINT) 。

        d.触发频率
            说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
            语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次。
            行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

    3.触发器的用途

        控制DDL语句的行为，如更改、创建或重命名对象
        控制DML语句的行为，如插入、更新和删除
        实施参照完整性、复杂业务规则和安全性策略
        在修改视图中的数据时控制和重定向DML语句
        通过创建透明日志来审核系统访问和行为的信息


二、创建DML触发器语法描述

    1.创建触发器的语法

        CREATE [OR REPLACE] TRIGGER trigger_name
        {BEFORE | AFTER | INSTEAD OF}     
        {INSERT | DELETE | UPDATE [OF column [, column …]]}                  --定义触发类型，即哪一种或多种DML以及特定的列
        ON {[schema.] table_name | [schema.] view_name}                       --特定的触发对象，表或视图
        [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
        [FOR EACH ROW ]                                                       --定义触发器为行级触发器
        [WHEN condition]
        BEGIN
            trigger_body;
        END;

        BEFORE | AFTER | INSTEAD OF
            BEFORE指在执行DML之前触发触发器，AFTER则是指在DML执行之后触发触发器
            INSTEAD OF触发器只针对视图和对象视图建立，而不能对表、模式和数据库建立INSTEAD OF 触发器

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

        [FOR EACH ROW ]
            定义触发器为行级触发器。
            行级触发器和语句级触发器的区别表现在：一个DML语句可能操纵多行，也可能操纵一行，使用行级触发器，不论是一行还是多
            行数据被操纵，行触发器为该DML的每一行触发一次触发器操作。语句级触发器将整个语句操作作为触发事件，不论该语句影响
            了多少行，仅仅触发一次触发器。
            当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为默认语句触发器，而INSTEAD OF 触发器则默认为行触发器。    

        [WHEN condition]
            触发条件，当条件为TRUE时，触发器代码才会被执行，对于DML触发器，仅仅允许在行级触发器上指定触发条件。
            condition 为一个逻辑表达式，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。
            WHEN 子句可通过引用new或old伪记录、一个组件选择符和一个列名来访问伪字段。
            WHEN 子句不能用在INSTEAD OF 行触发器和其它类型的触发器中。

            
    2.DML触发器的触发顺序

        a.在单行数据上的触发顺序(触发代码仅被执行一次)
            BEFORE 语句级触发器
                BEFORE 行级触发器
                AFTER 行级触发器
            AFTER 语句级触发器

        b.在多行数据上的触发顺序(语句级触发器仅被执行一次，行级触发器在每个作业行上被执行一次)
            BEFORE 语句级触发器
                BEFORE 行级触发器
                AFTER 行级触发器
                BEFORE 行级触发器
                AFTER 行级触发器
            AFTER 语句级触发器

    3.触发器中的条件谓词
        ORACLE 提供三个参数INSERTING, UPDATING, DELETING 用于判断触发了哪些操作。
        INSERTING：如果触发语句是INSERT 语句，则为TRUE,否则为FALSE 。
        UPDATING：如果触发语句是UPDATE语句，则为TRUE,否则为FALSE 。
        DELETING：如果触发语句是DELETE 语句，则为TRUE,否则为FALSE 。

    4.NEW、OLD 限定符的使用
        使用被插入、更新或删除的记录中的列值，可以使用NEW和OLD限定符来表示
        :old 修饰符访问操作完成前列的值
        :new 修饰符访问操作完成后列的值

        限定符    INSERT操作     UPDATE操作  DELETE操作
        ---------  ---------     -----------  ----------
        OLD        NULL            有效         有效
        NEW        有效           有效         NULL

三、创建DML触发器   

    1.创建BEFORE 语句级触发器

        sys@ORCL> drop user scott cascade;     --删除scott方案
        sys@ORCL> start $ORACLE_HOME/rdbms/admin/utlsampl.sql   --重建scott方案
        sys@ORCL> grant dba to scott;  --授予Scott DBA角色

        scott@ORCL> create table emp_check(oper varchar2(30),upd_date date);    --创建表存放emp表的更新记录操作

        CREATE OR REPLACE TRIGGER tr_before_update_emp    --创建update触发器
          BEFORE UPDATE ON emp   
          -- FOR EACH ROW
        BEGIN
          INSERT INTO emp_check
          Values
            ('Before update, statement level', sysdate);
        END;

        scott@ORCL> select * from emp_check;  --未执行update前，跟踪表记录为空
        no rows selected

        scott@ORCL> update emp set sal = sal 100 where deptno = 20;   --更新了四条记录
        4 rows updated.

        scott@ORCL> select * from emp_check;     --跟踪表插入了一条跟踪记录
        OPER                           UPD_DATE
        ------------------------------ ---------
        Before update, statement level 24-DEC-10

        scott@ORCL> update emp set sal = sal 200 where empno = 7369; --更新了一条记录，跟踪表再次插入一条新记录
        1 row updated.

        scott@ORCL> select * from emp_check;
        OPER                           UPD_DATE
        ------------------------------ ---------
        Before update, statement level 24-DEC-10
        Before update, statement level 24-DEC-10        

        
    2.创建 BEFORE 行级触发器    

        使用上面的代码来创建行级触发器，与之不同的是将上面的代码中"-- FOR EACH ROW" 的"--"删除，则创建的触发器即为行级触发器
        代码省略

            scott@ORCL> update emp set sal = sal 200 where deptno = 20; --再次更新deptno为20的记录，且记录总数为四条
            4 rows updated.                     

            scott@ORCL> select * from emp_check;   --表emp_check中增加四条，即为update的每一行增加一条记录
            OPER                           UPD_DATE
            ------------------------------ ---------
            Before update, statement level 24-DEC-10
            Before update, statement level 24-DEC-10
            Before update, statement level 24-DEC-10
            Before update, statement level 24-DEC-10
            Before update, statement level 24-DEC-10
            Before update, statement level 24-DEC-10    
 

    3.创建 AFTER 语句级触发器       

        CREATE TABLE audit_table_emp   --创建一张表audit_table_emp存放emp表上DML操作的次数
        (
            name    VARCHAR2(20),
            ins     INT,
            upd     INT,
            del     INT,
            starttime   DATE,
            endtime     DATE
        );

        CREATE OR REPLACE TRIGGER tr_audit_emp
        AFTER INSERT OR UPDATE OR DELETE ON emp
        DECLARE
            v_temp INT;
        BEGIN
            SELECT COUNT(*) INTO v_temp FROM audit_table_emp WHERE name = 'EMP';
            IF v_temp = 0 THEN
                INSERT INTO audit_table_emp VALUES('EMP', 0, 0, 0, SYSDATE, NULL);
            END IF;
            CASE
                WHEN INSERTING THEN  --注意此例中条件谓词的使用INSERTING、UPDATING、DELETING
                    UPDATE audit_table_emp SET ins = ins 1, endtime = SYSDATE WHERE name = 'EMP';
                WHEN UPDATING THEN
                    UPDATE audit_table_emp SET upd = upd 1, endtime = SYSDATE WHERE name = 'EMP';
                WHEN DELETING THEN
                    UPDATE audit_table_emp SET del = del 1, endtime = SYSDATE WHERE name = 'EMP';
            END CASE;
        END;    

        scott@ORCL> update emp set sal=sal 200 where empno=7788;  
        scott@ORCL> update emp set sal=sal 200 where ename='SMITH';
        scott@ORCL> delete from emp where empno=7788;  

        scott@ORCL> select * from audit_table_emp;   --两次更新及一次删除被记录到表中

        NAME                        INS        UPD        DEL STARTTIME ENDTIME
        -------------------- ---------- ---------- ---------- --------- ---------
        EMP                           0          2          1 24-DEC-10 24-DEC-10   

        scott@ORCL> update emp set sal=sal 100 where deptno=10;
        3 rows updated.   --更新了行，但audit_table_emp表中仅仅记录一次，UPD的值增加到3

        scott@ORCL> select * from audit_table_emp;
        
        NAME                        INS        UPD        DEL STARTTIME ENDTIME
        -------------------- ---------- ---------- ---------- --------- ---------
        EMP                           0          3          1 24-DEC-10 24-DEC-10       


    4.创建 AFTER 行级触发器

        CREATE TABLE audit_emp_change    --创建audit_emp_change存放emp 表sal列被更新前后的值
        (
            name        VARCHAR2(10),
            oldsal      NUMBER(6, 2),
            newsal      NUMBER(6, 2),
            time        DATE
        );

        CREATE OR REPLACE TRIGGER tr_sal_change  
        AFTER UPDATE OF sal ON emp  --注意update 触发器中使用了OF 关键字，当sal列发生变化时，tr_sal_change被触发
        FOR EACH ROW                --使用行级触发器
        --WHEN （old.job='CLERK')
        DECLARE
            v_temp INT;
        BEGIN
            SELECT COUNT(*) INTO v_temp FROM audit_emp_change WHERE name = :old.ename;  --注意OLD 与NEW 的使用
            IF v_temp = 0 THEN
                INSERT INTO audit_emp_change VALUES(:old.ename, :old.sal, :new.sal, SYSDATE);
            ELSE
                UPDATE audit_emp_change SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE WHERE name = :old.ename;
            END IF;
        END;
        
        scott@ORCL> update emp set sal=sal-100 where empno=7369;  --更新一行
        
        scott@ORCL> select * from audit_emp_change;    --audit_emp_change表中记录了一行

        NAME           OLDSAL     NEWSAL TIME
        ---------- ---------- ---------- ---------
        SMITH            1400       1300 24-DEC-10

        scott@ORCL> delete from audit_emp_change;  --删除之前的记录

        scott@ORCL> update emp set sal=sal 200 where deptno=10;   --更新了三行
        3 rows updated.

        scott@ORCL> select * from audit_emp_change;   --audit_emp_change表中记录了三行

        NAME           OLDSAL     NEWSAL TIME
        ---------- ---------- ---------- ---------
        CLARK            2550       2750 24-DEC-10
        KING             5100       5300 24-DEC-10
        MILLER           1400       1600 24-DEC-10  

        
    5.限制行级触发器
        限制行级触发器是通过添加[WHEN condition]判断条件，来对满足特定condition的记录触发触发器。
        对于DML触发器而言，仅仅允许在行级触发器上指定触发条件。
        在上面第4小点创建AFTER 行级触发器的代码中，这一行"--WHEN （old.job='CLERK')" 去掉"--"，触发器变为具有限定条件的行级触发器，
        则对特定的条件，即job='CLERK'的记录在更新其sal时才会被触发。
        

    6.创建DML触发器的注意事项

        DML触发器中不能包含对基表的DQL查询操作

            CREATE OR REPLACE TRIGGER tr_emp_sal
              BEFORE UPDATE OF sal ON emp --注意update 触发器中使用了OF 关键字，当sal列发生变化时，tr_emp_sal被触发
              FOR EACH ROW --使用行级触发器
            DECLARE
              maxsal NUMBER(6, 2);
            BEGIN
              SELECT MAX(sal) INTO maxsal FROM emp;
              IF :new.sal > maxsal THEN
                RAISE_APPLICATION_ERROR(-20004, 'Beyond the highest salary');
              END IF;
            END;

            scott@ORCL> update emp set sal=5000 where empno=7369;
            update emp set sal=5000 where empno=7369
            *
            ERROR at line 1:
            ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
            ORA-06512: at "SCOTT.TR_EMP_SAL", line 4
            ORA-04088: error during execution of trigger 'SCOTT.TR_EMP_SAL'         
        

    7.使用DML触发器实现数据完整性，参照完整性

        对于复杂的数据完整性，参照完整性，可以通过DML触发器来完成普通约束所不能完成的任务

            CREATE OR REPLACE TRIGGER tr_del_upd_deptno
            AFTER DELETE OR UPDATE OF deptno ON dept
            FOR EACH ROW
            BEGIN
              IF (UPDATING AND :old.deptno<>:new.deptno) THEN
                UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
              END IF;
              IF DELETING THEN
                DELETE FROM emp WHERE deptno=:old.deptno;
              END IF;
            END;

            scott@ORCL> update dept set deptno=50 where deptno=10;
            1 row updated.      

            scott@ORCL> select * from emp where deptno=10;
            no rows selected

            scott@ORCL> select * from emp where deptno=50;

                 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
            ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                  7782 CLARK      MANAGER         7839 09-JUN-81       2750                    50
                  7839 KING       PRESIDENT            17-NOV-81       5300                    50
                  7934 MILLER     CLERK           7782 23-JAN-82       1600                    50
 

四、管理触发器

    1.查看系统中特定对象上的触发器

        scott@ORCL> select trigger_name,status from user_triggers
          2  where table_name='EMP';

        TRIGGER_NAME                   STATUS
        ------------------------------ --------
        TR_BEFORE_UPDATE_EMP           ENABLED
        TR_AUDIT_EMP                   ENABLED
        TR_SEC_EMP                     ENABLED
        TR_SAL_CHANGE                  ENABLED
        TR_EMP_SAL                     ENABLED  


    2.查看触发器的源代码

        scott@ORCL> col text format a65

        scott@ORCL> select line,text from user_source where name='TR_DEL_UPD_DEPTNO';
        
              LINE TEXT
        ---------- -----------------------------------------------------------------
                 1 TRIGGER tr_del_upd_deptno
                 2 AFTER DELETE OR UPDATE OF deptno ON dept
                 3 FOR EACH ROW
                 4 BEGIN
                 5   IF (UPDATING AND :old.deptno<>:new.deptno) THEN
                 6     UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
                 7   END IF;
                 8
                 9   IF DELETING THEN
                10     DELETE FROM emp WHERE deptno=:old.deptno;
                11   END IF;
                12 END;
                

    3.禁用触发器
        当触发器被禁用后，则表上的DML操作将不会触发该触发器，直到该触发器被解除禁用(alter trigger trigger_name disable)

        scott@ORCL> alter trigger tr_emp_sal disable;

       
    4.启用触发器
        被禁用的触发器可以被解除禁用(alter trigger trigger_name enable)

        scott@ORCL> alter trigger tr_emp_sal enable;
    

    5.禁用、启用表上的所有触发器
        scott@ORCL> alter table emp disable all triggers;

        scott@ORCL> alter table emp enable all triggers;


    6.重新编译触发器
        scott@ORCL> alter trigger tr_emp_sal compile;

        
    7.删除触发器
        scott@ORCL> drop trigger tr_emp_sal;

转自：http://blog.csdn.net/robinson_0612/article/details/6098258