ТРИГГЕРЫ И ХРАНИМЫЕ ПРОЦЕДУРЫ

Триггеры и их назначение Триггер — это отдельная, хранимая в базе подпрограмма, связанная с таблицей или обзором (представлением), которая автоматически включается, когда в таблицу или обзор вставляется (триггер добавления), модифицируется (триггер модификации) или удаляется (триггер удаления) строка.

Триггер никогда не вызывается непосредственно. Он выполняется всякий раз, когда приложение или пользователь пытается производить действия над строкой в таблице. Триггер жестко связан с данными и выполняется тогда и только тогда, когда делается попытка их изменить.

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

Триггеры позволяют:

- контролировать входные данные, обеспечивая повышение достоверности информации и ее логическую непротиворечивость;
- повышать независимость прикладного программного обеспечения. Изменение схемы контроля в триггере автоматически отображается во всех приложениях, не требуя ни внесения в них каких-либо изменений, ни их перетрансляции;
- обеспечивать автоматическую регистрацию изменений в таблицах. Приложение может хранить полный протокол изменений, используя триггеры, которые включаются при каждом изменении таблицы;
- выполнять синхронные изменения в нескольких таблицах, обеспечивая как логическую целостность данных, так и автоматическое поддержание соответствия первичных и агрегированных данных;
- автоматически уведомлять об изменениях в БД, используя события, создаваемые триггерами.

Триггеры создаются командой CREATE TRIGGER, модифицируются - ALTER TRIGGER и удаляются - DROP TRIGGER.

Триггер состоит из заголовка и тела.

Заголовок содержит:

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

Тело триггера содержит:

? необязательный список локальных переменных с указанием их типов;
? программный блок на языке процедур и триггеров InterBase (набор инструкций в операторных скобках BEGIN ... END). Выполняется при включении триггера, может включать в себя другие блоки.

Хранимые процедуры и их назначение

Хранимая процедура — это отдельная программа, написанная на SQL для процедур и триггеров InterBase. Сами процедуры хранятся в БД. Позволяют вести поиск и обработку данных непосредственно на сервере, обеспечивая максимальную независимость клиентской части приложений. В них могут использоваться любые конструкции SQL для процедур и триггеров InterBase. Они, как обычные программы, могут получать входные параметры и возвращать значения вызвавшим их приложениям. Кроме того, могут возвращать не только отдельный набор значений — строку, но и множество строк, которое можно рассматривать как виртуальную таблицу.

Хранимая процедура может также вызываться непосредственно из приложения, других хранимых процедур или триггеров. Процедуры, возвращающие множество строк, можно использовать в команде SELECT на месте таблиц или обзоров.

Использование хранимых процедур дает ряд преимуществ:

«/ Модульность проектирования. Приложения, которые обращаются к одной БД, могут совместно использовать хранимые процедуры, устраняя двойной код и потенциальные ошибки, уменьшая размер приложений.

? Локализация изменений. Если процедура модифицируется, то все внесенные изменения автоматически отражаются в приложениях, которые используют процедуру, обеспечивая их согласованность. При этом нет необходимости в перетран- сляции и перекомпоновке приложений.
? Ускорение обработки. Хранимые процедуры выполняются сервером, а не клиентом, что позволяет ускорить обработку запросов и сократить сетевой трафик.

Процедуры по своему назначению разделяются на два вида: выполнимые процедуры и процедуры выбора.

Выполнимая процедура — это обычная программа, которая получает (возможно и не получает) несколько параметров, выполняет какие-либо действия в БД и возвращает (возможно и не возвращает) несколько значений.

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

SQL для процедур и триггеров в InterBase

SQL для процедур и триггеров в InterBase представляет собой законченный язык программирования для манипулирования данными.

Язык включает:

— инструкции манипулирования данными SQL: добавление, модификация, удаление, выборка данных из базы в список переменных;
— операторы SQL и выражения, включая функции пользователя (UDF);
— расширение SQL, включающее операторы присваивания, управления последовательностью вычислений, возможность использования собственных и контекстных переменных, операторы генерации событий и исключений, а также команды обработки ошибок.

При работе с данными используются следующие операции:

— конкатенации (объединения) для строковых данных;
— арифметические;
— сравнения;
— логические операции.

Хотя хранимые процедуры и триггеры используются различным образом и в разных целях, они базируются на одном и том же языке. И хранимые процедуры, и триггеры могут использовать любые конструкции языка, за исключением следующих:

— контекстные переменные допустимы только в триггерах;
— входные и выходные параметры, а также инструкции SUSPEND и EXIT, которые возвращают значения, применимы только в хранимых процедурах.

Оператор присваивания «=»

Синтаксис:

Переменная = выражение;

Оператор объявления переменных

