| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Mutating table problem with foreign key constraints
A copy of this was sent to Jim Smith <jim_at_jimsmith.demon.co.uk>
(if that email address didn't require changing)
On Wed, 16 Jun 1999 19:43:08 +0100, you 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.
>
we have to postpone writing to the child table until the after trigger (not the after, for each row). we do that by:
SQL> create table applications( app_code int primary key ); Table created.
SQL> create table application_instances( ai_app_code int references applications,
2 ai_env_code int );Table created.
SQL> create table environments( env_code int, env_mandatory_ind char(1) ); Table created.
SQL> insert into environments values ( 1, 'Y' ); SQL> insert into environments values ( 2, 'Y' ); SQL> insert into environments values ( 3, 'N' );
SQL> create or replace package state_pkg 2 as
3 type arrayType is table of applications.app_code%type index by 4 binary_integer; 5 5 app_code arrayType; 6 empty arrayType;
SQL> create or replace trigger app_bi
2 before insert on applications
3 begin
4 state_pkg.app_code := state_pkg.empty;
5 end;
6 /
Trigger created.
SQL> create or replace trigger app_a_i_r
2 after insert on applications
3 for each row
4 begin
5 state_pkg.app_code( state_pkg.app_code.count+1 ) := :new.app_code;
6 end;
7 /
Trigger created.
SQL> create or replace trigger app_ai
2 after insert on applications
3 begin
4 for i in 1 .. state_pkg.app_code.count loop 5 insert into application_instances 6 ( ai_app_code, ai_env_code ) 7 select state_pkg.app_code(i), env_code 8 from environments 9 where env_mandatory_ind = 'Y'; 10 end loop;
SQL> insert into applications values ( 1 );
1 row created.
SQL> select * from application_instances;
AI_APP_CODE AI_ENV_CODE
----------- -----------
1 1
1 2
>
>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;
>
>/
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |