Базы данных -> SQL -> Журналирование изменений структуры БД и данных

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

СОДЕРЖАНИЕ

Задачи, которые решаются контролем изменений структуры
Задачи, которые решаются контролем изменений данных
Стандартные средства мониторинга событий
Изменения в структуре базы (создание/удаление/изменение таблиц, процедур...)
    Журналирование изменений структуры базы стандартными средствами
    Другие способы контроля изменений структуры базы
Изменения в данных
    Журналирование изменений данных стандартными средствами
    Журналирование изменений данных с помощью триггеров
        Хранение сведений о последних изменениях в данных
        Фиксирование только факта выполнения операции с данными
            Реализация в одном триггере
            Реализация в трех триггерах
        Фиксирование факта выполнения операции с определенными полями
        Журналирование изменений вместе с изменяемыми значениями
            Структура журнала
                Одна таблица журнала, одно поле для хранения значений
                Несколько таблиц журнала, копии структур рабочих таблиц
                Одна таблица журнала, количество полей равно количеству типов
            Хранение старых и новых значений при операциях UPDATE
            Особенности работы с BLOB-полями
        Фиксирование реального изменения данных
    Сосуществование триггеров журналирования с другими триггерами системы
    Автоматизация процесса создания триггеров для журналирования данных
Новое в версии MS SQL Server 2005: триггеры на DDL
Заключение
Приложения
Пример 1: только последние изменения; хранятся в той же таблице
Пример 2: отдельная таблица архива; только факт операции; один триггер
Пример 3: только факт операции; три триггера
Пример 4: хранятся изменяемые значения; 1 таблица архива; одно поле для значений
Пример 5: хранятся изменяемые значения; N таблиц архива; копии рабочих таблиц
Пример 6: хранятся изменяемые значения; 1 таблица архива; N полей
Пример 7: хранятся изменяемые значения; BLOB-поля, только новые значения
Пример 8: хранятся изменяемые значения; BLOB-поля, новые и старые значения
Пример 9: хранятся изменяемые значения; только реальные изменения
Пример 10: триггеры на DDL

Задачи, которые решаются контролем изменений структуры

1. Кто, когда, с какой машины и каким приложением менял структуру базы.
   Задача скорее административная. Для разбирательств постфактум "кто, когда и зачем все сломал".

2. Ведение истории изменений для последовательного наращивания версий.
   Пример 1: у пользователей есть версия X базы, разработчики постепенно вносят некоторые изменения, и спустя какое-то время появляется версия Y. Задача состоит в том, чтобы, не ломая существующей у пользователей базы, также последовательно нарастить ее до версии Y.
   Впрочем, такую задачу можно (и даже более удобно) решить и без накапливания последовательных изменений: сравнивая две базы с использованием системных представлений, процедур и таблиц.

3. Автоматизация различных рутинных действий в базе.
   Пример 2: в разрабатываемой системе есть два типа пользовательских таблиц - реплицируемые и нереплицируемые. Структура репликации хорошо продумана, все обкатано и работает. Пусть имена реплицируемых таблиц начинаются с префикса "rt", а нереплицируемые с префикса "t". Новая таблица автоматически включается в публикацию, если имеет соответствующий префикс. Изменение структуры базы - добавление таблицы - является сигналом для запуска кода, который может добавить таблицу в существующую публикацию.
   Пример 3: на таблицы в базе (все или некоторые) автоматически создается набор триггеров определенной структуры для решения стандартных для данной системы задач. Или набор процедур, когда прямые действия с таблицами запрещены, а разрешены только через хранимые процедуры.

[В начало]

Задачи, которые решаются контролем изменений данных

1. Кто, когда, с какой машины и каким приложением менял данные в базе.
   Задача, скорее относящаяся к безопасности. Журнал используется для восстановления реальной картины событий, когда данные испорчены либо утеряны, а те пользователи, кто имеет доступ к базе и права на работу с данными, искажают (намеренно или нет) картину прошлых действий. Данные, которые нужны для такой задачи: user_name, date/time, host_name, app_name, table_name, record_id.
   Подвидом данной задачи и ее упрощенным вариантом является хранение сведений о последнем изменении в данных. То есть last_user, last_datetime и т.д.
   Как в полном виде, так и в сокращенном, данный тип отвечает скорее за административный аспект необходимости журналирования. Длительность хранения данных в журнале может сильно варьироваться.

2. Ведение истории изменения данных.
   Здесь в большей степени имеет значение, какие именно данные менялись, старые и/или новые значения. Используется в случаях, когда нужна полная история изменения данных. История хранится достаточно долго, возможно, на протяжении всей жизни данных. В некоторых случаях история хранится и после того, как данные были удалены.

3. Уведомление об изменениях данных.
   Приложение может хранить на промежуточном или клиентском уровне некоторый объем данных, которые необходимо своевременно обновлять при изменениях в базе. Например, это может быть некий кэш, используемый для ускорения работы, или данные, выводимые на пользовательском интерфейсе.
   Здесь реализация может сильно различаться в зависимости от решаемой задачи. В некоторых случаях достаточно журналирования лишь факта операции, в некоторых журнал должен содержать все сведения, вплоть до старых и/или новых значений. Характерным признаком данного класса задач является короткая продолжительность хранения данных в журнале. Типичная ситуация: с некоторой периодичностью запускается процесс, который считывает все изменения из журнала, рассылает уведомления, очищает журнал и отключается.

4. Нестандартная репликация.
   В MS SQL 2000 есть различные типы репликации, которые при грамотном использовании способны решить большинство задач, связанных с синхронизацией данных на разных серверах. Большинство, но не все. Типичный пример - off-line репликация (с использованием дискет, компакт-дисков или передачи данных по почте). Данную задачу при некотором упорстве можно решить, используя триггеры и журналирование данных.

[В начало]

Стандартные средства мониторинга событий

В MS SQL 2000 есть стандартные средства для мониторинга событий SQL-сервера: режим c2 audit и SQL Profiler.

Включение режима c2 audit приводит к созданию файлов логов, которые могут быть впоследствии просмотрены в SQL Profiler. Более подробно о "c2 audit" можно прочитать в справке к SQL-серверу.

В SQL Profiler (далее по тексту "профайлер") можно настроить фильтр только на нужные события, поскольку отслеживание всех действий в системе сильно замедляет работу и требует много места на диске. Этот универсальный инструмент может быть использован в административных целях (отслеживание соединений с сервером, смену пароля, раздачу прав и т.д.), для обнаружения узких мест в системе с целью повышения производительности, обнаружения блокировок, мониторинга изменений в структуре базы и изменений в данных, а также это отличный отладочный инструмент для тестирования приложений и разрешения проблем самого широкого спектра.

Результаты работы профайлера могут выводиться на экран, в файл со специальной структурой или в таблицу SQL сервера.

