Дополнительные возможности описания ограничений целостности

ELSE IGNORE

Предложение SIGNAL

Предложение SIGNAL используется для генерации сообщения об ошибке или предупреждения и может быть задано одним из двух способов:

SIGNAL SQLSTATE [ VALUE ] значение_SQLSTATE [ дополнительная_информация ]

или

SIGNALимя_условия [ дополнительная_информация ]

Дополнительная информация представляет собой текст сообщения об ошибке и может быть задана в виде:

SET MESSAGE_TEXT = диагностическое_сообщение

SQLSTATE VALUEзначение_SQLSTATE – задает строковую константу, представляющую значение SQLSTATE. Константа должна иметь длину 5 символов и должна удовлетворять правилам формирования значения SQLSTATE:

• каждый символ строки может быть представлен цифрой (от '0' до '9') или буквой верхнего регистра (от 'A' до 'Z');

• первые два символа SQLSTATE, определяющие класс ошибки, не могут иметь значение '00', так как это значение определяет успешное выполнение предложения.

Если предложение SIGNAL используется в определении триггера или функции, должны выполняться еще и следующие правила:

• первые два символа SQLSTATE не могут иметь значение '01' или '02', так как эти значения не относятся к классу ошибок;

• если класс ошибки в SQLSTATE начинается цифрой от '0' до '6' или буквой от 'A' до 'H', тогда подкласс ошибки (последние три символа в значении SQLSTATE) должен начинаться буквой от 'I' до 'Z';

• если класс ошибки в SQLSTATE начинается цифрой '7', '8', '9', или буквой от 'I' до 'Z', тогда подкласс ошибки может использовать любые цифры от '0' до '9' или буквы от 'A' до 'Z'.

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

Вместо конкретного значения SQLSTATE может быть указано имя переменной, имеющей тип CHAR(5) и значение, удовлетворяющее описанным выше правилам.

Имя условия – указывает имя условия, объявленного в том же составном предложении, в котором используется предложение SIGNAL.

SET MESSAGE_TEXT =диагностическое_сообщение – указывает строку, описывающую ошибку или предупреждение.

Если выполняется предложение SIGNAL, возвращаемое значение SQLCODE определяется в зависимости от указанного в SIGNAL значения SQLSTATE: если класс ошибки, указанный в SQLSTATE, имеет значение ’01’ или ’02’, возвращается предупреждение или состояние «не найдено», и значение SQLCODE устанавливается равным +438. В остальных случаях, возвращается состояние ошибки, и значение SQLCODE устанавливается равным -438.

Пример:

SIGNAL SQLSTATE’75002’ SET MESSAGE_TEXT =’Customer number is not known’;

Понятие триггера, назначение триггера

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

Каждое предложение SQL информирует о том, как оно завершилось – успешно или нет. Для этой цели используются специальные переменные SQLCODE (типа INTEGER) и SQLSTATE (типа CHAR(5)), значения которых устанавливаются менеджером базы данных после выполнения каждого предложения SQL.

Приняты следующие соглашения:

• если SQLCODE = 0 и переменная SQLWARN0 не установлена, предложение завершилось успешно;

• если SQLCODE = 100, обнаружена ситуация “нет данных” (например, предложение SELECT вернуло пустой результат);

• если SQLCODE > 0 и не равно 100, предложение завершилось с некоторыми предупреждениями;

• если SQLCODE = 0 и SQLWARN0 = 'W', предложение выполнилось успешно, но установлены некоторые индикаторы предупреждений;

• если SQLCODE < 0, выполнение предложения завершилось с ошибками.

Значения SQLCODE, отличные от 0 и 100, зависят от продукта.

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

• значение SQLSTATE ‘00ххх’ соответствует выполнению предложения без ошибок:

• значение SQLSTATE ‘01ххх’ отмечает наличие предупреждений;

• значение SQLSTATE ‘02000’ соответствует ситуации "нет данных";

• другие значения SQLSTATE соответствуют выполнению предложений SQL с ошибками.

Типы триггеров с точки зрения функциональности

Триггеры обычно используются в следующих случаях:

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

• для обеспечения бизнес-правил, например, при назначении сотрудника на должность его оклад должен соответствовать некоторым заданным в таблице окладов ограничениям; или оклад сотрудника не может быть увеличен более чем на 10 процентов за один раз;

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

• вызова функций для выполнения специальных задач;

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

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

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

• предложение SIGNAL SQLSTATE

• встроенная функция raise_error()

• вызов хранимой процедуры или UDF, возвращающей некоторое значение SQLSTATE.

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

3. Чтение из других таблиц для целей взаимных ссылок.

4. Запись в другие таблицы в целях контроля.

5. Поддержка уведомлений (например, с помощью сообщений электронной почты).

Общие правила определения триггеров в SQL

Прежде чем рассмотреть правила создания триггера, введем некоторые определения.

Рабочий, или целевой объект – объект (таблица или представление; мы будем пока в основном использовать таблицы) базы данных, с которым должен взаимодействовать триггер.

