The population of data into audit tables, regardless of database platform, should be managed as described below. Deviations from this policy should be addressed on a case by case and as-needed basis.
- Tables to be audited should be identified by the development staff and requested through the Data Administration team.
- Audit table population on Online Transaction Processing (OLTP) databases should be populated utilizing an update or delete trigger. All data rows will be copied as a complete atomic unit.
- The result of this methodology is that the current row will only exist in the primary table. All history of changes should be logged in the audit table upon update or delete.
- Audit tables, by default should structurally mirror their primary table, with the exception of two additional columns:
A - add
C - change
D - delete
- Audit tables should be defined with one index based on the primary key of the parent table. Other indexes may be requested as needed.
- Audit tables should not be defined with constraints or triggers.
- No request should be required to create a trigger for an audit table, unless it is a request to deviate from this policy.
- The Database Administration Team will create the DDL for the trigger when a model is delivered by the Data Administration Team containing an audit table.
- It is important to note that the decision to enable this audit process should include consideration of the adverse effects that auditing may have on application performance.