問(wèn)題描述

好的.我正在對(duì)表中的單行進(jìn)行更新.除主鍵外，所有字段都將被新數(shù)據(jù)覆蓋.但是，并非所有值都會(huì)更改更新的 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

然后我執(zhí)行 UPDATE tbl SET foo = 'hi', bar = 'something else' WHERE id = 1

我想知道的是哪個(gè)列的值發(fā)生了變化，其原始值是多少，新值是什么.

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

可以不進(jìn)行逐列比較嗎?是否有一些優(yōu)雅的 SQL 語(yǔ)句，例如 EXCEPT 會(huì)比行更細(xì)粒度?

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?

謝謝.

推薦答案

沒(méi)有您可以運(yùn)行的特殊語(yǔ)句來(lái)準(zhǔn)確地告訴您哪些列發(fā)生了更改，但是查詢并不難編寫(xiě):

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

如果你想真正做一些事情作為這些變化的結(jié)果，那么最好寫(xiě)一個(gè)觸發(fā)器:

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

(當(dāng)然，您可能希望在觸發(fā)器中執(zhí)行更多操作，但有一個(gè)非常簡(jiǎn)單的操作示例)

(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 但我覺(jué)得這很痛苦，而且它仍然不會(huì)告訴您哪些列實(shí)際更改了，只是哪些列包含在 UPDATE 語(yǔ)句中.因此，例如，您可以編寫(xiě) UPDATE MyTable SET Col1 = Col1 并且它仍然會(huì)告訴您 Col1 已更新，即使沒(méi)有一個(gè)值實(shí)際更改.編寫(xiě)觸發(fā)器時(shí)，您需要實(shí)際測(cè)試各個(gè)前后值，以確保獲得真正的更改(如果這是您想要的).

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 所說(shuō)的那樣 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.

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