Отслеживание изменений в SQL Server без триггеров на .NET, C#

Быстрый ответ

Для отслеживания изменений в таблице SQL Server можно использовать триггеры.

Вот пример эффективного аудит-триггера:

CREATE TRIGGER dbo.TableAuditTrigger
ON dbo.YourTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO dbo.AuditLog (EventType, RecordID, ChangedData)
    SELECT CASE WHEN i.ID IS NOT NULL AND d.ID IS NULL THEN 'INSERT'
                WHEN i.ID IS NOT NULL AND d.ID IS NOT NULL THEN 'UPDATE'
                WHEN i.ID IS NULL AND d.ID IS NOT NULL THEN 'DELETE'
           END, COALESCE(i.ID, d.ID), COALESCE(i.YourColumn, d.YourColumn)
    FROM inserted i
    FULL OUTER JOIN deleted d ON d.ID = i.ID
END;

Схему AuditLog следует скорректировать, заменив названия YourTable и YourColumn на названия вашей таблицы и соответствующего столбца. Триггер будет регистрировать все действия INSERT, UPDATE, DELETE, определяя их тип и затрагиваемые данные.

Границы возможностей триггеров

Регулярные проверки – контрольные суммы приходят на помощь

Если использование триггеров невозможно, например, когда база данных не принадлежит вам, можно применить контрольные суммы. С помощью функций BINARY_CHECKSUM и CHECKSUM_AGG можно получить идентификатор, который меняется при любом изменении в таблице.

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.YourTable WITH (NOLOCK);

В случае использования WITH (NOLOCK) будьте осторожны, чтобы избежать блокировок, и готовы к возможным неточностям, обусловленным грязными чтениями.

Подробнее об этом расскажет наш спикер на видео

Контрольные суммы – "Вы уверены?"

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

SQL Server и механизмы отслеживания изменений

В SQL Server начиная с версии 2005 доступен класс SQLDependency. Основываясь на уведомлениях о запросах, он автоматически отслеживает изменения в результатах запросов.

Визуализация

Отслеживание изменений можно сравнить с наблюдением за изменениями в песочнице:

Исходное состояние (🏖️):

1. Фигурки в T1:        [🚀, 🏰, 🐢, 🚗]
2. Фигурки в T2:        [🚀, 🏰, 🌵, 🚗]
3. Обнаружены изменения: "🐢 превратился в 🌵!"

Подобно тому, как мы замечаем новую игрушку там, где она раньше не была, SQL Server улавливает изменения.

🏖️ Снимок 1:    [🚀, 🏰, 🐢, 🚗]
🕐 Время идет...
🏖️ Снимок 2:    [🚀, 🏰, 🌵, 🚗]
🚨 Уведомление об изменении: [🐢] теперь [🌵]

Мониторинг изменений в SQL Server позволяет обнаруживать именно такие моменты.

Повышение эффективности отслеживания изменений

Хотите большей точности? Используйте отслеживание изменений и CDC

Для ещё более точного отслеживания изменений рассмотрите возможность использования функций Отслеживания изменений и Захвата изменений данных (CDC) SQL Server. Они требуют дополнительных ресурсов, но предоставляют детальную историю изменений.

Сотрудничество с поставщиками для уведомления об изменениях

Договоритесь с поставщиками программного обеспечения о системе уведомления о данных, обновляющихся без вмешательства в структуру базы данных. Это часто доступно через API или специфические интеграции.

Взвешивание компромиссов

Найдите баланс между размером таблиц и частотой изменений для оптимизации работы системы отслеживания изменений. Для больших таблиц возможно следует применять индексированные представления или пакетные вычисления контрольных сумм.

API как инструмент отслеживания изменений

API предлагает эффективный способ связать изменения в таблицах с внешними приложениями. Это удобно и легко масштабируется, особенно в микросервисной архитектуре систем.

Полезные материалы

1. О функции отслеживания изменений в SQL Server — Подробное руководство.
2. Пример использования отслеживания изменений в SQL Server — Кейс из практики.
3. Введение в CDC в SQL Server — Глубокий анализ возможностей CDC.
4. Использование временных таблиц в SQL Server — Методика для работы с историческими данными.
5. Мониторинг заданий SQL Server Agent — Настройка уведомлений для контроля заданий.