Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ERROR WHEN EXECUTING TRIGGER

Re: ERROR WHEN EXECUTING TRIGGER

From: damorgan <dan.morgan_at_ci.seattle.wa.us>
Date: Mon, 18 Feb 2002 17:40:24 GMT
Message-ID: <3C713C94.B4A7E97C@ci.seattle.wa.us>


Go to http://technet.oracle.com. Click on search. Enter the search criterion "Mutating Trigger".

Or ... upgrade to Oracle 8.1.7. You can get it from http://store.oracle.com for about $39 US.

Daniel Morgan

auxitec e2ia wrote:

> I work on Oracle personal 7.2
>
> CREATE TABLE BP.RELAIS
> (
> NUM_AFF VARCHAR2(8),
> NOM_RELAI VARCHAR2(10),
> FONCTION NUMBER(8, 0)
> ) TABLESPACE C_BP;
>
> ALTER TABLE BP.RELAIS
> MODIFY NUM_AFF CHAR(8)
> CONSTRAINT NN_RELAISNUM_AFF NOT NULL;
>
> ALTER TABLE BP.RELAIS
> MODIFY NOM_RELAI CHAR(10)
> CONSTRAINT NN_RELAISNOM_RELAI NOT NULL;
>
> ALTER TABLE BP.RELAIS ADD CONSTRAINT
> PK_RELAIS PRIMARY KEY
> (NUM_AFF,
> NOM_RELAI
> ) USING INDEX TABLESPACE C_BP;
>
> CREATE TABLE BP.CONTACTS (NUM_AFF VARCHAR2(8),
> NOM_RELAIS VARCHAR2(10),
> NOM_CONTACT VARCHAR2(10),
> TYPE NUMBER(1, 0),
> LIB VARCHAR2(50)) TABLESPACE C_BP
> /
> ALTER TABLE BP.CONTACTS ADD CONSTRAINT PK_CONTACTS PRIMARY KEY
> (NUM_AFF,NOM_RELAIS,NOM_CONTACT) USING INDEX TABLESPACE C_BP
> /
> ALTER TABLE BP.CONTACTS ADD CONSTRAINT FK_CONTACTS FOREIGN KEY
> (NUM_AFF,NOM_RELAIS) REFERENCES BP.RELAIS (NUM_AFF,NOM_RELAI);
> /
> CREATE TRIGGER BP.CONTACTS
> AFTER
> INSERT
> ON BP.RELAIS
> FOR EACH ROW
> BEGIN
> insert into BP.CONTACTS (NUM_AFF,NOM_RELAIS,NOM_CONTACT)
> values (:new.NUM_AFF,:new.NOM_RELAI,'C01');
> END;
> /
>
> THE PROBLEME IS :
> - if the contraint FK_CONTACTS is create and not the trigger = ok, the
> constraint work.
> - if the trigger is create and not the contraint FK_CONTACTS = ok, the
> trigger work.
> - But if the trigger and the contraint FK_CONTACTS are create = the
> constraint work but not the trigger !!! I DON'T know why.
>
> ERROR at line 1:
> ORA-04091: table BP.RELAIS is mutating, trigger/function may not see it
> ORA-06512: at line 2
> ORA-04088: error during execution of trigger 'BP.CONTACTS'
>
> Thanks,
>
> Flavien
Received on Mon Feb 18 2002 - 11:40:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US