[Stackoverflow] [Progress OpenEdge ABL] Create SEQUENCE and TRIGGER by external procedure

[Raphael Frei]

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)

Continue reading...