CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Filip
Filip

Reputation: 127

Trigger for rows where specific column was changed

I would like to find a way in SQL Server, how to update rows where some specific property has changed. Simple example, I want set Flag = True in all rows, where KeyProperty was changed. I have created SQL Trigger:

CREATE TRIGGER testTrigger
ON table
AFTER UPDATE
AS
BEGIN
  IF(UPDATE(KeyProperty))
  BEGIN
    UPDATE table
    SET Flag = True
    WHERE EXISTS (SELECT 1 FROM inserted WHERE inserted.Id = table.Id)
  END
END
GO

But from the results I assume, UPDATE(KeyProperty) looks, if there is at least one row, where KeyProperty was updated. So if I have initial table:

Id KeyProperty OtherProperty Flag
1 100 red False
2 200 blue False
3 300 black False

Update looks like:

Id KeyProperty OtherProperty Flag
1 1000 red True
2 200 blue False
3 300 white True

So even the Flag in the third row is updated, because there was another update and the row occurs in the inserted logical table.

Is there a way how to match row from inserted table together with UPDATE() condition on that specific row? Putting the condition to WHERE statement made the same result.

Upvotes: 1

Views: 2815

Answers (1)

Charlieface
Charlieface

Reputation: 71119

You need to compare the inserted and deleted tables, joining by primary key, and verify the results are different.

Note that the UPDATE() function only tells you if the column was present in the UPDATE, not if the value actually changed.

CREATE TRIGGER testTrigger
ON table
AFTER UPDATE
AS

  SET NOCOUNT ON;

  IF(UPDATE(KeyProperty))
  BEGIN
    UPDATE t
    SET Flag = 'True'
    FROM (
        SELECT i.Id, i.KeyProperty
        FROM inserted i
        EXCEPT
        SELECT d.Id, d.KeyProperty
        FROM deleted d
    ) i
    JOIN table t ON t.Id = i.Id
  END

GO

Upvotes: 6

Related Questions