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

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 29 May 2002 21:41:04 GMT
Message-ID: <QVbJ8.21536$R53.9555184@twister.socal.rr.com>

Sybrand Bakker wrote:

<snip>

> > 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?

No, the trigger inserts into x_hist. In the demonstration, I insert into x.

> The record comes with a non-null id column. True?

Possibly true. What record x or x_hist? The value :new.id is not null for the trigger on table x. There is no trigger on x_hist so I'm not sure this makes sense in that context.

> *The before insert trigger on x_hist fires* True?

False. There is no trigger on x_hist. Are you talking about the trigger on x? Also note, the trigger on x is 'after insert'. In any regard, I still don't understand.

> 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.

Can you point me to the specific line in my code that changes the value of :new.id? Note: I'm not trying to be a wise ass, I want to understand if you found a problem in my demonstration.

> Yes, it IS a bug, but is in a bug in YOUR code. NOT in Oracle.
>
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA

Here's a different demonstration that may clarify...

create sequence s;
create table x (id number);

create or replace trigger t_x
after insert on x
for each row
declare

local_variable number;
begin

for i in 1 .. 3 loop

dbms_output.put_line('===========================================');
        dbms_output.put_line('the value of :new.id is [' || :new.id ||
']');
        dbms_output.put_line('selecting the nextval into a local

variable');

select s.nextval into local_variable from dual;

dbms_output.put_line('done selecting the nextval into a local variable');

dbms_output.put_line('the value of :new.id is now [' || :new.id || ']');

end loop;

end;
/

Now, you can see that selecting s.nextval in the trigger changes the value of :new.id ...

insert into x values (s.nextval);

the value of :new.id is [1]
selecting the nextval into a local variable done selecting the nextval into a local variable the value of :new.id is now [2]

the value of :new.id is [2]
selecting the nextval into a local variable done selecting the nextval into a local variable the value of :new.id is now [3]

the value of :new.id is [3]
selecting the nextval into a local variable done selecting the nextval into a local variable the value of :new.id is now [4]

Notice that if I use a literal value instead of the expression 's.nextval' it works fine...

insert into x values (1);

the value of :new.id is [1]
selecting the nextval into a local variable done selecting the nextval into a local variable the value of :new.id is now [1]

Note: that is the complete code. There are no hidden triggers or other objects. If you try it and don't see this behavior please post the sample and version information.

Thanks,
Richard Received on Wed May 29 2002 - 16:41:04 CDT