Post
Ask Question

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

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.
  1. CREATE TABLE [dbo].[tblClients](  
  2.     [fldGuid] [uniqueidentifier] NULL,  
  3.     [fldClientID] [int] NULL,  
  4.     [fldClientName] [nvarchar](50) NULL,  
  5.     [fldDateCreated] [datetime] NULL,  
  6.     [fldCellPhone] [nvarchar](30) NULL,  
  7.     [fldEMail] [nvarchar](60) NULL,  
  8.     [fldAddress] [nvarchar](60) NULL,  
  9.     [fldState] [nvarchar](60) NULL,  
  10.     [fldCountry] [nvarchar](60) NULL,  
  11.     [fldPK] [int] IDENTITY(1,1) NOT NULL,  
  12.     CONSTRAINT [tblClients_pk] PRIMARY KEY NONCLUSTERED   
  13. (  
  14.     [fldPK] ASC  
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  16. )  
  17.   
  18. CREATE TABLE [dbo].[tblClients_Trigger](  
  19.     [fldGuid] [uniqueidentifier] NULL,  
  20.     [fldClientID] [int] NULL,  
  21.     [fldClientName] [nvarchar](50) NULL,  
  22.     [fldDateCreated] [datetime] NULL,  
  23.     [fldCellPhone] [nvarchar](30) NULL,  
  24.     [fldEMail] [nvarchar](60) NULL,  
  25.     [fldAddress] [nvarchar](60) NULL,  
  26.     [fldState] [nvarchar](60) NULL,  
  27.     [fldCountry] [nvarchar](60) NULL,  
  28.     [fldPK] [int] NOT NULL,  
  29.     CONSTRAINT [tblClients_Trigger_pk] PRIMARY KEY NONCLUSTERED   
  30. (  
  31.     [fldPK] ASC  
  32. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  33. )  
 Now, create the trigger myTriggerClient on tblClients table.
  1. CREATE TRIGGER [dbo].[myTriggerClient]  
  2. ON [dbo].[tblClients]  
  3. AFTER UPDATE  
  4. AS  
  5. BEGIN  
  6.     --Check whether columns 3 or 5 have been updated. If any or all columns 3 or 5 have been changed, create an trigger record.   
  7.     --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                
  8.     IF (COLUMNS_UPDATED() & 20) > 0   
  9.     --Use IF (COLUMNS_UPDATED() & 20) = 20 to see whether all columns 3 and 5 are updated.  
  10.     BEGIN  
  11.         INSERT INTO tblClients_Trigger(fldGuid,fldClientID,fldClientName,fldDateCreated,  
  12.         fldCellPhone,fldEMail,fldAddress,fldState,fldCountry,fldPK)  
  13.         SELECT I.fldGuid,I.fldClientID,I.fldClientName,I.fldDateCreated,I.fldCellPhone,  
  14.         I.fldEMail,I.fldAddress,I.fldState,I.fldCountry,I.fldPK FROM INSERTED I  
  15.     END  
  16. END  
Now, insert a record into the tblClienttable.
  1. INSERT INTO tblClient('563CE358-720F-4051-944C-964A11D603ED',101,'Amit Mohanty','2019-03-13 13:27:23.990',  
  2. '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.
  1. 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.