mutating tables

From: Arkusa GbR <arkusa_at_t-online.de>
Date: 1996/10/21
Message-ID: <54gok3$jce_at_news00.btx.dtag.de>#1/1

SOS !! I think the experts from you need only a short view to my problem to see what is going wrong. I learned from statements in the news group, that it is a good way to write own check-procedures to be able to handle special problems. But this runs me into conflicts.
When I try to update SAMPLE (even when I use attributes which are not in the primary key) I get a mutating table error, because the updated table is accessed using a select-statement to find out whether the updated tuple violates the prinmary key relation.
In the oracle docu I found, that I should not access the table which is responsable for the mutating problem. But I have to check the primary key!!! Another suggestion from the docu is to use a package variable, a PL/SQL-Table or a temporary table.

Does anyone have a small sample for this approach? Or does anyone find a solution to my problem without changing the way to use own procedures?

Another question: How can I find out, which attributes are used in the 'set-term' of an update-statement? Would this be a way to allow only updates to tuples in the database which do not change the primary key? Then I could leave thios check out.

Thanks for any help (and sorry for the specialists in this group for this question)

Manfred Tischendorf

It follows the code in which the mutating error occurs.  

---

CREATE TABLE SAMPLE (

 GNR     NUMBER(1),
 SEITE   NUMBER(1),
 HSW     NUMBER(3),
 VSW     NUMBER(2),

 ZTYP CHAR(1),
 WTYP CHAR(1),

 FACHH   NUMBER(4),
 PSTAT   NUMBER(1),
 ISTAT   NUMBER(1),
 UTNR    CHAR(14), 
 ANR     CHAR(6),
 GEW     NUMBER(6,1),

 NULLD DATE)
 TABLESPACE TS_LVS001; CREATE OR REPLACE PROCEDURE SAMPLE_CHECK_PK (lo_gnr IN NUMBER, lo_seite IN NUMBER,  lo_hsw IN NUMBER, lo_vsw IN NUMBER) AS
/* checks Table SAMPLE for primary key */
lo_var NUMBER;
BEGIN
  Select GNR into lo_var from sample where GNR = lo_gnr and SEITE = lo_seite and HSW = lo_hsw and VSW = lo_vsw;
  IF SQL%ROWCOUNT >= 1 THEN
    RAISE_APPLICATION_ERROR (-20101,'double primary key');   END IF;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
     NULL; /* manages empty table */
END SAMPLE_CHECK_PK;
/

CREATE OR REPLACE PROCEDURE SAMPLE_CHECK_FK_PSTM (lo_utnr IN CHAR) AS
/* checks Table SAMPLE - Table Second for foreign key */
lo_var CHAR(14);
BEGIN
  Select UTNR into lo_var from Second where UTNR = lo_utnr;   EXCEPTION
    WHEN NO_DATA_FOUND THEN /* handles emtpy table and foreign key not found */     RAISE_APPLICATION_ERROR
      (-20101,'foreign key error (table second)'); END SAMPLE_CHECK_FK_PSTM;
/

CREATE OR REPLACE TRIGGER TRG_SAMPLE_B_INSUPD   BEFORE INSERT OR UPDATE ON SAMPLE
    FOR EACH ROW
  BEGIN
    SAMPLE_CHECK_PK(:new.GNR,:new.SEITE,:new.HSW,:new.VSW);     SAMPLE_CHECK_FK_PSTM(:new.UTNR);

    SANMPLE_CHECK_ALL_ATTR(:new.GNR, :new.SEITE, :new.HSW, :new.VSW, :new.ZTYP,                    
                    :new.WTYP, :new.FACHH, :new.GEW, :new.PSTAT, :new.ISTAT);

  END;
/

The error occurs in the PROCEDURE SAMPLE_CHECK_PK in line 6 when the select-statement should be executed.

If it is possible, please email me, because I do not read the "comp.databases.oracle" daily and the refresh is very fast in this group.

---

      /\
     / a\       Manfred Tischendorf
    / s  \
   / u    \     Bruehlstr. 2
  / k      \    D 74379 Ingersheim
 / r        \   Deutschland / Germany
 \a         /
  \        /    Tel.:    +49 7142 9800-27 / +49 171 6821030
   \      /     Fax:     +49 7142 9800-29
    \____/      E-Mail:  tischendorf_at_arkusa.de
     \  /       WWW:     http://www.arkusa.de/
      \/
       -----------------------------------------------------------

Received on Mon Oct 21 1996 - 00:00:00 CEST