KiranKW

 Profile Answers by KiranKW 

• May 2nd, 2007

 

A mutating error comes when u r trying to do some DML operation on a table that owns the trigger.... there are a lot of options to avoid mutation .. eg, Making it autonomous transaction or making it a statement level trigger ... or using temporary table... the following example gives how to use temporary pl/sql table to avoid mutation error :

        
create table CUG (
  id_cug      number(12) not null primary key,
  id_B        number(12) not null,
  type        number(1),
foreign key (id_B) references CUG (id_cug)
on delete cascade);

Next we create a temporary table to avoid the "Mutating Table Problem".

drop table CUGTMP;
create global temporary table CUGTMP (
  id_B        number(12),
  type        number(1))
on commit delete rows;

The following trigger checks new rows (Inserts) in CUG

create or replace trigger bi_r
before insert on CUG
for each row
declare
  l_type     CUG.type%type;
begin
  if (:new.type in (3,4)) then
    select type into l_type from CUG
     where id_cug = :new.id_B;
  end if;
  if (l_type != 2) then
     raise_application_error(-20002,
     'C and D CUGs must have a leading B');
  end if;
end;
/

The following Trigger saves the new values for id_B in the temporary table.

create or replace trigger au_r
after update of id_B on CUG
for each row
begin
  insert into CUGTMP (id_B,type)
  values (:new.id_B,:new.type);
end;
/

The following Trigger finally checks, that C and D CUGs belong to a B CUG.

create or replace trigger au_s
after update of id_B on CUG
declare
  l_id_B        number(12);
  l_typeCD      number(1);
  l_typeB       number(1);
  cursor cur_cugtmp is
  select id_B,type
   from CUGTMP;
begin
  open cur_cugtmp;
  loop
    fetch cur_cugtmp into l_id_B,l_typeCD;
    exit when cur_cugtmp%notfound;
     select type into l_typeB from CUG
      where id_cug = l_id_B;
    if (l_typeB != 2) then
       raise_application_error(-20002,
       'C and D CUGs must have a leading B');
    end if;
  end loop;
  close cur_cugtmp;
end;
/

Test insert and update

insert into CUG (id_cug,id_B,type)
  values (0,0,0);
insert into CUG (id_cug,id_B,type)
  values (1,0,2);
insert into CUG (id_cug,id_B,type)
  values (2,0,2);
insert into CUG (id_cug,id_B,type)
  values (3,1,3);
insert into CUG (id_cug,id_B,type)
  values (4,2,3);
insert into CUG (id_cug,id_B,type)
  values (5,1,4);
insert into CUG (id_cug,id_B,type)
  values (6,2,4);
commit;

SQL> select * from CUG;

    ID_CUG       ID_B       TYPE
---------- ---------- ----------
         0          0          0
         1          0          2
         2          0          2
         3          1          3
         4          2          3
         5          1          4
         6          2          4

Now, we want that that the CUGs 3,4,5,6 changes the leadership to CUG 2

SQL> update CUG set id_B = 2 where id_cug in (3,4,5,6);

4 rows updated.

SQL> select * from cug;

    ID_CUG       ID_B       TYPE
---------- ---------- ----------
         0          0          0
         1          0          2
         2          0          2
         3          2          3
         4          2          3
         5          2          4
         6          2          4

Next we delete the "Leader" with ID_CUG = 2. All childs must be deleted automatically with the DELETE CASCADE.

SQL> select * from cug;

    ID_CUG       ID_B       TYPE
---------- ---------- ----------
         0          0          0
         1          0          2

Everything looks fine - cool isn't it ?