Событие, или контролируемая операция триггера – операция SQL (вставка, удаление или изменение), которая вызывает активацию (срабатывание) триггера каждый раз, когда она выполняется с рабочим объектом. События возникают, когда выполняются следующие операции:

вставка – вставляется новая строка с помощью предложения SQL INSERT;

удаление – выполняется предложение SQL DELETE или действуют правила ссылочной целостности ON DELETE CASCADE;

изменение – выполняется предложение SQL UPDATE или действуют правила ссылочной целостности ON DELETE SET NULL.

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

Время активации триггера – характеристика триггера, указывающая, когда должен быть активирован триггер: до (триггер before), после (триггер after) возникновения события триггера или вместо (instead of) события. Триггер before будет активирован до наступления события триггера; следовательно, он увидит новые значения данных до того, как они будут введены в рабочую таблицу. Триггер after будет активирован после наступления события триггера; следовательно, он может видеть лишь те значения, которые были уже введены в рабочую таблицу. Триггеры before и after могут быть использованы только с таблицами. Особое место занимает триггер instead of, который может быть использован только с представлениями.

Набор изменяемых строк – строки рабочего объекта, для которых осуществляется операция вставки, изменения или удаления.

Кратность триггера – характеристика триггера, которая определяет, сколько раз выполняется действие триггера: один раз для всей операции с объектом или по одному разу для каждой строки, затрагиваемой операцией.

Предложение SQL CREATE TRIGGER определено в стандарте SQL, в соответствии с которым в нем можно выделить две части: заголовок триггера и тело триггера.

Если заголовок триггера представляет собой некоторое описание и, в основном, кодируется везде одинаково, то тело триггера кодируется с помощью предложений расширенного языка SQL, допускающего некоторую алгоритмическую обработку данных. Поэтому в разных СУБД полное предложение CREATE TRIGGER кодируется по-разному.

Предложение CREATE TRIGGER

Так, в DB2 UDB это предложение имеет следующий синтаксис:

CREATE TRIGGER имя_триггера

время_активации_триггера

контролируемая_операция

ONимя_целевого_объекта

набор_изменяемых_строк

кратность_триггера

тело_триггера

Пояснения:

имя_триггера – определяет имя триггера. Имя, включая явное или неявное имя схемы, не должно идентифицировать уже существующий триггер. Имя схемы не может начинаться символами ’SYS’.

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

NO CASCADE BEFORE – указывает, что соответствующее действие триггера должно быть выполнено прежде, чем будет выполнено предложение SQL, приведшее к активации триггера. Оно также указывает, что действие триггера не вызывает активации других триггеров.

AFTER – указывает, что соответствующее действие триггера выполняется после выполнения соответствующих предложений SQL, приведших к активации триггера.

INSTEAD OF– указывает, что соответствующее действие триггера замещает выполнение операций для представлений, соответствующих предложениям SQL, вызвавшим активацию триггера. Для любой операции с представлениями допускается только триггер INSTEAD OF.

Контролируемая операция – указывает, выполнение какого предложения SQL (INSERT,DELETEилиUPDATE) для целевого объекта вызовет активацию триггера:

INSERT – указывает, что действие, определяемое триггером, должно выполняться каждый раз, когда для целевой таблицы или представления выполняется предложение INSERT;

DELETE – указывает, что действие, определяемое триггером, должно выполняться каждый раз, когда для целевой таблицы или представления выполняется предложение DELETE;

UPDATE [ OF имя_колонки , … ] – указывает, что действие, определяемое триггером, должно выполняться каждый раз, когда для целевой таблицы или представления выполняется предложение UPDATE, обновляя указанные явно (OF имя_колонки , … ) или подразумеваемые колонки. Если не обязательный список имен_колонок не указан, подразумевается каждая колонка таблицы, поэтому отсутствующий список имен_колонок предполагает, что триггер будет активироваться при обновлении любой колонки таблицы.

OFимя_колонки,...– каждое указанное имя_колонки должно определять колонку базовой таблицы. Для триггера BEFORE указанное имя колонки не может быть генерируемой колонкой, за исключением колонки identity. Ни одно имя колонки не может появиться в списке имен колонок более одного раза. Триггер активируется только при обновлении колонки, указанной в списке имен колонок.

Конструкция OFимя_колонки,...не может быть указана для триггера INSTEAD OF.

В определении одного триггера может быть указано только одно предложение SQL.

имя_целевого_объекта – задает целевую таблицу в определении триггера BEFORE или AFTER или целевое представление в определении триггера INSTEAD OF. Имя таблицы должно определять базовую таблицу и не может определять таблицу системного каталога, таблицу материализованного запроса или временную таблицу. Имя представления должно определять не типизированное представление и не может задавать представление системного каталога или представление, определенное с опцией WITH CHECK OPTION.

Набор изменяемых строк – определяет, каким образом в теле триггера можно получить значения, которыми оперирует выполняемое предложение SQL. Способ задания набора изменяемых строк зависит от задания кратности триггера.

