9.1d ABL Trigger

Richard Grimmett · Aug 19, 2015

I know 9.1d is old hat, but we really cannot migrate currently.

I am trying to develop a WRITE trigger which I can use across multiple tables. These tables already have some complex triggers and so I simply want to install a run statement into the existing trigger to my new routine. The routine I have developed looks like this:

(trigw.p)

Code:

Def Parameter BUFFER workrec for workrec.
Def Parameter BUFFER oldworkrec for workrec.
Def INPUT Parameter TKey AS CHARACTER.

DEFINE VARIABLE TKeyVal as CHARACTER NO-UNDO.
DEFINE VARIABLE TKeyValFld as HANDLE NO-UNDO.
DEFINE VARIABLE TKeyValFldType as CHARACTER NO-UNDO.

DEFINE VARIABLE Action AS CHARACTER INIT "WRITE" NO-UNDO.
DEFINE VARIABLE i AS INTEGER.
DEFINE VARIABLE j AS INTEGER NO-UNDO.
DEFINE VARIABLE hOldRecord AS HANDLE  NO-UNDO.
DEFINE VARIABLE hNewRecord AS HANDLE  NO-UNDO.
DEFINE VARIABLE hOldField AS HANDLE  NO-UNDO.
DEFINE VARIABLE oldFieldVal AS CHARACTER  NO-UNDO.
DEFINE VARIABLE hNewField AS HANDLE  NO-UNDO.
DEFINE VARIABLE newFieldVal AS CHARACTER  NO-UNDO.
DEFINE VARIABLE cChangedFields  AS CHARACTER  NO-UNDO.
DEFINE VARIABLE iChangedFields AS INTEGER  NO-UNDO.

j=1.
BUFFER-COMPARE workrec TO oldworkrec SAVE RESULT IN cChangedFields NO-ERROR.

IF cChangedFields <> "" THEN DO:
  ASSIGN
  hNewRecord = BUFFER  workrec:HANDLE
  hOldRecord = BUFFER  oldworkrec:HANDLE.
  tKeyValFld = hNewRecord:BUFFER-FIELD(TKey).
  tKeyValFldType = tKeyValFld:DATA-TYPE.
  tKeyVal=tKeyValFld:STRING-VALUE.
   CREATE Monitor.
  ASSIGN

     Monitor.tDate = TODAY
     Monitor.tTime = STRING(TIME,"HH:MM:SS")
     Monitor.Action = Action
     Monitor.TableName = hNewRecord:NAME
     Monitor.TableKey = TKey
     Monitor.TableKeyType=tKeyValFldType
     Monitor.TableKeyValue = TKeyVal.

  DO iChangedFields = 1 TO NUM-ENTRIES(cChangedFields):
   hOldField = hOldRecord:BUFFER-FIELD(ENTRY(iChangedFields, cChangedFields)).
   hNewField = hNewRecord:BUFFER-FIELD(ENTRY(iChangedFields, cChangedFields)).

   IF hOldField:EXTENT = 0 THEN DO:
     oldFieldVal = hOldField:STRING-VALUE.
     newFieldVal = hnewField:STRING-VALUE.

     ASSIGN
       Monitor.ft[j] = hOldField:DATA-TYPE    
       Monitor.fov[j] = oldFieldVal
       Monitor.fnv[j] = newFieldVal
       Monitor.fn[j] = ENTRY(iChangedFields, cChangedFields).
     j = j + 1.
    
   END.
   ELSE DO:  
     DO i = 1 TO hOldField:EXTENT:
         oldFieldVal = STRING(hOldField:BUFFER-VALUE(i)).
       newFieldVal = STRING(hnewField:BUFFER-VALUE(i)).
       IF oldFieldVal<>newFieldVal THEN DO:
         ASSIGN
           Monitor.ft[j] = hOldField:DATA-TYPE
           Monitor.fov[j] = oldFieldVal
           Monitor.fnv[j] = newFieldVal
           Monitor.fn[j] = ENTRY(iChangedFields, cChangedFields) + "@" + STRING(i).
         j = j + 1.
       END.
     END.
   END.
  END.
END.

and I call it by inserting RUN trig4(workrec. oldworkrec, "wrecid") into the existing trigger.

This all works fine for the workrec table, but I am trying to find a way of being able to do this for all tables without needing to modify my code, and I cannot find a way of not needing to define the BUFFER parameters as explicitly being required for specific tables. Is there a way I can get around this please?

Thanks
Richard

Stefan · Aug 19, 2015

0. please add code tags around code
1. pass the dynamic handles of the buffers instead of the static buffers:

Code:

/* note the extra parenthesis required when passing buffer lala:handle */
RUN trig4 ( ( BUFFER workrec:HANDLE ), ( BUFFER oldworkrec:HANDLE ), "wrecid" ).

2. and use them:

Code:

DEFINE INPUT PARAMETER i_hbnew AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER i_hbold AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER i_ckey AS CHARACTER NO-UNDO.

/* replacing the rest of the static code with dynamic code is for you */

Richard Grimmett · Aug 19, 2015

