Re: -- insert instead of an update

"Frank" <fbortel_at_nescape.net> wrote in 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
|

instead of triggers on views are supported beginning with at least 8.0.4 (but up to some point may have been an extra cost option)

• mcs