Оператор DECLARE применяется в хранимых процедурах и триггерах и размещается в начале их тела непосредственно перед скобкой BEGIN, за которой размещаются все исполнимые операторы. Все используемые переменные должны быть объявлены. Одним оператором может быть объявлена только одна переменная, и объявления имеют смысл только внутри хранимой процедуры или триггера.

Синтаксис:

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

Оператор генерации исключения

Генерирует сообщение об ошибке (исключение). Применяется в хранимых процедурах и триггерах.

Синтаксис:

EXCEPTION имя_исключения;

Имя_исключения — имя генерируемого исключения. Исключение с данным именем должно быть предварительно создано в базе командой CREATE EXCEPTION.

Оператор вызова процедуры

Оператор осуществляет вызов хранимой процедуры. Применяется в хранимых процедурах и триггерах.

Синтаксис:

EXECUTE PROCEDURE <название> [<список параметров>]

[RETURNING_VALUES <список параметров>];

<название> — имя вызываемой процедуры. Сама процедура должна быть предварительно создана в базе командой CREATE PROCEDURE.

<список параметров>, заданный после имени процедуры, — это список входных параметров процедуры (если процедура не требует параметров, то может отсутствовать). Отдельные параметры могут быть константами или переменными. Перед именем переменной ставится «:», кроме случаев использования контекстных переменных NEW и OLD. <список параметров>, заданный после RETURNING_VALUES - список возвращаемых процедурой значений (если процедура возвращает значения). Перед именем возвращаемых значений ставится «:». В триггерах и процедурах использование вызовов других процедур аналогично использованию подпрограмм в традиционных алгоритмических языках.

Замечание. При вызове из прикладных программ на базовых языках синтаксис вызова отличается от приведенных.

Оператор ветвления

Оператор ветвления IF ... THEN ... ELSE обеспечивает выполнение того или иного действия в зависимости от истинности проверяемого условия.

Синтаксис:

If (<условие>) THEN <выражение1>

[ELSE

<выражение 2>];

Если условие выполнено (значение TRUE), то выполняется оператор, следующий за конструкцией THEN, иначе выполняется оператор, следующий за конструкцией ELSE, если она присутствует.

Оператор цикла

Цикл FOR обеспечивает выполнение заданного оператора для каждой строки команды SELECT. Цикл FOR может использоваться в хранимых процедурах и триггерах.

Синтаксис:

FOR

<выражение_8ЕЕЕСТ>

<операторы>;

FOR SELECT — инструкция цикла, которая отыскивает строку, указанную в <выражение_8ЕЬЕСТ>, и для каждой строки исполняет оператор или блок операторов, указанный после ключевого слова DO.

Конструкция <выражение_8ЕЬЕСТ> представляет собой обычную команду SELECT, к которой добавлена обязательная здесь опция INTO и которая должна быть последней опцией команды SELECT.

В опции INTO перечисляются имена локальных переменных или параметров процедуры, которым присваиваются соответствующие значения выбранных командой SELECT столбцов. Имена переменных должны предваряться символом «:». Пример:

FOR SELECT surname, name FROM student INTO :fam, :s_name DO

IF (fam > PI) THEN

IF (fam < P2) THEN SUSPEND;

Здесь оператор SUSPEND обеспечивает передачу вызывающему приложению данных, удовлетворяющих дополнительному условию.

Пример: хранимая процедура, которая выполняет выборку полей 1D и NAME из таблицы:

CREATE PROCEDURE Simple_Select_SP

RETURNS (procID INTEGER, procNAME VARCHAR(80))

BEGIN

FOR

SELECT ID, NAME FROM table_example INTO :procID, :procNAME DO

BEGIN

SUSPEND;

END

Оператор цикла

Оператор цикла WHILE обеспечивает выполнение оператора, указанного после ключевого слова DO, пока указанное после WHILE условие истинно. Цикл WHILE может использоваться в хранимых процедурах и триггерах.

Синтаксис:

WHILE (<условие>) DO

<операторы>;

WHILE ... DO — оператор выполнения цикла, который повторяет оператор или блок операторов, указанных после DO, пока условие <условие> истинно. Условие проверяется в начале каждого цикла.

Пример: процедура, которая возвращает квадраты целых чисел от 0 до 99:

CREATE PROCEDURE QUAD RETURNS (QUADRAT INTEGER)

DECLARE VARIABLE INTEGER;

BEGIN I = 1;

WHILE (i < 100) DO BEGIN

QUADRAT = 1*1;

1 = 1+1;

SUSPEND;

END

Оператор генерации события

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

Сама обработка событий в InterBase строится по следующей схеме.

1. Приложение выдает команду:

EVENT INIT список событий (имя_события1 [, имя со- бытия2 ...]);

По этой команде создается список событий, содержащий имена событий имя_события1, имя_события2 ...

