Триггер журнала

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

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

Определение

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

Column1, Column2, ..., Columnn

Колонка Column1 предполагается, что это первичный ключ .

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

Type1, Type2, ..., Typen

работает Триггер журнала , записывая изменения ( операции INSERT , UPDATE и DELETE ) в таблице в другую таблицу истории , определенную следующим образом:

CREATE TABLE HistoryTable (
   Column1   Type1,
   Column2   Type2,
      :        :
   Columnn   Typen,

   StartDate DATETIME,
   EndDate   DATETIME
)

Как показано выше, эта новая таблица содержит те же столбцы , что и исходная таблица , и дополнительно два новых столбца типа DATETIME: StartDate и EndDate. Это известно как управление версиями кортежей . Эти два дополнительных столбца определяют период времени «действительности» данных, связанных с указанной сущностью (субъектом первичного ключа ), или, другими словами, они хранят, какими были данные в период времени между StartDate (включено) и EndDate (не входит в комплект).

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

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

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

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

CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT, DELETE, UPDATE AS
DECLARE @Now DATETIME
SET @Now = GETDATE()

/* deleting section */

UPDATE HistoryTable
   SET EndDate = @Now
 WHERE EndDate IS NULL
   AND Column1 = OLD.Column1

/* inserting section */

INSERT INTO HistoryTable (Column1, Column2, ...,Columnn, StartDate, EndDate) 
VALUES (NEW.Column1, NEW.Column2, ..., NEW.Columnn, @Now, NULL)

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

CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT, DELETE, UPDATE AS
DECLARE @Now DATETIME
SET @Now = GETDATE()

/* deleting section */

UPDATE HistoryTable
   SET EndDate = @Now
  FROM HistoryTable, DELETED
 WHERE HistoryTable.Column1 = DELETED.Column1
   AND HistoryTable.EndDate IS NULL

/* inserting section */

INSERT INTO HistoryTable
       (Column1, Column2, ..., Columnn, StartDate, EndDate)
SELECT (Column1, Column2, ..., Columnn, @Now, NULL)
  FROM INSERTED

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

Функция GetDate() используется для получения системной даты и времени, конкретная СУБД может либо использовать другое имя функции, либо получить эту информацию другим способом.
Некоторые СУБД (Db2, MySQL) не поддерживают возможность прикрепления одного и того же триггера к более чем одной операции ( INSERT , DELETE , UPDATE ). В таком случае для каждой операции необходимо создать триггер; Для операции INSERT только раздел вставки должен быть указан , для операции DELETE только раздел удаления должен быть указан , а для операции UPDATE должны присутствовать оба раздела, как показано выше ( раздел удаления сначала , затем раздел вставки). раздел ), поскольку операция UPDATE логически представлена как операция DELETE , за которой следует операция INSERT .
В показанном коде структура данных записи, содержащая старые и новые значения, называется OLD и NEW. В конкретной СУБД они могут иметь разные имена.
В показанном коде виртуальные таблицы называются DELETED и INSERTED. В конкретной СУБД они могут иметь разные имена. Другая СУБД (Db2) даже позволяла указывать имена этих логических таблиц.
В показанном коде комментарии выполнены в стиле C/C++, они не поддерживаются конкретной СУБД или следует использовать другой синтаксис.
Некоторые СУБД требуют, чтобы тело триггера было заключено между BEGIN и END ключевые слова.

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

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

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

Реализация в обычных СУБД

IBM DB2 ^[1]

Триггер не может быть прикреплен более чем к одной операции ( INSERT , DELETE , UPDATE ), поэтому триггер должен быть создан для каждой операции.
Старые и новые значения отображаются как поля структур данных записи. Имена этих записей могут быть определены, в этом примере они называются как O за старые ценности и N ради новых ценностей.

-- Trigger for INSERT
CREATE TRIGGER Database.TableInsert AFTER INSERT ON Database.OriginalTable
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN
   DECLARE Now TIMESTAMP;
   SET NOW = CURRENT TIMESTAMP;

   INSERT INTO Database.HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
   VALUES (N.Column1, N.Column2, ..., N.Columnn, Now, NULL);
END;

-- Trigger for DELETE
CREATE TRIGGER Database.TableDelete AFTER DELETE ON Database.OriginalTable
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
   DECLARE Now TIMESTAMP;
   SET NOW = CURRENT TIMESTAMP;

   UPDATE Database.HistoryTable
      SET EndDate = Now
    WHERE Column1 = O.Column1
      AND EndDate IS NULL;
END;

-- Trigger for UPDATE
CREATE TRIGGER Database.TableUpdate AFTER UPDATE ON Database.OriginalTable
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
   DECLARE Now TIMESTAMP;
   SET NOW = CURRENT TIMESTAMP;

   UPDATE Database.HistoryTable
      SET EndDate = Now
    WHERE Column1 = O.Column1
      AND EndDate IS NULL;

   INSERT INTO Database.HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
   VALUES (N.Column1, N.Column2, ..., N.Columnn, Now, NULL);
END;

Microsoft SQL-сервер ^[2]

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

CREATE TRIGGER TableTrigger ON OriginalTable FOR DELETE, INSERT, UPDATE AS

DECLARE @NOW DATETIME
SET @NOW = CURRENT_TIMESTAMP

UPDATE HistoryTable
   SET EndDate = @now
  FROM HistoryTable, DELETED
 WHERE HistoryTable.ColumnID = DELETED.ColumnID
   AND HistoryTable.EndDate IS NULL

