Skip repetitive navigation links.
United States Department of AgricultureFarm Services AgencySystem Development Life Cycle (SDLC)
Go to SDLC Home Go to SDLC Home Go to About SDLC Go to News Go to Help Go to Contact Us
Search FSA
Go To Advanced Search
Go To Search Tips
FSA Enterprise Architecture
Go to EA Overview
Go to Enterprise Architecture Program
Go to Enterprise Architecture
Go to FSA Infrastructure
Go to SDLC Overview
Go to Background
Go to Development Process
Go to Quick Start Guide
Go to FSA Quality Assurance & Control Process
Go to Project Management Process
Go to Configuration and Change Management
Mainframe & System 36 SDLC
Browse by Subject
Go to Developer Tools Overview
Go to Architectural Decisions/Waivers
Go to FSA Assets and Shared Services
Go to Approved Software
Go to Templates and Documents
Go to Information Bulletins & Memos
Browse by Subject
Go to Learning Overview
Go to Training Schedule
Development Process

Audit Tables


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:

data_action_code char(1)
A - add
C - change
D - delete
data_action_date datetime

  • 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.


Last Modified: 08/28/09 2:45:07 PM

SDLC Home | FSA Home | | Common Questions | Site Map | Policies and Links
FOIA | Accessibility Statement | Privacy Policy | Nondiscrimination Statement | Information Quality | | White House