SQL Server – Data Change Audit Plan

auditsql server

My company is changing from a distributed Access Database model to using a centralized SQL Database. The datatables were designed such that all of the tables have a modified date. In discussion it was suggested that since we will be creating a trigger on each table to handle the modified date perhaps we should have the trigger also log some information to an audit table.

Is this the best way to setup auditing so that we can track who is changing information or is there a better way? Links to articles on the subject are welcome.

For the audit I'm looking at capturing the table name, column name, date modified, row id and the username of the person making the change. Is there any information I'm not thinking of that I should be capturing that might help me avoid future pit falls?

Best Answer

Don't bother tracking audit data on a per-field basis. It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table.

This architecture has a few advantages:

You can easily create a view across the main table and audit table to show a complete history includint current versions.
It is relatively simple to implement the audit triggers - in fact if you hae a large number of tables you can write a utility to generate them from the system data dictionary.
The audit tables can be put on a separate disk to reduce the I/O load on the disk with the main tables.
It's very easy to reconstruct an as-at version of the record, rather than having to apply a list of field-level changes in reverse.

Audit information should include at least: user who created/changed the record, date/time of the creation/change, nature of change (insert, update, delete). You may want to use logical deletion (i.e. a 'Deleted' flag) if you have the option of doing so. Otherwise you need to capture the user and date/time from the session and put these on the deletion record, probably along with the last state of the record. If this is not available from the connection (often the case on N-tier apps where the user is not being impersonated at the database level) then you need to find some other way to get it.

Related Solutions

Audit Logins on PostgreSQL 9.0

For the IP address you can use the function inet_client_addr()

All the system information functions are documented in the manual in the chapter "System Information Functions"

http://www.postgresql.org/docs/current/static/functions-info.html

Postgresql – Inherit audit columns and triggers

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

Best Answer

Related Solutions

Audit Logins on PostgreSQL 9.0

Postgresql – Inherit audit columns and triggers

Related Question