Опубликован: 07.05.2010 | Уровень: специалист | Доступ: платный
Лекция 20:

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

< Лекция 19 || Лекция 20: 12 || Лекция 21 >

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

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

Таблица 20.1. . Варианты возможных событий триггера
Комбинация параметров Описание
BEFORE INSERT Триггер вызывается до создания новой строки. Такой триггер обычно используют для поддержки автоинкрементных полей. Также внутри триггера можно изменить входные значения, или сгенерировать значение для какого либо поля.
AFTER INSERT Триггер вызывается после создания новой записи, и не позволяет менять значения полей. Обычно такой триггер используют для модификации других, связанных таблиц.
BEFORE DELETE Триггер вызывается перед удалением записи. Чаще всего его используют для реализации бизнес-правил.
AFTER DELETE Триггер вызывается после удаления записи. Его также используют для реализации бизнес-правил, либо модификации других таблиц.
BEFORE UPDATE Триггер вызывается перед принятием новых значений в поля записи. Позволяет менять входные значения.
AFTER 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.

< Лекция 19 || Лекция 20: 12 || Лекция 21 >
Евгений Медведев
Евгений Медведев

В лекции №2 вставляю модуль данных. При попытке заменить name на  fDM выдает ошибку: "The project already contains a form or module named fDM!". Что делать? 

Анна Зеленина
Анна Зеленина

При вводе типов успешно сохраняется только 1я строчка. При попытке ввести второй тип вылезает сообщение об ошибке "project mymenu.exe raised exception class EOleException with message 'Microsoft Драйвер ODBC Paradox В операции должен использоваться обновляемый запрос'. 

Денис Попов
Денис Попов
Россия, Оренбург, Оренбургский государственный университет, 2015
Рустам Кадыров
Рустам Кадыров
Россия, Тирлян, Тирлянская школа №5, 2003