[Tutorial] Creating an audit trail

[gkick]

Some time ago I needed to implement an audit trail for some tables in order to find out what within a record was changed, when and by whom.
With some pointers of Sliderule and Villeroy I managed to get exactly what was needed. So if you should happen to have a similar need now or in the future here are the steps involved.

First up you need a split database with the HSQL Engine Version 2.5 or higher. As I do not have documentation of earlier versions, I do not know if it works for older versions.

You can download the latest HSQL package from here: https://sourceforge.net/projects/hsqldb/files/

If you do not happen to have a split setup already either use the split database wizard viewtopic.php?f=83&t=61183 Wizard

or use this procedure viewtopic.php?f=21&t=77543 FreeHSQL

to establish a front/back-end database, the latter choice offering more features such as automatic registration, extracting forms to standalone Writer forms etc.

So now you got a nice clean and most likely empty database. Here is the procedure to set up an audit trail for a table.

⦁ Create a new table and call it tblContacts

contact_id integer primary key auto increment
first_name varchar
lastname varchar
lastedit timestamp
changedby varchar

⦁ Once back in the database container go to Tools, SQL and enter as follows:

Code: Select all

⦁	ALTER TABLE "tblContacts" ADD PERIOD FOR SYSTEM_TIME(rs, re) ADD COLUMN rs TIMESTAMP GENERATED ALWAYS AS ROW START ADD COLUMN re TIMESTAMP GENERATED ALWAYS AS ROW END

⦁ then click Execute and then Close

Next add the following command

Code: Select all

⦁	ALTER TABLE "tblContacts" ADD SYSTEM VERSIONING

⦁ then click Execute and then Close

⦁ Create a query in SQL View and enter the following

Code: Select all

SELECT "contact_id", "first_name", "lastname", "lastedit", "changedby","RS","RE" FROM "tblContacts" FOR SYSTEM_TIME FROM CURRENT_TIMESTAMP - 2 YEAR TO CURRENT_TIMESTAMP;

and save the query as qryContactTableChangeLog

⦁ Now create a data entry form based on the tblContacts only displaying the contact_id, first_name, lastname fields and save the form as frmContactsDataEnty

⦁ Open the form and add 3 or 4 records of some imaginary people and close the form

⦁ Now create another form based on the query qryContactTableChangeLog, displaying all the fields and save the form as frmContactChangeLog

⦁ Finally create a macro by entering (copy/paste) as follows into any module

Code: Select all

⦁	Sub onUpdateContact( oEvent as object )
    	dim oDataForm as variant
    	dim oStatement as variant
   	dim oResultSet as variant
    if oEvent.Source.SupportsService( "com.sun.star.form.component.DataForm" ) then
        if oEvent.Action = com.sun.star.sdbc.ChangeAction.UPDATE then
            oDataForm = oEvent.Source
            oStatement = oDataForm.ActiveConnection.CreateStatement
            oStatement.EscapeProcessing = False
            oResultSet = oStatement.executequery( "CALL CURRENT_TIMESTAMP" )
            oResultSet.next
            oDataForm.updateTimeStamp( oDataForm.FindColumn( "lastedit" ), oResultSet.getTimestamp( 1 ) )
        end if
    end if
End Sub

⦁ Next open the frmContactsDataForm in design mode and assign the macro to the
Before record action Event and we are done.

⦁ Now go to back to the Data Entry Form and change some of the existing names, close the form an then check out the Changelog Form

Notice what and when things changed and the last edit field being separated into 2 fields. The change history is not visible in the actual table.
More detail on this can be found here

viewtopic.php?f=39&t=99274

aah, to incorporate who did the change the user name has to be incorporated into the macro somehow.

Hope this might be useful to some folks
Also there is a more sophisticated approach using triggers and stored procedures to have just one change log for all tables. Currently studying some postgres and MySql examples for adaptation.