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

Генераторы

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

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

CREATE GENERATOR Gen1;

Внимание! Генераторы можно создавать, но удалить их не получится, поэтому в реальной базе данных прежде продумайте, какие генераторы у вас будут, а потом только создавайте их. Откройте утилиту IBConsole, войдите в локальный сервер и откройте нашу базу данных FIRST. Затем запустите Interactive SQL, и создайте генератор Gen1, как в примере выше. Затем выделите раздел "Generators" в дереве серверов, и в правой части вы увидите наш генератор, а также его текущее значение:

Как видно из рисунка, генератору сразу присваивается значение 0. Тем не менее, во избежание возможных ошибок, вторым шагом нередко присваивают генератору это значение оператором SET GENERATOR:

SET GENERATOR Gen1 TO 0;

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

Иногда бывает необходимым присваивать генератору не нулевое, а другое значение. Например, если вы перенесли базу данных из Paradox в InterBase. В этом случае, таблица уже содержит записи, которые пронумерованы. Автоинкрементное поле при переносе превращается в INTEGER. Требуется посмотреть последнее значение этого поля, и присвоить генератору именно его.

Увеличение шага генератора

Как вы понимаете, для ввода каждой следующей записи вовсе нет необходимости открывать IBConsole, смотреть значение генератора и вручную устанавливать новое значение. Для этого используется специальная процедура GEN_ID() (выполнять этот пример не нужно):

GEN_ID(Gen1, 1)

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

SELECT GEN_ID(Gen1, 1) FROM RDB$DATABASE;

В нижнем окне Interactive SQL будет выведен результат:

Как видно из рисунка, мы получили значение 1. Выполните также команду COMMIT, чтобы завершить транзакцию, затем закройте Interactive SQL. Выделите раздел Generators и убедитесь, что значение изменилось. Что, собственно, произошло? Дело в том, что когда вы создаете новую базу данных, InterBase прежде всего создает в ней собственные системные таблицы. Одной из таких таблиц является RDB$DATABASE, которая всегда хранит только одну запись с некоторыми системными параметрами базы данных. Эту же таблицу иногда применяют для "пустых" запросов, которые возвращают значение одной из переменных или вычисляемое значение. Нашим предыдущим запросом мы вначале увеличили значение генератора на 1, затем вывели его на экран оператором SELECT. Узнать текущее значение генератора, не увеличивая его, можно строкой:

SELECT GEN_ID(Gen1, 0) FROM RDB$DATABASE;

где в процедуре GEN_ID() указывается шаг 0.

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

Совет: если генератор уже находится в использовании, в рабочей базе данных, НИКОГДА не переустанавливайте его значений вручную - это чревато порчей целостности и достоверности данных.

Триггеры

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

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

Триггер может выполняться в двух фазах изменения данных: до(Before) какого то события, или после(After) него. Синтаксис определения триггера следующий:

CREATE TRIGGER <имя_триггера> FOR <имя_таблицы>
[ACTIVE | INACTIVE]
{BEFORE | AFTER} {DELETE | INSERT \ UPDATE}
[POSITION <число>]
AS
[DECLARE [VARIABLE] <переменная тип_данных>;]
BEGIN
  <операторы_триггера>
END

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

[ACTIVE | INACTIVE]

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

{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;

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

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

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

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

Итак, приступим. В нашей базе данных имеется таблица 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:

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

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