Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Mutating table problem with foreign key constraints
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