能否在表的触发器中当一记录变化修改另一记录

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]