Управлять профайлером программным путем можно с помощью системных процедур и функций sp_trace* и fn_trace*.

[В начало]

Изменения в структуре базы (создание/удаление/изменение таблиц, процедур...)

MS SQL Server 2000 не предоставляет удобных средств для отслеживания изменений в структуре базы.

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

[В начало]

Журналирование изменений структуры базы стандартными средствами

С помощью профайлера можно отслеживать создание и удаление таблиц, процедур, функций, представлений и т.д. Для этого используются события Object:Created и Object:Deleted

Пример: создание и удаление таблицы t1 в профайлере выглядит так:

Есть сведения о базе, в которой происходит создание или удаление, есть имя объекта, класс события (создание или удаление) и вся необходимая системная информация - имя машины, имя SQL-сервера, приложение, имя пользователя, время и т.д.

Сведения об изменении и удалении объектов можно получать с помощью события Audit Object Derived Permission Event. Выглядит это так:

В документации заявлено, что данное событие должно отслеживать и CREATE -операции, но в реальности это не так.

"Records when a CREATE, ALTER, or DROP command is issued for specified object" (SQL Books Online)

Данное событие предоставляет больше данных об изменениях - см. поле TextData, но, к сожалению, CREATE этим событием игнорируется.

Достаточно полную информацию о тексте команды можно получить с помощью события SQL:StmtStarting

С помощью этого события можно отслеживать CREATE, ALTER, DROP, но у такого способа есть существенные недостатки:

• данное событие срабатывает для любых SQL-команд, т.е. в логе будут INSERT/UPDATE/DELETE/SELECT/EXEC PROC и т.д. Объем данных в логе критически увеличивается, в то время как процент полезной информации резко падает;

• поля ObjectName и ObjectID пустые, так что для того, чтобы понять, с каким объектом идет работа, придется выполнять парсинг поля TextData.

Использование профайлера с корректно настроенными фильтрами и автоматическим запуском на старте сервера может дать относительно простой и эффективный способ журналирования изменений в структуре базы.

Недостаток использования процедур sp_trace_* состоит в том, что они позволяют единственный способ хранения выходных данных - в файле.

Также стоит коротко упомянуть простой способ формирования скрипта для трассировки событий на сервере. Запустить профайлер, создать trace со всеми нужными настройками, и сохранить ее скрипт в файле: Menu->File->Script trace.

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

[В начало]

Другие способы контроля изменений структуры базы

При поиске различных путей для отслеживания изменений в структуре данных рано или поздно возникает идея создания триггеров на системные таблицы, как минимум на sysobjects и syscomments. Идея вполне логичная, но, к сожалению, MS SQL 2000 не позволяет создавать триггера на системные таблицы. Есть способы обойти этот запрет, но поскольку они не являются "чистыми" в плане разрешенности, приводить их нет смысла. К тому же в следующих версиях они могут и не сработать.

Остается вполне дозволенный, хотя и не совсем удобный, способ отслеживания изменений, который к тому же будет асинхронным.

В каждой базе есть таблица sysobjects. В ней есть поле schema_ver - "Version number that is incremented every time the schema for a table changes", которое может быть использовано для проверки, изменялся ли объект. Есть там и другие поля, анализируя изменения которых возникает предположение, что можно опираться и на них, желая узнать об изменениях объекта. Например, info или base_schema_ver. Но в документации они помечены как "Reserved. For internal use only", поэтому использовать их не рекомендуется.

Простейший вариант отслеживания изменений - это создание таблицы, в которую будет заноситься выборка из sysobjects. С некоторой периодичностью проводится сравнение сохраненных данных с реальными, а потом обновление пользовательской таблицы. Таким образом можно узнавать о создании, изменении и удалении объектов в базе. Если вас интересует не только факт изменений, но и какими именно они были, используйте таблицу syscomments, где хранятся тексты процедур, функций, представлений, триггеров и некоторых видов constraint'ов (DEFAULT и CHECK). Для изменений, происходящих в таблицах, можно использовать syscolumns, а также не забывайте о представлениях INFORMATION_SCHEMA и системных процедурах (sp_help, sp_helptext, sp_helpconstraint и прочих, начинающихся на sp_help).

Все упомянутые таблицы, процедуры и представления документированы.

Если изменения одного и того же объекта проводились несколько раз в промежутке между сравнениями, то достоверно узнать об этом не получится (анализ того, по какому алгоритму увеличивается значение schema_ver, это вероятный, но не достоверный способ).

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

Обратите внимание на возможности следующей версии, MS SQL 2005: Новое в версии MS SQL Server 2005: триггеры на DDL

[В начало]

Изменения в данных

Отслеживание изменений в данных, также как и отслеживание изменений в структуре базы, может быть выполнено как с помощью стандартных утилит, так и программным путем.

[В начало]

Журналирование изменений данных стандартными средствами

Уже рассмотренный нами профайлер может быть использован для мониторинга изменений данных.

Вернее сказать, в профайлере можно получить информацию о командах, которые приводили к изменению данных. Это событие SQL:StmtStarting. Выглядит это примерно так:

Обратите внимание на эту разницу: получать команды, приводящие к изменению данных, или получать изменения в данных. Первый вариант используется больше для административных задач (кто, когда и что делал в базе) и для получения комплексной информации о том, что происходит на сервере - какие процедуры, какие команды и в каком порядке выполняются от разных соединений. Второй вариант нужен в задачах, которые манипулируют именно данными, когда требуются точные сведения о том, в каких таблицах и какие данные изменялись. Например, нестандартная репликация или ведение истории данных.

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

NB: весь дальнейший материал подразумевает знакомство читателя с общим понятием триггеров в базах данных, а также знание особенностей работы триггеров в MS SQL Server.

Примеры кода рассчитаны на работу именно в MS SQL, они могут не работать в других СУБД. Тем не менее теоретическая часть материала является достаточно общей и может являться базой для разработки кода, подходящего для прочих СУБД.

[В начало]

Журналирование изменений данных с помощью триггеров

Журналирование изменений в данных - очень распространенная задача (вернее сказать, круг задач). Под обобщенным термином "журналирование работы с данными" могут пониматься несколько задач, разных не только по своей постановке, но и очень сильно отличающихся по реализации.
Прежде всего надо определиться, что именно подразумевается в вашей системе под изменением данных. Вопрос кажется простым, но на самом деле просьба детализировать данную задачу может привести к совершенно разным формулировкам. Ответьте на следующие вопросы:

1. Нужно ли журналировать запрос, не затрагивающий ни одной записи. Например:

   update t set f = 1 where id = 123

   В случае, если в таблице t нет записей с полем id = 123, данная операция не затронет ни одной строки. Тем не менее, во многих случаях считается предпочтительным отметить сам факт попытки изменения данных.
   Аналогичные примеры можно привести для INSERT и DELETE:

   insert into t (f1, f2, ...) select f3, f4 from t2 where <...>

   где SELECT не выбирает ни одной записи;

   delete from t where <...>

   где условию в WHERE не удовлетворяет ни одна запись.

