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

Richard -

Yeah, this looks mighty odd to me, too. I used your testcase exactly and was able to confirm the strange behavior on version 8.1.7.2 on Win2000.

However, I was able to do a quickie workaround (assuming that this truly is unintentional behavior, of course...) by using an intermediate variable to hold the value of :new.id in the trigger.

Here is my version of your trigger:
create or replace trigger t_x
after insert on x
for each row
declare
  dummy x_hist.id%type;
begin
  dummy := :new.id;
  insert into x_hist (
    id_hist,
    id
  ) values (
    s.nextval,
    dummy
  );
end;
/

And here is my output (the first two rows were used with your trigger in place, and the last one is with mine):

SQL> select * from x;

        ID

         1
         3
         5

SQL> select * from x_hist;

   ID_HIST ID
---------- ----------

         2          2
         4          4
         6          5

Good luck on figuring this out and/or fighting with Oracle Support. I'll be interested in your resolution.

TG

Richard Kuhler 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.
>
> Thanks,
> Richard
Received on Tue May 28 2002 - 17:51:39 CDT