Генераторы и триггеры. Реализация автоинкрементного поля

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

Два обязательных параметра, комбинация которых может запрограммировать триггер на шесть различных событий:

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

[POSITION <число>]

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

AS

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

[DECLARE [VARIABLE] <переменная тип_данных>;]

Переменные NEW и OLD

Эти переменные объявлять не нужно, они уже присутствуют в каждом триггере. Соответственно, переменные хранят старое и новое значения какого либо поля. Обращаться к этим значениям можно так:

NEW.<имя_поля>

Эти переменные могут быть использованы для:

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

Имеются некоторые ограничения на использование этих переменных. Так, значения NEW могут быть использованы в событиях INSERT и UPDATE, при удалении записи NEW имеет значение NULL. Значения OLD доступны в событиях UPDATE и DELETE, а при вставке новой записи OLD имеет значение NULL.

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

SET TERM ^;
CREATE TRIGGER NotOtric FOR Table_Cel
ACTIVE BEFORE INSERT
AS
BEGIN
   IF (NEW.Dlinnoe < 0) THEN NEW.Dlinnoe = 0;
END^
SET TERM ;^

Создайте этот триггер с помощью Interactive SQL. Затем в этой же утилите введите два значения (подробней о редактировании мы поговорим на следующей лекции):

INSERT INTO Table_cel (Dlinnoe) VALUES (5);
INSERT INTO Table_cel (Dlinnoe) VALUES (-10);
SELECT * FROM Table_cel;

Как видите, в таблице появились две новые строки:

Рис. 20.3. Две новые записи

В первом случае значение 5 сохранилось без изменения, а во второй записи триггер изменил значение -10 на 0.

Реализация автоинкрементных ключевых полей

Для создания поля, значение которого автоматически увеличивается на единицу, нужно сделать несколько действий:

1. Создать генератор для ключевого поля. Ключевое поле должно иметь тип INTEGER, быть NOT NULL и объявлено как PRIMARY KEY. Собственно, генератор можно использовать для любого автоинкрементного поля, не обязательно ключевого. Но чаще всего генераторы используют именно для ключевых полей.
2. Присвоить генератору значение 0 (или иное, если таблица перенесена из другой БД, и уже содержит записи).
3. Создать триггер BEFORE INSERT, увеличивающий это значение на 1.

Итак, приступим. В нашей базе данных имеется таблица Tovar, в которой первое поле ID объявлено как INTEGER NOT NULL. К сожалению, поле не было объявлено, как ключевое PRIMARY KEY. Изменим таблицу, добавив в нее первичный ключ по полю ID:

ALTER TABLE TOVAR ADD PRIMARY KEY (ID);

Теперь сделаем это поле автоинкрементным:

/*Создаем генератор*/
CREATE GENERATOR Gen_Tovar ;
/*Присваиваем генератору начальное значение*/
SET GENERATOR Gen_Tovar TO 0;
/*Создаем триггер*/
SET TERM ^;
CREATE TRIGGER Tr_Tovar FOR Tovar
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(Gen_Tovar, 1);
END^
SET TERM ;^
/* Завершаем транзакцию: */
COMMIT;

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

NEW.ID = GEN_ID(Gen_Tovar, 1);

делать это вместе с проверкой на NULL:

IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(Gen_Tovar, 1);

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

INSERT INTO Tovar (Nazvanie, Stoimost) VALUES ('Сахар', 10.50);
INSERT INTO Tovar (Nazvanie, Stoimost) VALUES ('Крупа', 8.20);
SELECT * FROM Tovar;

Если вы все сделали правильно, то в таблице появятся две записи, а поле ID будет автоматически увеличиваться на 1:

Рис. 20.4. Демонстрация работы автоинкрементного поля

Обратите внимание на то, что мы вносили значения только в поля Nazvanie и Stoimost. Значения для поля ID генерировались триггером автоматически. Не забудьте перед закрытием окна Interactive SQL закрыть транзакцию командой COMMIT.

В отличие от хранимых процедур, для триггеров не предусмотрен раздел в дереве серверов утилиты IBConsole. Однако увидеть наш триггер можно. Триггер создавался для таблицы Tovar. Выделите ее, нажмите правую кнопку мыши и в контекстном меню выберите команду Properties. Откроется окно свойств таблицы, в котором следует перейти на вкладку Metadata. В этом окне, после описания создания таблицы, вы увидите описание нашего триггера Tr_Tovar.