Tracking Data Changes - Audit Log - 1 of 2
The common requirement in many applications is logging of data changes in a database - which data has changed, who changed them and when (audit logging). There are many articles about this question and there are different approaches on how to do that in Caché.
I'm sharing a mechanism that can help you implement your framework to track and record data changes. This mechanism creates a trigger through an "objectgenarator" method once your persistent class inherits from the "Audit Abstract Class" (Sample.AuditBase). Since your persistent class inherits Sample.AuditBase, when you compile your persistent class the trigger for auditing changes will be generated automatically.
This is the class where the changes will be recorded.
Audit Abstract Class
This is the abstract class that your persistent class will inherits from. This class contains the trigger method (objectgenerator) that knows how to identify which field has been modified, who changed that, what are the old and new values, etc., besides writing the changes in the audit table (Sample.Audit).
Data Class (Persistent Class)
This is the user data class that the user (application) make changes, create records, delete records, do whatever you allow him to do. :). In summary, this is usually your %Persistent class.
To start traking and record changes you will need to inherits the Persistent Class from the Abstract Class (Sample.AuditBase).
Since you have inherited the data class Sample.Person) from the Audit Abstract Class (Sample.AuditBase) you are able to insert data, make changes and look at the changes recorded on the Audit Class (Sample. Audit).
In order to test that you will need to create a Test() class method on the Sample.Person class or any other class of your choice.
Run the Test() method:
Parameter 1 will kill extent from Sample.Person and Sample.Audit classes.
The Test class method does the following:
- Insert a new person with name "TEST";
- Shows the insert result;
- Update the person "TEST" to "TEST ABC";
- Show the update result;
Now you can check the audit log table. In order to do that open the System Managment Portal->System Explore->SQL. (Do not forget to switch to your namespace)
Run the following SQL command and check the results:
Note that the OldValue is "TEST" and the NewValue is "TEST ABC". From now on you can make your own tests by changing the name of "TEST ABC" to "Your own name" and or change the Age values, for example. See:
Considering that you have implemented the auditing mechanism bellow, on your computer launch the Studio (or Atelier), open the Persistent Class (Sample.Person) and inspect the intermediate code generated after compiling Sample.Person class. To do so type Ctrl + Shift + V (View Other Source Code) - inspect .INT. Scroll down up to zSaveAuditAfterExecute label and take a look at the generated code:
It is simple to implement audit logging based on rolling out old data. You do not need additional tables. Maintenance is simple, too. If you decide to remove old data, then it is the matter of one SQL.
If you need to implement audit logging in more tables, just inherits from the Abstract Class (Sample.AuditBase)
Make changes according to your need. e.g.: record changes on Streams.
Record just modified fields. Do not save the entire record changed.
The problem can be that when data changes, then the whole record is copied, i.e. also data which does not change.
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).
Another disadvantage is that the complexity of each table supporting audit logging increases. You must have in mind all the time that retrieving the records can not be simple. You always have to use the
SELECT clause with condition: "...WHERE Status = active" or considering some "DATE INTERVAL"
All data changes are logged in a common table.
Think about transcation an rollbacks.
Aduting is an important requirement for some applications to be efficient. Typically, to determine data changes, application developers must implement a custom tracking method in their applications by using a combination of triggers, timestamp columns, and additional tables. Creating these mechanism usually involves a lot of work to implement, leads to schema updates, and often carries a high performance overhead. This is a simple example that can help you to start creating your own framework.
Take a look at the next article!