2. Собираетесь ли вы журналировать все три операции с данными. Например, у вас есть таблица, содержащая сведения о сотрудниках фирмы и вас интересуют только факты приема на работу или увольнения, а смена сотрудником фамилии или места жительства - нет. Соответственно, вы будете журналировать INSERT и DELETE, а UPDATE - нет.

3. Собираетесь ли вы журналировать изменения во всех полях записей или только в некоторых. Например, при изменении данных в таблице сотрудников вас интересует журналирование изменений только заработной платы (кто и когда вносил данные изменения), а смена сотрудником фамилии вас по-прежнему не интересует.

4. Интересует вас "реальное" изменение данных или только факт выполнения операции с данными.
   Например: update t set f = <старое значение f> where <...>

   Реального изменения в данных нет - новое значение поля равно старому (т.н. "ложный" UPDATE). Но тем не менее, триггер на UPDATE срабатывает для тех записей, которые удовлетворяют условию в WHERE.

   Будет такая операция журналироваться в вашей системе или же нет?

   Часто на данный вопрос первым (и необдуманным) ответом является "нет". То есть вы считаете, что такие изменения данных журналироваться не должны, так как изменений не было. Прежде чем так ответить, взвесьте все "за" и "против". Если вы не хотите журналировать подобные изменения, то вам придется в триггере на UPDATE проверять поля затронутых записей (все или некоторые), сравнивая старые и новые значения между собой. Это может серьезно снизить производительность, особенно при большом количестве полей в таблице, да еще если там присутствуют строковые поля. А если есть еще поля типа TEXT / NTEXT / IMAGE с объемом реальных данных по 2GB на поле записи, то об адекватном времени отклика системы можно забыть.

   Также подумайте, насколько вам действительно нужно отслеживать "реальные" изменения в данных, когда новые значения полей не равны старым. Например, вы хотите на базе такого журнала изменений сделать оповещение клиентских приложений о том, что пора выполнить обновление отображаемой на пользовательском интерфейсе информации. С одной стороны, "ложные" оповещения создают излишнюю нагрузку на клиента и сеть, с другой стороны, частые сравнения больших объемов данных в триггерах отрицательно скажутся на производительности. Каков процент "ложных" UPDATE'ов в вашей системе? На что будет тратиться больше ресурсов - на постоянные проверки в триггерах, большая часть из которых не нужна, или на небольшое количество лишних запросов клиента к серверу?

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

   Вариант 1: вынести сравнение старых и новых значений для изменяемой записи из триггера на промежуточный уровень вашей системы или на клиента.
   Вариант 2: в журнал изменений данных триггеры вносят записи, не выполняя сравнения старых и новых значений. Только служебная информация (user_name, host_name, datetime и т.д., id записи) + новое значение поля в данной записи. В фоновом режиме с некоторой периодичностью запускается процесс проверки на "ложные" изменения, который удаляет из журнала ненужные записи. Поскольку процесс фоновый, его производительность критической не является, и он может выполнять самые строгие проверки в полном объеме. Если журнал изменений активно используется в рабочей системе (пример с оповещением клиентов), то имеет смысл синхронизировать отсев "ложных" изменений с вычиткой журнала, его очищением и посылкой сообщений.

   Является ли журналирование "ложных" изменений действительно ненужным? Например, по своему журналу вы делаете анализ активности работы с теми или иными таблицами. Исключив часть реальных действий с базой, вы тем самым искажаете реальную картину происходящего в системе, и как результат, ваш анализ будет неточным. В данной ситуации запись всех действий с базой как раз нужна и полезна.

   В общем, хочется подчеркнуть, что отслеживание "реальных" изменений данных для многих систем не является необходимым, хотя и кажется таковым в начале разработки. Но даже если оно действительно необходимо, внесение соответствующих проверок в триггер совершенно необязательно и почти в 100% случаев негативно по соображениям производительности. Тем не менее, грамотное проектирование системы в целом поможет вам найти оптимальный вариант, реализующий данную функциональность.

После ответов на эти вопросы можно считать, что вы более-менее проанализировали свою предметную область и готовы к практической реализации задачи.

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

NB: во всех рассматриваемых ниже примерах подразумевается, что таблицы имеют первичный ключ.

[В начало]

Хранение сведений о последних изменениях в данных

Поскольку задача о хранении истории изменений не стоит, дополнительных таблиц-журналов не требуется, достаточно дополнительных полей в рабочих таблицах. Или же, если изменение структуры рабочих таблиц не разрешено, заводятся дополнительные таблицы, связанные с основными отношением 1:1 по первичному ключу.

Пример 1: только последние изменения; хранятся в той же таблице

В этом и следующих примерах используются некоторые системные функции, такие как host_name(), app_name() и др.

NB: Не все приложения корректно выставляют значения app_name и host_name, так что будьте готовы к тому, что в журнале эти поля будут содержать NULL.

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

Ниже рассмотрим более удобный способ журналирования, когда журнал является отдельной таблицей.

[В начало]

Фиксирование только факта выполнения операции с данными

Журналируются:

• факт операции

• ее вид (INSERT/UPDATE/DELETE)

• имя пользователя, который модифицировал данные

• дата/время модификации

• название приложения, через которое проводилось данное изменение

• имя машины, с которой оно проводилось

Реализация данной функциональности может быть выполнена:

• в одном триггере на все три операции INSERT/UPDATE/DELETE

• в трех триггерах, по одному на каждую операцию

[В начало]

Реализация в одном триггере

Как правило, первым побуждением разработчика системы журналирования является попытка написать "универсальный" триггер FOR INSERT/UPDATE/DELETE, который по содержанию таблиц INSERTED и DELETED определяет, какой тип операции вызвал срабатывание триггера, и заносит данные в журнал.

Пример 2: отдельная таблица архива; только факт операции; один триггер

Как видим, способ неплох, если вам нужно отслеживать только факт проведения операций в базе, без детализации на уровне полей и без отслеживания реальных изменений в данных. Недостатком (и достаточно серьезным) данного способа реализации является то, что при операциях, не затрагивающих ни одной записи, невозможно определить, какая именно операция - INSERT, DELETE или UPDATE - была выполнена.

[В начало]

Реализация в трех триггерах

Более точным способом журналирования является реализация в трех триггерах, по одному на INSERT, DELETE и UPDATE.

Пример 3: только факт операции; три триггера

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

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

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

В приведенных примерах это реализовано через identity поля arch_id. Упорядочивание по этому полю является упорядочиванием в том порядке, как происходили действия в рабочих таблицах.

