T-SQL: what COLUMNS have changed after an update?

Question

OK. I'm doing an update on a single row in a table. All fields will be overwritten with new data except for the primary key. However, not all values will change b/c of the update. For example, if my table is as follows:

TABLE (id int ident, foo varchar(50), bar varchar(50))

The initial value is:

id   foo   bar
-----------------
1    hi    there

I then execute UPDATE tbl SET foo = 'hi', bar = 'something else' WHERE id = 1

What I want to know is what column has had its value changed and what was its original value and what is its new value.

In the above example, I would want to see that the column "bar" was changed from "there" to "something else".

Possible without doing a column by column comparison? Is there some elegant SQL statement like EXCEPT that will be more fine-grained than just the row?

Thanks.

Viceroy

Answer 1

There is no special statement you can run that will tell you exactly which columns changed, but nevertheless the query is not difficult to write:

DECLARE @Updates TABLE
(
    OldFoo varchar(50),
    NewFoo varchar(50),
    OldBar varchar(50),
    NewBar varchar(50)
)

UPDATE FooBars
SET <some_columns> = <some_values>
OUTPUT deleted.foo, inserted.foo, deleted.bar, inserted.bar INTO @Updates
WHERE <some_conditions>

SELECT *
FROM @Updates
WHERE OldFoo != NewFoo
OR OldBar != NewBar

If you're trying to actually do something as a result of these changes, then best to write a trigger:

CREATE TRIGGER tr_FooBars_Update
ON FooBars
FOR UPDATE AS
BEGIN
    IF UPDATE(foo) OR UPDATE(bar)
        INSERT FooBarChanges (OldFoo, NewFoo, OldBar, NewBar)
            SELECT d.foo, i.foo, d.bar, i.bar
            FROM inserted i
            INNER JOIN deleted d
                ON i.id = d.id
            WHERE d.foo <> i.foo
            OR d.bar <> i.bar
END

(Of course you'd probably want to do more than this in a trigger, but there's an example of a very simplistic action)

You can use COLUMNS_UPDATED instead of UPDATE but I find it to be pain, and it still won't tell you which columns actually changed, just which columns were included in the UPDATE statement. So for example you can write UPDATE MyTable SET Col1 = Col1 and it will still tell you that Col1 was updated even though not one single value actually changed. When writing a trigger you need to actually test the individual before-and-after values in order to ensure you're getting real changes (if that's what you want).

P.S. You can also UNPIVOT as Rob says, but you'll still need to explicitly specify the columns in the UNPIVOT clause, it's not magic.

Mcdermott

It's customary to leave a note when you downvote, folks. Don't be a jerk, what exactly was wrong with this answer? – Mcdermott

Thanks for the info. I was looking for a special statement, but if there isn't one, I guess I'll have to brute force it as you did above. – Viceroy

Answer 2

Try unpivotting both inserted and deleted, and then you could join, looking for where the value has changed.

Eskill

Could you provide an example using the case above? – Viceroy

/* Sorry about the formatting, hope it works okay / create trigger uBlah on dbo.blah after update as select d., i.value from ( select id, col, value from deleted unpivot (value for col in ([foo],[bar])) u ) d join ( select id, col, value from inserted unpivot (value for col in ([foo],[bar])) u ) i on d.id = i.id and d.col = i.col and d.value != i.value ; – Eskill

Answer 3

You could detect this in a Trigger, or utilise CDC in SQL Server 2008.

If you create a trigger FOR AFTER UPDATE then the inserted table will contain the rows with the new values, and the deleted table will contain the corresponding rows with the old values.

Craven

Using SQL Server 2005. How, exactly, would I detect this in a trigger? – Viceroy

Answer 4

Alternative option to track data changes is to write data to another (possible temporary) table and then analyse difference with using XML. Changed data is being write to audit table together with column names. Only one thing is you need to know table fields to prepare temporary table. You can find this solution here:

• part 1

• part 2

Leggat

Answer 5

OUTPUT deleted.bar AS [OLD VALUE], inserted.bar AS [NEW VALUE]

@Calvin I was just basing on the UPDATE example. I am not saying this is the full solution. I was giving a hint that you could do this somewhere in your code ;-)

Since I already got a -1 from the above answer, let me pitch this in:

If you don't really know which Column was updated, I'd say create a trigger and use COLUMNS_UPDATED() function in the body of that trigger (See this)

I have created in my blog a Bitmask Reference for use with this COLUMNS_UPDATED(). It will make your life easier if you decide to follow this path (Trigger + Columns_Updated())

If you're not familiar with Trigger, here's my example of basic Trigger http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/

Christian

The problem with this solution is that I don't know that "bar" is the field that has changed. Thanks though. – Viceroy

So I guess the only question I have for this is: Does the COLUMNS_UPDATED(), with the appropriate bitmask of power(2,fieldnum-1), actually tell me if the value changed or only if, as Aaronaught says, whether or not the field was included in the UPDATE statement? – Viceroy

@Viceroy "Does the COLUMNS_UPDATED()...ctually tell me if the value changed or only if, as Aaronaught says, whether or not the field was included in the UPDATE statement?" - code it yourself to see the answer. How's that? That's another opportunity to learn new things! Grab that opportunity now! – Christian

In SQL2005 it only is a bitmask to indicate that the column was referenced in the statement performing the update. So if the program sends all columns with unchanged data to the update the bitmask will reflect that all columns were "updated" even if some of the values had not changed. – Amedeo

@Christian the links you provided are not working. – Storiette

Answer 6

If you are using SQL Server 2008, you should probably take a look at at the new Change Data Capture feature. This will do what you want.

Flue

CDC doesnt really fit that use case. – Perrie

And I'm using SQL Server 2005. – Viceroy

Answer 7

Expanding on @Dalex's answer, I implemented a generic change log only where data had changed as follows

CREATE OR ALTER TRIGGER Temp.Andrew_TRG
    ON <TableName>
    AFTER INSERT, DELETE, UPDATE
AS
BEGIN
    SET NOCOUNT ON 

    INSERT Temp.SampleChangeLogTable
           ( AuditDate
           , TableName
           , AuditAction 
           , UniqueId
           , OldRowValue
           , NewRowValue )
    SELECT GETUTCDATE()
         , '<TableName>'
         , IIF(i.Id IS NULL, 'DELETE', IIF(d.Id IS NULL, 'INSERT', 'UPDATE'))
         , ISNULL(I.Id, D.Id)
         , CONVERT(VARCHAR(MAX), (SELECT * FROM deleted OLD WHERE OLD.Id = ISNULL(I.Id, D.Id) FOR XML PATH, TYPE))
         , CONVERT(VARCHAR(MAX), (SELECT * FROM inserted NEW WHERE NEW.Id = ISNULL(I.Id, D.Id) FOR XML PATH, TYPE))
      FROM inserted i 
               FULL OUTER JOIN 
           deleted d 
                 ON i.Id = d.Id
     WHERE i.Id IS NULL -- Deleted
        OR d.Id IS NULL -- Inserted
        OR (CONVERT(VARCHAR(MAX), (SELECT * FROM deleted OLD WHERE OLD.Id = ISNULL(I.Id, D.Id) FOR XML PATH, TYPE)) <>
            CONVERT(VARCHAR(MAX), (SELECT * FROM inserted NEW WHERE NEW.Id = ISNULL(I.Id, D.Id) FOR XML PATH, TYPE))) -- Changed

    SET NOCOUNT OFF
END
GO

Clabo