Re: -- insert instead of an update

From: Alkos <azerty_at_nospam.org>
Date: Wed, 19 Nov 2003 15:34:42 +0100
Message-ID: <bpfv24$4e03@news.rd.francetelecom.fr>

"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;
/ Received on Wed Nov 19 2003 - 08:34:42 CST