NB: упорядочивание по полю datetime НЕ является гарантией сохранения порядка изменений. Если несколько операций производились одна за другой, то они могут иметь одинаковые значения datetime вплоть до 3.33 миллисекунд, что является пределом точности данного типа.

[В начало]

Фиксирование факта выполнения операции с определенными полями

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

Отдельный пример делать не имеет смысла, поскольку он тривиален. Добавляется N-е количество проверок if update(f) или анализ битовой маски columns_updated() в теле триггера на UPDATE. Оба способа проверки описаны в справке для конструкции CREATE TRIGGER.

Не забывайте, что при операции INSERT проверка if update(f) в триггере вернет TRUE для всех полей, и битовая маска columns_updated() будет соответствовать всем полям записи.

[В начало]

Журналирование изменений вместе с изменяемыми значениями

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

[В начало]

Структура журнала

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

• скорость рабочей системы - насколько замедлятся операции с данными при наличии триггеров журнала

• объем журнала - в зависимости от выбранного способа одни и те же изменения могут храниться с разными затратами дискового пространства

• удобство работы с журналом - нужны ли сложные преобразования для просмотра и обработки данных в нем

• скорость работы с самим журналом - поиск, очистка

• блокировки - запись в дополнительные таблицы в любом случае означает дополнительные блокировки в системе; их надо хорошо продумать, чтобы минимизировать проблемы при многопользовательском доступе

• универсальность - насколько удобно будет применять уже разработанную систему, если понадобится журналировать новые таблицы, много ли кода придется переписывать или дополнять

• удобство сопровождения кода - количество триггеров, их сложность и объем, способ их формирования, исправление ошибок; все перечисленное в применении к вашей системе, возможно, насчитывающей сотни таблиц, которые подлежат журналированию

[В начало]

Одна таблица журнала, одно поле для хранения значений

При таком способе хранения создается одна таблица-журнал, в которую заносятся все изменения из всех таблиц.

Как это реализуется? Представьте, что у вас журналируются таблицы с самыми разными структурами, разными типами данных и количеством столбцов. Как универсально представить в одной таблице-журнале все изменения, происходящие в рабочих таблицах, учитывая, что размер записи ограничен примерно 8KB? Разумным решением будет для поля new_value (новое значение записи) выбрать наиболее универсальный тип, например, image или ntext. После чего одно изменение одного поля одной записи будет представлено одной строчкой в журнале. То есть

update t set f1 = 1, f2 = 'asd', f3 = getdate() where id = 123

в журнале выразится тремя записями:

Зачем введено поле group_id?
При таком способе хранения становится проблематичным понять, был ли это один UPDATE с несколькими полями или несколько. Для некоторых систем это может быть критично. Именно для этого добавляется поле group_id, которое будет одинаковым для всех строк в журнале, представляющих собой изменения в одной операции UPDATE. Оно может быть любого типа, значение которого формируется в триггере уникально для операции в целом. Тип uniqueidentifier хорошо подходит для данного случая.

Преимущества:

• одна таблица; следовательно, когда в число журналируемых добавляются новые таблицы, требуется меньше дополнительного кода; триггеры добавляются по образцу существующих; остальной механизм унифицирован

• изменения, приходящие от разных таблиц (в том числе parent-child), легко синхронизируются одним полем identity

• для UPDATE хранятся значения только измененных полей

Недостатки:

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

• разбиение одной операции на N строк в журнале может быть неудобным при считывании изменений и их обработке

NB: Тип sql_variant, который кажется идеальным для "универсального" поля, не является таковым, поскольку некоторые типы данных не могут быть к нему приведены.

Пример 4: хранятся изменяемые значения; 1 таблица архива; одно поле для значений

Выборки с приведением типов, представленные в конце скрипта, показывают, что для каждого поля каждой таблицы придется делать отдельный запрос с оператором CAST или CONVERT для приведения к исходным типам данных. На стороне клиента можно избежать этих действий, написав один запрос на вычитку всего журнала и преобразуя полученные сырые данные к нужному типу клиентским приложением.

Более читабельного вида таблицы можно добиться, используя вместо image тип text или ntext. Многие типы данных при просмотре с помощью SELECT * будут выглядеть "нормально" и будут приводиться к родному типу неявными преобразованиями. Это, безусловно, удобнее, но не обязательно быстрее. К тому же тип ntext хранит данные в UNICODE и его использование может быть невыгодно с точки зрения объема хранимых данных.

Негативный эффект, связанный со снижением производительности при приведении к выбранному универсальному типу данных, можно значительно снизить, если журнал будет содержать небольшое количество полей для хранения основных типов данных. Например, одно строковое, одно целочисленное и т.д. В каждой записи журнала будет заполнено только одно из них (наиболее близкое к типу поля, которое изменялось в рабочей таблице), а остальные будут содержать значения NULL.

Неудобство обработки журнала, связанное с расслоением записей - по одной записи на изменившиеся атрибуты - может быть уменьшено в MS SQL 2005, который предоставит операторы для "разворачивания" таблиц - PIVOT и UNPIVOT. PIVOT позволит из набора строк, каждая из которых представляет собой значение одного поля, сформировать одну строку с несколькими полями. UNPIVOT выполняет обратную операцию.

[В начало]

Несколько таблиц журнала, копии структур рабочих таблиц

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

Вот как будет выглядеть журналирование операции

update t set f1 = 1, f3 = getdate() where id = 123

Обратите внимание, что поле arch_id имеет тип timestamp, поскольку таких таблиц журнала в одной базе несколько, и нужен механизм синхронизации изменений в разных таблицах.

Поле columns_updated нужно в том случае, если вам надо точно знать, какие поля изменялись. Казалось бы, можно неизменившиеся значения просто не вносить в журнал, оставляя NULL в соответствующих колонках, но тогда надо как-то идентифицировать ситуации, когда до UPDATE поле было NOT NULL, а после UPDATE имеет значение NULL.

Преимущества:

• более высокая скорость работы, поскольку нет лишних преобразований типов

• выборка из журналов упрощается

Недостатки:

• значительное увеличение количества таблиц

• возможна ситуация, когда одной таблицы журнала на одну рабочую таблицу будет недостаточно, поскольку дополнительные поля занимают немало места; если рабочая таблица уже создавалась на пределе размера, то дополнительные поля могут уже и не поместиться

