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

Member_2_1316035
Member_2_1316035 used Ask the Experts™
on
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?  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT-specialist
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

Author

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?
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...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Shaju KumbalathDeputy General Manager - IT
Commented:
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
Shaju KumbalathDeputy General Manager - IT

Commented:
And also this is a dynamic procedure and can be used for any kind of id generation
Most Valuable Expert 2012
Distinguished Expert 2018

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

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

Author

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!
Most Valuable Expert 2012
Distinguished Expert 2018

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?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial