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
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.