IMO, you would have better performance if you wrote a utility that generated trigger designed for the specific tables instead of trying to work with something generic.

Run a test. Try your generic routine against something written for the specific table.

And then remember that this will run for EVERY change that your users make.

-Mike

>Hi, all
>
>I've been trying to write a generic utility to detect and log all changes to our database. We have a requirement to log any change that any user makes, and be able to display changes in a 'view log screen.'
>
>Here's what I've come up with (thanks to Sergey, who answered some questions that I had).
>
>Assume the following...
>
> 1) the update trigger has done a SELECT INSERTED.* INTO #TempInserted FROM
> Inserted, and a SELECT DELETED.* INTO #TempDeleted FROM Deleted...
> 2) the update trigger calls the following stored proc, which generically loops through all the columns in the table being updated (table name is passed as a parameter), and checks for changes...
>
>
>CREATE PROCEDURE dbo.LogChanges @cTableName nChar(8) AS
>
>DECLARE ColumnList CURSOR SCROLL FOR SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @cTableName
>
>DECLARE @cColumn CHAR(20)
>DECLARE @cOldVal CHAR(20)
>DECLARE @cNewVal CHAR(20)
>DECLARE @cCompareSQL CHAR(100)
>
>OPEN ColumnList
>FETCH ColumnList INTO @cColumn
>
>WHILE (@@FETCH_STATUS=0) BEGIN
> CREATE TABLE #TempNew (NewVal Char(30))
> CREATE TABLE #TempOld (OldVal Char(30))
>
> SET @cCompareSQL = ' SELECT ' + @cColumn + ' AS NewVal FROM #TempInserted'
> INSERT #TempNew EXECUTE (@cCompareSQL)
> SET @cCompareSQL = ' SELECT ' + @cColumn + ' AS OldVal FROM #TempDeleted'
> INSERT #TempOld EXECUTE(@cCompareSQL)
>
> SET @cNewVal = (SELECT NewVal FROM #TempNew)
> SET @cOldVal = (SELECT OldVal FROM #TempOld)
>
> IF @cNewVal <> @cOldVal
> BEGIN && here is where you could insert to a log
> PRINT 'Column Changed: ' + @cColumn
> PRINT 'OLD VALUE ' + @cOldVal
> PRINT 'NEW VALUE ' + @cNewVal
> END
> DROP TABLE #TempNew
> DROP TABLE #TempOld
> FETCH ColumnList INTO @cColumn
>END
>
>CLOSE ColumnList
>DEALLOCATE ColumnList
>GO
>
>
>
>Comments? Thoughts? This worked in a prototype environment, but was curious if anyone had any suggestions.
>
>Thanks,
>Kevin