Ответ.

Таблицы-журналы изменений (log/audit tables) — это обычные таблицы, в которые сохраняются события изменения данных в продакшн-таблицах: кто, когда и что изменил. Актуально для финансового сектора, e-commerce, госучреждений.

Подходы к реализации:

Явная запись в журнал через приложение или процедуру.
Использование триггеров для отслеживания любых изменений.
В новых СУБД — системные механизмы Change Data Capture (CDC) или Temporal Tables.

Типичный состав:

audit_id (PK), TableName, OperationType (I/U/D), RecordID, OldValue, NewValue, ChangedBy, ChangeDT

Пример кода (триггер для аудита UPDATE)

CREATE TABLE ProductAudit (
  AuditID int IDENTITY(1,1) PRIMARY KEY,
  ProductID int,
  OldPrice money,
  NewPrice money,
  ChangedBy sysname,
  ChangeDT datetime,
  OperationType char(1)
);
GO
CREATE TRIGGER trg_ProductAudit
ON Products
AFTER UPDATE
AS
BEGIN
  INSERT INTO ProductAudit(ProductID, OldPrice, NewPrice, ChangedBy, ChangeDT, OperationType)
    SELECT d.ProductID, d.Price, i.Price, SYSTEM_USER, GETDATE(), 'U'
    FROM inserted i JOIN deleted d ON i.ProductID = d.ProductID
    WHERE i.Price <> d.Price;
END

Подводные камни:

Большой объем аудит-таблицы может замедлять выборку.
Необходим грамотный партиционинг/удаление старых данных.
Нужно следить за блокировками и дедупликацией изменений.

Вопрос с подвохом.

В: Автоматически ли отменяется внесённая в log-таблицу запись при откате основной транзакции?

A: Если триггер — часть транзакции, то да, вставка в журнал откатывается вместе с основной операцией. Однако при логировании на уровне приложения/отдельной транзакции согласованности может не быть.

Примеры реальных ошибок из-за незнания тонкостей темы.

История

В кредитном учреждении не был реализован механизм чистки audit-таблицы. Объём превысил сотни миллионов строк, что привело к долгим бэкапам, нехватке места и деградации производительности всей БД.

История

Разработчик делал аудит через отдельное соединение вне транзакции. В результате появлялись записи о событиях, которых не существовало (транзакция прикладной логики откатывалась, а запись в log — нет).

История

При проектировании журнала лог изменений ввели всем полям тип NVARCHAR(MAX) для универсальности. Это не потребовалось, лишь перегрузило хранение и индексирование — данные невозможно было анализировать эффективно.