1

I'm using triggers to audit table changes. Right now I capture the individual column changes in the following:

DECLARE @statement VARCHAR(MAX)
SELECT @statement =
'Col1: ' + CAST(ISNULL(Col1, '') AS VARCHAR) + ', Col2: ' + CAST(ISNULL(Col2, '') AS VARCHAR) + ', Col3: ' + CAST(ISNULL(Col3, '') AS VARCHAR)
FROM INSERTED;

The problem is, I need to tweak the column names for every table/trigger that I want to audit against. Is there a way I can build @statement, independent of the table using a more generic approach?

cheers David

downatone
  • 1,936
  • 2
  • 23
  • 30
  • possible duplicate of [How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger](http://stackoverflow.com/questions/8873335/how-to-create-generic-sql-server-stored-procedure-to-perform-inserts-into-audit) – GSerg May 28 '12 at 15:36
  • Do all of your column datatypes have suitable conversions from `''` for use in `ISNULL()`, e.g. you don't use `VarBinary`? Is the loss of `NULL`s a problem? – HABO May 28 '12 at 16:08
  • 1
    Why not have a separate audit table for each table in the database? That way, the audit structure could mimic the original table structure. – Gordon Linoff May 28 '12 at 22:58
  • @user92546 - yes and loss of null's is not a problem. I'm not looking to capture a rigorous snapshot of the data (we have that in our data/log backups), just a simple overview of what changed. – downatone May 30 '12 at 14:01
  • @Gordon Linoff - I started down that path, but it doubled the amount of tables. Since we only use this to overview what changed I don't feel its necessary. One generic audit table is much cleaner imo and serves our purposes. – downatone May 30 '12 at 14:03

1 Answers1

1

what you need to do is build a memory table using the following query and then loop through the same to produce the SQL statement you want

select column_name from information_schema.columns where table_name like 'tName' order by ordinal_position

however i am not sure this would be the right thing to do for AUDIT. How are you going to pull it back later. Say in one of your releases you happen to drop the column what will happen then? how will you know which column held which data.

bhupendra patel
  • 3,139
  • 1
  • 25
  • 29
  • thanks, I should be able to build it dynamically using this. FYI - I use the word audit loosely here, we are really just looking for a simple overview of what changed. – downatone May 30 '12 at 14:08