Creating AutoNumber field
- Thread starter yoachan
- Start date
TomBascom
Curmudgeon
One method is to use a sequence and a trigger. The sports database uses this technique:
(Use the data dictionary to see the definition of the trigger and the sequence.)
Code:
/* crcust.p */
TRIGGER PROCEDURE FOR Create OF Customer.
/* Automatically Increment Customer Number using Next-Cust-Num Sequence */
ASSIGN Customer.Cust-Num = NEXT-VALUE( Next-Cust-Num ).(Use the data dictionary to see the definition of the trigger and the sequence.)
Thanks for your reply.
I tried the sequence, and it it works. I thank you for that too
But I have another problem. Some of my friend shared the table with me and it's not possible for everyone of them to change their code shortly. So I use NEXT-VALUE on my own code, not on Database-Trigger.
The problem is when my friend manually add the autonumber using find-last and +1, my sequence will crash on this manually generated auto-number...
e.g.
me using NEXT-VALUE (sequence) get number 100.
friend useing FIND LAST and +1 will get 101.
me using NEXT-VALUE (sequence) get number 101 again......
now what should i do....
PS: CMIIW, each sequence has it's own counter, so if I use the same sequence for several tables it won't keep each table's last counter but will only have one sequence counter.
I tried the sequence, and it it works. I thank you for that too
But I have another problem. Some of my friend shared the table with me and it's not possible for everyone of them to change their code shortly. So I use NEXT-VALUE on my own code, not on Database-Trigger.
The problem is when my friend manually add the autonumber using find-last and +1, my sequence will crash on this manually generated auto-number...
e.g.
me using NEXT-VALUE (sequence) get number 100.
friend useing FIND LAST and +1 will get 101.
me using NEXT-VALUE (sequence) get number 101 again......
now what should i do....
PS: CMIIW, each sequence has it's own counter, so if I use the same sequence for several tables it won't keep each table's last counter but will only have one sequence counter.
Agree but the quick and very dirty way of doing it could be
:awink:
Code:
DEF VAR I AS INT.
I = NEXT-VALUE( Next-Cust-Num ).
FIND CUSTOMER WHERE CUST-NUM = I NO-LOCK NO-ERROR.
DO WHILE AVAIL CUSTOMER:
I = NEXT-VALUE( Next-Cust-Num ).
FIND CUSTOMER WHERE CUST-NUM = I NO-LOCK NO-ERROR.
END.
CREATE CUSTOMER.
ASSIGN Customer.Cust-Num = I.:awink:
@sdjensen:
this is like my prev method, and sadly it's not working when concurency occurs.
Thanks for your reply too
As you can see in my code it tries to find a record with the new number and if a record is found it continues to increase the sequence until a record is not found.
In my idea Tom said the better thing to do. Make your decision and then align the code to this method. Try not to go into further troubles having different solutions ...
If you want, write some source code to use for create the new record and make all the others use that code ( inside it you will use sequence or whatever ). For maintenance purposes it is easier, i think.
If you dont think to use the sequence, it may be a good idea to remove it from the db so that no one will be able to use it anymore..
Bye!
If you want, write some source code to use for create the new record and make all the others use that code ( inside it you will use sequence or whatever ). For maintenance purposes it is easier, i think.
If you dont think to use the sequence, it may be a good idea to remove it from the db so that no one will be able to use it anymore..
Bye!
