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.
modified
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
IF UPDATE(MyCOlumn) AND @onlyOneColumnUpdated = 1
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.