--ORA-04091行触发器中访问变异表
--这个问题相信很多人都遇到过，我之前在做触发器的时候也遇到过几次，解决方法一般就是用两种，一是仅用自治事务的触发器就可以解决；二是在触发器中用临时变量
--也就是用临时变量保存行信息；当然改变一下涉及思路或许是最好的选择，但是在遇到既不能改变设计，而且必须用触发器解决的时候就会有问题了
--下面的就是一个这样的例子，也是我刚刚在工作中遇到的问题，记录下来和大家分享一下，对于高手来说不算什么，但或许对一些人来说还是有点用的。
--表test是测试表，具体需求是：如果更新C列的数据，则触发更新拥有和更新行相同值的A列，且不同值的B列数据，D字段可以看成是这个表的主键，
--当然没有这个主键字段也是没问题的。
SQL> select * from test;

         A          B          C          D                                     
---------- ---------- ---------- ----------                                     
         1          1        200          1                                     
         1          2        100          2                                     
         1          2        100          3                                     
         2          2        300          4                                     
--刚上来想到的触发器是这样的
SQL> create or replace trigger upd_index_data_tr
  2    before update of c on test
  3    for each row
  4  
  5  declare
  6    v_a number;
  7  
  8    v_b  number;
  9    v_c  number;
 10    v_r rowid;
 11    vs_c number;
 12  
 13  begin
 14  
 15    if :new.c is not null then
 16      select :new.a, :new.b, :new.c,:new.rowid  into v_a, v_b, v_c,v_r from dual;
 17      ----
 18      select sum(c)+v_c
 19        into vs_c
 20        from test
 21       where b = v_b
 22         and a = v_a and rowid<>v_r; 
 23      -----
 24      update test
 25         set c = vs_c
 26       where a = v_a
 27         and b = 1;
 28    end if;
 29  
 30  end;
 31  /

触发器已创建
--执行更新语句，不出意外的会报错
SQL> update test set c=123 where d=2;
update test set c=123 where d=2
       *
第 1 行出现错误: 
ORA-04091: 表 LYH.TEST 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "LYH.UPD_INDEX_DATA_TR", line 14
ORA-04088: 触发器 'LYH.UPD_INDEX_DATA_TR' 执行过程中出错 

--于是想到用自治事务
SQL> create or replace trigger upd_index_data_tr
  2    before update of c on test
  3    for each row
  4  
  5  declare
  6    v_a number;
  7  
  8    v_b  number;
  9    v_c  number;
 10    v_r rowid;
 11    vs_c number;
 12    PRAGMA AUTONOMOUS_TRANSACTION;
 13  begin
 14  
 15    if :new.c is not null then
 16      select :new.a, :new.b, :new.c,:new.rowid  into v_a, v_b, v_c,v_r from dual;
 17      ----
 18      select sum(c)+v_c
 19        into vs_c
 20        from test
 21       where b = v_b
 22         and a = v_a and rowid<>v_r;
 23      -----
 24      update test
 25         set c = vs_c
 26       where a = v_a
 27         and b = 1;
 28    end if;
 29  
 30  end;
 31  /

触发器已创建
--这里居然报了死锁，跟踪发现原来是执行到第24行的时候，又触发了触发器，两次触发造成了资源的争夺。
--当然insert的时候就没有这样的问题啦，可这里还是update。。。
SQL> update test set c=123 where d=2;
update test set c=123 where d=2
       *
第 1 行出现错误: 
ORA-00060: 等待资源时检测到死锁
ORA-06512: 在 "LYH.UPD_INDEX_DATA_TR", line 20
ORA-04088: 触发器 'LYH.UPD_INDEX_DATA_TR' 执行过程中出错
ORA-06512: 在 "LYH.UPD_INDEX_DATA_TR", line 20
ORA-04088: 触发器 'LYH.UPD_INDEX_DATA_TR' 执行过程中出错 

--于是在第15行增加了一个条件，保证第二次触发的时候，并不会试图去争夺已被锁住的行资源。
SQL> create or replace trigger upd_index_data_tr
  2    before update of c on test
  3    for each row
  4  
  5  declare
  6    v_a number;
  7  
  8    v_b  number;
  9    v_c  number;
 10    v_r rowid;
 11    vs_c number;
 12    PRAGMA AUTONOMOUS_TRANSACTION;
 13  begin
 14  
 15    if :new.c is not null and :new.b<>1 then
 16      select :new.a, :new.b, :new.c,:new.rowid  into v_a, v_b, v_c,v_r from dual;
 17      ----
 18      select sum(c)+v_c
 19        into vs_c
 20        from test
 21       where b = v_b
 22         and a = v_a and rowid<>v_r;
 23      -----
 24      update test
 25         set c = vs_c
 26       where a = v_a
 27         and b = 1;
 28    end if;
 29  
 30  end;
 31  /

触发器已创建
--这里的错就比较好理解了
--加个commit
SQL> update test set c=123 where d=2;
update test set c=123 where d=2
       *
第 1 行出现错误: 
ORA-06519: 检测到活动