Re: More easy way ?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/08/07
Message-ID: <8mm4jh$73p$1_at_s1.read.news.oleane.net>#1/1

Chris <stvllium_at_ms18.hinet.net> a écrit dans le message : 8mlvqg$hfn_at_netnews.hinet.net...
> Hi, All
>
> Is there have any easily way ? Cause NN have 30 columns.
>
> > Michel Cadot <micadot_at_netcourrier.com> wrote in message
> > news:8mlrn3$34q$1_at_s1.read.news.oleane.net...
> > > Create or replace trigger tgr1
> > > After insert on NN
> > > For each row
> > > begin
> > > insert into NNCopy (col1, col2, ...)
> > > values (:new.col1, :new.col2, ...);
> > > end;
>
> It's could resolve the problem, but it's not perfect.
> 30 columns ...

You only have to write once.
But if you're too lazy, here'a script to generate it (assuming the column names of the two tables are the same).

set feedback off
set pagesize 0
set sqlterminator off
col id noprint
col column_id noprint

select 1 id, column_id, decode(column_id,1, ' Create or replace trigger tgr1
After insert on NN
For each row
begin

    insert into NNCopy (
      ',' ,')||
column_name
from user_tab_columns
where table_name = 'NN'
union
select 2 id, column_id, decode(column_id,1, ' )

   values (

      ',' ,')||
':new.'||column_name||
decode(column_id, maxid, '

     );
end;
','')
from user_tab_columns,

     (select max(column_id) maxid from user_tab_columns
      where table_name = 'NN')

where table_name = 'NN'
order by id, column_id
/

>
> --
> > > Have a nice day
> > > Michel
> > >
> > >
> > > Chris <stvllium_at_ms18.hinet.net> a écrit dans le message :
 8mln40$56i_at_netnews.hinet.net...
> > > > Hi, All
> > > >
> > > > How do I insert the new row (from NN, inserting) into another
> > > > table(NNCopy) in the trigger tgr1 ?
> > > >
> > > > Create or replace trigger tgr1
> > > > After insert on NN
> > > > For each row
> > > > begin
> > > > insert into NNCopy
> > > > ?????????????????????
> > > > end;
> > > >
> > > > Table NN and NNCopy have the same table definition, there have about
 30
> > > > columns in NN.
> > > >
> > > > Thanks
> > > >
> > > > Chris Chi from Taiwan, R.O.C.
> > > >
> > > >

Regards
Michel Received on Mon Aug 07 2000 - 00:00:00 CEST