TSQL update trigger: joining inserted and deleted

Question

I have an on update trigger in SQL Server 2008. I only need to perform the trigger action if certain columns have been modified. Thus I'd like to check what has changed.

T-SQL offers an "if update( columnName )" construct. However, if many rows have been updated and only a single one of them has the particular column value changed "if update()" will have to return true. This'll make me perform the trigger action for far more rows than required.

So instead of using "if update()" I thought I'd just join the virtual deleted and inserted tables (the rows before and after update) and compare the relevant columns myself. However, how can I join the two tables? I cannot use the table's primary key since that may have been modified by the update. The only thing I can think of is joining by row_number(), i.e. implicit table ordering. This feels very wrong though and I don't know whether SQL Server actually offers any guarantuees that rows in inserted are ordered the same as in deleted.

Answer 1

If you use IDENTITY columns as Primary Keys, you don't have the problem of updated PK columns.

Answer 2

With your design (that allows changing primary keys) it seems very hard to build a consistent logic.

Say, you have this table:

id   value
1    2
2    1

and issue this operation:

UPDATE  mytable
SET     id = CASE WHEN id = 1 THEN 2 ELSE 1 END,
        value = CASE WHEN value = 1 THEN 2 ELSE 1 END

which updates both records but leaves the table as this:

id   value
2    1
1    2

, which from relational point of view is similar to not changing the table at all.

The whole point of the primary keys is that they never change.

Answer 3

to prevent a PK from changing, add this to the top of your trigger:

IF (UPDATE(yourPKcol1) OR UPDATE(yourPKcol2))
BEGIN 
    RAISERROR('Primary Key change not permitted!',16,1) 
    ROLLBACK
    RETURN
END

Answer 4

Your best bet might be to (as I mentioned in a comment above) create a new table, if possible, that includes all the data in the original but also includes an immutable primary key (IDENTITY works, or you can use something else if you prefer). You can then expose a view of this new table that mimics the name and schema of the original table. This will give you a fixed ID that you can use to track changes as you wish.

All this assumes that a view works adequately in your particular case -- if the app is doing something very weird it might not work properly, but if it's just throwing standard CRUD-style SQL at it it should work fine.