Triggers and Foreign Key definitions ...

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 - 15:26:32 CST