Лекции по "Систе́ма управле́ния ба́зами да́нных "

 
Рис. 19.2.  Результат работы процедуры PrimerProc

Теперь, открыв в дереве серверовIBConsoleбазу данныхFirst, и выделив подразделStored Procedures, вы увидите три созданных хранимых процедуры, которые в дальнейшем можно вызывать неоднократно.

 

Генераторы

 

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

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

CREATE GENERATOR Gen1;

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

 
Рис. 20.1.  Раздел "Generators" базы данных

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

SET GENERATOR Gen1 TO 0;

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

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

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

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

GEN_ID(Gen1, 1)

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

SELECTGEN_ID(Gen1, 1) FROMRDB$DATABASE;

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

 
Рис. 20.2.  Результат 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}

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

Таблица 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 ^;

CREATETRIGGERNotOtricFORTable_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);

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

/*Создаемгенератор*/

CREATEGENERATORGen_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: