Home -> Usenet -> comp.databases.oracle -> Re: Mutating Tables

Re: Mutating Tables

From: Reinhard Bergander <100330.2165_at_CompuServe.COM>
Date: 1995/08/01
Message-ID: <3vjqqg$4mv$1_at_mhadf.production.compuserve.com> 







Hi !



Here are the example for my mutating tables problem.



WORDS --1-------n---> WORDS_CUSTOMER <--n--------1--CUSTOMER



The problem is primary the update cascade. I'm using the WORS table
as a dictionary of attributes for the customers, which attributes are saved in
the words_customer table.



e.g.


WORDS:			'GOOD', 'POOR', 'FINE'
WORDS_CUSTOMER:		(1,'GOOD'),(2,'POOR'),(2,'FINE')
CUSTOMER		(1,'Michael'), (2,'Jeanne')





Now I want  a automatic update cascade (through database triggers) when I'm
changing the word POOR to VERY POOR.



This is  a very simple example. In my production databse I'm using nearly 150 tables
and I would need an update cascade in nearly 20 different master tables.



Thanx in advance,




reinhard





    
  
  •   ============================================================
  
  •   Table : WORDS                                             
  
  •   ============================================================
create table WORDS
(
    W_WORD                          VARCHAR2(20)           not null,
    constraint pk_words primary key (W_WORD)
);

  
  •   ============================================================
  
  •   Table : CUSTOMER                                          
  
  •   ============================================================
create table CUSTOMER
(
    CUST_NR                         number(3)              not null,
    CUST_NAME                       varchar2(40)                   ,
    constraint pk_customer primary key (CUST_NR)
);

  
  •   ============================================================
  
  •   Table : WORDS_CUSTOMER                                    
  
  •   ============================================================
create table WORDS_CUSTOMER
(
    CUST_NR                         number(3)              not null,
    W_WORD                          VARCHAR2(20)           not null,
    constraint pk_words_customer primary key (CUST_NR, W_WORD)
);

  
  •   ============================================================
  
  •   TRIGGER DEFINITION

  
  •   ============================================================
--
--
--

  
  •   Update trigger "tu_customer" for table "CUSTOMER"
create trigger tu_customer before update
on CUSTOMER for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    

  
      
    
    •   Declaration of UpdateParentRestrict constraint for "WORDS_CUSTOMER"
    cursor cfk1_customer(var_cust_nr number) is
       select 1
       from   WORDS_CUSTOMER
       where  CUST_NR = var_cust_nr
        and   var_cust_nr is not null;


  
    





begin


    
  
  •   Cannot modify parent code in "CUSTOMER" if children still exist in "WORDS_CUSTOMER"
    if (updating('CUST_NR') and :old.CUST_NR != :new.CUST_NR) then
       open  cfk1_customer(:old.CUST_NR);
       fetch cfk1_customer into dummy;
       found := cfk1_customer%FOUND;
       close cfk1_customer;
       if found then
          errno  := -20005;
          errmsg := 'Referenzen existieren in "WORDS_CUSTOMER". Referenzcode kann in "CUSTOMER" nicht modifiziert werden.';
          raise integrity_error;
       end if;
    end if;


  
      
    
    •   Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/

    
    •   Delete trigger "td_customer" for table "CUSTOMER"
create trigger td_customer before delete
on CUSTOMER for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;


  
    





begin
    


    
  
  •   Delete all children in "WORDS_CUSTOMER"
    delete WORDS_CUSTOMER
    where  CUST_NR = :old.CUST_NR;


  
      
    
    •   Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/

    
    •   Update trigger "tu_words" for table "WORDS"
create trigger tu_words before update
on WORDS for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;


  
    





begin


    
  
  •   Modify parent code of "WORDS" for all children in "WORDS_CUSTOMER"
    if (updating('W_WORD') and :old.W_WORD != :new.W_WORD) then
       update WORDS_CUSTOMER
        set   W_WORD = :new.W_WORD
       where  W_WORD = :old.W_WORD;
    end if;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


  
      
    
    •   Delete trigger "td_words" for table "WORDS"
create trigger td_words before delete
on WORDS for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;


  
    





