Click here to Skip to main content
15,919,500 members
Articles / Database Development / SQL Server
Article

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

Rate me:
Please Sign up or sign in to vote.
1.89/5 (4 votes)
6 Oct 2006CPOL 31.5K   23   4
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.

SQL
--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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralKill performance Pin
Simon Hughes25-Sep-06 22:50
Simon Hughes25-Sep-06 22:50 
GeneralRe: Kill performance Pin
Master DJon28-Sep-06 18:49
Master DJon28-Sep-06 18:49 
GeneralAre you sure this works Pin
Ewout Stortenbeker25-Sep-06 21:49
Ewout Stortenbeker25-Sep-06 21:49 
GeneralRe: Are you sure this works Pin
Master DJon28-Sep-06 19:03
Master DJon28-Sep-06 19:03 
You are right man. Thanks for this one.

I will update the article to use this instead :
LOG(CONVERT(int,COLUMNS_UPDATED()))/LOG(2)

Thanks for this attention. I would probably not correct it otherwise.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.