I'd like to know, what columns have been REALLY changed

Question

Hello all,

I have an after trigger implemented in SQLCLR assembly. Within it I'd like to know, what columns have been really updated (and their values have been changed).

Unfortunately, SqlContext.TriggerContext.IsUpdatedColumn returns true, even when a column value is still the same. I guess, it's just because an SQL query, prepared by a not-very-smart server application, rewrites all columns, even if some of them have not been changed by user.

The second problem is some of the columns have ntext type, so I cannot even SELECT them from INSERTED pseudo table (MS SQL Server doesn't allow SELECT fields which have ntext type from INSERTED). That's why now I SELECT changed rows with the following query:

SELECT * FROM [dbo].[MyTable] WHERE [id] IN (SELECT [id] FROM INSERTED)

What should I do to get to know, what columns are not just updated, but changed?

Now I have a simple idea: create another trigger, BEFORE, and save updated rows from within. Then, when AFTER trigger is being executed, compare column values. Is this idea the best thing I can do? If so, what is the best place to keep the changed rows between BEFORE and AFTER triggers? A temporary table will be dropped before the AFTER trigger is executed, since I close context connection (maybe, just don't close?).

Answer 1

[Obsolete, see the answer above] Fat chance. Since I cannot SELECT * from INSERTED, I cannot emulate before trigger with INSTEAD OF one.

Any ideas?

Answer 2

OK, now I've solved the problem.

First of all, I've created a full copy of source table (data + structure):

IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'copyTable')
SELECT * INTO copyTable FROM MyTable

Then, I'm comparing the source table with its copy at the beginning of my trigger:

SELECT A.* FROM MyTable A, copyTable B WHERE
    A.id IN (SELECT [id] FROM INSERTED) AND
    A.id = B.id AND
    A.{0} <> B.{0}

Replace {0} with a column you require. This column is exactly the column you have to know, is it updated or not. In my case, it is being defined dynamically, but you can count statically all columns you need.

Et voila - you've selected just the rows that are really changed.

Finally, at the end of the trigger, don't forget to update copyTable with the new values:

UPDATE copyTable SET
    id = s.id,
    col1 = s.col1,
    ... all columns you'd like to control ...
FROM MyTable s WHERE
    s.id IN (SELECT [id] FROM INSERTED) AND
    copyTable.id = s.id

Maybe, there is a better solution, but this works too as well.

Regards,