oracle触发器更新数据至另外一台服务器

触发器传参数给存储过程，存储过程中有insert tableA where id=1....接着update tableA where id=1.

由于insert语句不是自动提交，所以当insert语句没有commit的时候，update会报“表/视图发生了变化，程序不能读它”的错误，也就是id=1这一行被insert锁住了。

此时，在触发器中调用自治事务，问题得以解决。

 

Oracle自治事务(Autonomous Transaction)将一个主事务分割成几个子事务，在执行完子事务以后再继续主事务。这里的关键是，子事务是独立于主事务的，子事务中的Rollback和Commit操作只会影响子事务中的DML操作；同样，主事务中的Rollback和Commit操作只会影响事务中的DML操作，而不会影响子事务中的操作。在子事务中已经commit的操作，不会被主事务中的rollback撤销。

          制定PL/SQL程序块为自治事务可以通过在程序开头使用如下命令实现

          PRAGMA AUTONOMOUS_TRANSACTION
            定义自治事务必须遵循以下规则：         

如果要被定义为自治事务的程序是匿名的，则它必须是一个最外层的程序块。

如果不是匿名的，则它必须是函数或者过程，或者是包含在一个中。在一个包中，只有其中的函数或过程能够定义成自治事务。整个包不能申明为自治事务。

一个对象的方法可以申明为自治事务

触发器可以申明为自治事务

内嵌程序块不能申明为自治事务

注意：对于一个匿名的自治事务程序块来说，只有这个块的begin和end之间的代码被看作是自治事务。

 

触发器代码如下：

create or replace trigger trigger_main2_update
       before update on t_busi_main_presend2  for each row
         declare
pragma autonomous_transaction; --声明该触发器的事务为自治事务
begin 
          DBMS_OUTPUT.PUT_LINE(:new.SHSTATUS);
           if:new.SHSTATUS='1' and :old.SHSTATUS='0' 
            then
            p_main2_mx(:new.id,:new.smscontent,:new.allcode,:new.phonetype,:new.sjtongdaoid,:new.cjr,:new.pretongdaoid,:new.clientid,:new.shr,:new.pretime,:new.cjsj,:new.shstatus,:new.kouchucnt,:new.dxlx,:new.allcount); --调用存储过程，并给存储过程传参数
            :new.SENDSTATUS:='1' ;
           end if;        
           commit;
       end;

 

运用AT(autonomous_transaction)时,有一些注意事项,简单列举如下:

1.     在匿名PL/SQL块中,只有顶级的匿名PL/SQL块可以被设为AT

2.     如果AT试图访问被MT控制的资源,可能有deadlock发生.

3.     Package 不能被声明为AT,只有package所拥有的function和procedure 才能声明为AT

4.     AT程序必须以commit 或rollback结尾,否则会产生Oracle错误ORA-06519: active autonomous transaction detected and rolled back

 

 

按照上边，出于想把表所有数据更新过去，防止出现数据不完整问题的目的， 把修改触发器为自治事务后，对整个表的数据进行更新。触发器可以修改触发器所触发的表，但数据明显不对。

例如：针对表a建立触发器b，当a数据有变动时，将表a及相关表的全部数据同步更新至另一台服务器的相同表。

原触发器内容如下

 

 

[sql]  view plain copy

1. create or replace trigger tib_classModuleSet_Library after insert or update or delete  
2. on t_class_module_set for each row  
3. declare  
4.     integrity_error  exception;  
5.     errno            integer;  
6.     errmsg           char(200);  
7.     num              number;  
8.     ---修改模块设置时同步更新另一个数据库的领域，学科，模块，模块设置 信息  
9.     pragma autonomous_transaction;----声明该触发器的事务为自治事务  
10. begin  
11.      --增加删除修改  
12.      if inserting or updating or deleting then  
13.         delete from <a href="mailto:t_class_module_set@fzyzsmsnew_76;">t_class_module_set@76;  
14. </a>        commit;  
15.           
16.         insert into <a href="mailto:t_class_module@76">t_class_module@76</a> select *  from t_class_module t where t.subject_id in  (select subject_id  from t_class_subject tt  where tt.area_id in (select area_id from t_class_area ttt where ttt.area_is_library = '2'  and AREA_INVOCATION_FLAG='1') and SUBJECT_INVOCATION_FLAG='1')  and MODULE_INVOCATION_FLAG='1';  
17.         commit;  
18.     end if;  
19.  exception  
20.     when integrity_error then  
21.        raise_application_error(errno, errmsg);  
22. end;  

 虽然使用自治事务处理后，未出现“表/视图发生了变化，程序不能读它”的异常。

 对a执行insert or update or delete 操作之后，触发器被触发。

触发器被触发前，insert 的数据未出现在表a中

触发器执行中，insert 的数据未出现在表a中。

触发器执行完毕后，insert 数据此时才出现在表a中。

 这样，最新更新的数据并未被同步至另一个数据库中。触发器的使用目的未达到。

 

尝试把对数据库表操作移至存储过程中，在触发器中调用存储过程，问题和上边一致，最新数据仍未被更新过去。

 

 

最后只好回至最初，对最新数据进行单条操作，才达到目的。

 

[sql]  view plain copy

1. create or replace trigger tib_classArea_Library after insert or update or delete  
2. on t_class_area for each row  
3. declare  
4.     integrity_error  exception;  
5.     errno            integer;  
6.     errmsg           char(200);  
7.     num              number;  
8.     ---修改模块设置时同步更新第三图书馆的领域，学科，模块，模块设置 信息  
9.     --pragma autonomous_transaction;----声明该触发器的事务为自治事务  
10. begin  
11.      --增加删除修改  
12.   
13.           if inserting then  
14.                 insert into t_class_area@fzyzsmsnew_76 (area_id,area_name,AREA_INVOCATION_FLAG,AREA_IS_LIBRARY)values (:new.area_id,:new.area_name,:new.AREA_INVOCATION_FLAG,:new.AREA_IS_LIBRARY);  
15.          elsif updating then  
16.                 update t_class_area@fzyzsmsnew_76 set area_id=:new.area_id,area_name=:new.area_name,area_invocation_flag=:new.area_invocation_flag,area_is_library=:new.area_is_library where area_id=:old.area_id;  
17.          elsif deleting then   
18.                delete from t_class_area@fzyzsmsnew_76 where area_id=:old.area_id;  
19.          end if;  
20. exception  
21.     when integrity_error then  
22.        raise_application_error(errno, errmsg);  
23. end;  

 不明白为什么，哪位网友知道原因可以给予评论，谢谢。