• при UPDATE хранятся все поля, вне зависимости от того, какие из них действительно участвовали в операции (этот недостаток можно устранить, добавив в триггере проверки if update(f) или анализ битовой маски columns_updated() и заполняя неизмененные значения NULL'ами)

Пример 5: хранятся изменяемые значения; N таблиц архива; копии рабочих таблиц

[В начало]

Одна таблица журнала, количество полей равно количеству типов

В некотором смысле гибридный вариант первых двух способов. Создается таблица, поля которой имеют типы, используемые в рабочих таблицах. Самый простой вариант - все использованные типы (если позволит максимальный размер записи), чуть посложнее - типы берутся не все, а выбираются так, чтобы минимизировать количество полей, но не слишком увеличить затраты на преобразования значений. Например, одно поле типа bigint, куда будут записываться все целочисленные данные (tinyint, smallint, int, bigint), одно поле для строковых типов, имеющее максимальный размер, и т.д.

Пример 6: хранятся изменяемые значения; 1 таблица архива; N полей

Применим ли данный способ, если в таблице существует несколько полей одного типа? Например, в первой таблице три поля типа int и четыре строковых, причем каждое строковое со своей длиной, во второй - одно строковое, пять типа int и два типа uniqueidentifier. Какой в данном примере должна быть единственная таблица журнала? К примеру, int-int-int-int-int-ntext-ntext-ntext-ntext-uniqueidentifier-uniqueidentifier.
Тип ntext пришлось использовать, поскольку размер записи ограничен, и нельзя задать в одной таблице четыре поля типа nvarchar(4000).

Преимущества:

• одна таблица-журнал

• одна строка журнала - одно изменение

• минимальные потери на преобразованиях типов

• чтобы поместить в одну таблицу все поля нужных типов, возможно, придется идти на компромиссы, снижающие скорость работы; например, вместо строк varchar(8000) или nvarchar(4000) использовать text/ntext

• разнообразие типов и количества полей приводит к сильно меняющейся структуре журнала

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

[В начало]

Хранение старых и новых значений при операциях UPDATE

Эту задачу можно решать двумя способами.

1. Добавить поля old_f1, old_f2… old_fn в журнал. В зависимости от размера рабочей таблицы вам может не хватить одной таблицы журнала и придется заводить дополнительную.

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

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

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

Для получения старых и новых значений в триггере используется следующий запрос:

create trigger trg_t1_u
on t1
for update
as
begin
    insert into arch_t1 (op_id, id, f1, f1_old)
    select
        2, i.id, i.f1, d.f1
    from
        inserted i
        inner join deleted d on i.id = d.id
end

[В начало]

Особенности работы с BLOB-полями

С полями типа text/ntext/image нельзя работать в FOR триггерах, поскольку выборка из INSERTED и DELETED полей этого типа запрещена. Поэтому таблицы с BLOB-полями вместо выборки из INSERTED делается выборка из рабочей таблицы только для тех записей, которые присутствуют в INSERTED.

Пример 7: хранятся изменяемые значения; BLOB-поля, только новые значения

NB: Не забывайте о том, что раз вы используете join таблиц в триггере, вы можете получить проблему с блокировками при конкурирующих процессах - как на записи в рабочей таблице, так и на индексы. Возможно, вам придется использовать явные hint'ы в запросах или менять уровень изоляции транзакций.

Еще одна проблема при работе с BLOB-полями - как получить их старые значения. Поскольку через DELETED их получить нельзя, то нужно использовать связку с рабочей таблицей. Но в момент срабатывания триггера FOR UPDATE в рабочей таблице значения полей уже новые.

Хорошим выходом из положения является использование INSTEAD OF триггеров для журналирования таблиц с BLOB-полями, поскольку триггеры этого типа могут работать с BLOB-полями в INSERTED и DELETED.

Пример 8: хранятся изменяемые значения; BLOB-поля, новые и старые значения

NB: не забывайте, что в INSTEAD OF триггерах надо явно писать инструкции INSERT/DELETE/UPDATE для рабочих таблиц, если вы не собираетесь отменять или игнорировать эти действия.

[В начало]

Фиксирование реального изменения данных

По сравнению с предыдущими пунктами в триггер FOR UPDATE добавляется проверка на то, что данные действительно отличаются в INSERTED и DELETED.

Пример 9: хранятся изменяемые значения; только реальные изменения

С BLOB-полями та же проблема, что уже была описана - для того, чтобы сравнивать старое и новое значение BLOB-поля, надо использовать INSTEAD OF триггеры.

Со сравнением BLOB-полей тоже не все гладко. Во-первых, к полям этого типа неприменима операция сравнения "=", для точного равенства используется оператор LIKE без знаков "%". Это пишется как

f1 LIKE f2

или

f1 LIKE 'long string without percent sign'

Во-вторых, даже это не спасает, поскольку LIKE сравнивает только первые 8000 байт. Если хотя бы одно из значений длиннее, то LIKE в любом случае вернет FALSE. Так что для точного сравнения надо разбивать значения на куски по 8000 байт и сравнивать их последовательно.

[В начало]

Сосуществование триггеров журналирования с другими триггерами системы

MS SQL 2000 позволяет назначать одной таблице несколько триггеров одного типа. В идеале они должны быть независимы друг от друга, тогда порядок их срабатывания не имеет значения. Но в действительности это не всегда так. Например, у вас может быть триггер, который меняет значения некоторых полей у тех же самых записей, на которые делается UPDATE.

create trigger trg_t1_iu
on t1
for insert, update
as
begin
    update
        t1
    set
        t1.f1 = i.f1 + '+++'
    from
        inserted i
        inner join t1 t on i.id = t.id
end

Если триггер журналирования выполнится перед данным триггером, то в журнал пойдут неизмененные значения. Если после него - то измененные. Таким образом, вы должны следить за порядком срабатывания триггеров, если он имеет значение. Процедура sp_settriggerorder позволяет назначать первый и последний триггер одного типа для таблицы.

Также не забывайте про nested и recursive триггера. При неправильном написании кода или неправильном использовании опций сервера можно получить рекурсию, которая была бы бесконечной, если бы не ограничение на глубину вложенности 32.

[В начало]

Автоматизация процесса создания триггеров для журналирования данных

Если переходить от маленьких примеров к внедрению журналирования в серьезные системы, то сразу же возникает вопрос - неужели все эти триггера надо писать вручную? Требуется три триггера на таблицу, а таблиц сотни. А если в триггере обнаружена ошибка, то надо исправлять все эти сотни и сотни триггеров? Кажется, что задача совершенно неподъемная.

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

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

При добавлении новой таблицы для нее выполняются уже существующие отлаженные процедуры.

[В начало]

Новое в версии MS SQL Server 2005: триггеры на DDL

NB: возможности, описанные в данном разделе, относятся к версии 2005 beta 3. Не гарантируется, что релиз будет абсолютно совпадать с бета-версией. В любом случае читайте документацию, поставляемую с вашей версией сервера.

Наверняка вам приходилось сталкиваться с задачами, которые наводили на мысль, что хорошо бы создавать триггеры на системные таблицы. Например, для отслеживания создания, модификации и удаления таблиц, представлений, хранимых процедур, функций хотелось бы создавать триггеры на sysobjects и syscomments, для отслеживание подключений и отключений пользователей - на sysprocesses, для отслеживания блокировок - на syslockinfo. Существуют и другие задачи, когда вам - как разработчику или администратору - хочется иметь информацию об этих событиях, синхронную или асинхронную с данными событиями.

В MS SQL 2000 не было возможности создавать триггера на системные таблицы. Нет ее и в версии 2005.

Однако частично для описанных задач можно применить новые возможности, предоставляемые версией MS SQL 2005, например, создание триггеров на инструкции DDL. Такие триггеры могут быть заданы на уровне сервера и на уровне базы. Они могут быть определены для конкретного события, например, CREATE_TABLE, или для группы событий. Примеры событий:

• CREATE_TABLE (ON DATABASE)

• DDL_DATABASE_LEVEL_EVENTS (ON DATABASE)

• FOR DDL_LOGIN_EVENTS (ON ALL SERVER)

Пример 10: триггеры на DDL

Как и в обычном триггере на инструкции DML, тут можно воспользоваться откатом транзакции, чтобы запретить нежелательные изменения.

[В начало]

Заключение

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

В данной статье приведены основные методы решения задач журналирования, отмечены их достоинства и недостатки, выделены критерии, ориентируясь на которые вы можете выбрать тот или иной метод. Также отмечены некоторые технические проблемы, с которыми вы можете столкнуться, реализуя данные алгоритмы на MS SQL Server, и даны способы их решения.

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

[В начало]

Приложения

[В начало]

Пример 1: только последние изменения; хранятся в той же таблице

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id                 int primary key,
    f1                 varchar(10),
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name()
-- standart system info: end --
)
go
create trigger trg_t1_u
on t1
for update
as
begin
    update
        t1
    set
        t1.[user_name] = suser_sname(),
        t1.[datetime] = getdate(),
        t1.[host_name] = host_name(),
        t1.[app_name] = app_name()
    from
        inserted i
        inner join t1 on i.id = t1.id
