PL/SQL --> INSTEAD OF 触发器

港控/mmm° 2022-05-15 03:25 182阅读 0赞

\--==============================

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

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

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

六、更多参考

有关SQL请参考

[SQL 基础--> 子查询][SQL _--_]

[SQL 基础-->多表查询][SQL _--]

[SQL基础-->分组与分组函数][SQL_--]

[SQL 基础-->常用函数][SQL _-- 1]

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

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

有关PL/SQL请参考

[PL/SQL --> 语言基础][PL_SQL --_]

[PL/SQL --> 流程控制][PL_SQL --_ 1]

[PL/SQL --> 存储过程][PL_SQL --_ 2]

[PL/SQL --> 函数][PL_SQL --_ 3]

[PL/SQL --> 游标][PL_SQL --_ 4]

[PL/SQL -->隐式游标(SQL%FOUND)][PL_SQL --_SQL_FOUND]

[PL/SQL --> 异常处理(Exception)][PL_SQL --_ _Exception]

[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[PL/SQL --> 包的创建与管理][PL_SQL --_ 5]

[PL/SQL --> 包重载、初始化][PL_SQL --_ 6]

[PL/SQL --> DBMS\_DDL包的使用][PL_SQL --_ DBMS_DDL]

[PL/SQL --> DML 触发器][PL_SQL --_ DML]

[PL/SQL --> INSTEAD OF 触发器][PL_SQL --_ INSTEAD OF]

[SQL _--_]: http://blog.csdn.net/robinson_0612/archive/2010/05/14/5592002.aspx
[SQL _--]: http://blog.csdn.net/robinson_0612/archive/2010/05/06/5563199.aspx
[SQL_--]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx
[SQL _-- 1]: http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx
[SQL _--_ ROLLUP_CUBE]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063399.aspx
[PL_SQL --_ 1]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063422.aspx
[PL_SQL --_ 2]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063434.aspx
[PL_SQL --_ 3]: http://blog.csdn.net/robinson_0612/archive/2010/12/11/6069531.aspx
[PL_SQL --_ 4]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[PL_SQL --_ _Exception]: http://blog.csdn.net/robinson_0612/archive/2010/12/16/6080119.aspx
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084390.aspx
[PL_SQL --_ 5]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084475.aspx
[PL_SQL --_ 6]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092097.aspx
[PL_SQL --_ DBMS_DDL]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098248.aspx
[PL_SQL --_ DML]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098258.aspx
[PL_SQL --_ INSTEAD OF]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098263.aspx