Tracking Data Changes - Audit Log - 1 of 2

Fabio - does this work if you make changes via Object access as well, or just SQL?

Great article, Fabio!

The problem can be that when data changes, then the whole record is copied, i.e. also data which does not change.

That can be solved by checking m%<property> value. It has false positives sometimes (when you change value back, i.e.: a-b-a) and calling log code only if property value has changed.

Forgot that it's only about object access. Still, you have old and new value, so:

Do %code.WriteLine($Char(9,9,9)_"Set tOldValue = {"_tProperty.SqlFieldName_"*O}")
Do %code.WriteLine($Char(9,9,9)_"Set tNewValue = {"_tProperty.SqlFieldName_"*N}")
Do %code.WriteLine($Char(9,9,9)_"if {" _ tProperty.SqlFieldName _ "*C},tOldValue'=tNewValue {")

All data changes are logged in a common table.

Why not generate a separate log table for each class, inheriting from logged class, so 1 change == 1 record in log table. It also gives you the possibility of writing "easy" diff tool, so audit results can be displayed conveniently.

If table person has a column "photo" with binary data (stream) containing the photography then each and every time yhe user changes the picture  the role stream is recorded (consuming disk space). 

That problem can be solved by creating immutable stream-containing class.

/// Immutable wrapper for stream
Class User.Document Extends %Persistent
{

/// Global identifier to give to user (prevent id traversal)
Property guid As %String [ InitialExpression = {$system.Util.CreateGUID()}, Required ];

/// Creation date time
Property createDateTime As %TimeStamp [ InitialExpression = {$ZDATETIME($ZTIMESTAMP, 3, 1, 3)};

/// File name as supplied by user (only for display purposes)
Property fileName As %String(MAXLEN =1000) [ Required ];

/// File stream
Property content As %FileBinaryStream;

/// User who uploaded the file
Property user As %String [ InitialExpression = {$username} ];

/// Add new stream
/// realName - real name of a stored file
/// suppliedName - name supplied by user, used only for display purposes
/// stream - stream with data
Method %OnNew(realName As %String = "", suppliedName As %String = "", stream As %Stream.Object = {##class(%FileBinaryStream).%New()}) As %Status [ Private, ServerOnly = 1 ]
{
    #dim sc As %Status = $$$OK
    set ..fileName = ##class(%File).GetFilename(suppliedName)
    set ..content.Filename = realName
    set sc = ..content.CopyFromAndSave(stream)
    quit sc
}

/// Serve file on web
Method serve() As %Status
{
    #dim sc As %Status = $$$OK
    #dim %response As %CSP.Response
    kill %request.Data
    set %request.Data("STREAMOID",1)= ##class(%CSP.StreamServer).Encrypt(..content.%Oid())
    if ##class(%CSP.StreamServer).OnPreHTTP() {
        set %response.Headers("Content-Disposition")="attachment; filename*=UTF-8''"_##class(%CSP.Page).EscapeURL(..fileName,"UTF8")
        set st = ##class(%CSP.StreamServer).OnPage()
    }

    quit sc
}

That way your audit log table would contain only id references.

This is great stuff, many thanks Fabio.

Is there a way to get child tables created as array property projections (due to a property in a persistent class that's 'as array of' a %SerialObject class) to inherit  the generated trigger, as well as the parent persistent class inheriting this? Or is there a way otherwise to capture the result of inserts/updates/deletes into member rows of these collections via the generated trigger call in %SQLAfterTriggers?  

For direct 'as [%SerialObject class]' properties (not collection 'as array of'), this generated 'elseif property type=serial' logic works for me to create entries in a child audit table devoted to fieldname, old value, new value (note it checks disk against old, as pNew values still match pOld for the serial objects at this point in the aftersave):

 While tKey '= "" {
    set tColumnNbr = $Get($$$EXTPROPsqlcolumnnumber($$$pEXT,%classname,tProperty.Name))
    set tColumnName = $Get($$$EXTPROPsqlcolumnname($$$pEXT,%classname,tProperty.Name)) 
    set tPropertyType = $Get($$$EXTPROPtype($$$pEXT,%classname,tProperty.Name))
    set tPropertyTypeCategory = $Get($$$EXTPROPtypecategory($$$pEXT,%classname,tProperty.Name))
    set tPropertyOnDisk = $Get($$$EXTPROPondisk($$$pEXT,%classname,tProperty.Name))
    set tPropertyOnDisk = $replace(tPropertyOnDisk,"(id)","({id})")

    If tColumnNbr '= "" {
        Do %code.WriteLine($Char(9,9,9)_"if {" _ tProperty.SqlFieldName _ "*C} {") 
 
                 //%code.WriteLine logic from your example

        Do %code.WriteLine($Char(9,9,9)_"}elseif ("""_tPropertyTypeCategory_"""=""serial"") { ")
        Do %code.WriteLine($Char(9,9,9,9)_"if ##class(%Dictionary.CompiledClass).%ExistsId("""_tPropertyType_""") {")
        Do %code.WriteLine($Char(9,9,9,9,9)_"set (tOld,tNew)="""",(ptr,tChildCounter)=0")
        Do %code.WriteLine($Char(9,9,9,9,9)_"set tChildList = "_tPropertyOnDisk)
        Do %code.WriteLine($Char(9,9,9,9,9)_"if $listvalid(tChildList) {")
        Do %code.WriteLine($Char(9,9,9,9,9,9)_"while $listnext(tChildList,ptr,value) {")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9)_"set tChildCounter=tChildCounter+1")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9)_"set tNew(tChildCounter)=value")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9)_"set tOld(tChildCounter)=$listget({"_tProperty.SqlFieldName_"*O},tChildCounter)")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9)_"if tOld(tChildCounter)'=tNew(tChildCounter) {")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9)_"set tChildObj=##class(%Dictionary.CompiledClass).%OpenId("""_tPropertyType_""")")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9)_"if $ISOBJECT(tChildObj) {")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9,9)_"Set tAuditDelta = ##class(Sample.AuditDelta).%New(ParentId)") 
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9,9)_"do tAuditDelta.AuditParentSetObjectId(ParentId)")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9,9)_"set tAuditDelta.AuditDeltaField = tChildObj.Properties.GetObjectIdAt((tChildCounter+1))")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9,9)_"set tAuditDelta.AuditDeltaOldValue = tOld(tChildCounter)")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9,9)_"set tAuditDelta.AuditDeltaNewValue = tNew(tChildCounter)")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9,9)_"set tSC = $$$ADDSC(tSC,tAuditDelta.%Save())") 
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9,9)_"if $$$ISERR(tSC) $$$ThrowStatus(tSC)") 
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9)_"}")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9,9)_"kill tChildObj")
        Do %code.WriteLine($Char(9,9,9,9,9,9,9)_"}")
        Do %code.WriteLine($Char(9,9,9,9,9,9)_"}")
        Do %code.WriteLine($Char(9,9,9,9,9)_"}")  
        Do %code.WriteLine($Char(9,9,9,9)_"}")
        Do %code.WriteLine($Char(9,9,9)_"}") 

I kept the if-elseif in the generated code (rather than generating conditionally) because object %Save satisfies the trigger syntax *C (pChanged) check, but SQL update to properties in the serial object doesn't in my tests (the former dumps the full serial object list from the pOld and pNew arrays  into the child audit table's old value and new value fields, versus the serial list creating a child entry for each field updated in the serial object).  The GetObjectIdAt(counter+1) isn't really feasible based on key not always equaling list location, but it's a place holder until I refine it.

I did try your solution in Cache 2008. When I enter the Audit class in Studio it is all good, can save and compile alright. However, when I try to put in the AuditBase class I get loads of errors relating to 'Unknown Trigger Keyword'... what is wrong here? Is this to do with the version of Cache?

Thanks in advance, Rene.

💡 This article is considered as InterSystems Data Platform Best Practice.