Determine provided values and copied values in SQL Server instead of update trigger

Question

Let's say I have a table like this:

CREATE TABLE [A]
(
  [X] INT,
  [Y] INT,
  [Z] INT
)

..that has an instead of update trigger. If I insert a row into this table:

INSERT INTO [A]
  SELECT 1, 1, 1

Then I hit the update trigger with code like this:

UPDATE [A]
SET [X] = 2
WHERE [X] = 1

I'll end up with an [deleted] table like so:

X: 1, Y: 1, Z: 1

I'll end up with an [inserted] table like so:

X: 2, Y: 1, Z: 1

Is there any way to determine that only the X was actually set? In other words, how do I distinguish between:

UPDATE [A]
SET
  [X] = 2,
  [Y] = 1, 
  [Z] = 1
WHERE [X] = 1

...and the statement above?

Note: SQL Server 2008

gbn gbn · Accepted Answer · 2013-06-20T15:26:28

In the trigger, you can add a WHERE clause to compare old and new values and ignore non-changing rows.

There is the function UPDATE() you can use but it can still gives true if the old and new values are the same. It isn't very reliable.

However, there is no way to determine the actual columns in the SET clause of the triggering UPDATE call. That is, these look exactly the same to the trigger

UPDATE [A]
SET
  [X] = 2,
  [Y] = 1, 
  [Z] = 1
WHERE [X] = 1

UPDATE [A]
SET [X] = 2
WHERE [X] = 1

Determine provided values and copied values in SQL Server instead of update trigger

1 Answers