Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 512
Calvin Avatar asked Jan 26 '10 00:01

Calvin


People also ask

How can I tell which columns were changed in a SQL Server UPDATE?

Solution. Using a SQL Server trigger to check if a column is updated, there are two ways this can be done; one is to use the function update(<col name>) and the other is to use columns_updated().

Does updating CTE UPDATE original table?

You can update CTE and it will update the base table. Here is the script which you should execute all together.


3 Answers

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.

like image 118
Aaronaught Avatar answered Oct 04 '22 21:10

Aaronaught


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

like image 44
Rob Farley Avatar answered Oct 04 '22 22:10

Rob Farley


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.

like image 28
Mitch Wheat Avatar answered Oct 04 '22 20:10

Mitch Wheat