How To Check A Particular Column Of A Table Update Using COLUMNS_UPDATED() In SQL Server

In this blog, we will learn how to check if a particular column of a table is updated or not. If the column updates, the current record inserts into a trigger table using the trigger.

This example creates two tables - tblClients and tblClients_Trigger.

 

With the COLUMNS_UPDATED() function, we can quickly test for any changes made to columns containing client information.

 

Using COLUMNS_UPDATED() this way only works when we are trying to detect changes to the first eight columns in the table.

 

If the column number is greater than 8, then we use

SUBSTRING(COLUMNS_UPDATED(),(((@COL_NO-1/8) + 1),1) & POWER(2,((@COL_NO-1)%8))>0)

 

Example

 

DECLARE @COL_NO INT = 15 (More than 8)

IF(SUBSTRING(COLUMNS_UPDATED(),(((@COL_NO-1)/8)+1),1) & POWER(2,((@COL_NO-1)%8))>0))

       PRINT 'Column No 15 Updated'

 

First, create the given two tables in the database.

CREATE TABLE [dbo].[tblClients](
    [fldGuid] [uniqueidentifier] NULL,
    [fldClientID] [int] NULL,
    [fldClientName] [nvarchar](50) NULL,
    [fldDateCreated] [datetime] NULL,
    [fldCellPhone] [nvarchar](30) NULL,
    [fldEMail] [nvarchar](60) NULL,
    [fldAddress] [nvarchar](60) NULL,
    [fldState] [nvarchar](60) NULL,
    [fldCountry] [nvarchar](60) NULL,
    [fldPK] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [tblClients_pk] PRIMARY KEY NONCLUSTERED
(
    [fldPK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

CREATE TABLE [dbo].[tblClients_Trigger](
    [fldGuid] [uniqueidentifier] NULL,
    [fldClientID] [int] NULL,
    [fldClientName] [nvarchar](50) NULL,
    [fldDateCreated] [datetime] NULL,
    [fldCellPhone] [nvarchar](30) NULL,
    [fldEMail] [nvarchar](60) NULL,
    [fldAddress] [nvarchar](60) NULL,
    [fldState] [nvarchar](60) NULL,
    [fldCountry] [nvarchar](60) NULL,
    [fldPK] [int] NOT NULL,
    CONSTRAINT [tblClients_Trigger_pk] PRIMARY KEY NONCLUSTERED
(
    [fldPK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

 Now, create the trigger myTriggerClient on tblClients table.

CREATE TRIGGER [dbo].[myTriggerClient]
ON [dbo].[tblClients]
AFTER UPDATE
AS
BEGIN
    --Check whether columns 3 or 5 have been updated. If any or all columns 3 or 5 have been changed, create an trigger record.
    --The bitmask is: power(2, (3-1)) + power(2, (5-1)) = 20. To test whether all columns 3 and 5 are updated, use = 20 instead of > 0
    IF (COLUMNS_UPDATED() & 20) > 0
    --Use IF (COLUMNS_UPDATED() & 20) = 20 to see whether all columns 3 and 5 are updated.
    BEGIN
        INSERT INTO tblClients_Trigger(fldGuid,fldClientID,fldClientName,fldDateCreated,
        fldCellPhone,fldEMail,fldAddress,fldState,fldCountry,fldPK)
        SELECT I.fldGuid,I.fldClientID,I.fldClientName,I.fldDateCreated,I.fldCellPhone,
        I.fldEMail,I.fldAddress,I.fldState,I.fldCountry,I.fldPK FROM INSERTED I
    END
END

Now, insert a record into the tblClienttable.

INSERT INTO tblClient('563CE358-720F-4051-944C-964A11D603ED',101,'Amit Mohanty','2019-03-13 13:27:23.990',
'9876543210','[email protected]','Hyderabad','Telengana','India')

Inserting a new client does not cause the UPDATE trigger to fire.

 

Updating the client record for clientId 101 to change the CellPhone to 9999888877 or change the name causes the UPDATE trigger to fire.

UPDATE tblClient SET fldCellPhone ='9999888877' WHERE fldPK=101

Now, check the trigger table a record inserted into the table.

 

If any column other than 3 or more updates, then no record is inserted into the trigger table.