Search This Blog

07 August 2011

Simplify tracking changes with sysDatabaseLog on field-level

SysDatabaseLog is a great help to log any kind of data-changes and it is very easy to use. The setup of logs allows you to configure tracking the changes until the field-level. But unfortunately it is painful to look for the changes for these field-level based database-logs if the same table is already configured to track all modification because the information about the changed field is saved in a container. Here now a suggestion to simplify the tracking and reduce the need of ressources during the data-investigation by flagging database-logs that are done on fields that are tracked:

1 Add a new column hasTrackedFieldChanged (EDT: NoYesId) in the SysDatabaseLog-table
2 Create a new method hasTrackedFieldChanged in the Application-class
boolean hasTrackedFieldChanged(TableId _tableId, container changedFields, DatabaseLogType _logType)
{
    boolean hasField = false;
    DatabaseLog dbLog;//contains the information about tracked tables/ fields
    int counter;
    FieldId fieldId, extFieldId;
    ;

    if (conlen(changedFields) > 0) //are there items in the container?
    {
        for(counter = 0; counter <= conlen(changedFields); counter++)  // loops all elements in the container
        {
            extFieldId = conpeek(changedFields, counter); //gets the extended fieldId

            if (extFieldId != 0) //extFieldId is 0 if it concerns the entire table
            {
                fieldId = fieldExt2Id(extFieldId); //gets the fieldId based on the extFieldId

                SELECT FIRSTONLY
                    recID
                FROM
                    dbLog
                WHERE
                    dbLog.logfield == fieldId       //is the changed field in the list of tracked fields...
                    && dbLog.LogTable == _tableId   //...for that table...
                    && dbLog.LogType == _logType ;  //...and the current action (Update)
                if(dbLog)
                {
                    hasField = true;
                    break; //no need to continue
                }
            }
        }
    }
    return hasField;
}
3 Change the logUpdate-method in the Application class
sysDatabaseLog.hasTrackedFieldChanged =
            this.hasTrackedFieldChanged(recordUpdated.TableId, changedFields, DatabaseLogType::Update);
It is now easy to filter all logs for tracked fields:

No comments:

Post a Comment