6.7.4. Использование триггеров для обеспечения ссылочной целостности.

Триггер - это процедура БД, автоматически вызываемая SQL-сервером при обновлении, удалении или добавлении новой записи в ТБД. Непосредственно из программы к триггерам обратиться нельзя. Нельзя и передавать им входные параметры и получать от них значения выходных параметров. Триггеры всегда реализуют действие.

По событию изменения ТБД триггеры различаются на вызываемые при:

• добавлении новой записи;

• изменении существующей записи;

• удалении записи.

По отношению к событию, влекущему их вызов, триггеры различаются на:

• выполняемые до наступления события;

• выполняемые после наступления события

Преимущества использования триггеров:

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

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

ЗАМЕЧАНИЕ. При откате транзакции откатываются также и все изменения, внесенные в БД триггерами.

Триггер создается оператором CREATE TRIGGER, которой имеет следующий формат

CREATE TRIGGER Имя_Триггера

FOR ИмяТаблицы [ACTIVE | INACTIVE]

{BEFORE | AFTER} {DELETE | INSERT | UPDATE}

[POSITION номер]

AS

<тело триггера>

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

Структура тела триггера:

[<объявление локальных переменных процедуры>]

BEGIN

<оператор>

END

Вот элементы заголовка, которые могут использоваться в определении триггеров:

• AFTER UPDATE – запуск триггера после модификации таблицы;

• AFTER INSERT – запуск триггера после вставки записи в таблицу;

• AFTER DELETE – запуск триггера после удаления записи из таблицы;

• BEFORE UPDATE – запуск триггера перед модификацией таблицы;

• BEFORE INSERT – запуск триггера перед вставкой новой записи в таблицу;

• BEFORE DELETE – запуск триггера перед удалением записи из таблицы.

Значение OLD.ИмяСтолбца позволяет обратиться к состоянию столбца, имевшему место до внесения возможных изменений, а значение NEW.ИмяСтолбца – к состоянию столбца после внесения возможных изменений.

В том случае, если значение в столбце не изменилось, OLD.ИмяСтолбца будет равно NEW.ИмяСтолбца.

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

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

CREATE TABLE BookAuthors(

Code INTEGER NOT NULL,

…

PRIMARY KEY(Code));

CREATE TABLE Books(

Code INTEGER NOT NULL,

AuthorCode INTEGER NOT NULL,

…

PRIMARY KEY(Code),

FOREIGN KEY(AuthorCode) REFERENCES BookAuthors Code);

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

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

CREATE TRIGER UpAuthorCodeBooks FOR BookAuthors

ACTIVE

BEFORE UPDATE

AS

BEGIN

IF (OLD.Code <> NEW.Code) THEN

UPDATE Books

SET AuthorCode = NEW.Code

WHERE AuthorCode = OLD.Code

END

Триггер, реализующий каскадное удаление в дочерней таблице, будет выглядеть так.

CREATE TRIGER DelAuthorCodeBooks FOR BookAuthors

ACTIVE

AFTER DELETE

AS

BEGIN

DELETE FROM Books

WHERE AuthorCode = OLD.Code

END

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

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