11. Базовые триггеры (назначение, структура, процедура создания).

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

Триггеры – мощный инструмент для обеспечения целостности (правильности и непротиворечивости) хранимых данных. Только с помощью триггеров мы сможем обеспечить «автоматический» ввод в таблицы таких атрибутов, как Пользов и Измен, «автоматическое» наращивание значений Номер и Строка, а также заполнение таблицы Вед_изм по данным удаляемых и изменяемых строк таблицы Ведомость.

Триггер характеризуется следующими классификационными признаками, которые должны быть заданы при его создании:

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

• время активизации - выполнение триггера до или после выполнения операции над таблицей;

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

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

Команда для создания (CREATE) или замены (CREATE OR REPLACE) триггера имеет вид:

CREATE [OR REPLACE] TRIGGER [schema.]trigger

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE [OF column [, column] ...]}

[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...

ON [schema.]table

[ [REFERENCING { OLD [AS] old [NEW [AS] new]

| NEW [AS] new [OLD [AS] old] } ]

[ FOR EACH ROW ]

[WHEN (condition)] ]

pl/sql_block

где

[schema.]trigger — имя триггера, где необязательный параметр schema, идентифицируюет схему, в которой должен быть создан этот триггер;

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

INSERT, DELETE или UPDATE — SQL-предложение, при выполнении которого должен запуститься триггер; с помощью связки OR можно запускать один и тот же триггер при выполнении любых двух или всех трех предложений, связанных с вводом в таблицу новых строк, их удалением и (или) изменением;

[OF column [, column] ...]— может использоваться вместе с предложением UPDATE для указания перечня столбцов, изменение любого из которых должно запустить триггер (по умолчанию триггер будет запускаться при изменении любого столбца таблицы);

BEFORE — триггер должен запускаться перед выполнением соответствующего SQL-предложения, т.е. из тела триггера можно «увидеть» таблицу, в которую еще не введена новая строка или не произведено удаление (изменение) существующей;

AFTER — триггер должен запускаться после выполнения соответствующего SQL-предложения, т.е. из тела триггера можно «увидеть» таблицу, в которую уже введена новая строка или уже произведено удаление (изменение) существующей (если при выполнении тела триггера будет обнаружено, что строка не должна быть введена, удалена или изменена, то таблица будет возвращена в состояние, которое существовало до выполнения триггера);

[ FOR EACH ROW ] — этот параметр устанавливается для триггера строчного типа (который должен запускаться в процессе ввода (удаления, изменения) каждой строки таблицы; при отсутствии FOR EACH ROW создается триггер операторного типа, запускаемый только один раз, независимо от того, сколько строк должно быть введено (удалено, изменено) при выполнении заданного SQL-предложения;

[ [REFERENCING { OLD [AS] old [NEW [AS] new] | NEW [AS] new [OLD [AS] old] } ] — используется для изменения заданных по умолчанию префиксов NEW и OLD, которые указываются перед именами столбцов, новые или старые значения которых должны использоваться в тексте тела строчного триггера; естественно, что при выполнении предложения INSERT во вводимой строке существуют только новые значения (например, NEW.Фамилия при вводе данных в таблицу Фамилии или Студенты), при выполнении предложения DELETE – только старые значения, а при выполнении UPDATE – и те и другие; REFERENCING используется тогда, когда имя столбца должно уточняться именем таблицы, которую назвали NEW или OLD и возникает неоднозначность в описании столбца;

[WHEN (condition)] — используется для отбора тех вводимых, удаляемых или изменяемых строк, которые должны вызвать запуск строчного триггера (по умолчанию строчный триггер запускается при вводе, удалении или изменении каждой строки соответствующей таблицы);

pl/sql_block — тело триггера, содержащее все компоненты, которые допускаются при создании хранимых процедур.

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

CREATE OR REPLACE TRIGGER studins

BEFORE INSERT ON Студенты

FOR EACH ROW

DECLARE

err_fam EXCEPTION; err_im EXCEPTION;

BEGIN

:new.Фамилия := fio(:new.Фамилия); -- фамилия замещается результатом

-- работы функции fio

:new.Имя := fio(:new.Имя); -- имя замещается результатом работы

-- функции fio

IF :new.Фамилия = '0' THEN RAISE err_fam; END IF; -- выход по ошибке

-- при неправильном написании фамилии

IF :new.Имя = '0' THEN RAISE err_im; END IF; -- выход по ошибке

-- при неправильном написании имени

-- Формирование нового номера обучаемого

SELECT MAX(Номер)+1 INTO :new.Номер FROM Студенты;

EXCEPTION -- начало обработчика исключений основной программы

WHEN err_fam THEN RAISE_APPLICATION_ERROR(-20040,

'Фамилия должна состоять только из букв русского алфавита, '||

'пробела, дефиса и начинаться с заглавной буквы !');

WHEN err_im THEN RAISE_APPLICATION_ERROR(-20041,

'Имя должно состоять только из букв русского алфавита, '||

'пробела, дефиса и начинаться с заглавной буквы !');

WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,

'Какая-то другая ошибка');

END studins;

1. /

Создадим еще два триггера, срабатывающие перед удалением и перед изменением строки той же таблицы Студенты:

-- Строчный триггер срабатывающий перед удалением строки из таблицы Студенты

CREATE OR REPLACE TRIGGER studdel

BEFORE DELETE ON Студенты

FOR EACH ROW

DECLARE

test INTEGER; err_studdel EXCEPTION;

BEGIN

-- Проверка существования удаляемого номера студента

SELECT COUNT(Номер) INTO test FROM Студенты WHERE Номер = :old.Номер;

IF test = 0 THEN RAISE err_studdel; END IF;

EXCEPTION

WHEN err_studdel THEN RAISE_APPLICATION_ERROR(-20022,

'Нет студента с указанным номером !');

WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,

'Какая-то другая ошибка');

END studdel;

/

-- Строчный триггер срабатывающий перед изменением строки в таблице Студенты

CREATE OR REPLACE TRIGGER studupd

BEFORE UPDATE ON Студенты

FOR EACH ROW

DECLARE

err_nom EXCEPTION; err_fam EXCEPTION;

err_im EXCEPTION;

BEGIN

IF :new.Номер <> :old.Номер THEN RAISE err_nom; END IF;

:new.Фамилия := fio(:new.Фамилия);

:new.Имя := fio(:new.Имя);

IF :new.Фамилия = '0' THEN RAISE err_fam; END IF;

IF :new.Имя = '0' THEN RAISE err_im; END IF;

EXCEPTION -- начало обработчика исключений основной программы

WHEN err_nom THEN RAISE_APPLICATION_ERROR(-20044,

'Номер нельзя изменять !');

WHEN err_fam THEN RAISE_APPLICATION_ERROR(-20040,

'Фамилия должна состоять только из букв русского алфавита, '||

'пробела, дефиса и начинаться с заглавной буквы !');

WHEN err_im THEN RAISE_APPLICATION_ERROR(-20041,

'Имя должно состоять только из букв русского алфавита, '||

'пробела, дефиса и начинаться с заглавной буквы !');

WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,

'Какая-то другая ошибка');

END studupd;

2. /

Триггеры из базы автошколы: