Re: Mutating Tables and Triggers - Help!

From: Gilbert Schwaab <gilbert_at_butthead.fink.isar.de>
Date: 1996/06/26
Message-ID: <31D14949.7BEE_at_butthead.fink.isar.de>#1/1

Dear Mister Urmann !

We tried your suggestion about to work around the ORA-04091 error message while firing a trigger in the script below:

/////////////////////////////////////////

CREATE TRIGGER UpdateUniqueSendung
before update
of Archivnummer,Standort
on Sendung
referencing new as NewRec
for each row

DECLARE
   count1 NUMBER;
   count2 NUMBER;

begin

IF :NewRec.Standort like '0%' THEN
 select count(*) into count1 from Sendung where Archivnummer=:NewRec.Archivnummer

                                                and Standort like '0%';
IF count1 > 0 THEN raise DUP_VAL_ON_INDEX;         

END IF;
ELSE
select count(*) into count2 from Sendung where Archivnummer=:NewRec.Archivnummer

                                               and Standort not like '0%';

IF count2 > 0 THEN raise DUP_VAL_ON_INDEX;         

END IF;
END IF;
END;

/////////////////////////////////////////

As described in your Book "PL/SQL-Programming" we changed the listing above to the following:

/////////////////////////////////////////

CREATE OR REPLACE PACKAGE SendungData AS   TYPE t_Archivnummer IS TABLE OF Sendung.Archivnummer%TYPE     INDEX BY BINARY_INTEGER;
  TYPE t_Standort IS TABLE OF Sendung.Standort%TYPE     INDEX BY BINARY_INTEGER;
  TYPE t_SendungsID IS TABLE OF Sendung.SendungsID%TYPE     INDEX BY BINARY_INTEGER;

  v_SendungArchivnummer t_Archivnummer;
  v_SendungStandort     t_Standort;
  v_SendungSendungsID   t_SendungsID;
  v_NumEntries          BINARY_INTEGER := 0;

END SendungData;

CREATE OR REPLACE TRIGGER RUpdateUniqueSendung   BEFORE UPDATE
  OF Archivnummer, Standort
  ON Sendung

 FOR EACH ROW
 BEGIN
  /* Record the new data in SendungData. We don't make any

     changes, to avoid the ORA-4091 error. */

  SendungData.v_NumEntries := SendungData.v_NumEntries + 1;

  SendungData.v_SendungArchivnummer(SendungData.v_NumEntries)

:= :new.Archivnummer;

  SendungData.v_SendungStandort(SendungData.v_NumEntries)     


                := :new.Standort;


  SendungData.v_SendungSendungsID(SendungData.v_NumEntries)   


                := :new.SendungsID;

END RUpdateUniqueSendung;

CREATE OR REPLACE TRIGGER SUpdateUniqueSendung   AFTER UPDATE
  OF Archivnummer,Standort
  ON Sendung

DECLARE
   count1 NUMBER;
   count2 NUMBER;

   v_Archivnummer Sendung.Archivnummer%TYPE;
   v_Standort     Sendung.Standort%TYPE;
   v_SendungsID   Sendung.SendungsID%TYPE;

BEGIN
  /* Loop through each Sendung inserted or updated, and verify

     that we are still within the limit. */

FOR v_LoopIndex IN 1..SendungData.v_NumEntries LOOP

    v_SendungsID   := SendungData.v_SendungSendungsID(v_LoopIndex);
    v_Archivnummer := SendungData.v_SendungArchivnummer(v_LoopIndex);
    v_Standort     := SendungData.v_SendungStandort(v_LoopIndex);

   IF v_Standort like '0%' THEN
   select count(*) into count1
   from Sendung
   where Archivnummer = v_Archivnummer

         and Standort like '0%'
         and SendungsID <> v_SendungsID;
   IF count1 > 0 THEN raise DUP_VAL_ON_INDEX;          

   END IF;
   ELSE
   select count(*) into count2
   from Sendung
   where Archivnummer = v_Archivnummer
   and Standort not like '0%'
   and SendungsID <> v_SendungsID;

   IF count2 > 0 THEN raise DUP_VAL_ON_INDEX;    END IF;
   END IF; END LOOP;

• Reset the counter so the next execution will use new data. SendungData.v_NumEntries := 0; END SUpdateUniqueSendung;

//////////////////////////////////////////////////////////

Running those two Triggers affects the ORA-04091 error message no more, but unfortunately the after update - Triggers fires no more at all.

Do you have further suggestions to solve this problem ?

Please write us back as soon as possible !

Thank you !
Gs and pjo Received on Wed Jun 26 1996 - 00:00:00 CEST