Thanks Stefan, that has given me hope. I confess that I am a self taught newbie on Progress, having spent most of my professional life on Oracle, SQL Server and MSSQL.

I have made the changes you suggested, and defined variables for the Buffers and then created the buffers. My problem is now with the BUFFER-COMPARE - the compiler is showing "Unknown or ambiguous table newtab. (725)".

I include the code as it currently stands below. Any hints or solutions will be most welcome:

Code:

DEFINE INPUT PARAMETER hnewtab AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER holdtab AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER TKey AS CHARACTER NO-UNDO.

DEFINE VARIABLE newtab AS HANDLE     NO-UNDO.
DEFINE VARIABLE oldtab AS HANDLE     NO-UNDO.

DEFINE VARIABLE TKeyVal as CHARACTER NO-UNDO.
DEFINE VARIABLE TKeyValFld as HANDLE NO-UNDO.
DEFINE VARIABLE TKeyValFldType as CHARACTER NO-UNDO.

DEFINE VARIABLE Action AS CHARACTER INIT "WRITE" NO-UNDO.
DEFINE VARIABLE i AS INTEGER.
DEFINE VARIABLE j AS INTEGER NO-UNDO.
DEFINE VARIABLE hOldRecord AS HANDLE     NO-UNDO.
DEFINE VARIABLE hNewRecord AS HANDLE     NO-UNDO.
DEFINE VARIABLE hOldField AS HANDLE     NO-UNDO.
DEFINE VARIABLE oldFieldVal AS CHARACTER     NO-UNDO.
DEFINE VARIABLE hNewField AS HANDLE     NO-UNDO.
DEFINE VARIABLE newFieldVal AS CHARACTER     NO-UNDO.
DEFINE VARIABLE cChangedFields   AS CHARACTER  NO-UNDO.
DEFINE VARIABLE iChangedFields AS INTEGER    NO-UNDO.




j=1.

CREATE BUFFER newtab FOR TABLE hnewtab.

CREATE BUFFER oldtab FOR TABLE holdtab.
BUFFER-COMPARE newtab TO oldtab SAVE RESULT IN cChangedFields NO-ERROR.

IF cChangedFields <> "" THEN DO:
    ASSIGN
        hNewRecord = BUFFER  newtab:HANDLE
        hOldRecord = BUFFER  oldtab:HANDLE.
 
    tKeyValFld = hNewRecord:BUFFER-FIELD(TKey).
    tKeyValFldType = tKeyValFld:DATA-TYPE.
    tKeyVal=tKeyValFld:STRING-VALUE.
  
    CREATE Monitor.
        ASSIGN

        Monitor.tDate = TODAY
              Monitor.tTime = STRING(TIME,"HH:MM:SS")
        Monitor.Action = Action
        Monitor.TableName = hNewRecord:NAME
        Monitor.TableKey = TKey
        Monitor.TableKeyType=tKeyValFldType
        Monitor.TableKeyValue = TKeyVal.

    DO iChangedFields = 1 TO NUM-ENTRIES(cChangedFields):
      
    hOldField = hOldRecord:BUFFER-FIELD(ENTRY(iChangedFields, cChangedFields)).
    hNewField = hNewRecord:BUFFER-FIELD(ENTRY(iChangedFields, cChangedFields)).

    IF hOldField:EXTENT = 0 THEN DO:
        oldFieldVal = hOldField:STRING-VALUE.
        newFieldVal = hnewField:STRING-VALUE.

            ASSIGN
            Monitor.ft[j] = hOldField:DATA-TYPE           
                    Monitor.fov[j] = oldFieldVal         
                    Monitor.fnv[j] = newFieldVal
            Monitor.fn[j] = ENTRY(iChangedFields, cChangedFields).
        j = j + 1.
          
    END.
    ELSE DO:      
        DO i = 1 TO hOldField:EXTENT:
               oldFieldVal = STRING(hOldField:BUFFER-VALUE(i)).
            newFieldVal = STRING(hnewField:BUFFER-VALUE(i)).
            IF oldFieldVal<>newFieldVal THEN DO:
                    ASSIGN
                    Monitor.ft[j] = hOldField:DATA-TYPE
                            Monitor.fov[j] = oldFieldVal         
                            Monitor.fnv[j] = newFieldVal
                    Monitor.fn[j] = ENTRY(iChangedFields, cChangedFields) + "@" + STRING(i).
                j = j + 1.
            END.
        END.
    END.
  
    END.
END.

Osborne · Aug 19, 2015

When using BUFFER-COMPARE for handles the syntax is slightly different as you have to use BUFFER-COPY method instead of BUFFER-COPY statement:

Code:

DEFINE VARIABLE vEqual AS LOGICAL NO-UNDO.

vEqual = newtab:BUFFER-COMPARE(oldtab, "CASE-SENSITIVE").
IF NOT vEqual THEN DO:

Richard Grimmett · Aug 19, 2015

Thanks Osborne, but that will only give me whether they are equal or not. I already know they are not equal and I was using BUFFER-COMPARE to extract the differences. Are you suggesting I should compare on a field by field basis?

