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.

ACCIDENTID
A00001
A00002
A00003

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?
 
flow01Connect With a Mentor Commented:
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
0
 
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?
0
 
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...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Shaju KumbalathConnect With a Mentor Deputy 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

CREATE TABLE PRM_TABLE
(
PARAMETER_NAME VARCHAR2(10 BYTE) primary key,
SEQ_VALUE NUMBER(10)
);
-- Insert the current max value to the table (only numeric part)

INSERT INTO PRM_TABLE ( PARAMETER_NAME, SEQ_VALUE ) VALUES (
'ACCIDENTS', 200);
COMMIT;
-- here i'm assuming that there 200 records existing
--Create the follwing procedure
 
CREATE OR REPLACE PROCEDURE p_get_newno (
param_nm IN VARCHAR2,
series_len IN NUMBER,
prefix IN VARCHAR2,
new_cd OUT VARCHAR2
)
IS
BEGIN
BEGIN
SELECT (NVL (seq_value, 0) + 1)
INTO new_cd
FROM prm_table
WHERE UPPER (parameter_name) = UPPER (param_nm)
FOR UPDATE OF seq_value;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO prm_table
(parameter_name, seq_value
)
VALUES (param_nm, 1
);
new_cd := 1;
END;
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);
END;
/
 
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
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
And also this is a dynamic procedure and can be used for any kind of id generation
0
 
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?
0
 
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.
 
0
 
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?

Thanks!
0
 
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.