Триггеры аудита: используйте системные таблицы INSERTED или DELETED

Тема аудита таблиц недавно возникла в наших обсуждениях ... так что мне нравится ваше мнение о том, как лучше всего подойти к этому. У нас есть сочетание обоих подходов (что нехорошо) в нашей базе данных, поскольку каждый предыдущий администратор базы данных делал то, что он / она считал правильным. Поэтому нам нужно изменить их, чтобы они соответствовали какой-либо одной модели.

CREATE TABLE dbo.Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
)

CREATE TABLE dbo.Audit_Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
Audit_Type VARCHAR(1)  NOT NULL
Audited_Created_On DATETIME
Audit_Created_By VARCHAR(50)
)

Подход 1: Храните в таблицах аудита только те записи, которые заменены / удалены из основной таблицы (с использованием системной таблицы DELETED). Таким образом, для каждого UPDATE и DELETE в основной таблице заменяемая запись ВСТАВЛЯЕТСЯ в таблицу аудита со столбцом Audit_Type как с буквой U (для UPDATE) или D (для DELETE).

ВСТАВКИ не проходят аудит. Для текущей версии любой записи вы всегда запрашиваете основную таблицу. А для истории вы запрашиваете таблицу аудита.

Плюсы: кажется интуитивно понятным, хранить предыдущие версии записей. Минусы: если вам нужно знать историю конкретной записи, вам нужно объединить таблицу аудита с основной таблицей.

Оценка 2: Сохраните в таблице аудита каждую запись, которая входит в основную таблицу (с использованием системной таблицы INSERTED).

Каждая запись, которая вставлена ​​/ обновлена ​​/ удалена в основную таблицу, также сохраняется в таблице аудита. Таким образом, когда вы вставляете новую запись, она также вставляется в таблицу аудита. При обновлении таблица новой версии (из INSERTED) сохраняется в таблице аудита. При удалении таблица старой версии (из DELETED) сохраняется в таблице аудита.

Плюсы: если вам нужно знать историю конкретной записи, у вас есть все в одном месте.

Хотя я не перечислил их все здесь, у каждого подхода есть свои плюсы и минусы?


audit auditing sql-server-2005 audit-tables
person UB.    schedule 25.09.2009    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Я бы пошел с:

Оценка 2: Сохраните в таблице аудита каждую запись, которая входит в основную таблицу (с использованием системной таблицы INSERTED).

действительно ли еще одна строка на элемент убьет БД? Таким образом, у вас будет полная история.

Если вы удалите строки (диапазон старше X дней), вы все равно сможете определить, изменилось что-то или нет:

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

если вы выберете Appraoch 1: и очистите диапазон, будет сложно (нужно запомнить дату очистки) указать новые вставки по сравнению с теми, где были очищены все строки.

person KM.    schedule 25.09.2009
comment
После того, как я написал все свои мысли в этом вопросе / сообщении и перечитал его, у меня тоже возникла такая же мысль. Подход 2 кажется лучше. Небольшая над головой незначительна. Спасибо за ваш вклад. - person UB.; 25.09.2009

arrow_upward
0
arrow_downward

Третий подход, который мы часто используем, - это проверять только интересующие столбцы и сохранять как «новое», так и «старое» значение в каждой строке.

Итак, если у вас есть столбец «name», в таблице аудита будут «name_old» и «name_new».

В триггере INSERT для "name_old" установлено значение blank / null в зависимости от ваших предпочтений, а для "name_new" устанавливается значение INSERTED. В триггере UPDATE «name_old» устанавливается из DELETED, а «name_new» из INSERTED. В триггере DELETE «name_old» устанавливается из DELETED, а «new_name» - в пустое / нулевое значение.

(или вы используете ПОЛНОЕ соединение и один триггер для всех случаев)

Для полей VARCHAR это может показаться не очень хорошей идеей, но для INTEGER, DATETIME и т. Д. Это дает то преимущество, что очень легко увидеть разницу в обновлении.

Т.е. если у вас есть поле количества в вашей реальной таблице и вы обновите его с 5 до 7, у вас будет таблица аудита:

quantity_old  quantity_new
           5             7

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

Если у вас есть отдельные строки в таблице аудита, вам нужно будет объединить одну строку со «следующей», чтобы вычислить разницу, что в некоторых случаях может быть непросто ...

person Brimstedt    schedule 25.09.2009
comment
Спасибо за ответ. Я слышал об этом подходе раньше, но никогда не реализовывал. Я подумаю об этом дальше. спасибо, _UB - person UB.; 25.09.2009