Как отслеживать изменения данных в таблице?

Question

У меня простой вопрос. Как я могу отслеживать изменения строки в таблице SQL Server? Вот пример того, что я хочу.

Таблица: пользователи

Столбцы: Имя | Адрес | Имя пользователя UserType

Ряд 1: Кристофер | 123 Fake Street | Любовник1234 | 1

Как я могу отслеживать, в какое время пользователь меняет "Кристофер" на "Роберт" или же он меняет "123 Fake Street" на "124 Fake Street". Мне нужно знать время изменения, а также старое значение, которое было изменено. Я также хотел бы иметь возможность отслеживать изменения UserType (который является внешним ключом), поэтому мне нужно знать, как отслеживать изменения внешнего ключа.

Это может происходить в нескольких разных таблицах, и я хотел бы отслеживать несколько разных таблиц.

Answer 1

Есть разные варианты сделать это. Я упомяну два:

Опция 1:

Добавьте столбец с именем IsHistory в вашу таблицу. Вы получите что-то вроде этого:

Name        | Address         | Username  | UserType | IsHistory
------------+-----------------+-----------+----------+----------
Christopher | 123 Fake Street | Lover1234 | 1        | 0

Затем, когда вы обновите запись, измените значение IsHistory на старой записи на 1, а затем добавьте новую запись с обновленной информацией, с записью IsHistory на 0. Вы получите что-то вроде этого:

Name        | Address         | Username  | UserType | IsHistory
------------+-----------------+-----------+----------+----------
Christopher | 123 Fake Street | Lover1234 | 1        | 1
Robert      | 123 Fake Street | Lover1234 | 1        | 0

Вариант 2:

Добавьте таблицу журнала, где вы можете иметь следующее:

LogID | Entity | ActionType | Description | OldDataXML

Затем, каждый раз, когда происходит обновление, вставьте сюда запись.

• LogID - это ПК
• Entity - это таблица, на которую повлияло обновление
• Тип действия - это действие, выполняемое над объектом (INSERT, UPDATE, DELETE).
• Описание - это краткий текст, объясняющий изменение
• OldDataXML - это данные, которые были в записи до изменений в XML.

Например:

<User Name="Christopher" 
      Address="123 Fake Street" 
      Username="Lover1234" 
      UserType="1">
</User>

В этом случае запись в таблице "Пользователи" всегда будет самой последней, а в таблице журнала вы будете иметь информацию об изменениях.

Надеюсь, это поможет вам.

Answer 2

ChangeDataCapture встроен в SQL Server 2008 R2 и может справиться с этим ( http://technet.microsoft.com/en-us/library/bb522489%28v=sql.105%29.aspx)

Альтернатива, которую я обычно использовал, очень похожа на то, что @scubaFun имел в своем ответе: создать таблицу журналирования, которая отражает исходную таблицу, плюс столбец типа изменений и изменил (и все остальное, что вы хотите), а затем используйте триггеры на главной таблице, чтобы заполнить таблицу регистрации.