===一段英文不难,放心读下去
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. (最后一句说明了视图触发器的核心还是针对数据表上的DML操作,但是又有不同,否则就不用视图触发器了,区别在于通过视图修改基表的操作,如果不在触发器中对基表进行操作的话,上面操作将无效,等下有例子说明。实际上视图触发器的目的就是为了当发生DML操作时不去修改原视图关联的基本否则就没什么意思了。)
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.
现在有两个表 table_a 和 table_b, 还有一个视图 view_a, 表和视图的结构如下
SQL code
-- Create table
create table table_a
(
a number,
b varchar2(30),
c varchar2(30)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table table_a
add constraint PK_table_a primary key (A);
===================================================================
-- Create table
create table table_b
(
a number,
b varchar2(30),
c varchar2(30)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table table_b
add constraint PK_table_b primary key (A);
===================================================================
create or replace trigger trg_insurance
instead of update or insert or delete on view_a
begin
if inserting then
insert into table_a(a,b,c) values(:new.a,:new.b,:new.c);
--只有加上下面该语句通过修改view_a达到修改 table_b才能实现
--insert into table_b(a,b,c) values(:new.a,:new.b,:new.c);
elsif updating then
if (:new.b=1) then
insert into table_a(a,b,c) values(:new.a,:new.b,:new.c);
elsif (:new.b=0) then
delete from table_a where a=3;
end if;
elsif deleting then
delete from table_a where a=3;
end if;
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9778796/viewspace-662359/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9778796/viewspace-662359/