SQL Server has the UPDATE() function that you can use within your DML triggers to check whether or not a specific column has been updated.

While this function only accepts one column, there’s nothing to stop you from including multiple UPDATE() clauses with AND or OR to test for multiple column updates.

Example

Here’s the table:

CREATE TABLE t1 (
    id int IDENTITY(1,1) NOT NULL,
    c1 int DEFAULT 0,
    c2 int DEFAULT 0,
    c3 int DEFAULT 0,
    c4 int DEFAULT 0
);

And here’s the trigger:

CREATE TRIGGER trg_t1
ON t1
AFTER INSERT, UPDATE
AS
IF ( UPDATE(c1) OR UPDATE(c2) )
BEGIN
UPDATE t1
SET c4 = c4 + 1
WHERE id IN (SELECT DISTINCT id FROM inserted)
END;

In this case, the c4 column will increment only if either the c1 or c2 columns were updated. This will occur even if only one of those two columns is updated (due to me using OR as opposed to AND).

Now let’s test the trigger by inserting data into c1.

INSERT INTO t1 (c1) 
VALUES (1);

SELECT * FROM t1;

Result:

+------+------+------+------+------+
| id   | c1   | c2   | c3   | c4   |
|------+------+------+------+------|
| 1    | 1    | 0    | 0    | 1    |
+------+------+------+------+------+

As expected, c4 was also updated when c1 was updated.

This also applies whenever c2 is updated.

UPDATE t1 
SET c2 = c2 + 1
WHERE id = 1;

SELECT * FROM t1;

Result:

+------+------+------+------+------+
| id   | c1   | c2   | c3   | c4   |
|------+------+------+------+------|
| 1    | 1    | 1    | 0    | 2    |
+------+------+------+------+------+

And of course, it would also apply when both are updated.

However, it won’t apply if we update c3 (but not c1 or c2).

UPDATE t1 
SET c3 = c3 + 1
WHERE id = 1;

SELECT * FROM t1;

Result:

+------+------+------+------+------+
| id   | c1   | c2   | c3   | c4   |
|------+------+------+------+------|
| 1    | 1    | 1    | 1    | 2    |
+------+------+------+------+------+

Requiring Both Columns to be Updated

We can change the OR to AND to specify that the c4 column is only updated if both c1 and c2 are being updated.

Let’s alter our trigger to specify this:

ALTER TRIGGER trg_t1
ON t1
AFTER INSERT, UPDATE
AS
IF ( UPDATE(c1) AND UPDATE(c2) )
BEGIN
UPDATE t1
SET c4 = c4 + 1
WHERE id IN (SELECT DISTINCT id FROM inserted)
END;

Now update c1 only.

UPDATE t1 
SET c1 = c1 + 1
WHERE id = 1;

SELECT * FROM t1;

Result:

+------+------+------+------+------+
| id   | c1   | c2   | c3   | c4   |
|------+------+------+------+------|
| 1    | 2    | 1    | 1    | 2    |
+------+------+------+------+------+

So c1 was updated as specified, but c4 wasn’t.

The same would happen if we updated c2 but not c1.

But now let’s update both c1 and c2.

UPDATE t1 
SET c1 = c1 + 1, c2 = c2 + 1
WHERE id = 1;

SELECT * FROM t1;

Result:

+------+------+------+------+------+
| id   | c1   | c2   | c3   | c4   |
|------+------+------+------+------|
| 1    | 3    | 2    | 1    | 3    |
+------+------+------+------+------+

As expected, this time c4 was also updated.

Failed Updates

It’s important to note that the UPDATE() function merely indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. It will still return true if the attempt was unsuccessful.

The COLUMNS_UPDATED Function

Another way to check for updates across multiple columns is to use the COLUMNS_UPDATED function.

This function returns a varbinary bit pattern indicating the inserted or updated columns of a table or view.

For more information, see the Microsoft documentation for COLUMNS_UPDATED.