Re: Mutating table problem with foreign key constraints

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 20 Jun 1999 14:09:55 GMT
Message-ID: <3771f5a1.2917415@newshost.us.oracle.com>

A copy of this was sent to dbaoracle_at_aol.com (DBAOracle) (if that email address didn't require changing) On 20 Jun 1999 04:58:05 GMT, you wrote:

>This solution will only work with Oracle 7.3. If an earlier version the global
>PL/SQL tables you are setting up wont work. Then you have to use some sort of
>result set table and process from that.
>
>Robert

Don't tell my 7.1.6 database that -- it might stop doing it :)

Maybe you meant "the .count attribute of tables is available starting with 7.3, before that you will have to keep track of it yourself". Here is the same code maintaining the cnt of elements in the table that runs in 7.0 on up.

btw: your postings are somewhat hard to read as you never include any of the previous post -- very very hard to figure out exactly what it is you are talking about. You might consider including a little of the previous post in your for a little context.

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>
SQL> create table environments( env_code int, env_mandatory_ind char(1) );

Table created.

SQL>
SQL> insert into environments values ( 1, 'Y' );

1 row created.

SQL> insert into environments values ( 2, 'Y' );

1 row created.

SQL> insert into environments values ( 3, 'N' );

1 row created.

SQL>
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;
  7      cnt          number;

  8 end;
  9 /

Package created.

SQL>
SQL> create or replace trigger app_bi
  2 before insert on applications
  3 begin

  4     state_pkg.app_code := state_pkg.empty;
  5     state_pkg.cnt := 0;

  6 end;
  7 /

Trigger created.

SQL>
SQL> create or replace trigger app_a_i_r   2 after insert on applications
  3 for each row
  4 begin

  5      state_pkg.cnt := state_pkg.cnt+1;
  6      state_pkg.app_code( state_pkg.cnt ) := :new.app_code;

  7 end;
  8 /

Trigger created.

SQL>
SQL> create or replace trigger app_ai
  2 after insert on applications
  3 begin

  4      for i in 1 .. state_pkg.cnt 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;

 11 end;
 12 /

Trigger created.

SQL>
SQL> insert into applications values ( 1 );

1 row created.

SQL> 
SQL> 
SQL> select * from application_instances;

AI_APP_CODE AI_ENV_CODE
----------- -----------

          1           1
          1           2

SQL>
SQL> select * from v$version;

BANNER

---

Oracle7 Server Release 7.1.6.2.0 - Production Release PL/SQL Release 2.1.6.2.0 - Production
CORE Version 2.3.7.1.0 - Production (LSF Alpha) TNS for SVR4: Version 2.1.6.0.0 - Production NLSRTL Version 2.3.6.0.0 - Production

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 20 1999 - 09:09:55 CDT