INSERT INTO HistoryTable (ColumnID, Column2, ..., Columnn, StartDate, EndDate)
SELECT ColumnID, Column2, ..., Columnn, @NOW, NULL
  FROM INSERTED

MySQL

Триггер не может быть прикреплен более чем к одной операции ( INSERT , DELETE , UPDATE ), поэтому триггер должен быть создан для каждой операции.
Старые и новые значения отображаются как поля структур данных записи, называемых Old и New.

DELIMITER $$

/* Trigger  for INSERT */
CREATE TRIGGER HistoryTableInsert AFTER INSERT ON OriginalTable FOR EACH ROW BEGIN
   DECLARE N DATETIME;
   SET N = now();
    
   INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
   VALUES (New.Column1, New.Column2, ..., New.Columnn, N, NULL);
END;

/* Trigger for DELETE */
CREATE TRIGGER HistoryTableDelete AFTER DELETE ON OriginalTable FOR EACH ROW BEGIN
   DECLARE N DATETIME;
   SET N = now();
    
   UPDATE HistoryTable
      SET EndDate = N
    WHERE Column1 = OLD.Column1
      AND EndDate IS NULL;
END;

/* Trigger for UPDATE */
CREATE TRIGGER HistoryTableUpdate AFTER UPDATE ON OriginalTable FOR EACH ROW BEGIN
   DECLARE N DATETIME;
   SET N = now();

   UPDATE HistoryTable
      SET EndDate = N
    WHERE Column1 = OLD.Column1
      AND EndDate IS NULL;

   INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
   VALUES (New.Column1, New.Column2, ..., New.Columnn, N, NULL);
END;

Оракул

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

CREATE OR REPLACE TRIGGER TableTrigger
AFTER INSERT OR UPDATE OR DELETE ON OriginalTable
FOR EACH ROW
DECLARE Now TIMESTAMP;
BEGIN
   SELECT CURRENT_TIMESTAMP INTO Now FROM Dual;

   UPDATE HistoryTable
      SET EndDate = Now
    WHERE EndDate IS NULL
      AND Column1 = :OLD.Column1;

   IF :NEW.Column1 IS NOT NULL THEN
      INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate) 
      VALUES (:NEW.Column1, :NEW.Column2, ..., :NEW.Columnn, Now, NULL);
   END IF;
END;

Историческая информация

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

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

Используя триггер журнала, информация, которую мы можем знать, не дискретна, а непрерывна, мы можем знать точное состояние информации в любой момент времени, ограничиваясь только степенью детализации времени, обеспечиваемой DATETIME тип данных используемой СУБД .

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

Это просто.
Это не коммерческий продукт, он работает с доступными функциями обычных СУБД .
Это автоматический процесс: как только он создан, он работает без дальнейшего вмешательства человека.
Не требуется хороших знаний о таблицах базы данных или модели данных.
Изменения в текущей программе не требуются.
Изменения в текущих таблицах не требуются, поскольку данные журнала любой таблицы хранятся в другой.
Это работает как для программных, так и для специальных заявлений.
Регистрируются только изменения ( операции INSERT , UPDATE и DELETE ), поэтому скорость роста таблиц истории пропорциональна изменениям.
Нет необходимости применять триггер ко всем таблицам в базе данных, его можно применять к определенным или определенным столбцам таблицы таблицам .

Недостатки

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

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

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

SELECT Column1, Column2, ..., Columnn
  FROM HistoryTable
 WHERE EndDate IS NULL

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

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

Предположим, @DATE переменная содержит интересующую точку или время.

SELECT  Column1, Column2, ..., Columnn
  FROM  HistoryTable
 WHERE  @Date >= StartDate
   AND (@Date < EndDate OR EndDate IS NULL)

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

Предположим, @DATE переменная содержит интересующую точку или время, а @KEY переменная содержит первичный ключ интересующего объекта.

SELECT  Column1, Column2, ..., Columnn
  FROM  HistoryTable
 WHERE  Column1 = @Key
   AND  @Date >= StartDate
   AND (@Date <  EndDate OR EndDate IS NULL)

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

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

SELECT Column1, Column2, ..., Columnn, StartDate, EndDate
  FROM HistoryTable
 WHERE Column1 = @Key
 ORDER BY StartDate

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

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

SELECT H2.Column1, H2.Column2, ..., H2.Columnn, H2.StartDate
  FROM HistoryTable AS H2 LEFT OUTER JOIN HistoryTable AS H1
    ON H2.Column1 = H1.Column1
   AND H2.Column1 = @Key
   AND H2.StartDate = H1.EndDate
 WHERE H2.EndDate IS NULL

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

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

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

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

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

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

См. также

Примечания

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

Внешние ссылки

Триггер журнала на GitHub

Ссылки

^ «Microsoft SQL Server 2008 — Разработка баз данных», Тобиас Тернстрем и др. (Майкрософт Пресс, 2009 г.)
^ «Р. Угальде, Лоуренс; Триггер журнала» . Гитхаб . Проверено 26 июня 2022 г.

[1] «Основы баз данных», Наридж Шарма и др. (Первое издание, © IBM Corp., 2010 г.)

[2] «Microsoft SQL Server 2008 — Разработка баз данных», Тобиас Тернстрем и др. (Майкрософт Пресс, 2009 г.)

[GitHub-3] «Р. Угальде, Лоуренс; Триггер журнала» . Гитхаб . Проверено 26 июня 2022 г.

[1]

[2]

[3]