在视图上创建instead of触发器。
解决方案 »
- 这两种方式建的范围分区表有什么区别呢
- 快来救救我,我是个顽皮的孩子,Oracle把我拒之门外了!!!
- oracle11g冷备恢复,重建控制文件后报错
- 如何获得第一份DBA的工作?
- 求推荐学习proc c/c++的书籍
- 有谁用过ORACLE PORTAL没?要开发的话,在哪里下载?下载什么版本的软件?
- 怎么从异地oracle库中导入数据?怎么在PL/SQL块中删除大数据量表?
- 求教带参数查询并返回结果集的存储过程写法?
- sql问题。
- UNIX机器上安装Oracle 12c 报错“Failed to access the temporary location.”
- ORA-01652
- 新人请教问题,大家请进。。。。。。。。
--------------------------------------------------------------------------------
Note:
Oracle fine-grained access control lets you define row-level security policies on views. These policies enforce specified rules in response to DML operations. If an INSTEAD OF trigger is also defined on the view, then Oracle will not enforce the row-level security policies, because Oracle fires the INSTEAD OF trigger instead of executing the DML on the view.--------------------------------------------------------------------------------
Restrictions on INSTEAD OF Triggers
INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.
In this example, an oe.order_info view is created to display information about customers and their orders:CREATE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view (the PL/SQL trigger implementation is shown in italics):CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;
/You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):INSERT INTO order_info VALUES
(999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
create or replace view vtest as
select e.ename,d.dname,e.sal from scott.emp e,scott.dept d where e.deptno=d.deptnoename和sal可以直接修改如果要修改dname,需要使用instead of触发器,并且视图也只能使用instead of触发器:create or replace trigger ttest instead of update on vtest
begin
update dept set dname=:new.dname where deptno=(select deptno from emp where ename=:old.ename);
end ttest;