DB.Changelog: Keeping track of database changes using SQL Triggers and CodeMode = objectgenerator

In a customer project I was asked how you can keep track of database changes: Who changed what at which date and time. Goal was to track insert, update and delete for both SQL and object access.

This is the table that I created to keep the Change Log:

/// Changelog, keep track of changes to any table
Class ChangeLog.DB.ChangeLog Extends (%Persistent, %JSON.Adaptor)
{

/// Action 
Property Action As %String(%JSONFIELDNAME = "action", DISPLAYLIST = ",Create,Update,Delete", MAXLEN = 1, VALUELIST = ",0,1,2");

/// Classname of the %Persistent class
Property ClassName As %String(%JSONFIELDNAME = "table", MAXLEN = "") [ SqlFieldName = TableName ];

/// ID of the record
Property DRecordId As %String(%JSONFIELDNAME = "id") [ SqlFieldName = RecordId ];

/// Name of the user that made the change
Property DUsername As %String(%JSONFIELDNAME = "user") [ SqlFieldName = Username ];

/// ISO 8601 formatted UTC timestamp e.g 2023-03-20T15:14:45.7384083Z
Property ETimestamp As %String(%JSONFIELDNAME = "timestamp", MAXLEN = 28) [ SqlFieldName = Timestamp ];

/// Changed Data (only there for Action < 2)
Property NewData As %String(%JSONFIELDNAME = "changed-data", MAXLEN = "");

/// Old Data (only there for Action > 0)
Property OldData As %String(%JSONFIELDNAME = "old-data", MAXLEN = "");

}

The table for which I wanted to track changes was a simple Name-Value type:

Class ChangeLog.DB.NameValues Extends %Persistent
{

/// Name
Property name As %String;

Index nameIndex On name [ Unique ];

/// Value
Property value As %String(MAXLEN = "");

/// CreateOrUpdate
ClassMethod CreateOrUpdate(name As %String, value As %String = "") As %Status
{
    if ..nameIndexExists(name)
    {
        if (value = "")
        {
            return ..nameIndexDelete(name)
        }

        set record = ..nameIndexOpen(name)
    }
    else
    {
        set record = ..%New()
        set record.name = name
    }

    if (value = "") // Do not store!
    {
        return $$$OK        
    }

    set record.value = value

    return record.%Save()
}

}

I first attempted using an %OnAfterSave() method, which was easy enough, but it wasn't called when the update happened via SQL.
So I learned that I had to write a Trigger method instead, see https://docs.intersystems.com/healthconnectlatest/csp/docbook/DocBook.UI...

Once I got used to the specific syntax rules that apply, It was relatively straightforward to write a Trigger method, so I added the following Triggers to the NameValues class:

/// Write the DB changelog
Trigger LogUpdate [ Event = INSERT/UPDATE, Foreach = row/object, Time = AFTER ]
{
    New changelog
    set changelog = ##class(ChangeLog.DB.ChangeLog).%New()
    set changelog.ClassName = $CLASSNAME()
    set changelog.DRecordId = {ID}
    set changelog.Action = (%oper = "UPDATE")
    set changelog.DUsername = $SYSTEM.Process.UserName()
    set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)
    if (%oper = "UPDATE") // also add old data
    {
        set changelog.OldData = { "name": ({name*O}), "value": ({value*O}) }.%ToJSON()
    }
    set changelog.NewData = { "name": ({name*N}), "value": ({value*N}) }.%ToJSON()
    do changelog.%Save()
}

/// Write delete to changelog
Trigger LogDelete [ Event = DELETE, Foreach = row/object ]
{
    New changelog
    set changelog = ##class(ChangeLog.DB.ChangeLog).%New()
    set changelog.ClassName = $CLASSNAME()
    set changelog.DRecordId = {ID}
    set changelog.Action = 2
    set changelog.DUsername = $SYSTEM.Process.UserName()
    set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)
    set changelog.OldData = { "name": ({name*O}), "value": ({value*O}) }.%ToJSON()
    do changelog.%Save()
}

The above code has been specifically written for the Name-Values table, because it uses the specific syntax to access old- and new property values, like
{nameO} for the old value of the name property and {valueN} to represent the new value of the value property. In addition, I could have used
{value*C} to check out if the value property did change during a specific update.

Now that I was able to create a Trigger for a specific table, I wondered how I could change it to work the same but be completely generic, given that the Trigger syntax supports specific property names, but no wildcards.

