Hello forum,

I have a couple comments on Karen's posting.  First, bravo for taking on the hotly controversial topic of handling change logging with triggers - only the brave tread here.  And I think that you've adequately cautioned your readers that this is not the ideal solution, as most would agree that if you designed a brand new database with change logging you would abstract the application's direct access to the tables entirely and handle both the updates and change logging entirely through a batch of sp's.

I have taken Karen's code as a template and devised a solution in a client's existing database.  This is a case where I cannot change all the client-sourced update commands (which quite often reference the tables directly), so the solution here had to be based on triggers.  This is also a database that typically updates relatively few rows at a time and has plenty of horsepower on the server so there will be no appreciable performance degredation to the end user.  There are rare situations where bulk updates are performed, but I was careful to make sure that all these new triggers were diabled prior to - and reenabled after - the bulk update process is run.

I did also find one teensy little error in Karen's code:  Just before the WHILE loop, where the @CurrentRowID and @tmpname variables are initialized with this statement.

SELECT @CurrentRowId = colid,

                @tmpName = name

FROM      syscolumns

WHERE   colid = @NextRowId

The where clause is not specific enough to zero in on one row.  The colid column in syscolumns is definitely not unique in the table.  Here's a suggestion:

SELECT @CurrentRowId = colid, @tmpName = name

FROM syscolumns

WHERE id = object_id('''+@TableName+''') and objectproperty(id, ''IsUserTable'') = 1 and colid = @NextRowId

I think this is what Karen had in mind.

Lastly, thanks Karen for a great article!  Even though this solution has a specific niche where it might fit perfectly, we should know that there are very few "silver bullet" solutions out there for SQL Server and should carefully measure EVERY solution we borrow from someone else before throwing it into production!