Re: Triggers and Foreign Key definitions ...

broker2000_at_my-dejanews.com wrote:

> Hi Oracle gurus:
>
> I am trying to create a trigger. To explain my problem I have created a
> simplified the table definitions. What I am trying to do is insert rows into
> table B whenever any rows are inserted into table A. The definitions of the
> table are as follows :
>
> CREATE TABLE A (
> A NUMBER,
> PRIMARY KEY (A)
> );
>
> CREATE TABLE B (
> B NUMBER,
> A NUMBER,
> PRIMARY KEY (A),
> FOREIGN KEY (A) REFERENCES A(A)
> );
>
> CREATE OR REPLACE TRIGGER A_CREATE
> AFTER INSERT ON A
> REFERENCING OLD AS OLD NEW AS NEW
> FOR EACH ROW
> BEGIN
> INSERT INTO B (A) VALUES (:NEW.A);
> END;
>
> Now when I try to insert rows into A I get an error "ORA04091 table A is
> mutating, trigger/function may not see it." Now if I eliminate the FOREIGN KEY
> definition in the definition of table B, that eliminated the error. It looks
> like when the trigger tries to insert a row into B, it looks up rows in A to
> ensure referential integrity but A is still in the midst of inserting records.
> So I get an error.
>
> Is there any way to get circumvent this problem other than to eliminate the
> FOREIGN KEY definition? I am not in a position to combine the table A and B
> even though they have the same primary key. Any pointers you can provide is
> appreciated. Thanks and have a nice day.
>
> --
> Sincerely,
> Broker2000
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

  I think they call this the 'Law of the Mutating Table'. It is explicitly forbidden for a trigger to alter a table connected to the table causing the trigger to fire. The table that caused the trigger to fire is called the mutating table, the connected table is called the constaining table. Also, your trigger may not even read the mutating table. Received on Wed Nov 11 1998 - 21:57:54 CST