-- #############################################################################################
--
-- %Purpose: Solutions for the "Mutation Table Problem" with DELETE CASCADE and Cascade Update
--
-- #############################################################################################
--
-- See https://www.akadia.com/services/ora_mutating_table_problems.html for the whole text.
--
-- Solution: Using a temporary table
--
-- If you need to update a mutating table, then you could use a temporary table,
-- a PL/SQL table, or a package variable to bypass these restrictions. For example,
-- in place of a single AFTER row trigger that updates the original table, resulting in
-- a mutating table error, you may be able to use two triggers - an AFTER row trigger that
-- updates a temporary table, and an AFTER statement trigger that updates the original table
-- with the values from the temporary table.
--
-- In the next example "from the real world", we want to show this. The table CUG can
-- only have records of the following types
--
-- A: Type = 1
-- B: Type = 2 (Leader for C or D)
-- C: Type = 3 (Lead by B)
-- D: Type = 4 (Lead by B)
-- Note, that the types C and D must be lead by the CUG type B. 
--
drop table CUG cascade constraints;
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);

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

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,
     'Project- and Community CUGs must have a leading company');
  end if;
end;
/

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;
/

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;
    dbms_output.put_line('DEBUG: au_s: id_B ' || to_char(l_id_B) || ', type : ' || to_char(l_typeCD));
    select type into l_typeB from CUG
     where id_cug = l_id_B;
     dbms_output.put_line('au_s: type : ' || to_char(l_typeB));
    if (l_typeB != 2) then
       raise_application_error(-20002,
       'Project- and Community CUGs must have a leading company');
    end if;
  end loop;
  close cur_cugtmp;
end;
/

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

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