解决ORA-04091变异表的问题

SQL code

--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 14ORA-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 20ORA-04088: 触发器 'LYH.UPD_INDEX_DATA_TR' 执行过程中出错ORA-06512: 在 "LYH.UPD_INDEX_DATA_TR", line 20ORA-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  /触发器已创建--这里的错就比较好理解了--加个commitSQL> update test set c=123 where d=2;update test set c=123 where d=2       *第 1 行出现错误: ORA-06519: 检测到活动的自治事务处理, 已经回退ORA-06512: 在 "LYH.UPD_INDEX_DATA_TR", line 26ORA-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 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  commit; --------commit！！！ 30  end; 31  /触发器已创建SQL> update test set c=123 where d=2;已更新 1 行。--成功SQL> select * from test;         A          B          C          D                                     ---------- ---------- ---------- ----------                                              1          1        223          1                                              1          2        123          2                                              1          2        100          3                                              2          2        300          4                                     SQL> spool off;--当然，这个例子是适合只能更新字段B=2的需求，如果没有限制就得稍微改动一下第15行的条件。 
 

[解决办法]
先顶一下了。
[解决办法]
不错，我害怕触发器，一般稍微大点的表都写存储过程进行相应的处理
[解决办法]
不错 虽然不具典型性
[解决办法]
记得是用全局变量和复合触发器，可以解决变异表