The changes done in a given table, column and record are viewed by showing the corresponding record ID or UI of the records in the database.
In order to track audit information the system administrator needs to perform two tasks:
- Enable the audit trail for one or more tables in the system
- Run the 'Update Audit Trail infrastructure' process
In the following sections, a step-by-step guide with more detailed information is provided.
Enabling audit trail for a table
Enabling/disabling the audit trail feature for a table is done in the Table definition in the Application Dictionary.
- Switch to the System Administrator role
- Goto Application Dictionary -> Tables and Columns
- Navigate to the table for which you want to enable the Audit Trail
- Switch to Edit View
- Mark the "Fully Audited" checkbox and save
When a table is flagged as Fully Audited, the user can decide if he wants to audit the insertions done in that table.
If the Audit Inserts field is checked in a table, when a new row is inserted in that table several records will be inserted in the Audit Trail table, one for each column in the audited table. This records will contain the original value of the columns of the new row.
Usually it is not needed to store this information, because the original value of a column could be easily obtained by using the Old Value and New Value fields of the Audit Trial table that corresponds with that column. If the Audit Inserts field is left unchecked, only one row will be inserted in the Audit Trial table for each record inserted in the audited table. At least this one record needs to be inserted in the Audit Trial table to be able to store which process was used to create the record in the audited table.
By default when a table is audited, modifications on any of its columns are audited. In some cases it makes sense not to audit changes for some of them. This can be configured by setting the Exclude Audit flag in Tables and Columns > Table > Column tab.
Running the 'Update Audit Trail infrastructure process'
The audit trail system uses a number of generated triggers (one per table to be audited) to collect the audit data for all changes.
These triggers need to be regenerated once the following actions have been performed:
- The Audit Trail feature has been enabled or disabled for a table
- There has been any structural change to a table being audited (i.e. new columns, changed columns)
This process can be execute as described below:
- Switch to System Administrator role
- Go to Application Dictionary
- Start the 'Update Audit trail infrastructure process'
- After confirmation, the process starts and recreates all audit triggers (by removing and creating them again).
- While this process is executed the background scheduler is suspended
NOTE: This process is also automatically executed on each compilation if the audit trail feature has been enabled for any table.
The Audit Trail Popup
This popup allows examination of the history of the record which is currently shown in the window. It has two main view modes which allow to examine the following data:
- 'Record history' of a single record
- 'Deleted records' of a single tab
The 'Record History' view
This view is displayed when the popup is opened from an existing record via the new toolbar button.
The top area always shows a reference to the entity (i.e. Sales Order) and the record "1000175 - 2016-04-03 00:00:00.0-0.00" for which the history is displayed.
Then a number of filters are available which allow some restriction on the changes displayed to ease the use for record with many modifications.
The grid in the lower area shows all changes done to this record while the audit trail feature was enabled. The changes are shown sorted from the most recent change back to earlier changes.
A row in this grid corresponds to a single changed field. For changes to an existing record the number of grid entries shown correspond to the number of fields changed. For new record creations or record deletion one row in the grid is shown per field of the inserted/deleted record.
Finally a link just on top of the grid allow switching to the 'Deleted Records' view. Following that link will show deleted records for the tab from which the Audit Trail popup was opened.
The 'Deleted Records' view
This view allow examination of records which have been deleted from a tab and are otherwise no longer accessible in the user interface.
The general layout of the view is similar to the record history view.
An info on the top shows a reference to the entity for which the deleted records are shown. Directly below a number of filters is available to restrict the records shown.
Then a grid displays all deleted records belonging to this tab/entity. Here one row shown corresponds to a single deleted record and the columns shown are the same as the ones shown in the normal grid view of the same tab.
This view offers a number of navigation choices to view related or more detailed information.
The first one being Back to history. Following this link the view is just switched back to 'Record History' showing the same records as shown before going to the deleted records view.
The next one View history of selected deleted record below allows to examine the detailed history of a deleted record, instead of the summary view which is shown here.
This detailed history is displayed in the same 'Record History' view, however its top info area notes the fact that the history of a deleted record is displayed.
The following screenshot shows an example for the history view of the same deleted 'Sales Order' entry. Comparing with the previous example of this view new history entries corresponding to the deletion are shown in addition to the older information about the record creation and modification.
As the last method of navigation the popup allow filtering of records based on a parent record. This can be useful to i.e. search for deleted lines belonging to some sales order.
There are two possible ways based on if status the parent record: still existent or already deleted.
If the parent record (i.e. a Sales Order) does still exist, then the following steps can be done two view its deleted lines:
- Go to the the lines tab of the Sales Order
- Click the audit trail icon to open the record history view
- Use the 'Deleted Records' links to switch to deleted records view
As the lines tab is not a top level tab (it has a parent tab Sales Order) the deleted records view is automatically filtered to only show lines belonging to the current Sales Order. As a visual information that the information shown is filtered, the top info area shows:
If the parent record (i.e. a Sales Order) does not exist anymore, then the same can be accomplished by using the following steps:
- Goto the 'Deleted Records' view of the Sales Order tab
- Search the Sales Order for which the deleted lines should be shown
- Click on the Lines link just below the grid
Then the deleted records view will show the deleted lines belonging to the selected (deleted) Sales Order.
A generated Audit Trail Window
The second interface to view audit data is a normal generated window which is based on the AuditTrail entity, and allows browsing all audit information filtered by the currently active client. Open the Application menu and navigate to General Setup, Security and select Audit Trail.
It offers a raw view on the audit data meaning that no translation of raw values is done but instead the raw column values of each change are displayed.
Simultaneously this window allow a much more flexible filtering/searching.
The audit trail feature will record all data changes (for the table for which it has been enabled) with the following exceptions:
- text fields of types (char,varchar) with a length >= 4k will not be audited
- text fields of types (nchar,nvarchar) with a length >= 2k will not be audited
- BLOB fields (binary stored inside the database) will not be audited