mutating tables --- second try ----
Date: 1996/10/22
Message-ID: <54isfq$9eq_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 Tue Oct 22 1996 - 00:00:00 CEST