ITPub博客

首页 > 数据库 > Oracle > ORACLE INSTEAD OF 触发器

ORACLE INSTEAD OF 触发器

Oracle 作者:3834110 时间:2010-06-24 10:44:05 0 删除 编辑

      这两天就遇到一个问题,有两张表的一个字段需要进行同步更新,也就是A表修改时要把对应的B表的记录
字段修改,反过来B表修改时也要把A表的修改,保持两边数据的一个同步,这个可以在前台很容易的实现,但开发
人员不想修改代码了,就考虑在后台用trigger实现。
      功能很简单,但在实现时遇到一个问题,就是A上的DML触发了上面的TRIGGER,然后这个TRIGGER去更新B表,这样
就会触发B表上的触发器,而B表上的TRIGGER又会更新A表,这样就迭代触发,没有结束了,也就是会产生变异表(mutating)
我不知道ORACLE的触发器是否有属性来限制这种情况的发生,但以前做SQL SERVER时知道有种Instaed of的触发器,他表示
当DML启动他后,他将以TRIGGER里的代码来代替这个DML动作,也就是DML不会真正的执行,只会启动INSTEAD TRIGGER,最终
执行的是TRIGGER里面的编码。
      查看了Docs,看到ORACLE也支持这个类型的触发器,但这个只能建立到视图上,不能基于表建立,我要的功能肯定是可以
实现的,在这里我把原表进行了rename,引如了两张视图,名字就是以前的表名,这样对于他们前台应用就做了个
透明的切换,然后在两个视图上建立INSTEAD触发器,将任何两个视图上的更新都传播到后面的两个基表,这样不管你更新那个
视图,我都可以捕获到数据,以代码在后面更新,也不存在互相触发,因为触发器修改的对象已经转移到表了,而此时表上是没有
trigger的,呵呵!!!
过程如下

--创建测试表
SQL> create table mytest1(row_num number,row_name varchar2(50));

表被创建

SQL> create table mytest2(row_num number,row_name varchar2(50));

表被创建

--测试数据
SQL> INSERT INTO MYTEST1 VALUES(1,'Fuck!!!');

1 行 已插入

SQL> INSERT INTO MYTEST2 VALUES(1,'Watch your mouth!!!');

1 行 已插入

SQL> COMMIT;

提交完成

--先在一个表上创建触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
  BEFORE UPDATE
  ON MYTEST1
  FOR EACH ROW
  DECLARE
  lv_new VARCHAR2(20);
  lv_parent VARCHAR2(20);
  BEGIN
      lv_new := :new.row_name;
 10      lv_parent := :OLD.row_name;
 11      IF lv_new <> lv_parent THEN
 12          UPDATE MYTEST2
 13          SET ROW_NAME = :NEW.ROW_NAME
 14          WHERE ROW_NUM = :NEW.ROW_NUM;
 15      END IF;
 16      DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
 17  END;
 18  /

触发器被创建

--测试更新
SQL> set serveroutput on
SQL> UPDATE MYTEST1 SET ROW_NAME = 'DO it!!!';
DO it!!! Fuck!!!

1 行 已更新

--更新成功
SQL> SELECT * FROM MYTEST2;

   ROW_NUM ROW_NAME
---------- --------------------------------------------------
         1 DO it!!!
        
--另外张表创建触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST2             
  BEFORE UPDATE                                   
  ON MYTEST2                                      
  FOR EACH ROW                                    
  DECLARE                                         
  lv_new VARCHAR2(20);                            
  lv_parent VARCHAR2(20);                         
  BEGIN                                           
      lv_new := :new.row_name;                       
 10      lv_parent := :OLD.row_name;                    
 11      IF lv_new <> lv_parent THEN                     
 12          UPDATE MYTEST1                               
 13          SET ROW_NAME = :NEW.ROW_NAME                 
 14          WHERE ROW_NUM = :NEW.ROW_NUM;                
 15      END IF;                                        
 16      DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);     
 17  END;                                            
 18  
 
--产生了变异表,更新失败
SQL> update mytest1 set row_name = 'mouthkkkkkoo';

update mytest1 set row_name = 'mouthkkkkkoo'

ORA-04091: table MYTEST1 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2'
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'   


--更新失败
SQL> update mytest2 set row_name = 'mouthkkkkkoo';

update mytest2 set row_name = 'mouthkkkkkoo'

ORA-04091: table MYTEST2 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2' 

--删除触发器
SQL> drop trigger TRI_TEST2;

触发器被删掉

SQL> drop trigger TRI_TEST1;

触发器被删掉


--创建视图
SQL> CREATE VIEW V_TEST1 AS SELECT * FROM MYTEST1;

视图被创建

SQL> CREATE VIEW V_TEST2 AS SELECT * FROM MYTEST2;

视图被创建

--基于视图创建Instead触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
  INSTEAD OF UPDATE
  ON V_TEST1
  FOR EACH ROW
  DECLARE
  lv_new VARCHAR2(20);
  lv_parent VARCHAR2(20);
  BEGIN
      lv_new := :new.row_name;
 10      lv_parent := :OLD.row_name;
 11      IF lv_new <> lv_parent THEN
 12          UPDATE MYTEST2
 13          SET ROW_NAME = :NEW.ROW_NAME
 14          WHERE ROW_NUM = :NEW.ROW_NUM;
 15          UPDATE MYTEST1
 16          SET ROW_NAME = :NEW.ROW_NAME
 17          WHERE ROW_NUM = :NEW.ROW_NUM;
 18      END IF;
 19      DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
 20  END;
 21  /

触发器被创建

SQL> CREATE OR REPLACE TRIGGER TRI_TEST2
  INSTEAD OF UPDATE
  ON V_TEST2
  FOR EACH ROW
  DECLARE
  lv_new VARCHAR2(20);
  lv_parent VARCHAR2(20);
  BEGIN
      lv_new := :new.row_name;
 10      lv_parent := :OLD.row_name;
 11      IF lv_new <> lv_parent THEN
 12          UPDATE MYTEST2
 13          SET ROW_NAME = :NEW.ROW_NAME
 14          WHERE ROW_NUM = :NEW.ROW_NUM;
 15          UPDATE MYTEST1
 16          SET ROW_NAME = :NEW.ROW_NAME
 17          WHERE ROW_NUM = :NEW.ROW_NUM;
 18      END IF;
 19      DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
 20  END;
 21  /

触发器被创建  

--功能已经实现
SQL> update v_test1 set row_name = 'I rock with you!!!';                                        
                                                            
1 行 已更新                                                 
                                                            
SQL> commit;                                                
                                                            
提交完成                                                    
                                                            
SQL> select * from v_test2;                                 
                                                            
   ROW_NUM ROW_NAME                                         
---------- --------------------------------------------------
         1 I rock with you!!!                                                                    
                                                            
                                                            
SQL> update v_test2 set row_name = 'Don''t kick me!!!';     
                                                            
1 行 已更新                                                 
                                                            
SQL> commit;                                                
                                                            
提交完成                                                    
                                                            
SQL> select * from v_test1;                                 
                                                            
   ROW_NUM ROW_NAME                                         
---------- --------------------------------------------------
         1 Don't kick me!!!                                 
                                                            
SQL> 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7422731/viewspace-1117061/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论