Re: More easy way ?

Sorry, you are right. In this case i was to "quick". I did not recognize that the source for the insert in the trigger should be from the current table.

Michel Cadot wrote:

> Hans Quick <quick_at_numrich.de> a écrit dans le message : 398EA4E6.556AF8DD_at_numrich.de...
> > if the two table are of the same structure (datatype and column-order) you can simply code:
> >
> > insert into table-a (select * from table-b);
>
> You should read the question before answering.
> You're in a trigger and can't do a select on the current table.
>
> Moreover, insert into (select *) is bad coding because it is
> dependent on the current schema.
> Imagine you recreate one of these tables with a different order
> for the columns.
>
> One of the major points of the relationnal model is the independence
> between code and storage.
>
> >
> >
> > Hans
> >
> > Michel Cadot wrote:
> >
> > > 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
> >
>
> --
> Cheers
> Michel
Received on Mon Aug 07 2000 - 00:00:00 CEST