触发器中ORA-04091 变异表错误原因和一般解决方法

在公司写了一个触发器,遇到了ORA-04091 错误,开始以为是触发器逻辑写错误了,后来反复修改和检查后感觉没有错误,可是还报这个错误.

后经过查资料知道原来在触发器中对触发器要更新的表是不能在本表上做查询的,最终我在触发器中绕过查询本表就可以了.现在记录下原因和一般的解决方法:

ORA-04091 产生原因:

http://blog.csdn.net/walkingleo/article/details/4056492

一般处理这个错误的方法(论坛上整理出来的):

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行的条件。