Триггер журнала - Log trigger

В реляционные базы данных, то Триггер журнала или же Триггер истории это механизм для автоматической записи информации об изменениях вставки и / или обновления или / и удаления ряды в таблица базы данных.

Это особая техника для изменение сбора данных, И в хранилище данных для работы с медленно меняющиеся размеры.

Определение

Предположим, есть стол которые мы хотим проверить. Этот стол содержит следующие столбцы:

Столбец1, Столбец2, ..., Столбец

В столбец Столбец1 предполагается, что это первичный ключ.

Эти столбцы определены как имеющие следующие типы:

Тип1, Тип2, ..., Тип

В Триггер журнала работает написание изменений (ВСТАВЛЯТЬ, ОБНОВИТЬ и УДАЛИТЬ операции) на стол в другой, таблица истории, определяемые следующим образом:

СОЗДАЙТЕ СТОЛ ИсторияТаблица (   Столбец1   Тип 1,   Столбец2   Тип 2,      :        :   Columnn   Typen,   Дата начала ДАТА ВРЕМЯ,   Дата окончания   ДАТА ВРЕМЯ)

Как показано выше, этот новый стол содержит тот же столбцы как оригинал стол, и дополнительно два новых столбцы типа ДАТА ВРЕМЯ: Дата начала и Дата окончания. Это известно как управление версиями кортежей. Эти два дополнительных столбцы определить период времени «достоверности» данных, связанных с указанной сущностью (сущность первичный ключ ), или, другими словами, он хранит, как данные были в период времени между Дата начала (в комплекте) и Дата окончания (не включено).

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

Обратите внимание, что если они показаны в хронологическом порядке, Дата окончания столбец любой ряд это точно Дата начала его преемника (если есть). Это не значит, что оба ряды являются общими для этого момента времени, поскольку - по определению - значение Дата окончания не входит.

Есть два варианта Триггер журнала, в зависимости от того, как старые значения (DELETE, UPDATE) и новые значения (INSERT, UPDATE) доступны для триггера (это зависит от СУБД):

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

СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК ИсторияТаблица НА OriginalTable ЗА ВСТАВЛЯТЬ, УДАЛИТЬ, ОБНОВИТЬ В КАЧЕСТВЕЗАЯВИТЬ @Сейчас же ДАТА ВРЕМЯНАБОР @Сейчас же = GETDATE()/ * удаление раздела * /ОБНОВИТЬ ИсторияТаблица   НАБОР Дата окончания = @Сейчас же КУДА Дата окончания ЯВЛЯЕТСЯ НОЛЬ   И Столбец1 = СТАРЫЙ.Столбец1/ * вставка раздела * /ВСТАВЛЯТЬ В ИсторияТаблица (Столбец1, Столбец2, ...,Columnn, Дата начала, Дата окончания) ЗНАЧЕНИЯ (НОВЫЙ.Столбец1, НОВЫЙ.Столбец2, ..., НОВЫЙ.Columnn, @Сейчас же, НОЛЬ)

Старые и новые значения в виде строк виртуальных таблиц

СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК ИсторияТаблица НА OriginalTable ЗА ВСТАВЛЯТЬ, УДАЛИТЬ, ОБНОВИТЬ В КАЧЕСТВЕЗАЯВИТЬ @Сейчас же ДАТА ВРЕМЯНАБОР @Сейчас же = GETDATE()/ * удаление раздела * /ОБНОВИТЬ ИсторияТаблица   НАБОР Дата окончания = @Сейчас же  ИЗ ИсторияТаблица, УДАЛЕНО КУДА ИсторияТаблица.Столбец1 = УДАЛЕНО.Столбец1   И ИсторияТаблица.Дата окончания ЯВЛЯЕТСЯ НОЛЬ/ * вставка раздела * /ВСТАВЛЯТЬ В ИсторияТаблица       (Столбец1, Столбец2, ..., Columnn, Дата начала, Дата окончания)ВЫБРАТЬ (Столбец1, Столбец2, ..., Columnn, @Сейчас же, НОЛЬ)  ИЗ ВСТАВЛЕННЫЙ

