Full Database Auditing (FDA) is an option when upsizing a new database to have an image database
created for auditing. Individual tables can then be selected to contribute to the auditing database.
The auditing database will contain a set of tables matching those selected from the main application.
This feature is only available for SQL Server 2005/2008/SQL Express and later versions of SQL Server.
With FDA every record which is inserted or deleted from the selected tables is copied into the
auditing table and tagged with a flag 'I' for inserted and 'D' for deleted.When a record is changed
both before image 'B' and after image 'A' are written to the auditing database (which means that when
you find a changed record, the companion before or after image is immediately available). An additional
counter field is added to uniquely identify each entry in the audit table.
Additional fields recording the users details and when the change was made are also present but
not shown above.
Auditing Fields
MUST has an option when upsizing a database to add basic auditing fields which record when a record
was created or last changed, and the user who made the change. This simple effective system is limited
in that it does not provide an audit trail of what was changed, but is very useful in an application
in immediately indicating who and when data was last changed. FDA simply extends this existing feature
by writing the entire row to the auditing tables.
Security
When using windows authentication, users only require insert permissions on the tables in the auditing
database tables.
Viewing Auditing Data
To view auditing data simply link another front-end application to the auditing database and prepare appropriate forms
for searching the audit tables.
Auditing Triggers
Shown below is an example of a main database and below that the auditing database, where only
one table has been selected for auditing. MUST automatically writes the trigger code on each table
to move data to the auditing tables and record who/when changes are made.
