解决ORA-04091变异表的有关问题
解决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 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: 检测到活动的自治事务处理, 已经回退
ORA-06512: 在 "LYH.UPD_INDEX_DATA_TR", line 26
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 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行的条件。