Hi, Kurt/Bharat,

First, Kurt....that's not really how to handle audit logging. The UPDATE function will return a value of true if the column was merely specified in an update statement. So it won't tell you if a value has changed (same deal with COLUMNS_UPDATED), it only tells you if the column was part of an UPDATE statement.

What you want is something to detect if the value actually changed, and you'll need to look at the INSERTED and DELETED system tables.

So a basic example would be more like...

-- Update trigger on a Price table...write out to an audit log, if the Price was updated...
CREATE TRIGGER Upd_Price ON dbo.Price
FOR  UPDATE  
AS
  DECLARE @dLastUpdate DATETIME
  SET @dLastUpdate = GETDATE()

  INSERT INTO AuditLog (TableName, PrimaryKey, LastUpdate, 
                          ColName, OldValue, NewValue, UserKey)
          SELECT 'PRICE' AS TableName, I.PrimaryKey, 
                 @dLastUpdate AS LastUpdate, 'Price' AS ColName,
                 CONVERT(CHAR(20),D.Price) AS OldValue, 
                 CONVERT(CHAR(20),I.Price) AS NewValue, 
                 I.UserKey
          FROM Inserted I
          JOIN Deleted D ON D.PrimaryKey = I.PrimaryKey
          WHERE I.Price <> D.Price

In this example, I'm utilizing the INSERTED and DELETED system tables, which are available inside a trigger. They contain the row(s) before and after the UPDATE. I also have an AuditLog with a column for tablename, primarykey, and character columns for oldvalue/newvalue

You could write a script that generated Update trigger code for all columns that you want to track for audit trail logging. Since you mentioned you have many columns in your table, you might want to consider a script, which would be easier to deal with, as opposed to doing it manually.

In the March/April 2005 issue of CoDe magazine, I wrote an article on T-SQL programming. One of the topics I covered was SQL Server audit trail logging. Here's a link to the article:

http://www.code-magazine.com/Article.aspx?quickid=0503071

Look specifically for tips 10 and 11.

Let me know if that helps...
Kevin