Примечания по совместимости

• Функция GetDate () используется для получения системной даты и времени, конкретного СУБД может использовать другое имя функции или получить эту информацию другим способом.
• Несколько СУБД (DB2, MySQL) не поддерживают возможность присоединения одного и того же триггера к нескольким операциям (ВСТАВЛЯТЬ, УДАЛИТЬ, ОБНОВИТЬ ). В таком случае для каждой операции должен быть создан триггер; Для ВСТАВЛЯТЬ операция только вставка раздела необходимо указать, для УДАЛИТЬ операция только удаление раздела должны быть указаны, а для ОБНОВИТЬ должны присутствовать оба раздела, как показано выше ( удаление раздела сначала, затем вставка раздела), потому что ОБНОВИТЬ операция логически представлена ​​как УДАЛИТЬ операция, за которой следует ВСТАВЛЯТЬ операция.
• В показанном коде структура данных записи, содержащая старое и новое значения, называется СТАРЫЙ и НОВЫЙ. По конкретному СУБД они могли иметь разные имена.
• В показанном коде виртуальные таблицы называются УДАЛЕНО и ВСТАВЛЕННЫЙ. По конкретному СУБД они могли иметь разные имена. Другой СУБД (DB2) даже позволяет указывать имена этих логических таблиц.
• В показанном коде комментарии написаны в стиле C / C ++, они не могут поддерживаться конкретным СУБД, или следует использовать другой синтаксис.
• Несколько СУБД требовать, чтобы корпус спускового крючка был заключен между НАЧИНАТЬ и КОНЕЦ ключевые слова.

Хранилище данных

Согласно медленно меняющееся измерение методологии управления, триггер журнала распадается на следующее:

• Тип 2 (управление версиями кортежей вариант)
• Тип 4 (использование таблиц истории)

Совместная реализация СУБД

IBM DB2^[1]

• Триггер не может быть привязан более чем к одной операции (ВСТАВЛЯТЬ, УДАЛИТЬ, ОБНОВИТЬ ), поэтому для каждой операции необходимо создавать триггер.
• Старые и новые значения отображаются в виде полей структур данных записи. Имена этих записей могут быть определены, в этом примере они названы как О для старых ценностей и N для новых ценностей.

- Триггер для INSERTСОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК База данных.TableInsert ПОСЛЕ ВСТАВЛЯТЬ НА База данных.OriginalTableССЫЛКИ НОВЫЙ В КАЧЕСТВЕ NЗА КАЖДЫЙ РЯД РЕЖИМ DB2SQLНАЧИНАТЬ   ЗАЯВИТЬ Сейчас же TIMESTAMP;   НАБОР СЕЙЧАС ЖЕ = ТЕКУЩИЙ TIMESTAMP;   ВСТАВЛЯТЬ В База данных.ИсторияТаблица (Столбец1, Столбец2, ..., Columnn, Дата начала, Дата окончания)   ЗНАЧЕНИЯ (N.Столбец1, N.Столбец2, ..., N.Columnn, Сейчас же, НОЛЬ);КОНЕЦ;- Триггер для УДАЛЕНИЯСОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК База данных.TableDelete ПОСЛЕ УДАЛИТЬ НА База данных.OriginalTableССЫЛКИ СТАРЫЙ В КАЧЕСТВЕ ОЗА КАЖДЫЙ РЯД РЕЖИМ DB2SQLНАЧИНАТЬ   ЗАЯВИТЬ Сейчас же TIMESTAMP;   НАБОР СЕЙЧАС ЖЕ = ТЕКУЩИЙ TIMESTAMP;   ОБНОВИТЬ База данных.ИсторияТаблица      НАБОР Дата окончания = Сейчас же    КУДА Столбец1 = О.Столбец1      И Дата окончания ЯВЛЯЕТСЯ НОЛЬ;КОНЕЦ;- Триггер для ОБНОВЛЕНИЯСОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК База данных.TableUpdate ПОСЛЕ ОБНОВИТЬ НА База данных.OriginalTableССЫЛКИ НОВЫЙ В КАЧЕСТВЕ N СТАРЫЙ В КАЧЕСТВЕ ОЗА КАЖДЫЙ РЯД РЕЖИМ DB2SQLНАЧИНАТЬ   ЗАЯВИТЬ Сейчас же TIMESTAMP;   НАБОР СЕЙЧАС ЖЕ = ТЕКУЩИЙ TIMESTAMP;   ОБНОВИТЬ База данных.ИсторияТаблица      НАБОР Дата окончания = Сейчас же    КУДА Столбец1 = О.Столбец1      И Дата окончания ЯВЛЯЕТСЯ НОЛЬ;   ВСТАВЛЯТЬ В База данных.ИсторияТаблица (Столбец1, Столбец2, ..., Columnn, Дата начала, Дата окончания)   ЗНАЧЕНИЯ (N.Столбец1, N.Столбец2, ..., N.Columnn, Сейчас же, НОЛЬ);КОНЕЦ;

