How you get a list of updated columns in SQL server trigger ?

Question

I want to know what columns where updated during update operation on a triger on first scaaning books online it looks like COLUMNS_UPDATED is the perfect solution but this function actualy don't check if values has changed , it check only what columns where selected in update clause, any one has other suggestions ?

Answer 1

The only way I can think of is that you can compare the values in DELETED and INSERTED to see which columns have changed.

Doesn't seem a particularly elegant solution though.

Answer 2

The only way you can check if the values have changed is to compare the values in the DELETED and INSERTED virtual tables within the trigger. SQL doesn't check the existing value before updating to the new one, it will happily write a new identical value over the top - in other words, it takes your word for the update and tracks the update rather than actual changes.

Answer 3

I asked this same question!

The previous posters are correct -- without directly comparing the values, you can't tell for sure whether the data has actually changed or not. However, there are several ways to do this type of checking, depending on what else you're trying to do in the trigger. My question has some good advice in the answers about those different mechanisms and their tradeoffs.

Answer 4

As the others have posted, you'll need to interrogate INSERTED and DELETED. The only other useful bit of advice might be that you can get only the rows that have changed values (and discard the rows that didn't change) by using the EXCEPT operator - like this:

SELECT * FROM Inserted
EXCEPT
SELECT * FROM Deleted