T-SQL: что СТОЛБЦЫ изменили после обновления?
Хорошо. Я делаю обновление на одной строке в таблице. Все поля будут перезаписаны с новыми данными за исключением первичного ключа. Однако не все значения изменят b/c обновления. Например, если моя таблица следующие:
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
То, что я хочу знать, - то, какому столбцу изменили его значение и что было его исходным значением и что является его новым значением.
В вышеупомянутом примере я хотел бы видеть, что столбец "панель" был изменен от "там" до "чего-то еще".
Возможный, не делая столбца по сравнению столбца? Есть ли некоторый изящный SQL-оператор как то, ЗА ИСКЛЮЧЕНИЕМ ТОГО, ЧТО будет более мелкомодульным, чем просто строка?
Спасибо.
5 ответов
Нет специального оператора, которое вы можете запустить, что точно скажет, какие столбцы изменялись, но, тем не менее, запрос не Сложно написать:
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
Если вы пытаетесь на самом деле сделать что-нибудь в результате этих изменений, то лучше всего написать триггер:
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
(конечно, вы, вероятно, захотите сделать больше Чем это в триггере, но есть пример очень упрощенного действия)
Вы можете использовать Columns_Updated вместо обновления , но я нахожу это боль, и он все еще выиграл Не скажу, какие столбцы фактически изменились , только какие столбцы были включены в оператор обновление . Так, например, вы можете написать Обновить MyTable Set COL1 = COL1 , и он все равно будет сообщать вам, что COL1 был обновлен, хотя ни одно одно значение фактически не изменилось. При написании триггера необходимо на самом деле тестировать индивидуальные значения до и после того, чтобы убедиться, что вы получаете реальные изменения (если это то, что вы хотите).
P.S. Вы также можете unpivot Как говорит Роб, но вам все равно нужно явно указывать столбцы в unpivot , это не волшебство.
Попробуйте неподвижность как вставленных, так и удаленных, а затем вы можете присоединиться, ищите, где значение изменилось.
Если вы используете SQL Server 2008, вы, вероятно, должны взглянуть на новую функцию захвата данных. Это сделает то, что вы хотите.
Можно обнаружить это в триггере или использовать CDC в SQL Server 2008.
Если вы создадите триггер FOR AFTER UPDATE, то вставленная таблица будет содержать строки с новыми значениями, а таблица deleted будет содержать соответствующие строки со старыми значениями.
OUTPUT deleted.bar AS [OLD VALUE], inserted.bar AS [NEW VALUE]
@Кальвин, я просто основывался на примере UPDATE. Я не говорю, что это полное решение. Я намекал, что вы можете сделать это где-нибудь в своем коде ;-)
Так как я уже получил -1 из приведенного ответа, позвольте мне внести это:
Если вы на самом деле не знаете, какой столбец был обновлен, я бы сказал создать триггер и использовать функцию COLUMNS_UPDATED() в теле этого триггера (см. this)
Я создал в своем блоге Ссылку на битовую маску для использования с этим COLUMNS_UPDATED(). Это сделает вашу жизнь легче, если вы решите следовать по этому пути (Trigger + Columns_Updated())
Если вы не знакомы с Trigger, вот мой пример базового триггера http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/