How to find if only one particular column has been modified in a trigger

Introduction

Hi people! The following code has been written for SQL Server 2005 Express, and the program running the database has been written in VB.NET 2005.

This SQL code will allow you to know if only one particular column has been modified in a trigger, without having to know the number of columns in the related table.

--Determine if only one column has been 
modified
--@onlyOneColumnUpdated = 0 if not, otherwise @onlyOneColumnUpdated = 1
DECLARE @logForOneColumn float
DECLARE @onlyOneColumnUpdated bit
SET @logForOneColumn = (SELECT (LOG(CONVERT(int,COLUMNS_UPDATED()))/LOG(2)))
SET @onlyOneColumnUpdated = 0
IF CONVERT(int,@logForOneColumn) = @logForOneColumn
    SET @onlyOneColumnUpdated = 1

-- After then you test the column you want to know if it has been modified
IF UPDATE(MyCOlumn) AND @onlyOneColumnUpdated = 1
    -- PUT YOUR CODE HERE FOR THE COLUMN.

The principle is the following:

• Each table columns has been assigned a multiple of 2. That way, when a column is updated, it is added up to a common number which is always unique. It would not be possible to determine if two columns were modified (it would, but not without knowing the table structure), but we can do for only one.
• Because CONVERT(int,COLUMNS_UPDATED()) is returning a number, which if the log base 2 gives another integer, then you know that only one column has been modified.