MS SQL Auditing

Question

Hello. I have a problem with a Database at my work. There is currently auditing in place, but its clunky, requires a lot of maintence, and it falls short in a few regards. So I am replacing it.

I want to do this in as generic of a way as possible and have designed the tables, and how everything will link and be updated.

Now, thats all fine and good, but I want to be able to write a generic way to insert records into these audit tables. (Without having to enter a command for each column in each table being changed.)

Is there anyway within a Stored Procedure to iterate over all the columns in a table? And I would like to write this in such a way that it will work with several tables, and automatically pickup and audit added columns and such.

Any ideas?

EDIT: Guess I should Clarify. I will be auditing data that is in the tables. But I will be using the same table(s) to store the audited data for every table in the database.

And I can not use Triggers because usually, when an update occurs, it occurs across multiple tables, but I would like all of these updates to be part of a single Change Set.

That is not a problem, because I can do all the Updates from within a single Stored Proc. I would just prefer some way like a loop, that i can get all the updated fields, figure out which ones changed, and the insert those changed ones into the audit table.

And I would like to do this without have a long list of if statements and insert statements for each column. (By doing this in a generic loop, it will handle added columns automatically and not be bothered by deleted columns)

Answer 1

There would be performance considerations, but you could add insert and update triggers to all of your tables, and have the triggers insert into your audit tables.

Answer 2

By "added columns" I guess you are looking to audit DDL. If you use SQL 2005, then you want this link.

If don't use SQL 2005, then you probably want to either use one of the many SQL schema comparison tools, like SQL Red Gates tool set probably has something in there.

If you don't have $ for tools, then you might just want to run periodic queries against information_schema.tables and information_schema.columns. By periodically capturing these in permenant tables, you can identify when they have gained or lost rows (and hence a schema changed occured)

If you are doing data audit instead, then you'll want want to code generate some triggers, again using information_schema.tables and information_schema.columns.

Answer 3

Use DDL Triggers (assuming you have SQL Server 2005+)!

http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

http://technet.microsoft.com/en-us/library/ms189871.aspx

Answer 4

That could be done if you were using a data access layer that could trap which tables and columns are being update and generating the insert statements for the audit table. In a stored procedure? Which stored procedure? Do you have a single one that does updates? Or are you creating one per table?

Answer 5

If it's an option for you, just upgrade to sql server 2008 and turn on Change Data Capture.