Цель работы:
научиться использовать генераторы, исключения и триггеры для работы с данными.
Задание:
а)изучить команды для работы с генераторами, исключениями и триггерами
б)научиться использовать генератор для получения последовательности значений;
в)научиться использовать исключения для вывода сообщений об ошибках;
г)создать триггер, который при вставке новой записи получает следующее значение генератора и вставляет его в ключевое поле;
д)создать несколько исключений, которые обрабатывали бы случаи ввода некорректных данных;
в)оформить и сдать отчет по лабораторной работе N 5.
Генераторы представляют собой механизм создания последовательности уникальных чисел, которые можно автоматически вставлять в поля записей таблицы данных. Очень часто генераторы используют для формирования значений первичных ключей. Количество генераторов в базе данных не ограничено, но каждый генератор должен иметь свое уникальное имя.
Триггер - это отдельная, хранимая в базе программа, связанная с таблицей или представлением, которая автоматически активизируется, когда в таблицу или представление вставляется (триггер добавления), модифицируется (триггер модификации) или удаляется (триггер удаления) строка. Триггер жестко связан с конкретной операцией над данными и, как говорят, "загорается", когда эта операция происходит.
Триггеры могут использовать исключения (генерируемые сообщения об ошибках). Когда в триггере вызывается исключение, его работа завершается, отменяются все сделанные в триггере изменения и генерируется сообщение об ошибке, если не предусмотрена специальная обработка возникших ошибок.
Триггеры позволяют:
-контролировать входные данные, обеспечивая повышение достоверности информации и ее логическую непротиворечивость;
-повысить независимость прикладного программного обеспечения;
-обеспечить автоматическую регистрацию изменений в таблицах;
-выполнять синхронные изменения в нескольких таблицах;
-автоматически уведомлять об изменениях в базе данных, используя события, выдаваемые триггерами.
Подробная структура команд по работе с генераторами, исключениями и триггерами представлена в Language Reference.
Создание генератора
Для создания генератора в базе данных используется команда CREATE GENERATOR. Эта команда объявляет генератор в базе данных и задает его стартовое значение равное нулю (по умолчанию). Если необходимо получить стартовое значение генератора отличное от нуля, следует использовать команду SET GENERATOR, которая устанавливает новое значение.
Синтаксис команды создания генератора следующий.
CREATE GENERATOR name;
Пример. Создадим генератор нового значения уникального идентификатора ID в таблице Lichnost.
CREATE GENERATOR Un_Lich;
Установка стартового значения
Для задания стартового значения генератора отличного от нуля используется команда SET GENERATOR, причем новое значение int должно лежать в пределах -231 до 231-1 и быть целочисленным.
Синтаксис команды:
SET GENERATOR name TO int;
Пример. Зададим новое значение для генератора Un_lich равное 15, поскольку мы договорились, что уникальное значение должно быть отличным от нуля (поскольку мы уже вставляли некоторые записи в таблицу и последнее значение у нас было 15, установим его в качестве стартового).
SET GENERATOR Un_Lich TO 15;
Использование генераторов
Для генерации очередного значения генератора необходимо использовать специальную функцию GEN_ID(). Данная функция в качестве входных параметров имеет два аргумента: имя генератора и шаг инкрементирования (или декрементирования, если используются отрицательные значения шага).
Функция GEN_ID() может быть вызвана из триггеров, хранимых процедур или приложений, когда выполняются операции вставки, изменения или удаления записи.
Пример. Рассмотрим пример на вставку трех новых человек в таблицу Lichnost с использованием генератора.
INSERT INTO Lichnost (ID,Family, Name, Surname, Birthday) VALUES (GEN_ID(Un_Lich,1),'Михайлов','Олег','Викторович', '11.09.1970');
INSERT INTO Lichnost (ID,Family, Name, Surname, Birthday) VALUES (GEN_ID(Un_Lich,1),'Федоров','Семен','Александрович', '03.02.1955');
INSERT INTO Lichnost (ID,Family, Name, Surname, Birthday) VALUES (GEN_ID(Un_Lich,1),'Казакова','Анна','Петровна', '21.09.1973');
Если необходимо получить следующее значение генератор, но нет необходимости вставлять новую запись в базу данных, можно воспользоваться командой SELECT.
Пример. select GEN_ID(Un_Lich,1) from Lichnost;
Команды по работе с исключениями
Создание исключения
CREATE EXCEPTION name ''message";
где name - имя исключение, по которому оно может быть инициировано;
"message" - текст сообщения об ошибке, выдаваемый при инициировании исключения.
Модификация исключения
ALTER EXCEPTION name ''message";
Данная команда изменяет текст, связанный с существующим исключением. Исключение может быть изменено только его создателем. Исключение может быть изменено, даже если оно используется в хранимых процедурах и триггерах, поскольку команда изменяет не имя, а текст сообщения об ошибке.
Удаление исключения
Синтаксис:
DROP EXCEPTION name;
name - имя удаляемого исключения.
Команда может быть использована только создателем исключения.
Команды по работе с триггерами
Создание триггера
Триггер состоит из тела и заголовка. Заголовок содержит:
-имя триггера, уникальное в пределах базы данных;
-имя таблицы, для которой создается триггер;
-действия с таблицей, при наступлении которых триггер включается.
Тело триггера содержит:
-необязательный список локальных переменных с указанием их типов;
-программный блок на языке процедур и триггеров InterBase (набор инструкций в операторных скобках BEGIN и END).
Очень часто при создании триггеров используются исключения - сообщения, которые выводятся при наступлении некоторого события, другими словами поименованное сообщение об ошибке. Для работы с исключениями также используются команды: создание, модификация и удаление.
Синтаксис
CREATE TRIGGER name FOR {table | view}
[ACTIVE | INACTIVE]
{BEFOR | AFTER} {DELETE | INSERT | UPDATE}
[POSITION number]
AS Таблица 1 - Синтаксические конструкции команды
SQL для триггеров в Interbase представляет
собой законченный язык программирования для манипулирования данными.
Язык включает в себя инструкции манипуляции данных (добавление, модификация,
удаление из базы, выборка данных из базы в список переменных); операторы
SQL и выражения, включая функции пользователя.
Рассмотрим работу триггеров на нескольких примерах.
Пример 1
Обработаем такую ситуацию: если пользователь
случайно или преднамеренно вводит отрицательный номер квартиры,
ему выдается сообщение об ошибке ввода. Для обработки такой
ситуации выполним следующую последовательность шагов.
Во-первых, создадим исключение на соответствующую ошибку.
CREATE EXCEPTION Error_Box "Вы ввели номер квартиры меньший нуля";
Во-вторых, создадим триггер, который включится
при введении неправильного значения в таблицу. Этот триггер
активизируется на событие INSERT, то есть при вставке новой
записи в таблицу данных и осуществляет проверку заносимого
в таблицу данных значения new.Box<0. В случае при выполнении
этого условия.
CREATE TRIGGER Befor_Insert_Box
FOR Adr
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (new.Box < 0) THEN EXCEPTION Error_Box;
END;
Пример 2
Предположим такую ситуацию: в базе данных хранится
таблица пользователей и их прав в системе - User. Добавлять данные
в эту таблицу может любой пользователь системы, а удалять
и модифицировать только администратор базы данных. Обработать
эту ситуацию можно такими командами.
CREATE EXCEPTION No_Rights "У Вас нет прав на удаление из таблицы абонентов";
CREATE TRIGGER No_Rights_Update FOR Lichnost
ACTIVE BEFORE UPDATE DELETE POSITION 0
AS
BEGIN
IF (USER <> 'SYSDBA') THEN EXCEPTION No_Rights;
END;
Пример 3.
Создадим триггер, который при вставке новой записи
в таблицу данных Личность запускает ранее созданный генератор Un_Lich
для получения нового значения уникального идентификатора ID.
SET TERM ^;
CREATE TRIGGER Add_New_Lichnost FOR Lichnost
BEFORE INSERT AS
BEGIN
Lichnost.ID=gen_id(Un_Lich,1);
END^
SET TERM ;^
Использование оператора SET TERM необходимо только
в случае создания триггера при помощи sql-скрипта. Поскольку
в данном случае символ ";" используется для отделения друг от
друга команд внутри тела триггера, для отделения самого триггера
как отдельной команды требуется другой символ. Поэтому перед созданием
триггера с помощью команды SET TERM ^: осуществляется замена функций
символа ";" на символ "^", что позволяет для внешнего отделения
команд использовать уже символ "^", а внутри тела триггера для
отделения друг от друга команд можно использовать по-прежнему символ ";".
Изменение триггера
Команда ALTER TRIGGER предназначена для изменения
ранее созданных триггеров, используя эту команду можно изменить
заголовок триггера, тело триггера и то и другое одновременно.
В заголовке триггера можно изменить признак активности триггера,
условие включения триггера. Изменение тела триггера состоит
в его замене на вновь указанное.
Синтаксис команды:
ALTER TRIGGER name
[ACTIVE | INACTIVE]
[{BEFOR | AFTER} {DELETE | INSERT | UPDATE}]
[POSITION number]
AS Для изменения заголовка триггера необходимо
задать список изменяемых опций триггера, при этом остальные опции
не будут меняться.
Если меняется опция AFTER или BEFOR, то должна
быть указана и соответствующая им опция из перечня INSERT UPDATE DELETE.
Пример. Изменим триггер,
теперь он будет включаться не при вставке новой записи,
а при обновлении данных в таблице.
ALTER TRIGGER Befor_Insert_Box
ACTIVE BEFORE UPDATE POSITION 0;
Удаление триггера
Команда DROP TRIGGER используется для физического
удаления триггера из базы данных. Триггер может быть удален SYSDBA,
создателем базы данных или пользователем, имеющим право на удаление
данного триггера, если он не используется в данный момент времени.
Пример. Удалим триггер из предыдущего примера.
DROP TRIGGER Befor_Insert_Box;
Описание
Параметр Name
Имя триггера. Должно быть уникальным в базе данных Table | View
Имя таблицы, для которой создается триггер ACTIVE | INACTIVE
Необязательная конструкция. Определяет активность триггера.
ACTIVE - триггер включен, INACTIVE - триггер отключен. BEFOR | AFTER
Обязательный. Определяет, когда включается триггер:
BEFOR - перед операцией, выполняемой над таблицей;
AFTER - после операции, выполняемой над таблицей. DELETE | INSERT | UPDATE
Указывает, при выполнении какой именно операции
с таблицей будет включаться триггер: DELETE - удаление,
INSERT - вставка, UPDATE - модификация POSITION number
Задает порядок, в котором будут выполняться триггеры
(с одной таблицей и одними и теми же условиями включения может
быть связано несколько триггеров).Number должен быть целым числом
от 0 до 32767 включительно. Включение триггеров происходит
в порядке возрастания номеров.