Create sequence and trigger to auto increment primary key from max value

New to Oracle programming.  I have a table "Accidents" that has a primary key of AccidentID.  AccidentsID is a char(6) and must start with 'A' followed by 5 numbers.  I am trying to create a trigger and sequence to auto increment the AccidentsID field upon insert into that table.  There are already existing rows in that table, so I want it to first find the max value and increment it by 1.


Do I add the logic to find the max value of AccidentID to my sequence or the trigger?

Can someone point me in the right direction?  
Jinghui LiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

find the max value once
(select max(to_number(substr(accidentid,2))) from ...
and created the sequence with a starting value of the max + 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinghui LiAuthor Commented:
I have a couple questions;

1)  So this is hard coding the START WITH value in the sequence?
2)  Won't substr(accidentid,2) take the first 2 characters from the left and return A0?  How does that help us find the highest primary key that exisits?
Muhammad KhanManager, ITCommented:
1) Yeah.. start with value should be a hard coded constant
2) No... the code says.. leave out first two characters and take the string till the end.the query first take the number part from the primary key ... as this is inherently a text, to_number is used to convert this to a number and then max function returns the maximum of all numbers...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Shaju KumbalathDeputy General Manager - ITCommented:
Using the sequences may result in skipping the nos. to get a continues nos without skipping u can use the following method

-- Insert the current max value to the table (only numeric part)

'ACCIDENTS', 200);
-- here i'm assuming that there 200 records existing
--Create the follwing procedure
param_nm IN VARCHAR2,
series_len IN NUMBER,
prefix IN VARCHAR2,
SELECT (NVL (seq_value, 0) + 1)
INTO new_cd
FROM prm_table
WHERE UPPER (parameter_name) = UPPER (param_nm)
FOR UPDATE OF seq_value;
INSERT INTO prm_table
(parameter_name, seq_value
VALUES (param_nm, 1
new_cd := 1;
UPDATE prm_table
SET seq_value = new_cd
WHERE UPPER (parameter_name) = UPPER (param_nm);
new_cd := LPAD (new_cd, series_len - (LENGTH (prefix)), '0');
new_cd := prefix || new_cd;
DBMS_OUTPUT.put_line ('NEW_CD' || new_cd);
for new id execute the procedure IN THE BEFORE INSERT TRIGGER
 p_get_newno ( 'ACCIDENTS',5,'A',:NEW.ACCIDENTID);

The advantage of this trigger is
-> Doesn't skip nos
-> No duplicates codes generated like when u use max() in con corrent sessions
Shaju KumbalathDeputy General Manager - ITCommented:
And also this is a dynamic procedure and can be used for any kind of id generation
slightwv (䄆 Netminder) Commented:
>>Using the sequences may result in skipping the nos

I believe you can still skip numbers with your code the same way a non-cached sequence can:  someone calls the procedure to get the next record and doesn't commit the transaction.

Do you have a gap-less requirement?  I would probably go with a sequence and hard-code the start with with the first number you want.

You can limit the gaps by using NO CACHE with the sequence.  Once you get the number you can use to_char (or lpad) to format the result: select 'A' || to_char(myseq.nextval,'00009') from table;

Also, what happens on the 100,000th accident?
Shaju KumbalathDeputy General Manager - ITCommented:
it will not skip nos because as we are not commiting inside the procedure.
Also, what happens on the 100,000th accident?
By the accedentID pattern provided by him only supports 5 digits  , Even if u use sequnece A100000 will not be supported if there is a back end column length limitation.
Jinghui LiAuthor Commented:
slightwv - I do not have a gap-less requirement.  But the requirement is to have a dynamically generated START WITH value.  And I understand that a create sequence START WITH must have a value, not an expression (maxIncident) to evaluate, is that correct?

shajukg - I like your idea but it will take me some time to work through and digest.  I think this is close to the direction I was taking it - I was thinkinig that I need to wrap the create sequence into a procedure and pass the maxIncident as a parameter?

slightwv (䄆 Netminder) Commented:
>> to evaluate, is that correct?

Yes. I'm guessing there is a time-related issue here. In other words, ACCIDENTS are continually being inserted so you can't get the current 'max' value?

Just jump the starting point by X to account for the possible new rows. I would probably want to start with a nice round number anyway. This way you can remember any 'accidents' over 100000 are from the new sequence.

Either approach needs the value. Now you can write some PL/SQL to find the max and take the appropriate action but is it worth it?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.