Создание аудита вызывает в SQL-сервере
Я должен осуществить отслеживание изменения на двух столах в моей базе данных SQL Server 2005. Я должен ревизовать дополнения, удаления, обновления (с деталью о том, что было обновлено). Я был планированием использования спускового механизма, чтобы сделать, это, но после тыкания вокруг на Google I нашло, что было невероятно легко сделать это неправильно, и я хотел избежать, чтобы на получении - пошли.
Кто-либо может отправить пример спускового механизма обновления, который достигает этого успешно и изящным способом? Я надеюсь закончить с контрольной таблицей со следующей структурой:
- ID
- LogDate
- TableName
- TransactionType (обновляют/вставляют/удаляют)
- RecordID
- FieldName
- OldValue
- NewValue
... но я открыт для предложений.
Спасибо!
2 ответа
Я просто хочу выкрикнуть пару пунктов:
Используйте генераторы кода Вы не можете иметь единую процедуру для отслеживания всех таблиц, вам нужно будет генерировать похожие, но разные триггеры на каждой отслеживаемой таблице. Такая работа лучше всего подходит для автоматической генерации кода. На вашем месте я бы использовал XSLT преобразование для генерации кода из XML, и XML может генерироваться автоматически из метаданных. Это позволяет вам легко поддерживать триггеры, регенерируя их каждый раз, когда вы вносите изменения в логику/структуру аудита или добавляете/изменяете целевую таблицу.
Рассмотрим планирование емкости для аудита. Таблица аудита, которая отслеживает все изменения значений, будет на сегодняшний день самой большой таблицей в БД: она будет содержать все текущие данные и всю историю текущих данных. Такая таблица увеличит размер базы данных на 2-3 порядка (x10, x100). И таблица аудита быстро станет узким местом во всем:
- для каждой операции DML потребуются блокировки в таблице аудита
- все административные и обслуживающие операции должны будут вместить в себя размер БД из-за аудита
С учетом изменений схемы . Таблица с именем 'Foo' может быть опущена, а позже может быть создана другая таблица с именем 'Foo'. Аудиторский след должен уметь различать два разных объекта. Лучше использовать медленно изменяющийся размер .
Рассмотрим необходимость эффективного удаления аудиторских записей. Когда срок хранения, продиктованный политикой объекта вашего приложения, наступит, вы должны будете иметь возможность удалить соответствующие аудиторские записи. Сейчас это может показаться не таким уж важным, но 5 лет спустя, когда должны быть готовы первые записи, таблица аудита выросла до 9,5ТБ, это может стать проблемой.
Рассмотрим необходимость запросить аудит. Структура таблицы аудита должна быть подготовлена так, чтобы эффективно реагировать на запросы об аудите. Если Ваш аудит не может быть запрошен, то он не имеет значения. Запросы будут полностью зависеть от Ваших требований, и только Вы их знаете, но большинство записей аудита опрашивается в течение временных интервалов ("какие изменения произошли вчера между 19:00 и 20:00?"), по объектам ("какие изменения произошли с этой записью в этой таблице?") или по автору ("какие изменения произошли с Бобом в базе данных?").
Не существует универсального способа сделать это так, как вы хотите. В конечном итоге вы пишете кучу кода для каждой таблицы. Не говоря уже о том, что это может быть довольно медленным, если вам нужно сравнить каждый столбец на предмет изменений.
Также тот факт, что вы можете обновлять несколько строк одновременно, подразумевает, что вам нужно открыть курсор, чтобы перебрать все записи.
Я бы использовал таблицу со структурой, идентичной таблицам, которые вы отслеживаете, и откатил бы ее позже, чтобы показать, какие столбцы действительно изменились. Я также отслеживал сеанс, в котором действительно произошли изменения. Это предполагает, что у вас есть первичный ключ в отслеживаемой таблице.
Итак, учитывая такую таблицу
CREATE TABLE TestTable
(ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
Name1 NVARCHAR(40) NOT NULL,
Name2 NVARCHAR(40))
, я бы создал такую таблицу аудита в схеме аудита.
CREATE TABLE Audit.TestTable
(SessionID UNIQUEIDENTIFER NOT NULL,
ID INT NOT NULL,
Name1 NVARCHAR(40) NOT NULL,
Name2 NVARCHAR(40),
Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),
RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),
ChangedDate DATETIME NOT NULL Default GETDATE(),
ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())
И триггер для обновления, подобный этому
CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @SessionID UNIQUEIDENTIFER
SET @SessionID = NEWID()
INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted
INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted
END
Это работает довольно быстро. Во время отчетности вы просто объединяете строки на основе идентификатора сеанса и первичного ключа и создаете отчет. В качестве альтернативы вы можете выполнить пакетное задание, которое периодически просматривает все таблицы в таблице аудита и подготавливает пару имя-значение, отображающую изменения.
HTH