2. То же или другое приложение выдает команду:

EVENT WAIT список_событий;

По этой команде приложение приостанавливается и ожидает наступления одного из событий в списке.

3. Само событие генерируется внутри триггера или хранимой процедуры командой POST_EVENT. После того, как такое событие произошло, ожидающее приложение получает соответствующее сообщение и продолжает свою работу. Такой механизм позволяет приложениям обрабатывать различные специфические ситуации при работе с БД.

Внутри триггеров и хранимых процедур реализуется только команда POST_EVENT.

Синтаксис:

POST_EVENT <имя_события>;

Параметр <имя_события> может быть либо символьным литералом в кавычках, либо строковой переменной.

Замечание. Имена переменных в хранимых процедурах не должны предваряться символом «:» нигде, кроме как в командах SELECT, INSERT, UPDATE, DELETE, что позволяет отличать их от имен столбцов.

При выполнении процедуры команда POST_EVENT сообщает диспетчеру событий о наступлении события. Диспетчер следит за приложениями, ждущими событий, и извещает об их наступлении.

Операторы SQL INSERT, UPDATE, SELECT, DELETE

Внутри хранимых процедур и триггеров могут использоваться стандартные команды SQL: INSERT, UPDATE, SELECT, DELETE. Единственной особенностью этих команд является то, что в них могут использоваться в качестве параметров локальные переменные процедур. Для того, чтобы отличать локальные переменные от столбцов таблиц в данных командах, имена локальных переменных предваряются символом «:».

Кроме того, для помещения результатов выборки в локальные переменные к команде SELECT добавляется опция INTO

Оператор возврата значений выходных параметров Оператор SUSPEND предназначен для использования в хранимых процедурах (в триггерах SUSPEND неприменим), причем только в процедурах выбора, хотя синтаксически допустим и в выполнимых процедурах.

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

SUSPEND не должен использоваться в выполнимых процедурах, так как команды, следующие за ним, никогда не будут выполнены. В выполнимых процедурах следует использовать оператор явного выхода EXIT.

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

Оператор прерывания процедуры

И в процедурах выбора, и в выполнимых процедурах оператор EXIT передает управление на конец процедуры (завершающий END).

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

s/ в процедуре выбора конечная команда END возвращает управление вызвавшему ее приложению и устанавливает SQL CODE в 100; это указывает, что список найденных ею строк закончен;

? в выполнимой процедуре конечная команда END возвращает управление вызвавшему ее приложению с установкой значений выходных параметров, если они есть.

Оператор обработки ошибок

Оператор WHEN ... DO обеспечивает обработку возникших ошибок. Оператор применяется в хранимых процедурах и триггерах.

Синтаксис:

WHEN {<список_ошибок> / ANY}

DO <операторы>

<список_ошибок> ::=

{EXCEPTION имя_исключения / SQL CODE номер / GDS CODE код_ошибки}.

Оператор WHEN должен быть последним в блоке BEGIN ... END. С его помощью процедуры и триггеры могут обрабатывать ошибки трех типов:

1. Исключения, инициированные оператором EXCEPTION в данной процедуре (процедурах), прямо или косвенно вызванной данным оператором, а также исключения, инициированные в триггерах, вызванных в результате действий этих процедур.
2. SQL-ошибки, идентифицирующиеся SQL CODE.
3. Ошибки, идентифицирующиеся кодами ошибок InterBase.

Описание синтаксиса оператора WHEN:

EXCEPTION имя_исключения - имя исключения, описанного в базе;

SQL CODE номер — код ошибки (SQL CODE);

GDS CODE код_ошибки - код ошибки (InterBase);

ANY — обеспечивает вызов обработчика для любых перехватываемых ошибок;

<операторы> — простой оператор или блок, осуществляющий обработку ошибок.

Обработка исключений

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

1. Прекращается выполнение блока BEGIN ... END, содержащего исключение, и отменяются действия, выполненные в блоке.
2. Если блок содержит оператор WHEN, управление передается ему, в противном случае, аналогичные действия производятся в блоке, содержащем данный оператор. Подобные действия производятся до тех пор, пока либо не будет найден соответствующий оператор WHEN, либо не будет достигнут уровень процедуры. В последнем случае процедура будет завершена, а исключение не будет обработано.
3. Выполняются действия, определенные оператором (блоком), заданным в конструкции WHEN (если он обнаружен).

Управление возвращается оператору (блоку) программы, следующему за оператором WHEN.

Если исключение обработано с помощью WHEN, то соответствующее сообщение об ошибке не выдается.

Обработка SQL-ошибок

Процедуры могут также обрабатывать SQL-ошибки по кодам, возвращенным в SQL CODE. После выполнения каждой команды формируется код ее завершения — SQL CODE, отражающий успешность выполнения, или код ошибки. SQL CODE может также содержать код предупреждения, типа того, что перечень строк в выборке по циклу FOR SELECT исчерпан.

