Re: mutating tables --- second try ----
Date: 1996/10/25
Message-ID: <327025dc.4975324_at_dcsun4>
First, don't check for a primary key violation in a trigger, that would be slow. Use a primary key (or unique) constraint. That will be fast and it will work. The trigger solution won't work since if two users do the same thing at the same time, both of them will find no matching rows in the database and let their inserts go through. Then you'll have dups. You need to let the database do it's job on this one. That way, you'll avoid the mutating table all together.
As for disallowing updates to the primary key you can:
SQL> grant update (dname,loc) on dept to public;
Grant succeeded.
SQL>
(eg: grant update on specific columns, not the whole table) or you can:
1 create or replace trigger xxx
2 after update of deptno on dept
3 for each row
4 begin
5 if ( :new.deptno <> :old.deptno ) then 6 raise_application_error( -20000, 'No, you can''t do that' ); 7 end if;
8* end;
SQL> / Trigger created.
SQL> this trigger fires only when the primary key has been updated and checks to see if the new value for it differs from the old....
On 22 Oct 1996 16:24:26 GMT, arkusa_at_t-online.de (Arkusa GbR) wrote:
>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/
> \/
> -----------------------------------------------------------
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Oct 25 1996 - 00:00:00 CEST