end
go
insert into t1 (id, f1) values (1, 'a')
insert into t1 (id, f1) values (2, 'b')
insert into t1 (id, f1) values (3, 'c')
select * from t1
waitfor delay '00:00:00.5'
update t1 set f1 = 'dd' where id = 2
select * from t1
waitfor delay '00:00:00.5'
update t1 set f1 = 'ee' where id = 3
select * from t1

[В начало]

Пример 2: отдельная таблица архива; только факт операции; один триггер

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    varchar(10)
)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch') is not null)
    drop table arch
go
create table arch(
    arch_id            int identity(1, 1) primary key,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    table_name         sysname,
    rec_id             int
)
go
create trigger trg_t1_iud
on t1
for insert, delete, update
as
begin
    if (not exists(select * from deleted)) and (not exists(select * from inserted))
        return

    if not exists(select * from deleted)
    begin
        insert into arch (op_id, table_name, rec_id)
        select 0, 't1', id from inserted
        return
    end

    if not exists(select * from inserted)
    begin
        insert into arch (op_id, table_name, rec_id)
        select 1, 't1', id from deleted
        return
    end

    insert into arch (op_id, table_name, rec_id)
    select 2, 't1', id from inserted
end
go

insert into t1 (id, f1) values (1, 'a')
insert into t1 (id, f1) values (2, 'b')
insert into t1 (id, f1) values (3, 'c')
update t1 set f1 = 'dd'
delete from t1 where id = 2

select * from arch order by arch_id

[В начало]

Пример 3: только факт операции; три триггера

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    varchar(10)
)
go
if (object_id('t2') is not null)
    drop table t2
go
create table t2(
    id    int primary key,
    f2    int
)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch') is not null)
    drop table arch
go
create table arch(
    arch_id            int identity(1, 1) primary key,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    table_name         sysname,
    rec_id             int
)
go
create trigger trg_t1_i
on t1
for insert
as
begin
    insert into arch (op_id, table_name, rec_id)
    select 0, 't1', id from inserted
end
go
create trigger trg_t1_d
on t1
for delete
as
begin
    insert into arch (op_id, table_name, rec_id)
    select 1, 't1', id from deleted
end
go
create trigger trg_t1_u
on t1
for update
as
begin
    insert into arch (op_id, table_name, rec_id)
    select 2, 't1', id from inserted
end
go
create trigger trg_t2_i
on t2
for insert
as
begin
    insert into arch (op_id, table_name, rec_id)
    select 0, 't2', id from inserted
end
go
create trigger trg_t2_d
on t2
for delete
as
begin
    insert into arch (op_id, table_name, rec_id)
    select 1, 't2', id from deleted
end
go
create trigger trg_t2_u
on t2
for update
as
begin
    insert into arch (op_id, table_name, rec_id)
    select 2, 't2', id from inserted
end
go
insert into t1 (id, f1) values (1, 'a')
insert into t2 (id, f2) values (1, 100)
insert into t1 (id, f1) values (2, 'b')
insert into t1 (id, f1) values (3, 'c')
update t1 set f1 = 'dd'
update t2 set f2 = 101 where id = 1
insert into t2 (id, f2) values (2, 200)
delete from t2

select * from arch order by arch_id

[В начало]

Пример 4: хранятся изменяемые значения; 1 таблица архива; одно поле для значений

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    varchar(10),
    f2    datetime
)
go
if (object_id('t2') is not null)
    drop table t2
go
create table t2(
    id    int primary key,
    f3    int,
    f4    uniqueidentifier
)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch') is not null)
    drop table arch
go
create table arch(
    arch_id            int identity(1, 1) primary key,
    group_id           uniqueidentifier,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    table_name         sysname,
    rec_id             int,
    [col_name]         nvarchar(128),
    value              image
)
go
create trigger trg_t1_i
on t1
for insert
as
begin
    declare @group_id uniqueidentifier
    set @group_id = newid()

    insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
    select @group_id, 0, 't1', id, 'f1', f1 from inserted

    insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
    select @group_id, 0, 't1', id, 'f2', cast(cast(f2 as binary(8)) as image) from inserted
end
go
create trigger trg_t1_d
on t1
for delete
as
begin
    declare @group_id uniqueidentifier
    set @group_id = newid()

    insert into arch (group_id, op_id, table_name, rec_id)
    select @group_id, 1, 't1', id from deleted
end
go
create trigger trg_t1_u
on t1
for update
as
begin
    declare @group_id uniqueidentifier
    set @group_id = newid()

    if update(f1)
        insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
        select @group_id, 2, 't1', id, 'f1', f1 from inserted

    if update(f2)
        insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
        select @group_id, 2, 't1', id, 'f2', cast(cast(f2 as binary(8)) as image) from inserted
end
go
create trigger trg_t2_i
on t2
for insert
as
begin
    declare @group_id uniqueidentifier
    set @group_id = newid()

    insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
    select @group_id, 0, 't2', id, 'f3', cast(cast(f3 as binary(4)) as image) from inserted

    insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
    select @group_id, 0, 't2', id, 'f4', cast(cast(f4 as binary(16)) as image) from inserted
