PL/SQL --> INSTEAD OF 触发器

--==============================

-- PL/SQL --> INSTEAD OF 触发器

--==============================

 

    INSTEAD OF 触发器常用于管理编写不可更新的视图,INSTEAD-OF触发器必须是行级的。

    可以用INSTEAD OF触发器来解释INSERTUPDATEDELETE语句,并用备用的程序代码替换那些指令。

 

一、不可更新视图

    基于下列情形创建的视图,不可直接对其进行DML操作

        使用了集合操作运算符(UNION,UNION ALL ,INTERSECT,MINUS)

        使用了分组函数(MIN,MAX,SUM,AVG)

        使用了GROUP BY ,CONNECT BY ,START WITH 子句

        使用了DISTINCT 关键字

        使用了连接查询

      对于基于上述情况创建的视图,不能对其直接执行DML,但可以在该视图上创建INSTEAD OF触发器来间接执行DML

   

二、创建INSTEAD OF 触发器的语法

    CREATE [OR REPLACE] TRIGGER trigger_name

    INSTEAD OF {dml_statement }

    ON {object_name | database | schema}

    FOR EACH ROW

    [WHEN (logical_expression)]

    [DECLARE]

        declaration_statements;

    BEGIN

        execution_statements;

    END [trigger_name];

    /

 

三、创建视图

    --在下面创建的视图中,由于使用了连接查询,因此视图将不可更新

        CREATE OR REPLACE VIEW vw_dept_emp

        AS

          SELECT deptno,d.dname,e.empno,e.ename

          FROM dept d

          JOIN emp e

          USING (deptno);

   

    --从数据字典(user_updatable_columns)中查询某一视图哪些列是可更新或不可更新的

        scott@ORCL> col owner format a15

        scott@ORCL> select * from user_updatable_columns where table_name='VW_DEPT_EMP'; 

 

        OWNER           TABLE_NAME                     COLUMN_NAME     UPD INS DEL

        --------------- ------------------------------ --------------- --- --- ---

        SCOTT           VW_DEPT_EMP                    DEPTNO          YES YES YES

        SCOTT           VW_DEPT_EMP                    DNAME           NO  NO  NO   --可以看到列DNAME不能执行DML

        SCOTT           VW_DEPT_EMP                    EMPNO           YES YES YES

        SCOTT           VW_DEPT_EMP                    ENAME           YES YES YES

         

    --尝试更新视图时,更新失败

        scott@ORCL> update vw_dept_emp set dname='Developement' where deptno=10;

        update vw_dept_emp set dname='Developement' where deptno=10

                               *

        ERROR at line 1:

        ORA-01779: cannot modify a column which maps to a non key-preserved table      

     

        scott@ORCL> update vw_dept_emp set ename='Henry' where empno=7369;

 

        1 row updated.

 

        scott@ORCL> select empno,ename,job from emp where empno=7369;

 

             EMPNO ENAME      JOB

        ---------- ---------- ---------

              7369 Henry      CLERK

     

    --创建一个基于UPDATE INSTEAD OF 触发器

        CREATE OR REPLACE TRIGGER tr_vw_dept_emp

        INSTEAD OF UPDATE

        ON vw_dept_emp

        FOR EACH ROW

        BEGIN

          UPDATE dept

          SET dname=:new.dname

          WHERE deptno=:old.deptno;

        END;

 

    --更新视图

        scott@ORCL> update vw_dept_emp set dname='Developement' where deptno=20;

 

        4 rows updated.

   

    --验证更新后的结果

        scott@ORCL> select * from vw_dept_emp where rownum<2 and deptno=20;

 

            DEPTNO DNAME               EMPNO ENAME

        ---------- -------------- ---------- ----------

                20 Developement         7369 Henry

 

        scott@ORCL> select * from dept where deptno=20;

 

            DEPTNO DNAME          LOC

        ---------- -------------- -------------

                20 Developement   DALLAS     

 

