Re: Table with trigger mutation

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Mon, 7 Dec 98 21:37:39 +0200
Message-ID: <AB3w2RsK42@protasov.kiev.ua>

Hi,

You can try usual workaround for mutating table error - PL/SQL tables and after (not for each row) triggers:

SVRMGR> create table obj
     2> (id integer
     3> ,constraint obj_pk primary key(id)
     4> );

Statement processed.

SVRMGR> create table org
     2> (id integer
     3> ,constraint org_pk primary key(id)
     4> ,constraint org_fk foreign key(id) references obj(id)
     5> );

Statement processed.

SVRMGR> create or replace package id_table as
     2>  type Tid_table is table of integer index by binary_integer;
     3>  org_id_table Tid_table;
     4>  procedure del(t in out Tid_table);
     5>  procedure ins(t in out Tid_table,i integer);
     6> end;
     7> /

Statement processed.

SVRMGR> create or replace package body id_table as
     2>  procedure del(t in out Tid_table) is
     3>  begin
     4>   t.delete;
     5>  end;
     6>  procedure ins(t in out Tid_table,i integer) is
     7>  begin
     8>   t(t.count+1):=i;
     9>  end;
    10> end;
    11> /

Statement processed.

SVRMGR> create or replace trigger org_ins
     2> before insert on org
     3> for each row
     4> begin
     5>  insert into obj(id) values(:new.id);
     6> end;
     7> /

Statement processed.

SVRMGR> create or replace trigger org_del1
     2> before delete on org
     3> begin
     4>  id_table.del(id_table.org_id_table);
     5> end;
     6> /

Statement processed.

SVRMGR> create or replace trigger org_del2
     2> after delete on org
     3> for each row
     4> begin
     5>  id_table.ins(id_table.org_id_table,:old.id);
     6> end;
     7> /

Statement processed.

SVRMGR> create or replace trigger org_del3
     2> after delete on org
     3> begin
     4>  for i in 1..id_table.org_id_table.count loop
     5>   delete from obj where id=i;
     6>  end loop;
     7> end;
     8> /

Statement processed.
SVRMGR> insert into org(id) values(1);
1 row processed.
SVRMGR> select * from obj;
ID

---

         1
1 row selected.
SVRMGR> delete from org where id=1;
1 row processed.
SVRMGR> select * from obj;
ID

---

0 rows selected.
SVRMGR> commit;
Statement processed.

Andrew Protasov

> It was, in my case, relation, that in ER-diagrams has name "Incomplete
> subtype cluster". I need in table Obj marks about not only Org
> (Organization, #Org_ID), but copies of rows from another tables (for example
> Product(#Prod_ID)). For that I use same sequence for Product and Org primary
> key fields and triggers for both tables that insert rows in Obj table by
> insertion of rows in Product and Org. And I need backward action - by
> deletion from Product or Org I need to delete row from Obj. Both tables (Org
> and Product) references to table Obj. Insert and Update triggers work
> normally. Any suggestions? "On delete cascade" clause works in Master-Detail
> direction, I need backward action (Detail-Master).
>
> Valentine
Received on Mon Dec 07 1998 - 13:37:39 CST