Thanks
Richard

Osborne · Aug 19, 2015

That is a problem as the BUFFER-COMPARE method does not extract the differences like the BUFFER-COMPARE statement does. The only option would be to compare each field for the buffer handles. A very basic example:

Code:

DO i = 1 TO newtab:NUM-FIELDS:
   IF newtab:BUFFER-FIELD(i):EXTENT = 0 THEN DO:
      IF newtab:BUFFER-FIELD(i):BUFFER-VALUE <> oldtab:BUFFER-FIELD(i):BUFFER-VALUE THEN
      /* Add to list */
   END.
   ELSE
   DO j = 1 TO newtab:BUFFER-FIELD(i):EXTENT:
      IF newtab:BUFFER-FIELD(i):BUFFER-VALUE(j) <> oldtab:BUFFER-FIELD(i):BUFFER-VALUE(j) THEN
      /* Add to list */
   END.
END.

Richard Grimmett · Aug 19, 2015

Thanks again to Stefan and Osborn. I now have a working procedure which I can use for any table in my database. For the record, and anyone looking for the same, here is the solution:

Code:

DEFINE INPUT PARAMETER hnewtab AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER holdtab AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER TKey AS CHARACTER NO-UNDO.

DEFINE VARIABLE TKeyVal as CHARACTER NO-UNDO.
DEFINE VARIABLE TKeyValFld as HANDLE NO-UNDO.
DEFINE VARIABLE TKeyValFldType as CHARACTER NO-UNDO.

DEFINE VARIABLE Action AS CHARACTER INIT "WRITE" NO-UNDO.
DEFINE VARIABLE i AS INTEGER.
DEFINE VARIABLE j AS INTEGER NO-UNDO.
DEFINE VARIABLE k as INTEGER INIT 1 NO-UNDO.

    tKeyValFld = hnewtab:BUFFER-FIELD(1).
        tKeyValFldType = tKeyValFld:DATA-TYPE.
        tKeyVal=tKeyValFld:STRING-VALUE.

    CREATE Monitor.
        ASSIGN
        Monitor.tDate = TODAY
              Monitor.tTime = STRING(TIME,"HH:MM:SS")
        Monitor.Action = Action
        Monitor.TableName = hnewtab:NAME
        Monitor.TableKey = TKey
        Monitor.TableKeyType=tKeyValFldType
        Monitor.TableKeyValue = TKeyVal.

    DO i = 1 TO hnewtab:NUM-FIELDS:
           IF hnewtab:BUFFER-FIELD(i):EXTENT = 0 THEN DO:
                  IF hnewtab:BUFFER-FIELD(i):BUFFER-VALUE <> holdtab:BUFFER-FIELD(i):BUFFER-VALUE THEN DO:
                    ASSIGN
                    Monitor.ft[k] = hnewtab:BUFFER-FIELD(i):DATA-TYPE           
                            Monitor.fov[k] = holdtab:BUFFER-FIELD(i):STRING-VALUE         
                            Monitor.fnv[k] = hnewtab:BUFFER-FIELD(i):STRING-VALUE
                    Monitor.fn[k] = hnewtab:BUFFER-FIELD(i):NAME.
                k = k + 1.
            END.          
           END.
           ELSE
               DO j = 1 TO hnewtab:BUFFER-FIELD(i):EXTENT:
                      IF hnewtab:BUFFER-FIELD(i):BUFFER-VALUE(j) <> holdtab:BUFFER-FIELD(i):BUFFER-VALUE(j) THEN DO:
                          ASSIGN
                        Monitor.ft[k] = hnewtab:BUFFER-FIELD(i):DATA-TYPE
                                Monitor.fov[k] = holdtab:BUFFER-FIELD(i):STRING-VALUE(j)         
                                Monitor.fnv[k] = hnewtab:BUFFER-FIELD(i):STRING-VALUE(j)
                        Monitor.fn[k] = hnewtab:BUFFER-FIELD(i):NAME + "@" + STRING(j).
                    k = k + 1.
                END.              
               END.
    END.

Stefan · Aug 20, 2015

You can access a non-extent field with extent index 0, so you can deduplicate the compare to something like:

Code:

DO ifield = 1 TO hnewtab:NUM-FIELDS:
   ASSIGN
      hfnew = hnewtab:BUFFER-FIELD( ifield )
      hfold = holdtab:BUFFER-FIELD( ifield )
      .
   DO iextent = IF hfnew:EXTENT = 0 THEN 0 ELSE 1 TO hfnew:EXTENT:
      IF hfnew:BUFFER-VALUE( iextent ) <> hfold:BUFFER-VALUE( iextent ) THEN DO:
         /* the rest of the handling */
      END.
   END.
END.

9.1d ABL Trigger

Richard Grimmett

New Member

Stefan

Well-Known Member

Richard Grimmett

New Member

Osborne

Active Member

Richard Grimmett

New Member

Osborne

Active Member

Richard Grimmett

New Member

Stefan

Well-Known Member