Re: -- insert instead of an update

"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 Received on Thu Nov 20 2003 - 01:57:41 CST