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:If9J8.20313$R53.9448891_at_twister.socal.rr.com...
>
>
> 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
>

Yes, you are
In fact you just confirmed my suspicions. You insert a record in x_hist. True?
The record comes with a non-null id column. True? *The before insert trigger on x_hist fires* True? The trigger doesn't check for the id column to be not-null. Hence *your code* overrides the value assigned to the column by your trigger x.
Yes, it IS a bug, but is in a bug in YOUR code. NOT in Oracle.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Wed May 29 2002 - 15:42:08 CDT