In a recent post (see https://community.intersystems.com/post/how-add-webterminal-when-you-hav...) I had used [ CodeMode = objectgenerator ], and I wondered if I could use that here too.

Interestingly enough, section https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls... showed me right away how to list properties during object generation. And yes, CodeMode = objectgenerator is supported for Triggers too!

With that background, I set of to change the Triggers to be completely generic, and here is the resulting code:

/// This class adds logic to write records to ChangeLog.DB.ChangeLog for %Persistent classes
/// Extend from %Persistent before this class, like in:
///    Class ChangeLog.DB.NameValues Extends (%Persistent, ChangeLog.DB.ChangeLogWriter)
/// That way you avoid records with only an id column being created in the table ChangeLog.DB.ChangeLogWriter
Class ChangeLog.DB.ChangeLogWriter Extends %Persistent [ Abstract, PropertyClass = ChangeLog.DB.ChangeLogPropertyParameters ]
{

/// Write the DB changelog
Trigger LogUpdate [ CodeMode = objectgenerator, Event = INSERT/UPDATE, Foreach = row/object, Time = AFTER ]
{
    do %code.WriteLine(" new changelog")
    do %code.WriteLine(" set changelog = ##class(ChangeLog.DB.ChangeLog).%New()")
    do %code.WriteLine(" set changelog.ClassName = ..%ClassName()")
    do %code.WriteLine(" set changelog.DRecordId = {ID}")
    do %code.WriteLine(" set changelog.Action = (%oper = ""UPDATE"")")
    do %code.WriteLine(" set changelog.DUsername = $UserName")
    do %code.WriteLine(" set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)")
    do %code.WriteLine(" if (%oper = ""UPDATE"") {")
    do %code.WriteLine("     new data")
    do %code.WriteLine("     set data = {}")

    for i = 1:1:%compiledclass.Properties.Count()
    {
        set prop = %compiledclass.Properties.GetAt(i)
        set propName = prop.Parameters.GetAt("%JSONFIELDNAME")

        if (propName = "")
        {
            set propName = prop.Name
        }

        if (prop.Name '[ "%") && 'prop.Transient && (prop.Type ? 1"%Library"0.E)
        {
            do %code.WriteLine("     do data.%Set(""" _ propName _ """, {"_ prop.Name _ "*O})")
        }
    }

    do %code.WriteLine("     set changelog.OldData = data.%ToJSON()")
    do %code.WriteLine(" }")
    do %code.WriteLine(" set data = {}")

    for i = 1:1:%compiledclass.Properties.Count()
    {
        set prop = %compiledclass.Properties.GetAt(i)
        set propName = prop.Parameters.GetAt("%JSONFIELDNAME")

        if (propName = "")
        {
            set propName = prop.Name
        }

        if (prop.Name '[ "%") && 'prop.Transient && (prop.Type ? 1"%Library"0.E)
        {
            do %code.WriteLine(" if {"_ prop.Name _ "*C} && '$ISOBJECT({"_ prop.Name _ "*N}) {")
            do %code.WriteLine("     do data.%Set(""" _ propName _ """, {"_ prop.Name _ "*N})")
            do %code.WriteLine(" }")
        }
    }
    do %code.WriteLine(" set changelog.NewData = data.%ToJSON()")
    do %code.WriteLine(" do changelog.%Save()")
    return $$$OK
}

/// Write delete to changelog
Trigger LogDelete [ CodeMode = objectgenerator, Event = DELETE, Foreach = row/object ]
{
    do %code.WriteLine(" new changelog")
    do %code.WriteLine(" set changelog = ##class(ChangeLog.DB.ChangeLog).%New()")
    do %code.WriteLine(" set changelog.ClassName = ..%ClassName()")
    do %code.WriteLine(" set changelog.DRecordId = {ID}")
    do %code.WriteLine(" set changelog.Action = 2")
    do %code.WriteLine(" set changelog.DUsername = $UserName")
    do %code.WriteLine(" set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)")
    do %code.WriteLine(" new data")
    do %code.WriteLine(" set data = {}")

    for i = 1:1:%compiledclass.Properties.Count()
    {
        set prop = %compiledclass.Properties.GetAt(i)
        set propName = prop.Parameters.GetAt("%JSONFIELDNAME")

        if (propName = "")
        {
            set propName = prop.Name
        }

        if (prop.Name '[ "%") && 'prop.Transient && (prop.Type ? 1"%Library"0.E)
        {
            do %code.WriteLine(" do data.%Set(""" _ propName _ """, {"_ prop.Name _ "*O})")
        }
    }

    do %code.WriteLine(" set changelog.OldData = data.%ToJSON()")
    do %code.WriteLine(" do changelog.%Save()")
    return $$$OK
}

I have tested this of course with the Name-Values class:

/// Test table with name values
/// Each change must be recorded in the ChangeLog Table
Class ChangeLog.DB.NameValues Extends (%Persistent, ChangeLog.DB.ChangeLogWriter)
{

/// Name
Property name As %String;

Index nameIndex On name [ Unique ];

/// Value
Property value As %String(MAXLEN = "");

/// CreateOrUpdate
ClassMethod CreateOrUpdate(name As %String, value As %String = "") As %Status
{
    if ..nameIndexExists(name)
    {
        if (value = "")
        {
            return ..nameIndexDelete(name)
        }

        set record = ..nameIndexOpen(name)
    }
    else
    {
        set record = ..%New()
        set record.name = name
    }

    if (value = "") // Do not store!
    {
        return $$$OK        
    }

    set record.value = value

    return record.%Save()
}

}

This worked great! After executing the following 3 commands:
1. Inserting a record into NameValues using

w ##class(ChangeLog.DB.NameValues).CreateOrUpdate("name", "value1")

2. Updating that instance using

w ##class(ChangeLog.DB.NameValues).CreateOrUpdate("name", "value2")

3. Delting all records using

delete FROM ChangeLog_DB.NameValues

This is what the the ChangeLog looked like:

Then, I changed all 12 classes extending %Persistent defined in the customer project to extend ChangeLog.DB.PersistentWithChangeLog instead. This lead to a couple of changes (already in the code above):
1. I wanted to exclude properties %%OID en %Concurrency properties which are part of the class by default
2. Transient Properties need to be excluded, as these do not exist as SQL properties.
3. Given that we log the old and new data as JSON, it made sense to use the "%JSONFIELDNAME" parameter as the property name when that is defined.

I did ran into one unexplained issue, where a class using a unique index named "UniqueIndex" compiled when extending %Persistent, but no longer when extending ChangeLog.DB.PersistentWithChangeLog. Changing the Index name to UniqueIndex2 solved the issue.

To summarize, I am really excited about the power of [ CodeMode = objectgenerator ], and I hope this article is helpful to you too!