I run in an interesting problem lately. One of my application needs some auditing abilities, it needs to write the change to the change history table if a particular field is updated.
I google around, and found some information on MSDN, There is a T-sql statement called UPDATE, and allows you check whether it's a field is updated or not.
So, I think if write some T-sql statement
If Update( field)
//write to the another audit table.
However, I was confused that the audit table keeps get new entries even I didn't change any data. Yes, that's trick, be aware the difference between "update" and "change", the If Update(filed) will returns true even you don't modify any data. If you want to log to the another table only when the data changes, then you need compare the deleted field value with the inserted field value. After all, the update statement is a delete statement followed by the insert statement.
So, you need so something like.
INSERT INTO AuditTable ( Field )
INNER JOIN Deleted