Microsoft SQL Server^[2]

• Один и тот же спусковой крючок может быть прикреплен ко всем ВСТАВЛЯТЬ, УДАЛИТЬ, и ОБНОВИТЬ операции.
• Старые и новые значения в виде строк виртуальных таблиц с именами УДАЛЕНО и ВСТАВЛЕННЫЙ.

СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК TableTrigger НА OriginalTable ЗА УДАЛИТЬ, ВСТАВЛЯТЬ, ОБНОВИТЬ В КАЧЕСТВЕЗАЯВИТЬ @СЕЙЧАС ЖЕ ДАТА ВРЕМЯНАБОР @СЕЙЧАС ЖЕ = CURRENT_TIMESTAMPОБНОВИТЬ ИсторияТаблица   НАБОР Дата окончания = @сейчас же  ИЗ ИсторияТаблица, УДАЛЕНО КУДА ИсторияТаблица.ColumnID = УДАЛЕНО.ColumnID   И ИсторияТаблица.Дата окончания ЯВЛЯЕТСЯ НОЛЬВСТАВЛЯТЬ В ИсторияТаблица (ColumnID, Столбец2, ..., Columnn, Дата начала, Дата окончания)ВЫБРАТЬ ColumnID, Столбец2, ..., Columnn, @СЕЙЧАС ЖЕ, НОЛЬ  ИЗ ВСТАВЛЕННЫЙ

MySQL

• Триггер не может быть привязан более чем к одной операции (ВСТАВЛЯТЬ, УДАЛИТЬ, ОБНОВИТЬ ), поэтому для каждой операции необходимо создавать триггер.
• Старые и новые значения отображаются в виде полей структур данных записи, называемых Старый и Новый.

