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

Sybrand Bakker wrote:
>
> "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

Sorry, I don't follow what your saying here (same trigger on x_hist?). That sample code is a complete demonstration of the problem. You should be able to run it just as is on any database and see it work the same way. By "the same sequence is being used" I meant that in the sample you can see I have used the same sequence for both the insert on x and the triggers insert on x_hist. In fact, that is the source of the problem. Apparently (as another poster suggests) Oracle is reusing the same buffer to get the new nextval and overwriting the :new.id value in the process. We've opened a TAR for this but if you see something I'm doing wrong please clarify for me.

Thanks,
Richard Received on Wed May 29 2002 - 13:39:36 CDT