begin
    


    
  
  •   Delete all children in "WORDS_CUSTOMER"
    delete WORDS_CUSTOMER
    where  W_WORD = :old.W_WORD;


  
      
    
    •   Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/

    
    •   Insert trigger "ti_words_customer" for table "WORDS_CUSTOMER"
create trigger ti_words_customer before insert
on WORDS_CUSTOMER for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    

  
    
  
  •   Declaration of InsertChildParentExist constraint for the parent "CUSTOMER"
    cursor cpk1_words_customer(var_cust_nr number) is
       select 1
       from   CUSTOMER
       where  CUST_NR = var_cust_nr
        and   var_cust_nr is not null
        for update of CUST_NR;
    
  
  •   Declaration of InsertChildParentExist constraint for the parent "WORDS"
    cursor cpk2_words_customer(var_w_word varchar) is
       select 1
       from   WORDS
       where  W_WORD = var_w_word
        and   var_w_word is not null
        for update of W_WORD;






begin
    


    
  
  •   Parent "CUSTOMER" must exist when inserting a child in "WORDS_CUSTOMER"
    if :new.CUST_NR is not null then
       open  cpk1_words_customer(:new.CUST_NR);
       fetch cpk1_words_customer into dummy;
       found := cpk1_words_customer%FOUND;
       close cpk1_words_customer;
       if not found then
          errno  := -20002;
          errmsg := 'Referenz existiert nicht in "CUSTOMER". Es kann kein Datensatz in "WORDS_CUSTOMER" erzeugt werden .';
          raise integrity_error;
       end if;
    end if;
    
  
  •   Parent "WORDS" must exist when inserting a child in "WORDS_CUSTOMER"
    if :new.W_WORD is not null then
       open  cpk2_words_customer(:new.W_WORD);
       fetch cpk2_words_customer into dummy;
       found := cpk2_words_customer%FOUND;
       close cpk2_words_customer;
       if not found then
          errno  := -20002;
          errmsg := 'Referenz existiert nicht in "WORDS". Es kann kein Datensatz in "WORDS_CUSTOMER" erzeugt werden .';
          raise integrity_error;
       end if;
    end if;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


  
      
    
    •   Update trigger "tu_words_customer" for table "WORDS_CUSTOMER"
create trigger tu_words_customer before update
on WORDS_CUSTOMER for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    

  
    
  
  •   Declaration of UpdateChildParentExist constraint for the parent "CUSTOMER"
    cursor cpk1_words_customer(var_cust_nr number) is
       select 1
       from   CUSTOMER
       where  CUST_NR = var_cust_nr
        and   var_cust_nr is not null
        for update of CUST_NR;
    
  
  •   Declaration of UpdateChildParentExist constraint for the parent "WORDS"
    cursor cpk2_words_customer(var_w_word varchar) is
       select 1
       from   WORDS
       where  W_WORD = var_w_word
        and   var_w_word is not null
        for update of W_WORD;






begin


    
  
  •   Parent "CUSTOMER" must exist when updating a child in "WORDS_CUSTOMER"
    if :new.CUST_NR is not null then
       open  cpk1_words_customer(:new.CUST_NR);
       fetch cpk1_words_customer into dummy;
       found := cpk1_words_customer%FOUND;
       close cpk1_words_customer;
       if not found then
          errno  := -20003;
          errmsg := '"CUSTOMER" existiert nicht. Kann Datensatz in "WORDS_CUSTOMER"  nicht modifizieren.';
          raise integrity_error;
       end if;
    end if;
    
  
  •   Parent "WORDS" must exist when updating a child in "WORDS_CUSTOMER"
    if :new.W_WORD is not null then
       open  cpk2_words_customer(:new.W_WORD);
       fetch cpk2_words_customer into dummy;
       found := cpk2_words_customer%FOUND;
       close cpk2_words_customer;
       if not found then
          errno  := -20003;
          errmsg := '"WORDS" existiert nicht. Kann Datensatz in "WORDS_CUSTOMER"  nicht modifizieren.';
          raise integrity_error;
       end if;
    end if;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/






-- 
-------------------------------------------------
Reinhard Bergander 100330.2165_at_compuserve.com
KLINIK-OP DATA Software GmbH
-------------------------------------------------


Received on Tue Aug 01 1995 - 00:00:00 CEST












Original text of this message