PL/SQL12.2 ——DML 触发器

简介: http://blog.csdn.net/robinson_0612/article/details/6098263  INSTEAD OF 触发器常用于管理编写不可更新的视图,INSTEAD-OF触发器必须是行级的。

http://blog.csdn.net/robinson_0612/article/details/6098263

 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<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

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

 


相关文章
|
1月前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
19 2
|
4月前
|
SQL Oracle 关系型数据库
SQL与PL/SQL:数据库编程语言的比较
【8月更文挑战第31天】
74 0
|
4月前
|
SQL 存储 数据管理
深入理解SQL中的触发器
【8月更文挑战第31天】
55 0
|
4月前
|
SQL 关系型数据库 数据库
|
4月前
|
SQL Oracle 关系型数据库
DML触发器
8月更文挑战第22天
|
6月前
|
SQL 数据库
零基础学习数据库SQL语句之操作表中数据的DML语句
零基础学习数据库SQL语句之操作表中数据的DML语句
59 0
零基础学习数据库SQL语句之操作表中数据的DML语句
|
6月前
|
存储 SQL 数据库
SQL 语言:存储过程和触发器
SQL 语言:存储过程和触发器
90 6
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库——图形化界面工具(DataGrip),SQL(2)-DML(插入、修改和删除数据)
MySQL数据库——图形化界面工具(DataGrip),SQL(2)-DML(插入、修改和删除数据)
565 1
|
6月前
|
SQL 数据库 Perl
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
519 0
|
7月前
|
SQL 关系型数据库 MySQL
【MySQL-4】简述SQLの通用语法及4种基本语句介绍(DDL/DML/DQL/DCL)
【MySQL-4】简述SQLの通用语法及4种基本语句介绍(DDL/DML/DQL/DCL)