CREATE trigger in an OpenEdge database

Question

We have the following situation:

Our primary database is an OpenEdge one, and we have a SQL database which we have to sync up with our OE one. We came up with a plan to set up staging tables for our OE tables where we'd copy newly created records with the addition of a couple extra fields for logging the user that created the record, dates, etc. We've managed to connect those two databases using the OE DataServer, but we're having trouble with setting up triggers that would find the newly created records and copy them to the staging tables and assigning values to those additional logging fields.

We tried using RECID as an identifier for the newly created record and came up with a trigger procedure which would handle that. The thing is, when ran as a procedure, it works just fine. It finds the record and copies everything to the staging table without a problem. But when we set up the CREATE trigger on the database to run that procedure, we get errors.

The procedure looks like this:

DEFINE VARIABLE cRECID AS RECID NO-UNDO.
DEFINE VARIABLE cUser AS CHARACTER NO-UNDO.

cUser = USERID("maticna"). 

FOR EACH Artikli NO-LOCK BY RECID(Artikli):
    ASSIGN cRECID = RECID(Artikli).
END.

IF cRECID <> ? THEN
DO:
    FOR EACH Artikli WHERE RECID(Artikli) = cRECID :
    
        FIND FIRST stagingArtikli WHERE RECID(stagingArtikli) = cRECID NO-LOCK NO-ERROR.
        IF NOT AVAILABLE stagingArtikli THEN
        DO:
            CREATE stagingArtikli.
            BUFFER-COPY Artikli TO stagingArtikli.
            ASSIGN
                stagingArtikli.CreatedBy = cUser
                stagingArtikli.CreateDate = NOW
                stagingArtikli.Replicated = NO
                stagingArtikli.Edited = NO
                stagingArtikli.EditDate = ?
                stagingArtikli.EditUser = " "
                .
        END.
    END.
END.

When trying to create a new Artikli record with a simple procedure like:

CREATE Artikli.
ASSIGN
    Artikli.SifArtik = "12345678"
    Artikli.Name = "Test"
    etc...

it throws an error saying Artikli.SifArtik is mandatory, but has unknown (?) value. (110)

Answer 1

You probably created your Artikli table and set the "mandatory" field to TRUE for the SifArtik field. You probably ought not to be doing that and the simplest fix is to set the mandatory field to FALSE using the data dictionary tool.

The snippet of code that you are showing does not, to me, seem to have an obvious problem that would throw error (110). So I suggest that you add "-clientlog somefile.debug" to your startup parameters. This will identify what line of code is throwing error (110). Also - make sure that you do not have multiple statements on a single line.

Beyond that you need to be aware that RECID is not a unique value. RECIDs can be reused and the same RECID may exist for different tables and even for the same table if it is spread across multiple partitions or tenants. RECIDs will also change if your data is ever reorganized via dump & load or tablemove or similar methods. So you probably want to find a different way to uniquely identify your staging records. A sequence number or a GUID for instance.

Next, your FIND FIRST is either going to lead to bugs (because you may have duplicate RECIDs) or it is pointless (if you have a truly unique relationship). FIRST is not required syntactic sugar that goes with every FIND. It should only be used when you are actually, truly, intending to process the FIRST record of a set of records and, if that is the case, you are missing any code to handle the 2nd, 3rd, and so forth records. Plus you have nothing which is establishing how that "first" relationship is determined. You are hoping to be lucky that, in this case. RECID is an integer and you will get the lowest numbered record "first". But that will not always be the case with other WHERE clauses. A GUID, for instance, will not have such a lucky ordering relationship.

And, actually, while RECIDs are integers and so there is an ordering of sorts to them... over time records get created in effectively random blocks and in no particular physical sequence so RECID 1234 may very well have been created long after RECID 6789. You cannot actually infer anything about "firstness" from a RECID.

Lastly, using triggers for this purpose will only capture changes that occur via 4GL code. If someone is allowed to make a SQL connection to the database and given permission to create or update data then those changes do NOT fire 4gl triggers and your attempt to track such changes will fail.

Ok, one more thing... this whole thing is what the "Change Data Capture" feature is designed for. It would be much more reliable and straight-forward to implement CDC for this purpose. You do need to be running at least OpenEdge 11.7 for that. There is also a product called "Pro2" that will do this work for you. On older releases Pro2 uses triggers, as you are. On newer releases it can use CDC to track changes.

Answer 2

We went with a different approach for the trigger. We've set up a WRITE trigger using NEW and OLD buffers which allows us to handle both the creation and updating of records.

Thanks a lot for the help, guys! :)