Introduction
Tracking changes to database records is becoming an increasingly important feature. This is especially true for businesses with high-profile requirements for data integrity. Data security and activity "footprints" are essential to comply with governmental and industry regulations. The Audit Manager makes this easy to provide.
Components
| Name | Description |
|---|---|
| DBW_AUDIT_MANAGER | Primary form where audit configuration for specific tables is stored. This is an MDI Child and is designed to run from within FRW_MAIN. |
| NDW_AUDIT_TRAIL | Displays the entire audit history for a specific record within a database table. This is an MDI Child and is designed to run from within FRW_MAIN. |
| NDW_AUDIT_VIEWER | Displays the entire raw contents of a record from the audit table. It provides a record restoration tool and the ability to compare an audit record to the live record. This is an MDI Child and is designed to run from within FRW_MAIN. |
| Audit_Manager | Stored procedure that updates the Audit table to track modifications of a record. |
| Audit_Manager_MFS | Stored procedure MFS for tracking the audit history of a particular record. |
| DBW_Audit_Manager_Events | Stored procedure Commuter Module for the DBW_AUDIT_MANAGER form. |
| NDW_Audit_Trail_Events | Stored procedure Commuter Module for the NDW_AUDIT_TRAIL form. |
| NDW_Audit_Viewer_Events | Stored procedure Commuter Module for the NDW_AUDIT_VIEWER form. |
Using the Audit Manager
The FrameWorks Audit Manager is designed to provide the developer, as well as authorized users of the application, with the ability to tag a database table for auditing. When this occurs, a separate database table is created and records from the production table are automatically copied over whenever changes are made. This ultimately creates a historical log of all activity for a given record that can be retrieved at any time.
Audit Configuration
Use the DBW_AUDIT_MANAGER MDI Child form to configure tables for audit tracking. This should be accessed from within the FRW_MAIN window. By default, this form is accessible from the Admin menu and the Administrator section of the side ShortcutBar:
Once the Audit Manager form is launched, the user is presented with a configuration grid:
| Column | Description |
|---|---|
| Table | Name The name of the database table that requires audit tracking. Type this in or use the Option button to pull up a popup of available tables. |
| Modified by Pos | The field number in the current database table (not the audit table) where the name of the current user can be stored. By default, the next two field numbers will also store the current date and time. |
| Write | Check this box if the Audit Manager should track changes to a record being written to the database table. This will also track when a record is first created. |
| Delete | Check this box if the Audit Manager should track when a record is being deleted from the database table. |
| Read | Check this box if the Audit Manager should track when a record is being read from the database table. No copies of the record will be made in the audit table, but the audit trail will be updated. |
| Tracker | Check this box if the Audit Manager will be used for special purposes. For instance, a long and complex process might make several updates to the database record in memory. The developer may want to track these changes in specific areas in the code. See the documentation for the Audit_Manager stored procedure for more information. |
| Status | Identifies the audit status of the database table: |
| Activate Indicates that this table does not yet have audit tracking enabled. Saving the configuration record will add audit tracking and change this status to Active. The Audit table for the current database table will also be created at this time. | |
| Active Indicates that audit tracking is active for the current database table. | |
| Suspend Indicates that this table will have audit tracking suspended. This does not happen until the configuration record is saved. At this point the status will be changed to Suspended. | |
| Suspended Indicates that this table has audit tracking suspended. When a database table has audit tracking suspended the the Audit table itself is left alone and no prior audit tracking is removed. If the status is changed back to Active then audit tracking will resume. | |
| Remove Indicates that this table will have audit tracking removed. This does not happen until the configuration record is saved. At this point the status will be changed to Removed and the Audit table will be permanently deleted. | |
| Removed Indicates that this table has audit tracking removed. |
Audit Trail
Once audit tracking has been activated for a database table, the Audit Trail form can be used to view the audit history for a specific record:
In the above screenshot, there are two instances where the Audit Manager was triggered due to a physical WRITE to the database table. The highlighted row, however, shows an example of the Audit_Manager stored procedure being called from within another stored procedure (see the comments for the Tracker configuration option above.)
When only one row is selected the Restore button is enabled. Clicking this will prompt the user to restore the audit record into the live database table. If one or more rows are selected, then the View Records button is enabled. Clicking this will bring up one or more Audit Viewer forms that display the raw data as it is stored in the Audit table.
Exercise caution before using the Restore feature. Once this is done the live database record is permanently updated with the audit record. The only way to recover from this is to restore from the most recent audit record in the Audit Trail display. However, please note that the Restore process creates another entry in the Audit Trail.
Audit Viewing
At times it will be necessary to view the content of an audit record. As noted above, this is done by clicking on the View Records button which then brings up the Audit Viewer form:
Clicking on the Restore button accomplishes the same thing as the Restore button from the Audit Trail form. Clicking on the Compare button brings up the current record in a side-by-side view:
When viewed in this mode, differences between the audit record and the current record are identified by a special marker in the field position column (In the screenshot above, fields 3 and 5 have a small red arrow next to them indicating that these fields are different.) Pressing Ctrl-T will cycle through and highlight each row that has a marker indicator.
