oracle instead of update触发器,INSTEAD OF触发器的使用陷阱

INSTEAD OF触发器的使用陷阱

对于两张表以上关联起来的视图，Oracle是分不出来应该对哪个表进行更新

而INSTEAD OF 触发器实现了我们对视图DML的需求，显示的告诉告诉Oracle要更新哪个表  www.2cto.com

和其他类型触发器不同的是，INSTEAD OF 触发器实际上并不是由某个事件触发的

语法：

[sql]

CREATE OR REPLACE TRIGGER trigger_name

INSTEAD OF operation    --delete,update,insert,create and so on

ON view_name

FOR EACH ROW

BEGIN

...

END;

因为对TRIGGER印象不太好，从未主动想去亲近她  www.2cto.com

这里Think想强调的一点是，9i的文档有提到：

"INSTEAD OF triggers defined in the view are dropped when a view is re-created"

一贯以来的思维方式就是：如果修改了底层对象，那么重新编译上层的视图、触发器、存储过程等即可

只要状态为 VALID 就视为没有问题

然而，INSTEAD OF 触发器却无异于当头棒喝，原来Oracle还会有这样的脾气！

下面用实验来证明这句话

[sql]

hr@ORCL> CREATE TABLE A (ID NUMBER(5),COL VARCHAR2(5));

Table created.

hr@ORCL> CREATE TABLE B (ID NUMBER(5),COL VARCHAR2(5));

Table created.

hr@ORCL> CREATE OR REPLACE VIEW V_AB AS

2      SELECT A.ID,A.COL AS COLA,B.COL AS COLB

3       FROM A,B

4      WHERE A.ID=B.ID

5      /

View created.

hr@ORCL> CREATE OR REPLACE TRIGGER TRG_V_AB

2      INSTEAD OF INSERT ON V_AB

3      FOR EACH ROW

4      BEGIN

5      INSERT INTO A(ID,COL)VALUES(:NEW.ID,:NEW.COLA);

6      INSERT INTO B(ID,COL)VALUES(:NEW.ID,:NEW.COLB);

7      END;

8      /

Trigger created.

hr@ORCL> SELECT TRIGGER_NAME,TRIGGER_TYPE FROM USER_TRIGGERS;

TRIGGER_NAME                   TRIGGER_TYPE

------------------------------ ----------------

TRG_V_AB                       INSTEAD OF

hr@ORCL> INSERT INTO V_AB VALUES(1,'AA','BB');

1 row created.

hr@ORCL> COMMIT;

Commit complete.

hr@ORCL> select * from a;

ID COL

---------- -----

1 AA

hr@ORCL> select * from b;

ID COL

---------- -----

1 BB

hr@ORCL> CREATE OR REPLACE VIEW  V_AB AS

2      SELECT A.ID,B.COL AS COLA,A.COL AS COLB

3       FROM A,B

4      WHERE A.ID=B.ID

5      /

View created.

hr@ORCL> INSERT INTO V_AB VALUES(1,'AA','BB');

INSERT INTO V_AB VALUES(1,'AA','BB')

*

ERROR at line 1:

ORA-01779: cannot modify a column which maps to a non key-preserved table

hr@ORCL> SELECT TRIGGER_NAME,TRIGGER_TYPE FROM USER_TRIGGERS;

no rows selected.

注释：

触发器，而且是行级触发器

:OLD：指更新以前的字段值

:new：指更新以后的字段值

http://www.dengb.com/oracle/482806.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/482806.htmlTechArticleINSTEAD OF触发器的使用陷阱 对于两张表以上关联起来的视图，Oracle是分不出来应该对哪个表进行更新 而INSTEAD OF 触发器实现了我们对视图D...