Re: Triggers and Foreign Key definitions ...

        There is a way to do this, but it is a hack. Here's how I'd do it. Follow along!

1. Create a package with a package variable that is an array of numbers.
2. In the "before each row" trigger, append :NEW.A to the array.
3. Create an "after" statement trigger which inserts the contents of the array into table B.

        You may need to have some counter variables and such to do this, but the key is that package variables maintain their scope between triggers. Any questions?

Ken

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
Received on Wed Nov 11 1998 - 20:51:34 CST