Триггеры.

Триггер - это процедура базы данных, автоматически вызываемая 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);

Для реализации автоматического выполнения каскадных обновлений и изменений необходимо, во-первых, удалить ссылочные целостности, блокирующие такие изменения в определении базе данных (фраза FOREIGN KEY(AuthorCode) REFERENCES BookAuthors Code) и, во-вторых, определить сами триггеры для родительской таблицы.

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

CREATE TRIGGER UpAuthorsCodeBooks FOR BookAuthors

/*Триггер предназначен для обеспечения ссылочной целостности

между таблицами Books и BookAuthors по полям AuthorCode и Code

соответственно. Если изменяется значение кода автора в таблице

BookAuthors, тогда изменяются соответствующие значения кода

автора в таблице Books.*/

ACTIVE

AFTER UPDATE

AS

BEGIN

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

UPDATE Books

SET AuthorCode = NEW.Code

WHERE AuthorCode = OLD.Code;

END

Очевидно, что если не определить триггер, отслеживающий изменения поля AuthorCode в таблице Books, то возможна ситуация при которой целостность данных может быть нарушена. Например, в поле AuthorCode поместят значение кода автора, которого нет в поле Code ни в одной записи таблицы BookAuthors.

CREATE TRIGGER UpCodeBooks FOR Books

/*Триггер предназначен для обеспечения ссылочной целостности

между таблицами Books и BookAuthors по полям AuthorCode и Code

соответственно. Если изменяется значение кода автора в таблице

Books, тогда проверяется есть ли соответствующие значения кода

автора в поле Code таблице BookAuthors. Если значение найдено,

тогда изменения в таблице будет осуществлено. В противном

случае вместо новых значений автора книги будут прописаны

старые.*/

ACTIVE

AFTER UPDATE

AS

DECLARE VARIABLE NewAuthorCodeCount INTEGER;

BEGIN

SELECT COUNT(Code)

FROM BookAuthors

WHERE Code = NEW.AuthorCode

INTO :NewAuthorCodeCount;

IF (:NewAuthorCodeCount = 0) THEN

UPDATE Books

SET AuthorCode = OLD.AuthorCode

WHERE AuthorCode = NEW.AuthorCode;

END

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

CREATE TRIGGER DelCodeInBookAuthors FOR BookAuthors

/*Триггер предназначен для обеспечения каскадного в дочерней

таблице Books, если удалить строку со сведениями об авторах в

родительской таблице BookAuthors.*/

ACTIVE

AFTER DELETE

AS

BEGIN

DELETE FROM Books

WHERE AuthorCode = OLD.Code;

END