如何在视图上面创建触发器

[winterhan]

我想在视图上建立一个触发器，该触发器用来监视视图中数据的变化，并将变化的数据写入其他表中。哪位指教一下，多谢了！

[sasacat]

我一直认为视图上不可能有触发器的，作个记号听课。

不过你为什么要把触发器放在视图上呢，放上表上不是正常多了吗

[子陌红尘]

在视图上创建instead of触发器。

[bobfang]

Specify INSTEAD OF to cause Oracle to fire the trigger instead of executing the triggering event. INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.

If a view is inherently updatable and has INSTEAD OF triggers, then the triggers take preference. In other words, Oracle fires the triggers instead of performing DML on the view.

If the view belongs to a hierarchy, then the trigger is not inherited by subviews.


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

[bobfang]

Creating an INSTEAD OF Trigger: Example
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);

[nebulaly]

如果通过视图可以确定记录的对应关系，可以直接修改视图，例如
create or replace view vtest as
select e.ename,d.dname,e.sal from scott.emp e,scott.dept d where e.deptno=d.deptno

ename和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;