PL/SQL-->INSTEAD OF 触发器

PL/SQL-->INSTEAD OF 触发器

      INSTEAD OF 触发器常用于管理不可更新的视图，INSTEAD-OF触发器必须是行级的。
      可以用INSTEAD OF触发器来解释INSERT、UPDATE和DELETE语句，并用备用的程序代码替换那些指令。

一、不可更新视图

      基于下列情形创建的视图，不可直接对其进行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触发器的应用

      在工作中，有时候会遇到将两个或多个表中的字段进行同步的问题。即假定有表A和表B，表A中的字段COLa和表B中的字段COLb需要时时保持同步，当表A中COLa被更新时，需要将更新的内容同步到表B的COLb中，反之，当表B的COLb被更新时，需要将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_a，tb_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触发器不能指定BEFORE和AFTER选项
      INSTEAD OF触发器，必须指定FOR EACH ROW
      当创建的视图被重新定义之后，基于视图上创建的触发器将需要重新定义

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