最近遇到一个项目上的问题,需要写一个触发器,而这个触发器又需要更新自己的表,这就有可能循环调用触发器。简单举例说明,有一个表Test,如下所示:
A B C D
---------- ---------- ---------- ----------
1 21 200 11
2 22 100 2
3 23 100 33
4 24 300 44
SQL>create or replace trigger upd_test
before update of c on test
for each row
declare
v_d number;
begin
select max(D) into v_d from test;
update test set B = v_d ;
end;
触发器已创建
----执行更新语句,会报错
SQL>update test set c=123 where d=2;
update test set c=123 where d=2
*
第 1 行出现错误:
ORA-04091: 表 QY.TEST 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "QY.UPD_TEST", line 9
ORA-04088: 触发器 'QY.UPD_TEST' 执行过程中出错
----使用自治事物
SQL>create or replace trigger upd_test
before update of c on test
for each row
declare
v_d number;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select max(D) into v_d from test;
update test set B = v_d ;
end;
触发器已创建
---这里会报错,执行到第10行的时候,又触发了触发器,两次触发造成资源的争夺。
SQL>update test set c=123 where d=2;
update test set c=123 where d=2
*
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁
ORA-06512: 在 "QY.UPD_TEST", line 10
ORA-04088: 触发器 'QY.UPD_TEST' 执行过程中出错
ORA-06512: 在 "QY.UPD_TEST", line 10
ORA-04088: 触发器 'QY.UPD_TEST' 执行过程中出错
--于是在第12行增加了一个条件,保证第二次触发的时候,并不会试图去争夺已被锁住的行资源。
SQL>create or replace trigger upd_test
before update of c on test
for each row
declare
v_d number;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select max(D) into v_d from test;
if v_d not null and :new.d <> v_d then
update test set B = v_d ;
end;
触发器已创建
SQL> update test set c=123 where d=2;
已更新 1 行。
--成功
SQL> select * from test;
A B C D
---------- ---------- ---------- ----------
1 21 200 11
2 44 123 2
3 23 100 33
4 24 300 44
---这个例子只是简单说明,当需要其他条件时修改12行的条件即可
-------------笔者遇到的另外一个关于ORA-04091的问题---------
原触发器如下:
create or replace trigger TRG_KCXED2_INSERT
after insert on KCXED2
for each row
begin
update KCXED2 set KCXED2_FLHQZ = to_char(to_number(:new.KCXED2_flbh)) where KCXED2_lsbh = :new.KCXED2_lsbh
and KCXED2_flbh = :new.KCXED2_flbh;
end ;
当我想KCXED2表中插入数据时,又报了这个错误,由于业务原因,无法使用自治事物,若使用自治事物里面的代码无法回滚。后来采用这种方式:
create or replace trigger TRG_KCXED2_INSERT
before insert on KCXED2
for each row
begin
:new.KCXED2_FLHQZ := to_char(to_number(:new.KCXED2_flbh));
end ;
在插入之前将值改掉。