替代触发器的使用

当需要针对视图创建触发器来处理业务逻辑,或数据流程时, 可以用替代触发器来实现对视图相关表的操作.

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