Создание триггеров для поддержания ссылочной целостности

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

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

· добавлении новой записи (INSERT);

· изменении существующей записи (UPDATE);

· удалении записи (DELETE).

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

· выполняемые до наступления события (BEFORE);

· выполняемые после наступления события (AFTER).

Триггер создается следующим оператором:

CREATE TRIGGER Имя_Триггера FOR Имя_Таблицы

[ACTIVE | INACTIVE]

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE}

[POSITION Номер]

AS

[объявление локальных переменных]

BEGIN

<Операторы SQL>

END

POSITION Номер указывает, каким по счету будет выполняться триггер в случае наличия группы триггеров, обладающих одинаковыми характеристиками операции и времени (до, после операции) вызова триггера. Значение номера задается числом в диапазоне 0..32767. Триггеры с меньшими номерами выполняются раньше.

Для создания триггера необходимо выполнить пункт меню Database, New Trigger. IBExpert выдаст окно для создания будущего триггера (рис. 19).

Рис. 19. Окно создания триггера.

После написания триггера его необходимо откомпилировать и подтвердить выполнение транзакции (Commit).

На закладке DDL можно посмотреть DDL-сценарий триггера. Для триггера, показанного на рис. 15 он выглядит следующим образом:

CREATE TRIGGER INS_CLASS FOR CLASS

ACTIVE BEFORE INSERT POSITION 0

AS

begin

New.num_class = GEN_ID(CLASS_GEN,1);

End

Данный триггер срабатывает перед добавлением новой записи в таблицу Class. Новое значение поля num_class принимает значение генератора, увеличенное на единицу. GEN_ID – возвращает значение генератора, имя которого указано в круглых скобках.

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

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

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

CREATE TRIGGER UPD_KOL_PUPIL FOR PUPIL

ACTIVE AFTER UPDATE POSITION 0

AS

DECLARE VARIABLE oldnumclass INTEGER;

DECLARE VARIABLE newnumclass INTEGER;

begin

oldnumclass=old.num_class; /*Старое значение*/

newnumclass=new.num_class; /*Новое значение*/

if (:newnumclass<>:oldnumclass) then

/*Если ученика перевели в другой класс (номера не совпадают), то*/

Begin

/*Увеличиваем кол-во учеников в классе для перевода*/

update class

set kol_pupil=kol_pupil+1

where class.num_class=:newnumclass;

/*Уменьшаем кол-во учеников в классе, из которого он выбыл*/

update class

set kol_pupil=kol_pupil-1

where class.num_class=:oldnumclass;

End

end