IF UPDATE() in SQL server trigger

Question

If there's:

IF UPDATE (col1)

...in the SQL server trigger on a table, does it return true only if col1 has been changed or been updated?

I have a regular update query like

UPDATE table-name 
   SET col1 = 'x', 
       col2 =  'y' 
 WHERE id = 999

Now what my concern is if the "col1" was 'x' previously then again we updated it to 'x' would IF UPDATE ("col1") trigger return True or not?

I am facing this problem as my save query is generic for all columns, but when I add this condition it returns True even if it's not changed...So I am concerned what to do in this case if I want to add condition like that?

Answer 1

Within the trigger, you have access to two internal tables that may help. The 'inserted' table includes the new version of each affected row, The 'deleted' table includes the original version of each row. You can compare the values in these tables to see if your field value was actually changed.

Answer 2

It returns true if a column was updated. An update means that the query has SET the value of the column. Whether the previous value was the same as the new value is largely irelevant.

UPDATE table SET col = col

it's an update.

UPDATE table SET col = 99

when the col already had value 99 also it's an update.

Answer 3

What you do is check for different values in the inserted and deleted tables rather than use updated() (Don't forget to account for nulls). Or you could stop doing unneeded updates.