Triggers & Referencial integrity constraints

Hi everyone,

I'm having some problems with triggers and referencial integrity constraints in the tables which fire and are modified by the triggers.

Here's my problem:

• There are two tables : A and B.
• Table B has a referencial integrity constraint with table A. (table B has a foreign key from table A).
• Table A has a trigger wich inserts rows in table B.
• As you would have guessed the error ORACLE displays when the trigger is fired is :

                ORA-04091: table A is mutating, trigger/function may not see it

I think this error is raised because the insertion in table B requires checking table A for integrity. When this check is done, table A is beeing altered by the UPDATE which fired the trigger, i.e., table A is "mutating".

Is there a way to solve this problem ?

Please reply to agaldo_at_repsol.es

Best regards,

  Antonio Galdo

Here's the code i've used:

CREATE OR REPLACE TRIGGER hist_trigger
AFTER
UPDATE
ON table_A
FOR EACH ROW
BEGIN INSERT INTO table_B

          (CODE,YEAR,ADATE)
 VALUES
         (:old.CODE,:old.YEAR,:old.ADATE);  

END; create table table_A (
CODE VARCHAR2(6)
,

YEAR NUMBER(4)
,

ADATE DATE
 , constraint pk_table_A primary key
 (CODE,YEAR)
 );  

create table table_B (
CODE VARCHAR2(6)
,

YEAR NUMBER(4)
,

ADATE DATE
 , constraint pk_table_B primary key
 (CODE,YEAR)
 );

alter tabletable_B add constraint fk1_table_B foreign key (CODE,YEAR) references table_A(CODE,YEAR) ; Received on Wed Apr 09 1997 - 00:00:00 CDT