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
Received on Wed Nov 19 2003 - 14:42:46 CST
