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

The behavior you observed is expected within one row of one SQL statement...

"Within a single SQL statement, Oracle will increment the sequence only once for each row. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement."

However, the problem I'm demonstrating is across 2 SQL statements (the insert into x and the triggers insert into x_hist).

Oracle has opened a bug for this and assigned it to development for analysis. I'll post a follow up when they give me more information.

Richard

Galen Boyer wrote:
>
> On Tue, 28 May 2002, noone_at_nowhere.com wrote:
> > 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.
>
> Hm... I tried quite a few permutations of the trigger and got the
> same answer.
>
> insert into x_hist (
> id,
> id_hist
> ) values (
> :new.id,
> s.nextval
> );
>
> insert into x_hist (
> id_hist,
> id
> ) values (
> s.nextval,
> s.currval
> );
>
> insert into x_hist (
> id,
> id_hist
> ) values (
> s.currval,
> s.nextval
> );
>
> insert into x_hist (
> id,
> id_hist
> ) values (
> s.nextval,
> s.nextval
> );
>
> I also tried these as before triggers and got the same results.
>
> I then tried (for my sanity):
>
> insert into x_hist (
> id,
> id_hist
> ) values (
> s.currval,
> s.currval
> );
>
> and got x.id inserted into both columns of the history table.
>
> I then tried:
>
> insert into x_hist (
> id
> ) values (
> :new.id
> );
> update x_hist set id_hist = s.nextval where id = :new.id;
>
> and got the correct answer.
>
> So, then I thought for a bit and said, what about the following?
>
> SQL>create table t1 (id1 number, id2 number);
>
> SQL>drop sequence s;
>
> Sequence dropped.
>
> SQL>create sequence s;
>
> Sequence created.
>
> SQL>insert into t1 values (s.currval,s.nextval);
>
> 1 row created.
>
> SQL>select * from t1;
>
> ID1 ID2
> ---------- ----------
> 1 1
>
> SQL>insert into t1 values (s.nextval,s.currval);
>
> 1 row created.
>
> SQL>select * from t1;
>
> ID1 ID2
> ---------- ----------
> 1 1
> 2 2
>
> SQL>insert into t1 values (s.nextval,s.nextval);
>
> 1 row created.
>
> SQL>select * from t1;
>
> ID1 ID2
> ---------- ----------
> 1 1
> 2 2
> 3 3
>
> The same logic of the straight inserts must be used in the trigger as
> well. I'm guessing Oracle doesn't want to define the values of a
> sequence differently within a single statement based on the ordering
> within the statement of the calls to nextval and currval.
>
> The same logic holds true on selects.
>
> SQL>select s.currval, s.nextval from dual;
>
> CURRVAL NEXTVAL
> ---------- ----------
> 4 4
>
> SQL>select s.nextval, s.currval from dual;
>
> NEXTVAL CURRVAL
> ---------- ----------
> 5 5
>
> SQL>select s.currval, s.currval from dual;
>
> CURRVAL CURRVAL
> ---------- ----------
> 5 5
>
> SQL>select s.nextval, s.nextval from dual;
>
> NEXTVAL NEXTVAL
> ---------- ----------
> 6 6
>
> --
> Galen deForest Boyer
> Sweet dreams and flying machines in pieces on the ground.
Received on Thu May 30 2002 - 23:23:51 CDT