Determining the Updated Columns I am creating a trigger in SQL Server 2022 and want to detect a change in a table. Here is my table DDL: 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 If I want to detect that the creditlimit was updated, what IF statements should I use? Answer: IF (SUBSTRING(COLUMNS_UPDATED(),2,1) & 2) = 2 Explanation: The COLUMNS_UPDATED() function only returns a varbinary value of bytes representing the column changes. Beyond the first 8 columns, we need to use the substring function to get the correct byte for the columns. In this case, the credit limit column is the 10th column, so we need the second byte and we are looking for the 2nd bit being changed. 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 |
