--==============================
-- 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 基础--> 子查询
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 触发器
分享到:
相关推荐
instead of omitting it from the inserts statement<br>- Support for the ODAC instant client added<br>- Translated help files were not used<br>- Performance improvements for some dictionary queries<br>-...
instead of omitting it from the inserts statement<br>- Support for the ODAC instant client added<br>- Translated help files were not used<br>- Performance improvements for some dictionary queries<br>-...
开发触发器<br>13.1 触发器简介<br>13.2 建立DML触发器<br>13.2.1 语句触发器<br>13.2.2 行触发器<br>13.2.3 使用DML触发器<br>13.3 建立INSTEADOF触发器<br>13.4 建立系统事件触发器<br>13.5 管理触发器...
instead of omitting it from the inserts statement<br>- Support for the ODAC instant client added<br>- Translated help files were not used<br>- Performance improvements for some dictionary queries<br>-...
§8.2.3 创建替代(Instead_of)触发器 13 §8.2.3 创建系统事件触发器 13 §8.2.4 系统触发器事件属性 13 §8.2.5 使用触发器谓词 13 §8.2.6 重新编译触发器 13 §8.3 删除和使能触发器 13 §8.4 触发器和数据...
§8.2.3 创建替代(Instead_of)触发器 13 §8.2.3 创建系统事件触发器 13 §8.2.4 系统触发器事件属性 13 §8.2.5 使用触发器谓词 13 §8.2.6 重新编译触发器 13 §8.3 删除和使能触发器 13 §8.4 触发器和数据字典 ...
§8.2.3 创建替代(Instead_of)触发器 13 §8.2.3 创建系统事件触发器 13 §8.2.4 系统触发器事件属性 13 §8.2.5 使用触发器谓词 13 §8.2.6 重新编译触发器 13 §8.3 删除和使能触发器 13 §8.4 触发器和数据字典 ...
§8.2.3 创建替代(Instead_of)触发器 13 §8.2.3 创建系统事件触发器 13 §8.2.4 系统触发器事件属性 13 §8.2.5 使用触发器谓词 13 §8.2.6 重新编译触发器 13 §8.3 删除和使能触发器 13 §8.4 触发器和数据字典 ...
§8.2.3 创建替代(Instead_of)触发器 13 §8.2.3 创建系统事件触发器 13 §8.2.4 系统触发器事件属性 13 §8.2.5 使用触发器谓词 13 §8.2.6 重新编译触发器 13 §8.3 删除和使能触发器 13 §8.4 触发器和数据字典 ...
触发器分为:DML触发器(对表或视图执行DML操作时触发),INSTEAD OF触发器(只定义在视图上,替代实际的操作语句),系统触发器(对数据库系统进行操作时触发,如DDL语句、启动或关闭数据库等) 触发事件: 上述...
16.2.6 instead of触发器 332 16.2.7 嵌套触发器 334 16.2.8 递归触发器 336 16.2.9 sql server中触发器的管理 338 16.3 oracle数据库中触发器的操作 340 16.3.1 oracle触发器类型 340 16.3.2 触发器的创建 ...
在ORACLE系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块,不过有一点不同的是,触发器是隐式调用的,并不能接收参数。 触发器优点 (1)触发器能够实施的检查和操作比主键和外键约束、...
第一部分 Oracle SQL*PLUS基础 23 第一章 Oracle数据库基础 23 §1.1 理解关系数据库系统(RDBMS) 23 §1.1.1 关系模型 23 ...§18.2.2 创建替代(Instead_of)触发器 300 §18.2.3 创建系统触发器 300...
第一部分 Oracle SQL*PLUS基础 23 第一章 Oracle数据库基础 23 §1.1 理解关系数据库系统(RDBMS) 23 §1.1.1 关系模型 23 ...§18.2.2 创建替代(Instead_of)触发器 300 §18.2.3 创建系统触发器 300...
16.2.6 INSTEAD OF触发器 16.2.7 嵌套触发器 16.2.8 递归触发器 16.2.9 SQL Server中触发器的管理 16.3 Oracle数据库中触发器的操作 16.3.1 Oracle触发器类型 16.3.2 触发器的创建 16.3.3 创建...
2.替代触发器(instead of) 92 3.系统事件触发器 93 三.Alter Trigger语句 94 四.与触发器有关的数据字典 95 第12讲 安 全 98 一. 用户账号 98 1.用户配置文件(概要文件) 98 2.监视用户 98 二. 权限管理 99 ...
触发器包括DML触发器、INSTEAD-OF触发器和系统触发器。其中,DML触发器主要作用于表,其事件有INSERT、UPDATE、DELETE;INSTEAD-OF触发器主要主用于视图,其事件有INSERT、UPDATE、DELETE;系统触发器主要是DML事件...
4.8.3INSTEADOF触发器 4.8.4 系统触发器 4.8.5 触发器的管理 4.9 小结 第5章 Omele11g体系结构概述 5.1 存储结构 5.1.1 逻辑存储结构 5.1.2 物理存储结构 5.2 软件结构 5.2.1软件结构 ...