end
go
create trigger trg_t2_d
on t2
for delete
as
begin
    declare @group_id uniqueidentifier
    set @group_id = newid()

    insert into arch (group_id, op_id, table_name, rec_id)
    select @group_id, 1, 't2', id from deleted
end
go
create trigger trg_t2_u
on t2
for update
as
begin
    declare @group_id uniqueidentifier
    set @group_id = newid()

    if update(f3)
        insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
        select @group_id, 2, 't2', id, 'f3', cast(cast(f3 as binary(4)) as image) from inserted

    if update(f4)
        insert into arch (group_id, op_id, table_name, rec_id, [col_name], value)
        select @group_id, 2, 't2', id, 'f4', cast(cast(f4 as binary(16)) as image) from inserted
end
go
insert into t1 (id, f1, f2) values (1, 'a', '20050109 18:20:45')
insert into t2 (id, f3, f4) values (1, 100, 'F6CCEECA-4ED2-47E4-AB26-C6469FB6140F')
insert into t1 (id, f1, f2) values (2, 'b', '20061225 00:15:30')
insert into t1 (id, f1, f2) values (3, 'c', '19990423 23:00:34')
update t1 set f1 = 'dd'
update t2 set f3 = 101, f4 = '46AA28F3-051A-41C5-A760-34FF91C6B78C' where id = 1
insert into t2 (id, f3) values (2, 200)
delete from t2

select * from arch order by arch_id

select cast(cast(value as binary(10)) as varchar(10)) as cast_value 
	   from arch where arch_id = 9
select cast(cast(value as binary(8)) as datetime) as cast_value 
	   from arch where arch_id = 2
select cast(cast(value as binary(4)) as int) as cast_value 
	   from arch where arch_id = 12
select cast(cast(value as binary(16)) as uniqueidentifier) as cast_value 
	   from arch where arch_id = 13

"..." - это системная информация - user_name, datetime, host_name, app_name

[В начало]

Пример 5: хранятся изменяемые значения; N таблиц архива; копии рабочих таблиц

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    varchar(10),
    f2    datetime

)
go
if (object_id('t2') is not null)
    drop table t2
go
create table t2(
    id    int primary key,
    f3    int,
    f4    uniqueidentifier
)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch_t1') is not null)
    drop table arch_t1
go
create table arch_t1(
    arch_id            timestamp,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    [columns_updated]  varbinary(10),
    id                 int,
    f1                 varchar(10),
    f2                 datetime
)
go
if (object_id('arch_t2') is not null)
    drop table arch_t2
go
create table arch_t2(
    arch_id            timestamp,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    [columns_updated]  varbinary(10),
    id                 int,
    f3                 int,
    f4                 uniqueidentifier
)
go
create trigger trg_t1_i
on t1
for insert
as
begin
    insert into arch_t1 (op_id, id, f1, f2)
    select 0, id, f1, f2 from inserted
end
go
create trigger trg_t1_d
on t1
for delete
as
begin
    insert into arch_t1 (op_id, id)
    select 1, id from deleted
end
go
create trigger trg_t1_u
on t1
for update
as
begin
    insert into arch_t1 (op_id, [columns_updated], id, f1, f2)
    select 2, columns_updated(), id, f1, f2 from inserted
end
go
create trigger trg_t2_i
on t2
for insert
as
begin
    insert into arch_t2 (op_id, id, f3, f4)
    select 0, id, f3, f4 from inserted
end
go
create trigger trg_t2_d
on t2
for delete
as
begin
    insert into arch_t2 (op_id, id)
    select 1, id from deleted
end
go
create trigger trg_t2_u
on t2
for update
as
begin
    insert into arch_t2 (op_id, [columns_updated], id, f3, f4)
    select 2, columns_updated(), id, f3, f4 from inserted
end
go
insert into t1 (id, f1, f2) values (1, 'a', '20050109 18:20:45')
insert into t2 (id, f3, f4) values (1, 100, 'F6CCEECA-4ED2-47E4-AB26-C6469FB6140F')
insert into t1 (id, f1, f2) values (2, 'b', '20061225 00:15:30')
insert into t1 (id, f1, f2) values (3, 'c', '19990423 23:00:34')
update t1 set f1 = 'dd'
update t2 set f3 = 101, f4 = '46AA28F3-051A-41C5-A760-34FF91C6B78C' where id = 1
insert into t2 (id, f3) values (2, 200)
delete from t2

select arch_id, [user_name], [datetime], [host_name], 
                [app_name], op_id, 't1' as table_name, id 
  from arch_t1
union all
select arch_id, [user_name], [datetime], [host_name], 
                [app_name], op_id, 't2', id 
  from arch_t2
order by arch_id

select * from arch_t1
select * from arch_t2

"..." - это системная информация - user_name, datetime, host_name, app_name

[В начало]

Пример 6: хранятся изменяемые значения; 1 таблица архива; N полей

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    varchar(10),
    f2    int
)
go
if (object_id('t2') is not null)
    drop table t2
go
create table t2(
    id    int primary key,
    f3    int,
    f4    uniqueidentifier
)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch') is not null)
    drop table arch
go
create table arch(
    arch_id            int identity(1, 1) primary key,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    table_name         sysname,
    id                 int,
    intValue           int,
    strValue           varchar(1000),
    guidValue          uniqueidentifier
)
go
create trigger trg_t1_i
on t1
for insert
as
begin
    insert into arch (op_id, table_name, id, strValue, intValue)
    select 0, 't1', id, f1, f2 from inserted
end
go
create trigger trg_t1_d
on t1
for delete
as
begin
    insert into arch (op_id, table_name, id)
    select 1, 't1', id from deleted
end
go
create trigger trg_t1_u
on t1
for update
as
begin
    insert into arch (op_id, table_name, id, strValue, intValue)
    select 2, 't1', id, f1, f2 from inserted
end
go
create trigger trg_t2_i
on t2
for insert
as
begin
    insert into arch (op_id, table_name, id, intValue, guidValue)
    select 0, 't2', id, f3, f4 from inserted
end
go
create trigger trg_t2_d
on t2
for delete
as
begin
    insert into arch (op_id, table_name, id)
    select 1, 't2', id from deleted
end
go
create trigger trg_t2_u
on t2
for update
as
begin
    insert into arch (op_id, table_name, id, intValue, guidValue)
    select 2, 't2', id, f3, f4 from inserted
