Re: mutating table.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 11 May 1999 17:56:39 GMT
Message-ID: <373a60d3.17327615_at_192.86.155.100>

A copy of this was sent to "Dave Akin" <dakin_at_chis.com> (if that email address didn't require changing) On Tue, 11 May 1999 09:31:16 -0700, you wrote:

>Within an "after update row " trigger I need to get a count of records
>with in that table that are associated via a foriegn key. I've tried the
>following code
>
>select count(*) into x_cnt from table_name where foriegn_key_value =
>:new.foriegn_key_value.
>
>update referenced_table set table_name_cnt = x_cnt.
>
>I am getting a mutating table error (ora-04091).
>
>this is because I am trying to select from record count from the table the
>the trigger is on.
>
>can someone suggest a solution?
>

In a multi-user environment, this wouldn't work even if you didn't have the mutating table error. We need to do a little more work to ensure that the counts are correct else you will find that 2 people updating rows can come up with different -- incorrect answers.

We can get around the multi-user problem and the mutating table error as follows:

SQL> create table dept as select * from scott.dept;
SQL> alter table dept add constraint dept_pk primary key(deptno);
SQL> create table emp as select * from scott.emp;
SQL> alter table emp add constraint emp_pk primary key(empno);
SQL> alter table emp add constraint emp_fk_to_dept foreign key (deptno)

  2 references dept(deptno);

SQL> alter table dept add emp_cnt number; SQL> update dept
  2 set emp_cnt = ( select count(*) from emp where emp.deptno = dept.deptno )   3 /

4 rows updated.

so, those are the tables we will use. we'll maintain a dept count in the dept table.

SQL> create or replace package state_pkg   2 as
  3 type deptnoArray is table of emp.deptno%type index by binary_integer;   4

  4      changed    deptnoArray;
  5      empty    deptnoArray;
  6  
  6      function is_in_table( p_deptno in number ) return boolean;

  7 end;
  8 /

Package created.

SQL> create or replace package body state_pkg   2 as
  3
  3 function is_in_table( p_deptno in number ) return boolean   4 is
  5 begin

  6      for i in 1 .. changed.count loop
  7          if ( changed(i) = p_deptno ) then
  8              return TRUE;
  9          end if;
 10      end loop;
 11      return FALSE;

 12 end;
 13
 13 end;
 14 /
Package body created.

The above package is used to maintain a state. We'll use 3 triggers to defer processing of parent table until after the insert/update/delete happens. To do this, we'll remember the deptno's that have been affected during this session.

SQL> create or replace trigger emp_biud
  2 before insert or update or delete on emp   3 begin
  4 state_pkg.changed := state_pkg.empty;   5 end;
  6 /

Trigger created.

This trigger just resets our state to a clean state before we begin. this is mandatory in the event the row level trigger fails (leaving the plsql table partially full) or the DML fails (again, leaving the table full from the last statement)

SQL> create or replace trigger emp_biud_fer   2 before insert or update of deptno or delete on emp   3 for each row
  4 declare
  5 l_lock_id number;
  6 begin

  7      if ( inserting or updating ) And NOT state_pkg.is_in_table(:new.deptno)
  8      then
  9          l_lock_id :=
 10              dbms_utility.get_hash_value( to_char( :new.deptno ), 0, 1024 );
 11          if ( dbms_lock.request(  id                => l_lock_id,
 12                                   lockmode          => dbms_lock.x_mode,
 13                                   release_on_commit => TRUE ) = 1 )
 14          then
 15               raise_application_error(-20001, 'Bad status from dbms_lock' );
 16          end if;
 17          state_pkg.changed(state_pkg.changed.count+1) := :new.deptno;
 18      end if;
 19  
 19      if ( updating or deleting ) And NOT state_pkg.is_in_table(:old.deptno)
 20      then
 21          l_lock_id :=
 22              dbms_utility.get_hash_value( to_char( :old.deptno ), 0, 1024 );
 23          if ( dbms_lock.request(  id                => l_lock_id,
 24                                   lockmode          => dbms_lock.x_mode,
 25                                   release_on_commit => TRUE ) = 1 )
 26          then
 27               raise_application_error(-20001, 'Bad status from dbms_lock' );
 28          end if;
 29          state_pkg.changed(state_pkg.changed.count+1) := :old.deptno;
 30      end if;

 31 end;
 32 /
Trigger created.

This trigger does 2 things

• it remembers the :old and or :new.deptnos that have been affected. On insert or update of deptno, it remembers :new. On update of deptno or delete, it remembers :old. It only remembers a deptno once per DML operation
• it uses dbms_lock to generate a lock based on the deptno being affected. This allows us to serialize changes at the deptno level. Only one person can concurrently insert into the department. Only one person can delete from the department. Only one person can update the department column for a given departmet. So, >1 person can insert concurrently -- if they insert emps into different departments. >1 person can delete -- if they delete from different depts. And so on.

SQL> create or replace trigger emp_aiud
  2 after insert or update or delete on emp   3 begin

  4      for i in 1 .. state_pkg.changed.count
  5      loop
  6          update dept set emp_cnt = ( select count(*)
  7                                        from emp
  8                                       where deptno = state_pkg.changed(i) )
  9           where deptno = state_pkg.changed(i);
 10      end loop;

 11 end;
 12 /
Trigger created.

Now, the after trigger can go through and make the changes to the parent table.

To see it working:

SQL> select * from dept;

    DEPTNO DNAME LOC EMP_CNT ---------- -------------- ------------- ----------

        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          RESTON                 6
        40 OPERATIONS     BOSTON                 0

SQL>
SQL> update emp
  2 set deptno = decode( deptno, 10, 20, 20, 30, 30, 40, 40, 10 )   3 /

14 rows updated.

we've changed every department -- 10 to 20, 20 to 30, 30 to 40 and 40 to 10.

SQL>
SQL> select * from dept;

    DEPTNO DNAME LOC EMP_CNT ---------- -------------- ------------- ----------

        10 ACCOUNTING     NEW YORK               0
        20 RESEARCH       DALLAS                 3
        30 SALES          RESTON                 5
        40 OPERATIONS     BOSTON                 6

SQL>
SQL> delete from emp where deptno = 40 and rownum = 1   2 /

1 row deleted.

SQL> select * from dept;

    DEPTNO DNAME LOC EMP_CNT ---------- -------------- ------------- ----------

        10 ACCOUNTING     NEW YORK               0
        20 RESEARCH       DALLAS                 3
        30 SALES          RESTON                 5
        40 OPERATIONS     BOSTON                 5

SQL>
SQL> insert into emp ( empno, deptno, sal ) values ( 123, 40, 0 );

1 row created.

SQL> select * from dept;

    DEPTNO DNAME LOC EMP_CNT ---------- -------------- ------------- ----------

        10 ACCOUNTING     NEW YORK               0
        20 RESEARCH       DALLAS                 3
        30 SALES          RESTON                 5
        40 OPERATIONS     BOSTON                 6

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
[Quoted] Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Tue May 11 1999 - 19:56:39 CEST