Mutating table problem with foreign key constraints

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 - 13:43:08 CDT