Re: Oracle trigger HHHEEELLLPP !

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 22 Jan 1999 14:21:55 GMT
Message-ID: <36aa886b.82281374@192.86.155.100>

A copy of this was sent to apmramos_at_mail.telepac.pt (Pedro Ramos) (if that email address didn't require changing) On Fri, 22 Jan 1999 12:24:21 GMT, you wrote:

>Hello all,
>
>I have an INSERT trigger on a table that has about 100 columns, when a
>row is inserted in the table (TABLEA) I need to insert the same row in
>another table (TABLEB).
>I don't want to use column names for the insertion, I want to use
>something of the kind :
>
>INSERT INTO TABLEB SELECT * FROM :NEW
>
>or
>
>INSERT INTO TABLEB SELECT * FROM TABLEA WHERE KEY=:NEW.KEY
>
>But neither of these work, can you give me some help !
>
>
>Pedro Ramos
>
>PS - The trigger is an after trigger.
>I don't want to do something like :
>
>INSERT INTO TABLEB (COL1,COL2,COL3,....) VALUES
>(:NEW.COL1,:NEW.COL2,:NEW.COL2,...)
>
>

the only way to do this is to defer reading TABLEA until an AFTER (not for each row) trigger. It would look something like:

SQL> create table tablea ( x int, y int, z int ); Table created.

SQL> create table tableb ( x int, y int, z int ); Table created.

SQL> create or replace package tablea_pkg   2 as

  3      type ridArray is table of rowid index by binary_integer;
  4      rids     ridArray;
  5      empty    ridArray;

  6 end;
  7 /
Package created.

SQL> create or replace trigger tablea_bi   2 before update on emp
  3 begin
  4 tablea_pkg.rids := tablea_pkg.empty;   5 end;
  6 /
Trigger created.

SQL> create or replace trigger tablea_aifer   2 after insert on tablea
  3 for each row
  4 begin
  5 tablea_pkg.rids( tablea_pkg.rids.count+1 ) := :new.rowid;   6 end;
  7 /
Trigger created.

SQL> create or replace trigger tablea_ai   2 after insert on tablea
  3 begin

  4      for i in 1 .. tablea_pkg.rids.count loop
  5                  insert into tableb
  6                  select * from tablea where rowid = tablea_pkg.rids(i);
  7      end loop;

  8 end;
  9 /
Trigger created.

SQL> insert into tablea
  2 select rownum, rownum+1, rownum+2
  3 from all_users
  4 where rownum < 5
  5 /

4 rows created.

SQL>
SQL> select * from tablea;

         X Y Z
---------- ---------- ----------

         1          2          3
         2          3          4
         3          4          5
         4          5          6

SQL> select * from tableb;

         X Y Z
---------- ---------- ----------

         1          2          3
         2          3          4
         3          4          5
         4          5          6

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 22 1999 - 08:21:55 CST