| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger/sequence bug in 8.1.7.2.0 and 9.0.1.3.0 and ... ?
SQL> insert into x values(s.nextval);
1 rij is aangemaakt.
SQL> commit
2 /
Commit is voltooid.
SQL> select * from x
2 /
ID
1
SQL>
SQL> rem now 'proper' syntax
SQL> insert into x select s.nextval from dual;
1 rij is aangemaakt.
SQL> select * from x
2 /
ID
1
5 -- as was expected
SQL> insert into x values(6)
2 /
1 rij is aangemaakt.
SQL> select * from x
2 /
ID
1
5
6
SQL> Note : in the previous step I changed the after in before. You should assign sequences *before* insert. Not that it makes any difference
Now a small variation
create or replace trigger t_x
before 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('value of local_variable = '||local_variable);
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;
/
and again
SQL> insert into x select s.nextval from dual;
1 rij is aangemaakt.
SQL> commit
2 /
Commit is voltooid.
SQL> select * from x
2 /
ID
1
5
6
12 <===========
SQL>
So, is the *final* result, the record in the table *ever* incorrect. I don't
think so.
So a bug? I doubt it. You could debate about that, as the result *is*
correct.
Oracle PE 8.1.7.0.0
Regards
-- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail address SQL> "Richard Kuhler" <noone_at_nowhere.com> wrote in message news:QVbJ8.21536$R53.9555184_at_twister.socal.rr.com...Received on Wed May 29 2002 - 17:17:06 CDT
> 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]
> ===========================================
> 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]
> ===========================================
> 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
>
 |
 |