end
go
insert into t1 (id, f1, f2) values (1, 'a', 100)
insert into t2 (id, f3, f4) values (1, 123, 'F6CCEECA-4ED2-47E4-AB26-C6469FB6140F')
insert into t1 (id, f1, f2) values (2, 'b', 200)
insert into t1 (id, f1, f2) values (3, 'c', 300)
update t1 set f1 = 'dd'
update t2 set f3 = 456, f4 = '46AA28F3-051A-41C5-A760-34FF91C6B78C' where id = 1
insert into t2 (id, f3) values (2, 789)
delete from t2

select * from arch order by arch_id

[В начало]

Пример 7: хранятся изменяемые значения; BLOB-поля, только новые значения

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    int,
    f2    ntext

)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch_t1') is not null)
    drop table arch_t1
go
create table arch_t1(
    arch_id            timestamp,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    [columns_updated]  varbinary(10),
    id                 int,
    f1                 int,
    f2                 ntext
)
go
create trigger trg_t1_i
on t1
for insert
as
begin
    insert into arch_t1 (op_id, id, f1, f2)
    select
        0, t.id, t.f1, t.f2
    from
        inserted i
        inner join t1 t on i.id = t.id
end
go
create trigger trg_t1_d
on t1
for delete
as
begin
    insert into arch_t1 (op_id, id)
    select 1, id from deleted
end
go
create trigger trg_t1_u
on t1
for update
as
begin
    insert into arch_t1 (op_id, [columns_updated], id, f1, f2)
    select
        2, columns_updated(), t.id, t.f1, t.f2
    from
        inserted i
        inner join t1 t on i.id = t.id
end
go
insert into t1 (id, f1, f2) values (1, 123, N'qwerty')
insert into t1 (id, f1, f2) values (2, 456, N'uiopasdfg')
update t1 set f1 = 789
delete from t1

select * from arch_t1

[В начало]

Пример 8: хранятся изменяемые значения; BLOB-поля, новые и старые значения

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    int,
    f2    ntext

)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch_t1') is not null)
    drop table arch_t1
go
create table arch_t1(
    arch_id            timestamp,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    [columns_updated]  varbinary(10),
    id                 int,
    f1                 int,
    f2                 ntext,
    f1_old             int,
    f2_old             ntext
)
go
create trigger trg_t1_i
on t1
instead of insert
as
begin
    insert into arch_t1 (op_id, id, f1, f2)
    select 0, id, f1, f2 from inserted i

    insert into t1 (id, f1, f2)
    select id, f1, f2 from inserted
end
go
create trigger trg_t1_d
on t1
for delete
as
begin
    insert into arch_t1 (op_id, id)
    select 1, id from deleted
end
go
create trigger trg_t1_u
on t1
instead of update
as
begin
    insert into arch_t1 (op_id, [columns_updated], id, f1, f2, f1_old, f2_old)
    select
        2, columns_updated(), i.id, i.f1, i.f2, d.f1, d.f2
    from
        inserted i
        inner join deleted d on i.id = d.id

    update
        t1
    set
        t1.f1 = i.f1,
        t1.f2 = i.f2
    from
        inserted i
        inner join t1 t on i.id = t.id
end
go
insert into t1 (id, f1, f2) values (1, 123, N'qwerty')
insert into t1 (id, f1, f2) values (2, 456, N'uiopasdfg')
update t1 set f1 = 789
update t1 set f1 = 321, f2 = N'zxcvbnm' where id = 2
delete from t1

select * from arch_t1

[В начало]

Пример 9: хранятся изменяемые значения; только реальные изменения

if (object_id('t1') is not null)
    drop table t1
go
create table t1(
    id    int primary key,
    f1    int,
    f2    varchar(10)
)
go
/*
op_id = 0 - insert
op_id = 1 - delete
op_id = 2 - update
*/
if (object_id('arch_t1') is not null)
    drop table arch_t1
go
create table arch_t1(
    arch_id            timestamp,
-- standart system info: begin --
    [user_name]        nvarchar(256) default suser_sname(),
    [datetime]         datetime default getdate(),
    [host_name]        nchar(30) default host_name(),
    [app_name]         nvarchar(128) default app_name(),
-- standart system info: end --
    op_id              tinyint,
    [columns_updated]  varbinary(10),
    id                 int,
    f1                 int,
    f2                 varchar(10)
)
go
create trigger trg_t1_i
on t1
for insert
as
begin
    insert into arch_t1 (op_id, id, f1, f2)
    select
        0, t.id, t.f1, t.f2
    from
        inserted i
        inner join t1 t on i.id = t.id
end
go
create trigger trg_t1_d
on t1
for delete
as
begin
    insert into arch_t1 (op_id, id)
    select 1, id from deleted
end
go
create trigger trg_t1_u
on t1
for update
as
begin
    insert into arch_t1 (op_id, [columns_updated], id, f1, f2)
    select
        2, columns_updated(), i.id, i.f1, i.f2
    from
        inserted i
        inner join deleted d on i.id = d.id
    where
        (i.f1 <> d.f1)
        or (i.f2 <> d.f2)
end
go
insert into t1 (id, f1, f2) values (1, 123, N'qwerty')
update t1 set f1 = 123 where id = 1
update t1 set f1 = 789 where id = 1

select * from arch_t1

[В начало]

Пример 10: триггеры на DDL

SET QUOTED_IDENTIFIER on
go
create trigger trg_createtable on database for create_table
as
begin
    select eventdata()

    declare @eventdata as XML
    set @eventdata = eventdata()

    select @eventdata.query('data(//EventType)')
    select @eventdata.query('data(//PostTime)')
    select @eventdata.query('data(//SPID)')
    select @eventdata.query('data(//ServerName)')
    select @eventdata.query('data(//LoginName)')
    select @eventdata.query('data(//UserName)')
    select @eventdata.query('data(//DatabaseName)')
    select @eventdata.query('data(//SchemaName)')
    select @eventdata.query('data(//ObjectName)')
    select @eventdata.query('data(//ObjectType)')
    select @eventdata.query('data(//CommandText)')
end
go
create table t1(id int)
go
drop table t1
go
drop trigger trg_createtable on database
go

- <EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType> 
  <PostTime>2005-01-17T22:57:00.687</PostTime> 
  <SPID>51</SPID> 
  <ServerName>SANHOME\INS2005</ServerName> 
  <LoginName>SANHOME\Administrator</LoginName> 
  <UserName>dbo</UserName> 
  <DatabaseName>master</DatabaseName> 
  <SchemaName>dbo</SchemaName> 
  <ObjectName>t1</ObjectName> 
  <ObjectType>TABLE</ObjectType> 
- <TSQLCommand>
  <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" 
   QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> 
  <CommandText>create table t1(id int)</CommandText> 
  </TSQLCommand>
  </EVENT_INSTANCE>


CREATE_TABLE
2005-01-18T00:03:15.413
51
SANHOME\INS2005
SANHOME\Administrator
dbo
master
dbo
t1
TABLE
create table t1(id int)

[В начало]

Автор: Наталья Кривонос