Как сохранить аудит/историю изменений в таблице

Также используйте триггеры.

Любой, кто рассматривает возможность мягкого удаления, должен прочитать книгу Ричарда Дингволла Проблема с мягким удалением .

Наиболее универсальным методом было бы создание другой таблицы для хранения версий записи из первой таблицы. Затем вы можете удалить все данные из основной таблицы. Предположим, вам необходимо управление версиями таблицы. Person (PersonId, Name, Surname):

CREATE TABLE Person 
(
   PersonId INT,                   // PK
   CurrentPersonVersion INT        // FK
);

CREATE TABLE PersonVersion
(
  PersonVersionId INT,             // PK
  PersonID                         // FK 
  Name VARCHAR,                    // actual data
  Surname VARCHAR,                 // actual data

  ChangeDate                       // logging data
  ChangeAuthor                     // logging data
)

Теперь любое изменение требует вставки новой версии PersonVersion и обновления CurrentPersonVersionID.

Другой способ сделать это помимо триггеров:

1. Имеет четыре столбца, UpdFlag , DelFlag , EffectiveDate и TerminatedDate для каждой таблицы, в которой вы хотите вести контрольный журнал.
2. закодируйте свой sproc таким образом, чтобы при обновлении передавать все данные столбца строки в sproc обновите строку, установив для TerminatedDate дату, которая была обновлена, отметьте UpdFlag и поместите дату и время в столбец
3. . Затем создайте новую строку с новыми данными (которые действительно обновлен). и введите новую дату сейчас для EffectiveDate и TerminatedDate , установленную на максимальную дату.

Аналогичным образом, если вы хотите удалить строку, просто обновите строку, пометив DelFlag как установленный, TerminatedDate с датой и временем сейчас. Фактически вы выполняете мягкое удаление, а не фактическое удаление sql.

Таким образом, когда вы хотите проверить данные и показать след изменений, вы можете просто отфильтровать строки для тех, которые имеют UpdFlag установлен или между EffectiveDate и TerminatedDate . Аналогичным образом для тех, которые были удалены, вы фильтруете те, у которых установлен DelFlag или между EffectiveDate и TerminatedDate . Для текущих строк отфильтруйте строки, у которых установлены оба флага. Преимущество состоит в том, что вам не нужно создавать другую таблицу для аудита при использовании триггера!

Таким образом, когда вы хотите проверить данные и показать след изменений, вы можете просто отфильтровать строки для тех, которые имеют UpdFlag установлен или между EffectiveDate и TerminatedDate . Аналогичным образом для тех, которые были удалены, вы фильтруете те, у которых установлен DelFlag или между EffectiveDate и TerminatedDate . Для текущих строк отфильтруйте строки, у которых установлены оба флага. Преимущество состоит в том, что вам не нужно создавать другую таблицу для аудита при использовании триггера!

Таким образом, когда вы хотите проверить данные и показать след изменений, вы можете просто отфильтровать строки для тех, которые имеют UpdFlag установлен или между EffectiveDate и TerminatedDate . Аналогичным образом для тех, которые были удалены, вы фильтруете те, у которых установлен DelFlag или между EffectiveDate и TerminatedDate . Для текущих строк отфильтруйте строки, у которых установлены оба флага. Преимущество состоит в том, что вам не нужно создавать другую таблицу для аудита при использовании триггера!

Я бы пошел по маршруту триггеров, создав таблицу со структурой, аналогичной обновленной, с дополнительными столбцами для отслеживания изменений, например ModifiedAt и т. Д. А затем добавив триггер обновления, который будет вставлять изменения в эту таблицу . Я считаю, что это легче поддерживать, чем содержать все в коде приложения. Конечно, многие люди склонны забывать о триггерах, когда речь идет о таких вопросах, как «Что, если эта таблица меняется?») Ура.

Лучший способ сделать это - настроить триггеры в базе данных, которые записывают в таблицы аудита.