Changelog

Overview

The Changelog functionality is used to track CRUD changes made to selected tables and fields in the database. 

If a change is made by the same user within 60 seconds of the previous change, the existing Changelog record is updated.

The fields that are audited are documented in each module.

How it works

Every table requires a USERUPDATED field which is populated from the front end on every Insert, Update or Delete.

The changes are recorded in the table CHANGELOG - Fields include:

• CHANGELOGID, 

• PKID - Primary key 

• FKID - Foreign key of the Parent table 

• CRUDID

• USERNAME

• TABLENAME

• FIELDNAME

• NEWVALUE (truncated to 100 chars)

A Trigger is created for every table that is monitored and is named: TR_TABLENAME_CHANGELOG

The trigger updates the CHANGELOG table using the stored procedure TR_TABLENAME_CHANGELOG

The procedure CHANGELOG_SELECT is used to provide a user-friendly log of changes.

Keys

CRUDID

• 1 - Insert - One record for every field that has value.

• 2 - Update - One record for every field that has a new value.

• 3 - Delete - One record linked to the table only.

Steps to implement

Document the fields that will be checked - in the module documentation under a heading Changelog

Create a trigger that updates Changelog after insert, update and delete - naming: TR_TABLENAME_CHANGELOG

Update procedure CHANGELOG_SELECT_CAPTION - converts NAMES to CAPTIONS

Create procedure to select the Changelog  - naming: TABLENAME_CHANGELOG

Add a menu item to View Changelog