T-SQL:更新后哪些 COLUMNS 发生了变化?
好的.我正在对表中的单行进行更新.除主键外，所有字段都将被新数据覆盖.但是，并非所有值都会更改更新的 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.
相关文章