Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Mutating table problem with foreign key constraints

Re: Mutating table problem with foreign key constraints

From: Marc Mazerolle <informaze_at_sympatico.ca>
Date: Wed, 16 Jun 1999 19:34:43 GMT
Message-ID: <3767FDB1.DC5A0541@sympatico.ca>


Typical.... With Oracle8, i was told you could differ the constraint checking. This might solve your problem. If using Oracle7, it's a lot more complicated to fix....

Regards,

Marc Mazerolle
InforMaze Inc.

Jim Smith wrote:

> I have an insert trigger (see below) which attempts to insert rows into
> a table which has a foreign key referencing the table which has the
> trigger. If I make this a before insert trigger it fails with a RI
> constraint violation, presumably because the primary key hasn't yet been
> inserted into the trigger table. As an after trigger, it fails with the
> mutating table error, presumably because a select on the primary table
> is necessary to check the foreign key constraint.
>
> It the above is true, how is it possible to implement a situation where
> a master record and its mandatory detail records are created at the same
> time?
>
> If the above isn't true, what am I doing wrong?
>
> here is the trigger. I have written it in various ways, but they all
> fail with the same error.
>
> create or replace trigger app_a_i_r
> after insert
> on applications
> for each row
>
> begin
>
> declare
>
> cursor env_cursor is
> select env_code
> from environments
> where env_mandatory_ind = 'Y';
>
>
> begin
> for env_rec in env_cursor loop
> dbms_output.put_line('new.app_code is '||:new.app_code);
>
> dbms_output.put_line('env_code is '||env_rec.env_code);
>
> insert into application_instances
> ( ai_app_code, ai_env_code)
> values (:new.app_code, env_rec.env_code);
>
>
> end loop;
>
> end;
> end;
>
> /
> --
> Jim Smith
Received on Wed Jun 16 1999 - 14:34:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US