Auditing: some useful (?) code and a question

I'm still trying to decide the "right" solution for auditing, but here's one
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?