Alkos wrote:
> "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
>
>
>
>
>
>
>
>
>
Yes I did - however, seeking confirmation is the docu on
tahiti.oracle.com, it states that instead of triggers can only
be defined on views.
I'd have swort I defined one on a table, yesterday. Sort of
code generator I wrote which was quite happy with an instead of.
I'll have to look into that again...
--
Regards, Frank van Bortel
Received on Thu Nov 20 2003 - 14:57:38 CST
