I have a table that is only created by passing in this procedure.
This is kinda the CREATE trigger and SEQUENCE generator.
(We can't stop the database to create TRIGGER and SEQ., I've already tried to ask for this several times... So got to create it by hand)
DO TRANSACTION ON ERROR UNDO, RETRY:
FIND LAST mensagem NO-LOCK NO-ERROR.
IF AVAIL im THEN
INextID = mensagem.mensagem_id + 1.
ELSE
INextID = 01.
IF CAN-FIND(mensagem WHERE mensagem.mensagem_id = iNextID) THEN
UNDO, RETRY.
CREATE mensagem.
/* mensagem_id is a unique, primary index */
ASSIGN
mensagem.mensagem_id = INextID
mensagem.field1 = value1
mensagem.field2 = value2
.
END.
This procedure is accessed several times during a minute, and sometimes may occur on two programs calling it at the same time, occuring in trying to create two objects with the same value.
My issue is the following: How can I prevent the error mensagem already exists with mensagem_id 'INextID'?
(Considers INextID as the next number).
When this error occurs, it's not looping thru the TRANSACTION BLOCK and is not creating the information on the table.
(I've already tried to call the .P with NO-ERROR param, but no success neither)
In your example code you are (probably) getting an occasional collision in the ASSIGN statement because a small amount of time can pass between the FIND, the CAN-FIND, and the CREATE & ASSIGN. And during that interval another process can slip in and use the value that you were hoping to use. So you need to add NO-ERROR to the ASSIGN and then handle the suppressed error by first detecting it and then retrying.
Something like this:
ASSIGN
mensagem.mensagem_id = INextID
mensagem.field1 = value1
mensagem.field2 = value2
NO-ERROR.
if error-status:num-messages > 0 then
undo, retry.
However - using a sequence is the best way to prevent collisions on sequence numbers and that would be a much better approach.
You can add sequences online, there is no need to stop the database to do that. You can add triggers online too if you are on OE12.2 or better.