--==============================
-- 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
当创建的视图被重新定义之后，基于视图上创建的触发器将需要重新定义
六、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
