Re: -- insert instead of an update

From: Alkos <rtd45Rd_at_nospam.org>
Date: Thu, 20 Nov 2003 20:42:39 +0100
Message-ID: <bpj5f8$a8g$1@news-reader1.wanadoo.fr>

<sybrandb_at_yahoo.com> a écrit dans le message de news: a1d154f4.0311200615.6e35217e_at_posting.google.com... > "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

Hope this was intended to be ironical :-/ My only wish is to discover what's new with that version because some companies
are using it so I don't want to close myself some job opportunities. But maybe a Senior Oracle DBA has some good advice to give me, hasn't you, Sybrand ;)

Alkos Received on Thu Nov 20 2003 - 13:42:39 CST