SQL> create or replace trigger addnum
2 before update on test1.test1
3 for each row
4 begin
5 update test1 set group1=(:new.group1+1) where group1=2;
6 end;
7 /
触发器已创建
已用时间: 00: 00: 00.00
SQL> update test1 set group1=7 where group1=1;
update test1 set group1=7 where group1=1
*
ERROR 位于第 1 行:
ORA-04091: 表 TEST1.TEST1 发生了变化,触发器/函数不能读
ORA-06512: 在"TEST1.ADDNUM", line 2
ORA-04088: 触发器 'TEST1.ADDNUM' 执行过程中出错
这样是肯定不行的,因为触发器会引起死循环。用表层触发器,但表层是不能用:new或:old
SQL> create or replace procedure test3(i number,m number)
2 as
3 a number;
4 str char(1000);
5 begin
6 update test1 set group1=i where group1=m;
7 execute immediate 'COMMIT';
8 a:=i;
9 for a in i..m loop
10 update test1 set group1=i+1 where group1=i;
11 execute immediate 'COMMIT';
12 end loop;
13 end test3;
14 /
过程已创建。
已用时间: 00: 00: 00.00
SQL> exec test3(2,4);
BEGIN test3(2,4); END;
*
ERROR 位于第 1 行:
ORA-04091: 表 TEST1.TEST1 发生了变化,触发器/函数不能读
ORA-06512: 在"TEST1.ADDNUM", line 2
ORA-04088: 触发器 'TEST1.ADDNUM' 执行过程中出错
ORA-06512: 在"TEST1.TEST3", line 6
ORA-06512: 在line 1
1、使用视图或者临时表
You can do:
1. Rename your table with another name;
2. Create a view for select * from the table with the original name of the table;
3. Create a instead of trigger on the view, in the view you can access and update yur base
table.
Example of instead of trigger for this case:
[ Code Start ]
create table t1(
id number(6) primary key
,pid number(6)
,value number(15,2)
,f1 varchar2(10)
,f2 varchar2(20)
);
create or replace view t1_v as select * from t1;
create or replace trigger bug_t1_v
instead of update on t1_v
for each row
declare
procedure update_parents(i_id in number, i_value in number);
procedure update_parents(i_id in number, i_value in number) is
begin
declare
l_pid t1.pid%type;
begin
select pid into l_pid
from t1
where id = i_id;
if l_pid <> 0 then
update t1 set value = nvl(value,0) + nvl(i_value,0)
where id = l_pid;
update_parents(l_pid, i_value);
end if;
exception
when no_data_found then
null;
end;
end update_parents;
begin
--
-- Update Value Field for current record and Parent records
--
if nvl(:new.value,0) - nvl(:old.value,0) <> 0 then
update t1 set value = value + nvl(:new.value,0) - nvl(:old.value,0)
where id = :new.id;
update_parents(:new.id, nvl(:new.value,0) - nvl(:old.value,0));
end if;
--
-- Update Others Fields
--
update t1 set f1 = :new.f1
,f2 = :new.f2
where id = :new.id;
end;
--
-- Testing
--
-- With this view: t1_v
--
begin
for i in 1..50 loop
Insert into t1_v values(i, i-1, 0, '', '');
end loop;
end;
/
delete from t1_v;
begin
for i in 1..50 loop
Insert into t1_v values(i, i-1, 0, '', '');
end loop;
end;
/
update t1_v set f1 = 'TEST' where id = 49;
update t1_v set value = value + 5 , f1 = 'AA', F2 = 'BB'
where id = 50;
[ Code End ]
使用临时表
11:04:36 SQL> CREATE GLOBAL TEMPORARY TABLE Tmp(
11:04:39 2 rid varchar2(20))ON COMMIT DELETE ROWS;
表已创建。
已用时间: 00: 00: 00.16
11:07:30 SQL> create or replace trigger trg_tb
11:07:36 2 after insert on tb
11:07:36 3 for each row
11:07:36 4 begin
11:07:36 5 if :new.col2='laji' then
11:07:36 6 insert into tmp values(:new.rowid);
11:07:36 7 end if;
11:07:36 8 end trg_tb;
11:07:36 9 /
触发器已创建
已用时间: 00: 00: 00.47
11:07:36 SQL> create or replace trigger trg_tb_del
11:07:44 2 after insert on tb
11:07:46 3 begin
11:07:46 4 delete from tb where exists(select 1 from tmp where rid=tb.rowid);
11:07:46 5 end trg_tb_del;
11:07:46 6 /
触发器已创建
已用时间: 00: 00: 00.31
11:08:59 SQL> select count(1) from tb;
COUNT(1)
----------
22
已用时间: 00: 00: 00.00
11:09:08 SQL> insert into tb(col1,col2) values('aaa','laji');
已创建 1 行。
已用时间: 00: 00: 00.00
11:09:12 SQL> select count(1) from tb;
COUNT(1)
----------
22
已用时间: 00: 00: 00.16
11:09:14 SQL>
From Metalink
Problem Description -------------------
Oracle does not allow you to read a mutating table in a row trigger because if you can read it,
the information may be incorrect (not read consistent). If you attempt this, the following error
is returned:
ORA-04091
Table %s.%s is mutating, trigger/function may not see it
However, you can perform this operation in a statement trigger.
One way to handle this situation is to use a package PL/SQL table to store ROWIDs of updated
records in a row trigger, and reprocess the updated
records in a statement trigger.
Below is an example.
Important Note --------------
Note that there are concurrency issues with this if more than one session tries to perform
operations simultaneously.
This is not intended as a total solution, but as the framework to help show one option.
Example Workaround ------------------
[code:1:50147eb56b]
create or replace package emp_pkg as
type
emp_tab_type is table of rowid index by binary_integer;
emp_tab
emp_tab_type;
emp_index binary_integer;
end emp_pkg;
/
create or replace trigger emp_bef_stm_all
before insert or update or delete on emp
begin
/*
Remember to reset the pl/sql table before each statement
*/
emp_pkg.emp_index := 0;
end;
/
create or replace trigger emp_aft_row_all
after insert or update or delete on emp
for each row
begin
/*
Store the rowid of updated record into global pl/sql table
*/
emp_pkg.emp_index := emp_pkg.emp_index + 1;
emp_pkg.emp_tab(emp_pkg.emp_index) := :new.rowid;
end;
/
create or replace trigger emp_aft_stm_all
after
insert or update or delete on emp
begin
for i in 1 .. emp_pkg.emp_index loop
/*
Re-process the updated records.
There is no restriction here.
*/
dbms_output.put_line(emp_pkg.emp_tab(i));
end loop;
emp_pkg.emp_index := 0;
end;
/
[/code:1:50147eb56b]