Creating audit triggers in SQL Server

by

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?’).

More Related Contents:

  1. SQL Server ON DELETE Trigger
  2. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  3. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  4. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  5. Parameter Sniffing (or Spoofing) in SQL Server
  6. What are the main performance differences between varchar and nvarchar SQL Server data types?
  7. SQL Server String or binary data would be truncated
  8. Datatype for storing ip address in SQL Server
  9. How to change identity column values programmatically?
  10. best way to convert and validate a date string
  11. Why use the INCLUDE clause when creating an index?
  12. Why is it considered bad practice to use cursors in SQL Server?
  13. SQL Server pivot vs. multiple join
  14. SQL Query to find the last day of the month
  15. SQL Server GUID sort algorithm. Why?
  16. WHERE clause on SQL Server “Text” data type
  17. SQL Server error on update command – “A severe error occurred on the current command”
  18. Unable to begin a distributed transaction
  19. Parse XML in SQL Server
  20. SQL server schema and default schema
  21. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. FOR XML PATH(”): Escaping “special” characters
  24. How to store directory / hierarchy / tree structure in the database?
  25. INFORMATION_SCHEMA vs sysobjects
  26. Why is casting from float to varchar being rounded in SQL Server?
  27. Does query plan optimizer works well with joined/filtered table-valued functions?
  28. What are row, page and table locks? And when they are acquired?
  29. Trigger to fire only if a condition is met in SQL Server
  30. Cross Domain SQL Server Logins Using Windows Authentication

Leave a Comment