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

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.