CODEWITHSUNDEEP
sql-serversql-server-2008triggers
omatase
omatase

Reputation: 1711

Suggestions on Adding Transaction Auditing In a SQL Server 2008 Database For a Financial System

I'm working on an application that is used to store payment information. We currently have a Transaction Audit table that works thusly:

Anytime a field changes in any table under audit we write an audit row that contains: 1 the table name, the field name, the old value, the new value and the timestamp. One insert takes place per field changed per row being updated.

I've always avoided Triggers in SQL Server since they're hard to document and can make troubleshooting more difficult as well, but is this a good use case for a trigger?

Currently the application determines all audit rows that need to be added on its own and sends hundreds of thousands of audit row INSERT statements to the server at times. This is really slow and not really maintainable for us.

Upvotes: 0

Views: 557

Answers (3)

Bryan
Bryan

Reputation: 17703

Take a look at Change Data Capture if you are running Enterprise edition. It provides the DML audit trail you're looking for without the overhead associated with triggers or custom user/timestamp logging.

Upvotes: 2

Leon
Leon

Reputation: 3401

I've implemented audit logic in SPROCS before, but same idea applies to doing it in Triggers.

Working Table: (id, field1, field2, field3, ... field-n)

History Table: (userID, Date/time, action (CUD), id, field1, field2, field3, ... field-n)

This also allows for easy querying to see how data historically changed.

Each time a row in a table is changed, a record is created in History table.

Some of our tables are very large - 100+ fields, so 100+ inserts would be too intense a load and also no meaningful way to quickly see what happened to data.

Upvotes: 0

Chris Shain
Chris Shain

Reputation: 51359

I have worked on financial systems where each table under audit had it's own audit table (e.g. for USERS there was USERS_AUDIT), with the same schema (minus primary key) plus:

  1. A char(1) column to indicate the type of change ('I' = insert, 'U' = update, 'D' = delete)

  2. A datetime column with a default value of GETDATE()

  3. A varchar(255) column indicating the user who made the change (defaulting to USER_ID())

These tables were always inserted into (append-only) by triggers on the table under audit. This will result in fewer inserts for you and better performance, at the cost of having to administer many more audit tables.

Upvotes: 0

Related Questions