問題描述

好的.我正在對表中的單行進行更新.除主鍵外，所有字段都將被新數據覆蓋.但是，并非所有值都會更改更新的 b/c.例如，如果我的表如下:

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))

初始值為:

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

然后我執行 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.

在上面的示例中，我希望看到列bar"從那里"更改為其他地方".

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

可以不進行逐列比較嗎?是否有一些優雅的 SQL 語句，例如 EXCEPT 會比行更細粒度?

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?

謝謝.

推薦答案

沒有您可以運行的特殊語句來準確地告訴您哪些列發生了更改，但是查詢并不難編寫:

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)

您可以使用 COLUMNS_UPDATED 而不是 UPDATE 但我覺得這很痛苦，而且它仍然不會告訴您哪些列實際更改了，只是哪些列包含在 UPDATE 語句中.因此，例如，您可以編寫 UPDATE MyTable SET Col1 = Col1 并且它仍然會告訴您 Col1 已更新，即使沒有一個值實際更改.編寫觸發器時，您需要實際測試各個前后值，以確保獲得真正的更改(如果這是您想要的).

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).

附言您也可以像 Rob 所說的那樣 UNPIVOT，但您仍然需要在 UNPIVOT 子句中明確指定列，這并不神奇.

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.

這篇關于T-SQL:更新后哪些 COLUMNS 發生了變化?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！