四、INSTEAD OF触发器的应用

    在工作中,有时候需要将两个或多个表中的字段进行同步的问题。即假定有表AB,表A中的字段COLa和表B中的字段COLb需要时时保持同

    步,当表ACOLa被更新时,需要将更新的内容同步到表BCOLb中,反之,当表BCOLb被更新时,需要将COLb的内容更新到A表的COLa中。

    对于这样的问题,按照一般的想法是在表A和表B分别创建触发器来使之保持同步,但实际上表A和表B上的触发器将会被迭代触发,即A表的

    更新将触发B表上的触发器,而B表上的触发器反过来又触发A上的触发器,最终的结果是导致变异表的产生。基于此,我们可以使用INSTEAD

    OF 触发器完成此项任务,下面给出全部过程。

   

    --分别创建表tb_a,tb_b并插入记录

        scott@ORCL> create table tb_a(ID int,COLa varchar2(40));

 

        scott@ORCL> create table tb_b(ID int,COLb varchar2(40));

 

        scott@ORCL> insert into tb_a select 1,'Robinson' from dual;

 

        scott@ORCL> insert into tb_b select 1,'Jackson' from dual;

 

        scott@ORCL> commit;

 

    --在表tb_a上创建触发器

        CREATE OR REPLACE TRIGGER tr_tb_a

          BEFORE UPDATE ON tb_a

          FOR EACH ROW

        DECLARE

          lv_newcol    VARCHAR2(40);

          lv_oldcol    VARCHAR2(40);

        BEGIN

          lv_newcol := :new.COLa;

          lv_oldcol := :old.COLa;

          IF lv_newcol <> lv_oldcol THEN

            UPDATE tb_b

               SET COLb = :new.COLa

             WHERE ID = :new.ID;

          END IF;

          DBMS_OUTPUT.PUT_LINE(lv_oldcol ||'=>'|| lv_newcol);

        END;

   

    --更新表tb_a时,表tb_b的字段也被更新

        scott@ORCL> update tb_a set COLa='Willson' where ID=1;

        Robinson=>Willson

 

        scott@ORCL> select * from tb_b;

 

                ID COLB

        ---------- ----------------------------------------

                 1 Willson

 

    --在表B上创建触发器

        CREATE OR REPLACE TRIGGER tr_tb_b

          BEFORE UPDATE ON tb_b

          FOR EACH ROW

        DECLARE

          lv_newcol    VARCHAR2(40);

          lv_oldcol    VARCHAR2(40);

        BEGIN

          lv_newcol := :new.COLb;

          lv_oldcol := :old.COLb;

          IF lv_newcol <> lv_oldcol THEN

            UPDATE tb_a

               SET COLa = :new.COLb

             WHERE ID = :new.ID;

          END IF;

          DBMS_OUTPUT.PUT_LINE(lv_oldcol ||'=>'|| lv_newcol);

        END;

   

    --更新表tb_b时,出现了表变异的提示,同样更新表tb_a时也会出现类似的提示

        scott@ORCL> update tb_b set COLb='Other'where ID=1;

        update tb_b set COLb='Other'where ID=1

               *

        ERROR at line 1:

        ORA-04091: table SCOTT.TB_B is mutating, trigger/function may not see it

        ORA-06512: at "SCOTT.TR_TB_A", line 8

        ORA-04088: error during execution of trigger 'SCOTT.TR_TB_A'

        ORA-06512: at "SCOTT.TR_TB_B", line 8

        ORA-04088: error during execution of trigger 'SCOTT.TR_TB_B'

 

    --禁用触发器

        scott@ORCL> alter trigger tr_tb_a disable;

 

        scott@ORCL> alter trigger tr_tb_b disable;

 

    --分别在表tb_atb_b上创建视图

        scott@ORCL> create view vw_tb_a as select * from tb_a;

 

        scott@ORCL> create view vw_tb_b as select * from tb_b;

   

    --基于视图vw_tb_a创建instead of 触发器

        CREATE OR REPLACE TRIGGER tr_vw_tb_a

          INSTEAD OF UPDATE ON vw_tb_a

          FOR EACH ROW

        DECLARE

          lv_newcol    VARCHAR2(40);

          lv_oldcol    VARCHAR2(40);

        BEGIN

          lv_newcol := :new.COLa;

          lv_oldcol := :old.COLa;

          IF lv_newcol <> lv_oldcol THEN

            UPDATE tb_a

             SET COLa = :new.COLa

            WHERE ID = :new.ID;

           

            UPDATE tb_b

              SET COLb = :new.cola

            WHERE ID=:new.ID;

          END IF;

          DBMS_OUTPUT.PUT_LINE(lv_oldcol ||'=>'|| lv_newcol);

        END;

   

    --基于视图vw_tb_b创建instead of 触发器

        CREATE OR REPLACE TRIGGER tr_vw_tb_b

          INSTEAD OF UPDATE ON vw_tb_b

          FOR EACH ROW

        DECLARE

          lv_newcol    VARCHAR2(40);

          lv_oldcol    VARCHAR2(40);

        BEGIN

          lv_newcol := :new.COLb;

          lv_oldcol := :old.COLb;

          IF lv_newcol <> lv_oldcol THEN

            UPDATE tb_a

             SET COLa = :new.COLb

            WHERE ID = :new.ID;

           

            UPDATE tb_b

              SET COLb = :new.colb

            WHERE ID=:new.ID;

          END IF;

          DBMS_OUTPUT.PUT_LINE(lv_oldcol ||'=>'|| lv_newcol);

        END;   

   

    --对视图进行更新,验证成功

        scott@ORCL> update vw_tb_a set COLa='Many' where ID = 1;

        Willson=>Many

 

        scott@ORCL> select * from tb_b;

 

                ID COLB

        ---------- ----------------------------------------

                 1 Many

 

        scott@ORCL> update vw_tb_b set COLb='Much' where ID = 1;

        Many=>Much

 

        scott@ORCL> select * from tb_a;

 

                ID COLA

        ---------- ----------------------------------------

                 1 Much

   

五、总结

    视图创建时未指定WITH CHECK OPTION选项

    INSTEAD OF触发器只适用于视图

    基于视图的INSTEAD OF触发器不能指定BEFOREAFTER选项

    INSTEAD OF触发器,必须指定FOR EACH ROW

    当创建的视图被重新定义之后,基于视图上创建的触发器将需要重新定义

 

六、更多参考

有关SQL请参考

        SQL 基础--> 子查询

        SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

 

    有关PL/SQL请参考

        PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器

 

 

   

上一篇:SQL优化常用方法17


下一篇:puppet安装