Use this option to set up temporary audit logs on selected tables. The audit logs are executed based on SQL Triggers that will be activated each time a new record is added to the table, or an existing record is edited or deleted from the table.
IMPORTANT NOTE: Performance may be hindered when audit trails are turned on for tables that have lots of transaction entries so be sure to Cancel the Audit Trail as soon as you have the information you need.
The SQL Scripts for the Audit Trails on Company Tables contains error code -700 to help identify any exception errors that occur when a table audit trail is enabled and the audit trail functionally is causing problems with normal processing.
Use the buttons on this form for setting up and canceling the audit trail feature.
|
Create Audit Trail on Table |
|
Cancel Audit Trail on Table |
Select a company table from the drop down list and click this button to set up the audit trail feature for the table. The system will then set up all of the necessary triggers on the table and create the audit trail table and view. Once the setup process is complete, a confirmation message will be displayed.
Note: The ESTDETL and JOBLIST tables have been excluded from this feature as these tables already have built in audit tracking. TABLEAUDIT is the name of the table that holds the audit trail for BOTH the JOBLIST and ESTDETL tables.
The setup process will create 3 separate trigger events on the table for APPEND, UPDATE, and DELETE. A free table will be created under the company folder to hold the audit records and the name of the table will be the same as the original table following by the "_AUDITTRAIL" characters. For example: AP1099S_AUDITTRAIL would be the name of the free table that is used to track the audit records for the AP1099S table. In addition, a company view will be created for the temporary table so that the audit table can be viewed from the Company Inquiries screen. The name of the view will be the name of the original table preceded by the "abAudit_" characters. For example: abAudit_AP1099S would be the name of the view for the AP1099S audit table.
The audit table will contain all of the fields as the original table plus 3 additional fields to hold the audit information:
| · | TriggerDateTime – Holds the date and time of the record change |
| · | TriggerType – Holds the type of data for the record. |
| o | NEW – represents data for a new record added to the table OR a copy of the record AFTER the record was edited. |
| o | OLD – represents the data for the old record before it was deleted OR edited. |
| · | TriggerRecType – Holds the type of trigger operation that caused the data change: |
| o | APPEND – When a record is added to the table, one NEW record will be written to the audit table. |
| o | UPDATE – When an existing record is edited, two records will be written to the audit table. An OLD record will be written to represent the fields before the edit was done along with a NEW record showing the field values after the record changes were made. |
| o | DELETE - When a record is deleted from the table, one OLD record will be written to the audit table to show the fields of the record before it was deleted. |
Once you have all of the information you need for the audit process, use this button to remove the audit trail process. A list of all table audit processes will be listed in the drop down control. Select the desired audit trail table and click the button to remove the audit process. The triggers will be removed from the table, the temporary table will be deleted from the company directory, and the view will be removed from the company view list.