Коды завершения команд SQL.

О — успешное завершение;

1—99 — предупреждение или информационное сообщение;
100 — конец файла (списка);

<0 — ошибка. Команда не выполнена.

Пример:

WHEN SQL CODE - 803

/*Попытка добавить строку со значением первичного ключа, которое уже есть в таблице*/

BEGIN

Обработка ошибок InterBase

Процедуры могут обрабатывать ошибки InterBase по кодам, возвращенным в GDS CODE.

Например, если команда в процедуре пытается модифицировать строку, уже модифицированную другой, еще не завершенной транзакцией, то в этом случае процедура могла бы получать код ошибки InterBase — isc_lock_conflict. При повторении попытки ее модификации, другая транзакция может выполнить откат, сняв таким образом блокировку, что позволит успешно завершить команду.

Используя инструкцию WHEN GDS CODE, процедура может обрабатывать ошибки конфликта блокировки и повторять его операцию.

В качестве примера рассмотрим фрагмент хранимой процедуры с возвращаемым параметром RET CODE, который устанавливается в 0 при нормальном выполнении процедуры и в 1 при возникновении каких либо ошибок:

BEGIN

RET CODE = 0;

WHEN ANY DO BEGIN

RET CODE = 1;

EXIT;

END

Команды создания> удаления и модификации триггеров

Синтаксис команды для создания триггеров:

CREATE TRIGGER <имя_триггера> FOR {ТАБЛИЦА | ОБЗОР} {ACTIVE | INACTIVE}

{BEFORE | AFTER} {DELETE | INSERT | UPDATE} [POSITION number]

<тело триггера>;

Синтаксические конструкции команды CREATE TRIGGER: <имя_триггера> — задает имя триггера;

ТАБЛИЦА | ОБЗОР — имя таблицы или обзора, для которых создается триггер;

ACTIVE | INACTIVE - необязательная конструкция определяет активность триггера. ACTIVE — триггер включен, INACTIVE — триггер отключен;

BEFORE | AFTER — обязательная конструкция. Определяет, когда включается триггер. BEFORE - перед операцией, выполняемой над таблицей, AFTER — после операции, выполняемой над таблицей;

DELETE | INSERT | UPDATE - указывает, при выполнении какой именно операции будет включаться триггер;

POSITION number — задает порядок, в котором будут выполняться триггеры (с одной таблицей и с одними и теми же включениями может быть связано несколько триггеров). Number должен быть целым числом между 0 и 32 767 включительно. Выполнение триггеров происходит в порядке возрастания номеров. Если триггеры имеют один и тот же номер, то они будут включаться в алфавитном порядке;

<тело триггера> — состоит из двух частей:

1) DECLARE VARIABLE <имя переменной> <тип_данных> - объявляет локальные переменные, используемые только в триггере;
2) непосредственно тело триггера как последовательный набор операций, заключенный в операторные скобки.

Примеры: 1) триггер для контроля добавления данных в таблицу STUDENT:

CREATE TRIGGER I_1 FOR STUDENT

ACTIVE BEFORE INSERT POSITION 0

BEGIN

IF (NEW.SURNAME IS NULL) THEN EXCEPTION NOJJSER; IF (NEW.NAME IS NULL) THEN NEW.NAME = ’ИМЯ'; END

2) проверка заполнения внешнего ключа таблицы STUDENT (значение идентификатора университета должно существовать в БД):

CREATE TRIGGER I_2

FOR STUDENT

ACTIVE BEFORE INSERT POSITION 1

BEGIN

IF (NEW.UNIVJD > 0) THEN IF (NOT EXISTS (SELECT * FROM UNIVERSITY WHERE (U NIV ID=N EW.U NIV ID))) THEN EXCEPTION NO UN IV;

END

3) триггер, который срабатывает при продаже билета (таблица «Sale») и изменяет статус билета «свободен» = 0 на «продан» = 1 (в таблице «Tickets»):

CREATE TRIGGER ’Status"

FOR "Sale"

ACTIVE BEFORE INSERT POSITION 0

BEGIN

UPDATE "Tickets"

SET "Status" = 1

WHERE "Ticket number" = NEW."ticket number";

END

Ш ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ
1. Дайте определение триггера в БД. Для чего он предназначен?
2. Опишите составные части триггеров.
3. Дайте определение хранимой процедуры в БД. Для чего она предназначена?
4. Перечислите преимущества использования хранимых процедур.
5. Назовите виды хранимых процедур.
6. Опишите операторы, применяемые в языке SQL для работы с триггерами и хранимыми процедурами.
7. Приведите синтаксис команд для работы с триггерами.