Триггеры и хранимые процедуры

Лекция 3

Триггеры и хранимые процедуры

Триггеры и их назначение

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

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

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

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

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

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

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

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

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

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

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

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

Формат:

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

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

Оператор 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<выражение_SELECT> DO<операторы>;

FOR SELECT – инструкция цикла, которая отыскивает строку, указанную в <выражение_ select> и для каждой строки исполняет оператор или блок операторов, указанный после ключевого слова DO.

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

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

Пример.

FOR SELECT surname, name FROM student

INTO :fam, :s_name

DO

IF(fam>P1) THEN

IF(fam<P2) THEN SUSPEND;

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

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

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

Формат:

WHILE ( <условие>) DO<операторы>;

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

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

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

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

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

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

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

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

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

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

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

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

Формат:

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

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

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

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

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

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

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

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

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

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

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

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

SQLCODE	Описание
	Успешное завершение
1-99	Предупреждение или информационное сообщение
	Конец файла (списка)
<0	Ошибка. Команда не выполнена

Пример:

WHEN SQLCODE -803

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

которое уже есть в таблице */

DO

BEGIN

…

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

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

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

Пример:

BEGIN

RETCODE=0;

WHEN ANY DO

BEGIN

RETCODE=1;

EXIT;

END

END

Создание триггеров

синтаксис команды:

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

{ACTIVE | INACTIVE}

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

[POSITION number]

AS

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

Синтаксические конструкции команды CREATE TRIGGER

Параметр	Описание
Имя_триггера	Задает имя триггера
Таблица | обзор	Имя таблицы или обзора, для которх создается триггер
ACTIVE | INACTIVE	Необязательная конструкция. определяет активность триггера. ACTIVE - триггер включен, INACTIVE – триггер отключен.
BEFORE | AFTER	Обязательный. Определяет, когда включается триггер. BEFORE – перед операцией, выполняемой над таблицей, AFTER – после операции, выполняемой над таблицей.
DELETE | INSERT | UPDATE	Указывает, при выполнении какой именно операции, будет включаться триггер.
POSITION number	Задает порядок, в котором будут выполняться триггеры (с одной таблицей и с одними и теми же включениями может быть связано несколько триггеров). Number должен быть целым числом между 0 и 32767 включительно. включение триггеров происходит в порядке возрастания номеров. Если триггеры имеют один и тот же номер, то они будут вклчаться в алфавитном порядке их имен.
тело триггера	Состоит из двух частей: 1) DECLARE VARIABLE <имя переменной> <тип_данных>; - объявляет локальные переменные, используемые только в триггере. 2) непосредственно тело триггера как последовательный набор операций, заключенный в операторные скобки

Рассмотрим примеры.

Пример 1. Триггер для контроля добавления данных в таблицу студент.

CREATE TRIGGER I_1

FOR STUDENT

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.SURNAME IS NULL) THEN EXCEPTION NO_USER;

IF (NEW.NAME IS NULL) THEN NEW.NAME = "ИМЯ";

END

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

CREATE TRIGGER I_2

FOR STUDENT

ACTIVE BEFORE INSERT POSITION 1

AS

BEGIN

IF (NEW.UNIV_ID >0) THEN

IF (NOT EXISTS (SELECT * FROM UNIVERSITY

WHERE (UNIV_ID = NEW.UNIV_ID))) THEN EXCEPTION NO_UNIV;

END

Лекция 3

Триггеры и хранимые процедуры

Триггеры и их назначение

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

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

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

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

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

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

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

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

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

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

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