Detecting Multiple Changes I want to write a trigger to detect changes to columns in SQL Server 2022. I have this table: CREATE TABLE CustomerLarge
(CustomerID INT NOT NULL IDENTITY(1, 1) CONSTRAINT CustomerLargePK PRIMARY KEY CLUSTERED
, CustomerName VARCHAR(20)
, CustomerContactFirstName VARCHAR(40)
, CustomerContactLastName VARCHAR(40)
, Address VARCHAR(20)
, Address2 VARCHAR(20)
, City VARCHAR(20)
, CountryCode CHAR(3)
, Postal VARCHAR(20)
, creditlimit INT
, discount NUMERIC(4, 2)
, lastorderdate DATETIME
, lastorderamount NUMERIC(10, 2)
, lastordercontact VARCHAR(20)
, created DATETIME
, modified DATETIME
, modifiedby VARCHAR(20)
, statusid INT
, active BIT
, customersize INT
, primarysalesid INT);
GO I want to determine if both the CustomerContactFirstName and CustomerContactLastName fields are changed, but no others. What is the mask I need to use with COLUMNS_UPDATED()? Answer: IF (SUBSTRING(COLUMNS_UPDATED (),1,1) & 12) = 12 Explanation: In testing, I found I needed to use the SUBSTRING) function or the bitwise math doesn't work. In this case, to check two columns, I need both bitmasks to be set. These are the 3rd and 4th columns, so the 4 and 8 places in binary. Adding these together gives me 12 and I need an exact match. A > 12 means other columns were updated as well. Ref: COLUMNS_UPDATED() - https://learn.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-ver16 Discuss this question and answer on the forums |
