INSTEAD OF 触发器的使用陷阱

        于两张表以上关联起来的视图,Oracle是分不出来应该对哪个表进行更新
       而INSTEAD OF 触发器实现了我们对视图DML的需求,显示的告诉告诉Oracle要更新哪个表
       和其他类型触发器不同的是,INSTEAD OF 触发器实际上并不是由某个事件触发的

       语法:

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印象不太好,从未主动想去亲近她
       这里Think想强调的一点是,9i的文档有提到:
       "INSTEAD OF triggers defined in the view are dropped when a view is re-created
       一贯以来的思维方式就是:如果修改了底层对象,那么重新编译上层的视图、触发器、存储过程等即可
       只要状态为 VALID 就视为没有问题
       然而,INSTEAD OF 触发器却无异于当头棒喝,原来Oracle还会有这样的脾气!
       下面用实验来证明这句话

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:指更新以后的字段值

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值