In SQL Server, you can create DML triggers that execute code only when a specific column is updated.

The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated.

You can do this by using the UPDATE() function inside your trigger. This function accepts the column name as its argument. It returns a boolean.

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
);

And here’s the trigger:

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

In this example, I create a table called t1 and a trigger called trg_t1.

I use an IF statement along with the UPDATE() function to test whether or not the c1 column was updated.

When the trigger runs, it will only execute the subsequent code if that condition is true.

Fire the Trigger

Let’s insert a row, but we’ll only insert a value into the c1 column.

INSERT INTO t1 (c1) 
VALUES (1);

SELECT * FROM t1;

Result:

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

As expected, the trigger fired, and the column c3 was also updated.

This happened because I included the INSERT argument in my trigger’s definition (i.e. I specified AFTER INSERT, UPDATE which means that the trigger is fired whenever data is inserted or updated). If I had only specified AFTER UPDATE, it would not have fired when I inserted data – it would only fire whenever I update existing data.

Remember that the table was defined with DEFAULT 0, so the c2 column defaulted to zero.

Now let’s update the c1 column.

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

SELECT * FROM t1;

Result:

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

Again, the c3 column was updated along with c1.

Now let’s do an update to the c2 column (this column isn’t included in the trigger).

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

SELECT * FROM t1;

Result:

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

So this time, c2 was updated but c3 wasn’t. That’s because the c1 column wasn’t updated, and our trigger only updates c3 when c1 is updated.

The same thing would’ve happened if we’d inserted a row without specifying c1 in the INSERT statement.

What if I Update the Column with the Same Value?

If you update a column with the same value, the UPDATE() function will return true.

Here’s an example.

We know from our previous examples that the c1 column contains a value of 2.

Let’s explicitly update that column with the same value: 1

UPDATE t1 
SET c1 = 2
WHERE id = 1;

SELECT * FROM t1;

Result:

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

So now c3 has incremented, even though the value for c1 is still the same.

Let’s do it again, but this time set it to itself (i.e. change c1 = 1 to c1 = c1).

UPDATE t1 
SET c1 = c1
WHERE id = 1;

SELECT * FROM t1;

Result:

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

Again, c3 has incremented.

Unsuccessful Update Attempts

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.