Need to modify triggers (en masse) to assign newly added fields generically

Added a set of new fields to all tables in the database and need to assign them, and continue to assign them programmatically when the records change. Need a generic way to access the OLD BUFFER and NEW BUFFER from within a trigger program that will run against all tables.

Sep 12, 2023
Knowledge

Title

URL Name

need-to-modify-triggers-en-masse-to-assign-newly-added-fields-generically

Article Number

000238410

Environment

Product: OpenEdge Version: All Supported Versions OS: All Supported Platforms

Question/Problem Description

Is there a way to dynamically reference the OLD BUFFER or NEW BUFFER from within a write trigger?
Added auditing fields to all tables in the database. In the write triggers for these fields need to review the OLD BUFFER and the NEW BUFFER to store which fields changed.
The problem is that this would have to be customized for every table if dynamic access to the old and new buffers is not possible.

Steps to Reproduce

Clarifying Information

Error Message

CRC for database trigger procedure <procedure> does not match schema. (3242)

Defect Number

Enhancement Number

Cause

Resolution

Follow the steps below to implement:

Write an include file which takes the old and new buffer as compile-time arguments.

This would require uniform naming of the OLD BUFFER across all applicable tables (For example: For table 'Customer ', use 'oldCustomer.'.

/* assignAuditFields.i */
DEFINE VARIABLE cDiffs AS CHARACTER   NO-UNDO.

BUFFER-COMPARE {2} TO {1} BINARY SAVE RESULT IN cDiffs.

ASSIGN {1}.AuditId   = GUID
       {1}.Timestamp = NOW
       {1}.Modfields = cDiffs.

Run the below code to update write triggers.

/* updateWriteTriggers.p */
FOR EACH _file WHERE _tbl-type EQ "T" NO-LOCK:
    FOR EACH _file-trig NO-LOCK
        WHERE _file-trig._file-recid EQ RECID(_file) AND 
              _file-trig._event      EQ "WRITE"      AND
              _file-trig._proc-name  NE ?:
        RUN updateFileTrig ( INPUT _file._file-name,
                             INPUT _file-trig._proc-name ).
    END.
END.

PROCEDURE updateFileTrig:
    DEFINE INPUT  PARAMETER pcFilename AS CHARACTER   NO-UNDO.
    DEFINE INPUT  PARAMETER pcProcName AS CHARACTER   NO-UNDO.
    
    DEFINE VARIABLE cFile AS CHARACTER   NO-UNDO.
    DEFINE VARIABLE cLine AS CHARACTER   NO-UNDO.
    
    cFile = REPLACE(pcProcName,".r",".p").
    FILE-INFO:FILE-NAME = cFile.
    cFile = FILE-INFO:FULL-PATHNAME.
    IF cFile NE ? THEN DO:
        INPUT FROM VALUE(cFile).
        OUTPUT TO VALUE(cFile + ".new").
        REPEAT:
            IMPORT UNFORMATTED cLine.
            PUT UNFORMATTED cLine CHR(10).
            
            IF cLine BEGINS "TRIGGER PROCEDURE FOR WRITE" THEN
                PUT UNFORMATTED CHR(10) "/~* Assign Audit Values ~*/~n" 
                                "~{assignAuditFields.i " + pcFilename + 
                                " old" + pcFilename + "~}~n"
                                "/~* End - Assign Audit Values ~*/~n".
        END.
        OUTPUT CLOSE.
        INPUT CLOSE.
        OS-COMMAND SILENT VALUE("move ~"" + cFile + "~" ~"" +
                                cFile + ".old~"").
        OS-COMMAND SILENT VALUE("move ~"" + cFile + ".new~" ~"" +
                                cFile + "~"").
    END.
END PROCEDURE.

With the trigger procedures up to date, now update the schema by calling the code in the below Progress knowledgebase article:

How to update database schema to reflect changed trigger procedures programatically

Workaround

Notes

References to Other Documentation:
Progress Article(s):
How to update database schema to reflect changed trigger procedures programatically

Keyword Phrase

Last Modified Date

9/12/2023 5:51 PM

Need to modify triggers (en masse) to assign newly added fields generically

Files(0)

Was this article helpful?

Was this article helpful?

Need to modify triggers (en masse) to assign newly added fields generically

Information

Related Articles