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