Записывать изменения, внесенные во все поля таблицы, в другую таблицу (SQL Server 2005)

Я хотел бы регистрировать изменения, внесенные во все поля таблицы, в другую таблицу. Это будет использоваться для ведения истории всех изменений, внесенных в эту таблицу (Ваша основная таблица журнала изменений).

Каков наилучший способ сделать это в SQL Server 2005?

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

Каков хороший способ обойти все поля, проверяя изменения без жесткого кодирования всех полей?

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

Я заметил, что в SQL Server 2008 появилась новая функция, называемая Change Data Capture (CDC). (Вот хорошее видео Channel9 на CDC). Это похоже на то, что мы ищем, за исключением того, что мы используем SQL Server 2005, уже располагаем макетом таблицы журналов и также ведем журнал пользователя, который внес изменения. Мне также трудно оправдать запись до и после изображения всей записи, когда одно поле может измениться.

Наша текущая структура файла журнала имеет столбец для Имя поля, Старые данные, Новые данные.

Заранее спасибо и хорошего дня.

Обновлено 22.12.08. Я провел еще несколько исследований и нашел эти два ответа в Live Search QnA.

  1. Вы можете создать триггер для этого. См. Как я проверяю изменения в данных сервера SQL .

  2. Вы можете использовать триггеры для регистрации изменений данных в таблицах журналов. Вы также можете приобрести Log Explorer на сайте www.lumigent.com и использовать его для чтения журнала транзакций, чтобы узнать, какой пользователь внес изменения. Однако для этой опции база данных должна быть полностью восстановлена.

Обновление от 23.12.08: Я также хотел получить чистый способ сравнить то, что изменилось, и это выглядело как обратная сторона PIVOT, который, как я обнаружил в SQL, называется UNPIVOT. Сейчас я склоняюсь к триггеру, использующему UNPIVOT для таблиц INSERTED и DELETED. Мне было любопытно, если это уже было сделано, поэтому я прохожу поиск по запросу «удалено добавлено ».

  1. Публикация Использование функции обновления из триггера «после» имело несколько разных идей, но я все еще верю, что UNPIVOT будет подходящим путем.
15.12.2008 22:57:35
3 ОТВЕТА

Под SQL '05 вам на самом деле не нужно использовать триггеры. Просто взгляните на предложение OUTPUT. ВЫХОД работает со вставками, обновлениями и удалениями.

Например:

INSERT INTO mytable(description, phone)
OUTPUT INSERTED.description, INSERTED.phone INTO #TempTable
VALUES('blah', '1231231234')

Затем вы можете делать с таблицей #TempTable все, что захотите, например вставлять эти записи в таблицу журналов.

В качестве примечания, это чрезвычайно простой способ получения значения поля идентичности.

4
15.12.2008 23:19:35
Не означает ли это, что вам нужно обновлять каждую хранимую процедуру / оператор обновления, чтобы использовать предложение вывода? Я считаю, что причина использования триггера в том, что вам не нужно этого делать.
user420667 14.12.2015 20:13:54
@ user420667: Это был просто альтернативный способ достижения цели. Не "лучший" способ в большинстве случаев, но альтернативный.
NotMe 14.12.2015 20:38:38
Ах я вижу. Я неверно истолковал «Под SQL '05 вам на самом деле не нужно использовать триггеры» как «не используйте триггеры», но теперь я понимаю, что вы имеете в виду. Спасибо!
user420667 14.12.2015 23:22:13

Вы можете использовать Log Rescue . Это почти так же, как Log Explorer, но это бесплатно.

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

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

0
22.12.2008 16:36:42

Довольно поздно, но, надеюсь, это будет полезно для других читателей ...

Ниже приведена модификация моего ответа, который я опубликовал на прошлой неделе на аналогичную тему.

Короткий ответ: нет «правильного» решения, которое бы подходило всем. Это зависит от требований и проверяемой системы.

Триггеры

  • Преимущества : относительно прост в реализации, большая гибкость в отношении того, что проверяется и как хранятся данные аудита, потому что у вас есть полный контроль
  • Недостатки : это становится грязным, когда у вас много таблиц и даже больше триггеров. Техническое обслуживание может стать тяжелым, если не будет никакого стороннего инструмента, чтобы помочь Кроме того, в зависимости от базы данных это может повлиять на производительность.

Создание триггеров аудита в SQL Server
Журнал изменений в таблице базы данных с триггером

CDC

  • Преимущества : очень прост в реализации, изначально поддерживается
  • Недостатки : доступно только в корпоративной версии, не очень надежно - если вы измените схему, ваши данные будут потеряны. Я бы не рекомендовал это для ведения долгосрочного аудита

Чтение журнала транзакций

  • Преимущества : все, что вам нужно сделать, это перевести базу данных в режим полного восстановления, и вся информация будет храниться в журнале транзакций.
  • Недостатки : для эффективного чтения вам необходим сторонний читатель логов

Прочитайте файл журнала (* .LDF) в SQL Server 2008
SQL Server Transaction Log Explorer / Analyzer

Сторонние инструменты

Я работал с несколькими инструментами аудита из ApexSQL, но есть также хорошие инструменты от Idera (менеджер соответствия) и программного обеспечения Krell (omni аудит)

Аудит ApexSQL - инструмент аудита на основе триггеров. Генерирует и управляет триггерами аудита

ApexSQL Log - позволяет проводить аудит путем чтения журнала транзакций

5
23.05.2017 12:09:14