Re: Trigger/sequence bug in 8.1.7.2.0 and 9.0.1.3.0 and ... ?

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:0fTI8.18348$R53.8535200_at_twister.socal.rr.com...
> Can someone please confirm this bug (I can't find it on metalink)...
>
> create sequence s;
> create table x (id number);
> create table x_hist (id_hist number, id number);
>
> create or replace trigger t_x
> after insert on x
> for each row
> begin
>
> insert into x_hist (
> id_hist,
> id
> ) values (
> s.nextval,
> :new.id
> );
>
> end;
> /
>
> insert into x values (s.nextval);
>
> select * from x;
>
> ID
> ------------
> 1
>
> select * from x_hist;
>
> ID_HIST ID
> ------------ ------------
> 2 2
>
>
> The value x_hist.id should be the same as x.id but it's getting changed
> by the trigger (or am I missing something?).
>
> Note: the same sequence is being used for both tables.
>
>
> Thanks,
> Richard
>

You must be having the same trigger on x_hist and your trigger doesn't test whether id is not null, so you assign a nextval unconditionally, even when id is already not null.
Wouldn't that be a far more logical explanation (giving your remark 'the same sequence is being used for both tables') than the 'quickie workaround' aka hacking of someone else?

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address