Re: Triggers & Referencial integrity constraints

Antonio Galdo wrote:
>
> 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) ;

There is no need nor is it desirable to enforce referential integrity with triggers in Oracle.
Try declaritive statements when creating the table, or alter table commands. Other constraints (like check constraints) can be used for many business rules. Reserve triggers for very complex, procedural constraints or rules. Then keep them very short and call packages.

-- 


Robt

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^~~~~....
Robert Miller         

Oracle Master DBA  
Inovative Information Systems Inc. 

robt.miller_at_airmail.net
(214) 532.6558

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^~~~~....
Any opinions expressed are my own and do not necessarily
represent any employer.