Mike,

Thanks for the info. You're right of course. Anyway, here's the update trigger as a result of BOb's idea. What I did in addition to the original idea is to handle the situation you mentioned. Performance was surprisingly good (on par or slightly faster than COLUMNS_UPDATED() used in BOL). Do you see any issues with the following trigger or any improvements?

Thanks in advance.

CREATE TRIGGER TESTTABLE_AUDIT_UPDATE ON TestTable
FOR UPDATE
AS

/*
Audit Trail update trigger. Only logs data that has actually been changed.
COLUMNS_UDPATED() and UPDATE() return true if the column was part of the update statement
even if the data has not been changed.
Based on an idea from BOb Archer on the UT.
*/

/*
The following select UNIONs all the fields to be audited from the inserted and the deleted tables.
If an item has not been changed, then the resulting temp table will contain only one record. This
allows us to ignore unchaged data.
*/

SELECT * INTO #TempTable
FROM (
SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,
del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax
FROM deleted del
UNION
SELECT ins.CustomerID,ins.CompanyName,ins.ContactName,ins.ContactTitle,
ins.Address,ins.City,ins.Region,ins.PostalCode,ins.Country,ins.Phone,ins.Fax
FROM inserted ins
) AS Temp1

/*
Get a list of PKs for items that have been changed into a temp table
*/

SELECT CustomerId INTO #TempTable2 FROM #TempTable
GROUP BY customerId
HAVING COUNT(customerId) > 1

/*
Finally we log the changes to the audit trail. The subquery makes sure that we only log data that
has been changed.
*/

/* original data
INSERT INTO CUSTOMERS_AUDIT(CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,cTrnsTyp)
SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax,'U' FROM deleted del
WHERE CustomerId IN (SELECT CustomerId FROM #TempTable2 GROUP BY customerId)
*/

/* changed data */
INSERT INTO CUSTOMERS_AUDIT(CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,cTrnsTyp)
SELECT ins.CustomerID,ins.CompanyName,ins.ContactName,ins.ContactTitle,ins.Address,ins.City,ins.Region,ins.PostalCode,ins.Country,ins.Phone,ins.Fax,'U' FROM inserted ins
WHERE CustomerId IN (SELECT CustomerId FROM #TempTable2 GROUP BY customerId)



>Just remember that a SQL Server trigger fires ONCE no matter how many rows are affected by the query - one, five, or even zero. After you UNION the Inserted and Deleted columns, it may not be enough to just refernce @@ROWCOUNT.