Кратность триггера задается одним из двух способов:

FOR EACH ROW– указывает, что действие триггера должно выполняться для каждой строки целевой таблицы или целевого представления, на которую воздействует контролируемая операция.

FOR EACH STATEMENT– указывает, что действие триггера должно выполняться только один раз, для всего предложения SQL. Этот тип триггера не может быть указан для триггеров BEFORE или INSTEAD OF. Если указано FOR EACH STATEMENT, триггеры для UPDATE или DELETE активируются, даже если контролируемые операции изменения или удаления не воздействуют ни на одну строку.

Если указано FOR EACH ROW, тогда набор изменяемых строк задается следующим образом:

REFERENCING OLD [ AS ] корреляционное_имя NEW [ AS ] корреляционное_имя

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

OLD ASкорреляционное имя – указывает корреляционное имя, которое идентифицирует состояние строки перед выполнением контролируемой операции;

NEW ASкорреляционное имя – указывает корреляционное имя, которое идентифицирует состояние строки, модифицированное контролируемым предложением SQL и любым предложением SET в триггере BEFORE, которое уже выполнилось.

Следующие правила применяются к конструкции REFERENCING:

• корреляционные имена, указанные в OLD и NEW, не могут совпадать;

• только одна фраза OLD и одна фраза NEW может быть указана в триггере;

• OLD корреляционное_имя может быть указано только в том случае, если контролируемой операцией является DELETE или UPDATE. Если операцией является DELETE, указанное корреляционное_имя определяет значение удаленной строки. Если операцией является UPDATE, указанное корреляционное_имя определяет значение строки перед выполнением операции UPDATE;

• NEW корреляционное_имя может быть указано, только если контролируемой операцией является INSERT или UPDATE. В обоих случаях, значение NEW представляет новое состояние строки, определяемое контролируемой операцией и модифицируемое любым триггером BEFORE, который выполнился к данному моменту времени;

• область видимости каждого корреляционного имени – все действие триггера.

Если же указано FOR EACH STATEMENT, тогда набор изменяемых строк задается следующим образом:

REFERENCING OLD_TABLE [ AS ] идентификатор NEW_TABLE [ AS ] идентификатор

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

OLD_TABLE ASидентификатор – указывает имя временной таблицы, которое определяет множество строк, на которые воздействует контролируемое предложение SQL, до выполнения этого предложения;

NEW_TABLE ASидентификатор – указывает имя временной таблицы, которое определяет множество строк, на которые воздействует контролируемое SQL предложение, модифицированных этим предложением и любым предложением SET в триггере BEFORE, которые уже выполнились.

Следующие правила применяются к конструкции REFERENCING:

• корреляционные имена таблиц, указанные в OLD_TABLE и NEW_TABLE, не могут совпадать;

• только одна фраза OLD_TABLE и одна фраза NEW_TABLE может быть указана в триггере;

• OLD_TABLE идентификатор может быть указано только в том случае, если контролируемой операцией является DELETE или UPDATE. Если операцией является DELETE, указанный идентификатор определяет значения всех удаленных строк. Если операцией является UPDATE, указанный идентификатор определяет значения всех изменяемых строк перед выполнением операции UPDATE;

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

• OLD_TABLE и NEW_TABLE не могут быть определены для триггера BEFORE;

• таблицы передачи не могут модифицироваться;

• область видимости каждого идентификатора – все действие триггера.

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

[ WHEN (условие_поиска)] действие_триггера

WHEN (условие_поиска) – указывает условие, которое может иметь значение истина (true), ложь (false) или не определенное (unknown). Условие поиска дает возможность определить, должно или не должно быть выполнено действие триггера. Соответствующее действие выполняется, только если указанное условие поиска имеет значение истина (true). Если фраза WHEN опущена, соответствующее действие триггера выполняется всегда.

Фраза WHEN не может быть указана для триггера INSTEAD OF.

Действие триггера имеет следующий вид:

[ метка: ] процедурное_предложение_SQL

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

Только предложения FOR, WHILE и динамические составные предложения могут включать метку.

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

• описания переменных (DECLARE),

• установки значения переменных (SET),

• цикла (WHILE и FOR),

• условное (IF),

• SIGNAL,

• ITERATE,

• LEAVE,

• GET DIGNOSTIC,

• формирования запроса (SELECT).

Однако только AFTER и INSTEAD OF триггеры могут содержать предложения SQL UPDATE, DELETE и INSERT.

В большинстве случаев, если любое из предложений, включенных в тело триггера, возвращает отрицательный код завершения, контролируемое предложение SQL, вместе с самим триггером и всеми действиями, определяемыми ссылочными ограничениями, откатывается, и возвращается код завершения SQLCODE -723 (SQLSTATE 09000).

Процедурное предложение SQL может включать предложение SIGNAL SQLSTATE или вызов функции RAISE_ERROR. В этих случаях возвращается значение SQLSTATE, указанное в соответствующих предложениях (SQLCODE -438).