DELIMITER $$/ * Триггер для INSERT * /СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК ИсторияТаблицаВставка ПОСЛЕ ВСТАВЛЯТЬ НА OriginalTable ЗА КАЖДЫЙ РЯД НАЧИНАТЬ   ЗАЯВИТЬ N ДАТА ВРЕМЯ;   НАБОР N = сейчас же();       ВСТАВЛЯТЬ В ИсторияТаблица (Столбец1, Столбец2, ..., Columnn, Дата начала, Дата окончания)   ЗНАЧЕНИЯ (Новый.Столбец1, Новый.Столбец2, ..., Новый.Columnn, N, НОЛЬ);КОНЕЦ;/ * Триггер для УДАЛЕНИЯ * /СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК HistoryTableDelete ПОСЛЕ УДАЛИТЬ НА OriginalTable ЗА КАЖДЫЙ РЯД НАЧИНАТЬ   ЗАЯВИТЬ N ДАТА ВРЕМЯ;   НАБОР N = сейчас же();       ОБНОВИТЬ ИсторияТаблица      НАБОР Дата окончания = N    КУДА Столбец1 = СТАРЫЙ.Столбец1      И Дата окончания ЯВЛЯЕТСЯ НОЛЬ;КОНЕЦ;/ * Триггер для ОБНОВЛЕНИЯ * /СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК HistoryTableUpdate ПОСЛЕ ОБНОВИТЬ НА OriginalTable ЗА КАЖДЫЙ РЯД НАЧИНАТЬ   ЗАЯВИТЬ N ДАТА ВРЕМЯ;   НАБОР N = сейчас же();   ОБНОВИТЬ ИсторияТаблица      НАБОР Дата окончания = N    КУДА Столбец1 = СТАРЫЙ.Столбец1      И Дата окончания ЯВЛЯЕТСЯ НОЛЬ;   ВСТАВЛЯТЬ В ИсторияТаблица (Столбец1, Столбец2, ..., Columnn, Дата начала, Дата окончания)   ЗНАЧЕНИЯ (Новый.Столбец1, Новый.Столбец2, ..., Новый.Columnn, N, НОЛЬ);КОНЕЦ;

Oracle

• Один и тот же спусковой крючок может быть прикреплен ко всем ВСТАВЛЯТЬ, УДАЛИТЬ, и ОБНОВИТЬ операции.
• Старые и новые значения отображаются в виде полей структур данных записи, называемых :СТАРЫЙ и :НОВЫЙ.
• Необходимо проверить нулевое значение полей :НОВЫЙ запись, определяющая первичный ключ (когда УДАЛИТЬ операция выполняется), чтобы избежать вставки новой строки с нулевыми значениями во все столбцы.

СОЗДАЙТЕ ИЛИ ЖЕ ЗАМЕНЯТЬ СПУСКОВОЙ КРЮЧОК TableTriggerПОСЛЕ ВСТАВЛЯТЬ ИЛИ ЖЕ ОБНОВИТЬ ИЛИ ЖЕ УДАЛИТЬ НА OriginalTableЗА КАЖДЫЙ РЯДЗАЯВИТЬ Сейчас же TIMESTAMP;НАЧИНАТЬ   ВЫБРАТЬ CURRENT_TIMESTAMP В Сейчас же ИЗ Двойной;   ОБНОВИТЬ ИсторияТаблица      НАБОР Дата окончания = Сейчас же    КУДА Дата окончания ЯВЛЯЕТСЯ НОЛЬ      И Столбец1 = :СТАРЫЙ.Столбец1;   ЕСЛИ :НОВЫЙ.Столбец1 ЯВЛЯЕТСЯ НЕТ НОЛЬ ТОГДА      ВСТАВЛЯТЬ В ИсторияТаблица (Столбец1, Столбец2, ..., Columnn, Дата начала, Дата окончания)       ЗНАЧЕНИЯ (:НОВЫЙ.Столбец1, :НОВЫЙ.Столбец2, ..., :НОВЫЙ.Columnn, Сейчас же, НОЛЬ);   КОНЕЦ ЕСЛИ;КОНЕЦ;

Историческая справка

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

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

С использованием триггер журнала информация, которую мы можем знать, не является дискретной, а непрерывной, мы можем знать точное состояние информации в любой момент времени, ограничиваясь только степенью детализации времени, предоставляемой ДАТА ВРЕМЯ тип данных СУБД использовал.

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

