Member_2_1316035
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And also this is a dynamic procedure and can be used for any kind of id generation
>>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,'000
Also, what happens on the 100,000th accident?
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,'000
Also, what happens on the 100,000th accident?
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.
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.
ASKER
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!
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!
>> 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?
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?
ASKER
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?