当需要针对视图创建触发器来处理业务逻辑,或数据流程时, 可以用替代触发器来实现对视图相关表的操作.
SQL> create table user_info ( id number,name varchar2(200));
Table created.
SQL> insert into user_info values(1,'DINYA');
1 row created.
SQL> commit;
Commit complete.
SQL> create table skill( id number,skill varchar2(300),user_id number);
Table created.
SQL> insert into skill values(1,'打字',1);
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace view user_skills as select a.id ,a.name,b.skill from user_info a,skill b wher
e a.id=b.user_id;
View created.
SQL> select * from user_skills;
ID NAME SKILL
--------------- -------------------------------- -----------------------------------------------
1 DINYA 打字
SQL> update user_skills t set t.skill='上网' ;
update user_skills t set t.skill='上网'
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> create or replace trigger user_skills_update
2 instead of update on user_skills
3 for each row
4 begin
5 update skill set skill=:new.skill where user_id=:old.id;
6 end ;
7 /
Trigger created.
SQL> update user_skills t set t.skill='上网' where t.id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from skill;
ID SKILL USER_ID
----------------- --------------------- ---------------------------------------------------------
1 上网 1
SQL>
#
Dinya