• Это просто.
• Это не коммерческий продукт, он работает с общими функциями. СУБД.
• Это происходит автоматически, как только он создан, он работает без дальнейшего вмешательства человека.
• Необязательно иметь хорошие знания о таблицах базы данных или модели данных.
• Изменений в текущем программировании не требуется.
• Изменения в текущем столы не требуются, потому что данные журнала любого стол хранится в другом.
• Он работает как для запрограммированных, так и для специальных операторов.
• Только изменения (ВСТАВЛЯТЬ, ОБНОВИТЬ и УДАЛИТЬ операций) регистрируются, поэтому скорость роста таблиц истории пропорциональна изменениям.
• Нет необходимости применять триггер ко всем таблицам в базе данных, его можно применить к определенным столы, или некоторые столбцы из стол.

Недостатки

• Он не сохраняет автоматически информацию о пользователе, производящем изменения (пользователь информационной системы, а не пользователь базы данных). Эта информация может быть предоставлена ​​явно. Это может быть реализовано в информационных системах, но не в специальных запросах.

Примеры использования

Получение актуальной версии таблицы

ВЫБРАТЬ Столбец1, Столбец2, ..., Columnn  ИЗ ИсторияТаблица КУДА Дата окончания ЯВЛЯЕТСЯ НОЛЬ

Он должен вернуть тот же набор результатов, что и весь оригинал стол.

Получение версии таблицы в определенный момент времени

Предположим, что @ДАТА переменная содержит интересующий момент или время.

ВЫБРАТЬ  Столбец1, Столбец2, ..., Columnn  ИЗ  ИсторияТаблица КУДА  @Дата >= Дата начала   И (@Дата < Дата окончания ИЛИ ЖЕ Дата окончания ЯВЛЯЕТСЯ НОЛЬ)

Получение информации о сущности в определенный момент времени

Предположим, что @ДАТА переменная содержит интересующий момент или время, а @КЛЮЧ переменная содержит первичный ключ заинтересованного лица.

ВЫБРАТЬ  Столбец1, Столбец2, ..., Columnn  ИЗ  ИсторияТаблица КУДА  Столбец1 = @Ключ   И  @Дата >= Дата начала   И (@Дата <  Дата окончания ИЛИ ЖЕ Дата окончания ЯВЛЯЕТСЯ НОЛЬ)

Получение истории сущности

Предположим, что @КЛЮЧ переменная содержит первичный ключ заинтересованного лица.

ВЫБРАТЬ Столбец1, Столбец2, ..., Columnn, Дата начала, Дата окончания  ИЗ ИсторияТаблица КУДА Столбец1 = @Ключ ПОРЯДОК К Дата начала

Получение того, когда и как была создана сущность

Предположим, что @КЛЮЧ переменная содержит первичный ключ заинтересованного лица.

ВЫБРАТЬ H2.Столбец1, H2.Столбец2, ..., H2.Columnn, H2.Дата начала  ИЗ ИсторияТаблица В КАЧЕСТВЕ H2 ОСТАВИЛИ ВНЕШНИЙ ПРИСОЕДИНИТЬСЯ ИсторияТаблица В КАЧЕСТВЕ H1    НА H2.Столбец1 = H1.Столбец1   И H2.Столбец1 = @Ключ   И H2.Дата начала = H1.Дата окончания КУДА H2.Дата окончания ЯВЛЯЕТСЯ НОЛЬ

Неизменность первичные ключи

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

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

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

Альтернативы

Иногда Медленно меняющееся измерение используется как метод, эта диаграмма является примером:

Модель scd

Смотрите также

• Реляционная база данных
• Первичный ключ
• Натуральный ключ
• Суррогатный ключ
• Сбор данных об изменениях
• Медленно меняющееся измерение
• Управление версиями кортежей

Примечания

Триггер журнала был написан Лоуренс Р. Угальде для автоматического создания истории транзакционных баз данных.

Рекомендации

1. «Основы баз данных» Наридж Шарма и др. (Первое издание, Copyright IBM Corp. 2010)
2. «Microsoft SQL Server 2008 - Разработка баз данных» Тобиаса Тернстрёма и др. (Microsoft Press, 2009 г.)