ERROR WHEN EXECUTING TRIGGER

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:37:23 CST