Re: -- insert instead of an update

"Alkos" <azerty_at_nospam.org> wrote in message news:<bphs5n$5m91_at_news.rd.francetelecom.fr>...
> "Frank" <fbortel_at_nescape.net> a écrit dans le message news:
> bpgk4n$ns2$1_at_news2.tilbu1.nb.home.nl...
> > Alkos wrote:
> >
> > > "Andre" <avanrossem_at_hotmail.com> a écrit dans le message news:
> > > 4d32d1be.0311190134.7b49ac68_at_posting.google.com...
> > >
> > >>Hello all,
> > >>
> > >>I want to do the following:
> > >>
> > >>If a record is updated I have made an before update trigger which is
> > >
> > > triggered.
> > >
> > >>What I want to do is, instead of an update, do an insert on the same
> > >
> > > table.
> > >
> > >>So modifying a record will insert an extra record in the table.
> > >>
> > >>Is this possible??
> > >>
> > >>Thanks for all comments,
> > >>Andre The Netherlands
> > >
> > >
> > > here below is an excerpt of a script i'm currently working on
> > > (this is why it's not commented)
> > >
> > > Think it's what you wanted. It uses 'instead of' triggers
> > > I'd like to thank you cause reading your post gave me the idea of using
> > > instead of triggers
> > > for that dev.
> > >
> > > Alkos
> > > ----my script----
> > > drop table tab_data cascade constraints;
> > > drop sequence seq_data_uid_gen;
> > >
> > > create table tab_data
> > > (
> > > id number not null ,
> > > label varchar2(64) ,
> > > deleted number(1) default 0 not null,
> > > version number(4) default 0 not null
> > > )
> > > /
> > >
> > > alter table tab_data add constraint cns_data_pk primary key
> (id,version);
> > > alter table tab_data add constraint cns_data_unqlbl unique
> (label,version);
> > >
> > > create or replace view data
> > > as select
> > > id,label
> > > from tab_data
> > > where deleted=0 and version=0;
> > >
> > > create sequence seq_data_uid_gen nomaxvalue start with 1;
> > >
> > > create or replace trigger trg_data_bi_001
> > > before insert
> > > on tab_data
> > > for each row
> > > declare
> > > gen_id number;
> > > begin
> > > select seq_data_uid_gen.nextval into gen_id from dual;
> > > :new.id:=gen_id;
> > > end;
> > > /
> > >
> > > create or replace trigger trg_data_au_001
> > > instead of update
> > > on data
> > > declare
> > > gen_vers integer:=1;
> > > begin
> > > begin
> > > select nvl(max(version)+1,1) into gen_vers from tab_data
> > > where id=:old.id;
> > > exception
> > > when OTHERS then
> > > gen_vers:=1;
> > > end;
> > >
> > >
> > > insert into tab_data(id,label,version)
> > > values(:old.id,:old.label,gen_vers);
> > >
> > > update tab_data set label=:new.label where id=:new.id and version=0;
> > >
> > > end;
> > > /
> > >
> > > create or replace trigger trg_data_id_001
> > > instead of delete
> > > on data
> > > begin
> > > update tab_data set deleted=1 where id=:old.id;
> > > end;
> > > /
> > >
> > >
> > >
> > >
> > >
> > >
> > Depending on the (not mentioned) version, instead of triggers
> > may not be supported. Not in 8iR3, definitely in 9iR2
> >
> > --
> > Regards, Frank van Bortel
> >
>
> Hello,
>
> INSTEAD OF are supported in 8i for updatable views.
> Does 'quote' definitely in 9iR2 'quote' mean that 9i allows instead of
> triggers to be defined on tables ?
> Could be great ! I should recycle on 9i asap but it's a bother that most of
> my customers don't :-(
>
> Alkos

Obviously they don't want to live in the same mess as you seem to be desiring to get in.

Sybrand Bakker
Senior Oracle DBA Received on Thu Nov 20 2003 - 08:15:12 CST