Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating audit triggers in SQL Server

I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but it seams that this is easy to do this incorrectly.

Can anybody post an example of an update trigger that accomplishes this successfully and in an elegant manner? I am hoping to end up with an audit table with the following structure:

  • ID
  • LogDate
  • TableName
  • TransactionType (update/insert/delete)
  • RecordID
  • FieldName
  • OldValue
  • NewValue

... thoughts?

like image 527
Mike Cole Avatar asked Dec 26 '09 01:12

Mike Cole


People also ask

What is audit trigger in SQL?

Database administrators can employ various techniques to audit the use of SQL Server databases for security and regulatory compliance purposes. One of the most common methods is creating SQL Server audit triggers to automatically capture information about transactions and changes to database tables.

What is audit trigger?

It means that when a trigger is fired, all this info has to be collected from a database or SQL Server instance and stored in an auditing repository. For example, a trigger fired when a record is inserted into the Currency. Sales table is created using the following SQL: CREATE TRIGGER Sales.

What are 3 types of SQL triggers?

SQL Server has three types of triggers: DML (Data Manipulation Language) Triggers. DDL (Data Definition Language) Triggers. Logon Triggers.


1 Answers

I just want to call out couple of points:

Use code generators You can't have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation to generate the code from XML, and the XML can be generated automatically from metadata. This allows you to easily maintain the triggers by regenerating them each time you make a change to the audit logic/structure or a target table is added/altered.

Consider capacity planning for the audit. An audit table that tracks all value changes will be, by far, the biggest table in the database: it will contain all the current data and all the history of the current data. Such a table will increase the database size by 2-3 orders of magnitude (x10, x100). And the audit table will quickly become the bottleneck of everything:

  • every DML operation will require locks in the audit table
  • all administrative and maintenance operations will have to accommodate the size of the database due to audit

Take into account the schema changes. A table named 'Foo' may be dropped and later a different table named 'Foo' may be created. The audit trail has to be able to distinguish the two different objects. Better use a slow changing dimension approach.

Consider the need to efficiently delete audit records. When the retention period dictated by your application subject policies is due, you need to be able to delete the due audit records. It may not seem such a big deal now, but 5 years later when the first records are due the audit table has grown to 9.5TB it may be a problem.

Consider the need to query the audit. The audit table structure has to be prepared to respond efficiently to the queries on audit. If your audit cannot be queried then it has no value. The queries will be entirely driven by your requirements and only you know those, but most audit records are queried for time intervals ('what changes occurred between 7pm and 8pm yesterday?'), by object ('what changes occurred to this record in this table?') or by author ('what changes did Bob in the database?').

like image 115
Remus Rusanu Avatar answered Sep 29 '22 20:09

Remus Rusanu