Most Helpful.
Christian Maslen, February 12, 2002 - 10:12 pm UTC
Excellent and to the point.
The Oracle docs said you couldn't assign the nexval in a trigger so I didn't think to use a select from dual into the transition variable.
Auto number trigger
Calvin Chu, May 16, 2002 - 9:02 pm UTC
I got an error message like this: "Warning: Trigger created with compilation errors." when compiling the trigger to do auto numbering.
After inserting a value into the table, I got "ORA-04098: trigger XXX is invalid and failed re-validation".
Please help!!
May 17, 2002 - 8:12 am UTC
show errors trigger TRIGGER_NAME
and fix your coding error.
Why Oracle doesn't provide something like identity
Moorthy Rekapalli, May 17, 2002 - 11:30 am UTC
Tom,
First of all, let me thank you for your excellent responses. I learned a lot from your site. In my view, you are doing a great service to the Oracle community.
Generating a sequence number for primary key is very common in lot of applications. It would be great if Oracle provided the capability like identity in SQL Server. This eliminates the need for everybody on the planet writing before insert trigger to generate a primary key.
I hope that Oracle development team is reading this post and add this feature in a future release.
I hope that Oracle development team is not!!!
J, May 17, 2002 - 12:35 pm UTC
create or replace function emp_ident return number is
l_return number;
begin
select id.nextval
into l_return
FROM dual;
if mainoff then l_return = l_return +1000000; end if;
if othoff then then l_return = l_return +2000000;end if;
--if what>> then then l_return = l_return +3000000;
return l_return ;
end;
/
insert into emp(prim_key, first) values (emp_ident,'Joe');
/
-- disclaimer - not tested half ranting
write your own!!!
A reader, April 12, 2003 - 5:03 pm UTC
thanks Tom. very useful.
10G?
Kashif, March 26, 2004 - 6:51 pm UTC
Hi Tom,
Is the auto-increment functionality implemented in a different way in 10G, e.g. through an IDENTIFIER type (as is used in other databases) or something similar? Or is using a before insert trigger to set the value of an ID column still the only way to do it in Oracle? I hadn't heard anything different in 9i either. Thanks in advance.
Kashif
March 26, 2004 - 7:09 pm UTC
nope, it is still as functional and flexible as it always has been.
Is this safer/better
A reader, March 27, 2004 - 12:13 am UTC
Tom,
Assume you have just created the table t and sequence myseq starts with 1.
insert into t(y) values(2)
insert into t(x,y) values(2,2)
insert into t(y) values(3)
The third insert PK unique contraint will fail.
Is it better to use
create or replace trigger t_trigger
before insert on t for each row
begin
select nvl(:new.x, myseq.nextval) into :new.x from dual;
end;
/.
March 27, 2004 - 10:37 am UTC
what is you point here? You made a programming choice to do what you did and in your case it would be "the wrong thing to do"
What if?
Sikandar Hayat, March 27, 2004 - 12:26 am UTC
Although this is a good way to generate PK but I would like to know if someone is using web based application and the db trigger is generating the PK the how the user will be notified about his/her transaction#.
Secondly what will be the performance impact in multiusers environment of this technique?
pl explain
A reader, March 27, 2004 - 11:34 am UTC
Can you please explain why
"in
your case it would be "the wrong thing to do" "
for the nvl in trigger ?.
We generate seq for every insert this way whether we use it as PK or not use it ( when user supplies one)
March 27, 2004 - 4:43 pm UTC
because the third insert would fail and you seemed to want to do that third insert?
so -- in that case -- it would be the "wrong approach" as you would have failed inserts lots.
The only time it might make total sense would be:
"we are going to load data with existing primary keys. these keys range between 1 and 1 billion. We will therefore start a sequence for new records at one billion -- if the inserted record did not supply a primary key, we'll generate one (starting at 1,000,000,000,001) else we'll use the supplied one"
some clarification
reader, June 16, 2004 - 8:39 am UTC
Hi Tom,
I am not sure where I read this " **it said it's better to have the before insert trigger to generate the value, as in your example rather than use sequence.nextval in insert to avoid losing the number in case insert statement fails **". I tried to test it having a table and before insert on it
for e.g
table t(col1 number, col2 number)
check constraint on col2 < 10;
sequence test1
before insert row level on t to have :new.col1 assigned value test1.nextval
i inserted a record with col2 value > 10 the insert failed (which had to) but the sequence value got incremented.
Tom as you say don't believe on what books say test them or have them give the test, I am not able to recollect where I read it, is my test right and the statement mentioned in book ** incorrect
please throw some light on this.
June 16, 2004 - 12:52 pm UTC
tell me how having a trigger would "avoid losing the number"
it would not.
and it really doesn't matter as a sequence is NEVER in a BILLION years going to be "gap free"
it is best to use it in the insert statement directly for performance, efficiency, ease of maintenance and so on.
Maybe becuase of this reason...
Kamal Kishore, June 16, 2004 - 1:17 pm UTC
I think what the "author" might be hinting at is that in cases where the trigger does a lot of processing besides generating the unique sequence numbers and generating the sequence number is the last step in the trigger, then if any of those above steps in the trigger fail, you do not generate a new sequence number (because you do not reach to that line of code in trigger) and thus save on that sequence number for some other future insert.
So, you have a choice - save on a sequence number for some future insert or get performance benefits by doing it in the insert statement itself and not care about losing few sequence numbers (as mentioned by Tom above).
June 16, 2004 - 1:30 pm UTC
it isn't really a good reason, i would still put the s.nextval right in the insert.
since "gap free" "is not possible"
there must exist gaps, there will be gaps
and using a sequence at the rate of a billion per second
ops$tkyte@ORA9IR2> set numformat 999,999,999,999,999
ops$tkyte@ORA9IR2> select rpad('9',27,'9')/1000000000/60/60/24/366 from dual;
RPAD('9',27,'9')/1000000000/60/60/24/366
----------------------------------------
31,623,153,208
would still take 31.6 BILLION years to run out of values.......
(and the fact that a trigger that does alot of processing is in my opinion "a hugely bad idea......")
the never ending gap debate
Mark A. Williams, June 16, 2004 - 2:05 pm UTC
Ambassador de Sadesky:
The deciding factor was when we learned that your country was working along similar lines, and we were afraid of a doomsday gap.
[snip]
General "Buck" Turgidson:
We cannot have a mineshaft gap!
Dr. Strangelove or: How I learned to Stop Worrying and Love the Bomb, 1964
Seems the gap debate has been raging for years.
- Mark
June 16, 2004 - 3:39 pm UTC
I just re-watched Dr. Strangelove about a month ago :) It is still funny today.
Thanks for your help Tom
reader, June 17, 2004 - 2:13 am UTC
Hi Tom,
thanks a lot for clearing the myth again thanks a ton, one small help though will you please give some more details on your statement "it is best to use it in the insert statement directly for performance,
efficiency, ease of maintenance and so on"
Thanks a lot in advance.
your fan.
June 17, 2004 - 9:37 am UTC
it is faster to insert into t values (s.nextval) then to create a trigger and select from dual.
it is easier to maintain, since triggers are magic that happen in the background. how many times have i inserted x,y,z into a table only to find a,b,c because some trigger was in the background doing "magic"
it is easier to maintain, well, because there is nothing to maintain!
Don't use a sequence.
Bill, June 18, 2004 - 9:57 am UTC
If you have a requirement that an ID number can't have gaps, then simply don't use a sequence for that number. A sequence is used to only produce a unique number and with caching on, you will always have gaps. Even without caching, there will be instances where the update fails so a gap will appear. The only way that I can think of that would ensure a non gapped id is:
1) Grab a lock on the table.
2) find the maximum value of the key in the table.
3) add 1 to that value.
4) insert the new record.
5) release the lock (commit).
I do not suggest this methoid as it would be slow. and if you had to remove a record, you would still have gaps.
Gaps will happen, learn to live with them.
aziz, July 15, 2004 - 2:31 am UTC
juast excellent and to the point