Когда-либо вам понадобилось быстрое и ретроспективное решение для отслеживания изменений в вашей базе данных SQL Server? В этой статье мы рассмотрим метод записи обновлений, выполненных через веб-сайт, хранящий информацию, такую как данные пользователя, информацию о странице, старые и новые значения измененных данных и временную метку.

Таблица журнала

Первый шаг в реализации этого решения – создание таблицы журнала. Таблица журнала будет хранить следующую информацию:

• LogID: Уникальный идентификатор для каждой записи в журнале
• TableName: Имя таблицы, в которой произошло изменение
• RecordNumber: Идентификатор записи, которая была обновлена
• ActionBy: Пользователь, сделавший изменение
• ActionPage: Страница, на которой было выполнено действие
• ChangeClmn: Имя столбца, который был обновлен
• OldValue: Старое значение обновленного поля
• NewValue: Новое значение обновленного поля
• ActionDate: Дата и время, когда произошло изменение

Важно отметить, что длина столбцов OldValue и NewValue должна быть такой же, как у самого длинного поля в отслеживаемой базе данных. Если поля слишком большие для отслеживания, вы можете рассмотреть их усечение.

План

Самый простой способ создать журнал обновлений – это использование триггеров. Однако создание триггеров для каждой таблицы может быть затратным по времени и ресурсам. Вместо этого мы можем создать маршрутизирующую хранимую процедуру, которая динамически создает триггер для обновляемой таблицы, запускает исходную хранимую процедуру, а затем удаляет триггер.

Маршрутизирующая хранимая процедура

Маршрутизирующая хранимая процедура ожидает следующие параметры:

• @SPName: Имя запускаемой хранимой процедуры
• @str1: Параметры для хранимой процедуры @SPName
• @TableName: Имя таблицы, на которой должен быть создан триггер
• @RecordId: Значение идентификатора таблицы
• @OpName: Имя пользователя, вносящего изменения
• @PageName: Имя страницы, на которой было выполнено действие

Маршрутизирующая хранимая процедура создает триггер на указанной таблице, запускает исходную хранимую процедуру с предоставленными параметрами, а затем удаляет триггер.

Триггер

Цель триггера – найти обновленные значения и вставить запись в таблицу журнала для каждого обновленного поля. Он делает это, перебирая каждый столбец и сравнивая старое значение с новым значением.

Триггер использует функцию columns_updated(), чтобы определить, какие столбцы были обновлены. Затем он сравнивает старые и новые значения с помощью динамического SQL и вставляет запись в таблицу журнала, если значения отличаются.

Ограничения

У этого решения есть несколько ограничений. Оно не будет работать для типов данных text, ntext или image. Также рекомендуется поддерживать небольшие длины полей, чтобы избежать превышения максимальной длины параметра @str1 и оптимизировать системные ресурсы.

Вывод

Хотя это решение может быть не самым эффективным способом отслеживания изменений в базе данных, оно предоставляет быстрый и эффективный метод для ретроспективной записи обновлений. Используя комбинацию триггеров и маршрутизирующей хранимой процедуры, вы легко можете реализовать эту функциональность в ваших проектах SQL Server.

Спасибо за чтение!