Особенности разработки триггеров и хранимых процедур в СУБД

DELETE FROM emp;

INSERT INTO emp VALUES ( . . . );

INSERT INTO emp SELECT . . . FROM . . . ;

UPDATE emp SET . . . ;

Список столбцов для UPDATE

Если предложение триггера специфицирует UPDATE, то в эту спецификацию может быть включен необязательный список столбцов. Если включить список столбцов, то данный триггер возбуждается по предложению UPDATE лишь тогда, когда это предложение обновляет один из перечисленных столбцов. Если вы опускаете список столбцов, то триггер возбуждается при обновлении любого столбца ассоциированной таблицы. Список столбцов не может быть специфицирован для предложений триггера INSERT или DELETE.

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

. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .

Опция FOR EACH ROW

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

Фраза WHEN

В определение триггера строки может быть включено необязательное ограничение триггера, путем специфицирования булевского выражения SQL в фразе WHEN (фраза WHEN не может быть включена в определение триггера предложения). Выражение в фразе WHEN, если эта фраза присутствует, вычисляется для каждой строки, затрагиваемой триггером. Если результат выражения дает TRUE для строки, то тело триггера исполняется для этой строки. Однако, если это выражение вычисляется для строки как FALSE или NULL, то тело триггера не исполняется для этой строки. Вычисление условия фразы WHEN не влияет на исполнение самого предложения триггера (т.е. предложение триггера НЕ откатывается, если выражение в фразе WHERE вычисляется как FALSE).

Например, для триггера DUMMY, тело триггера не исполняется, если новое значение столбца EMPNO нулевое, отрицательное или NULL. В более реалистичных примерах вы могли бы проверять, скажем, чтобы одно значение столбца было меньше другого. Выражение в фразе WHEN для триггера строки может включать корреляционные имена, которые объясняются ниже. выражение в фразе WHEN должно быть выражением SQL (но не выражением PL/SQL), и не может включать подзапрос.

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

Тело триггера - это блок PL/SQL, который может содержать предложения SQL и PL/SQL. Эти предложения исполняются тогда, когда выдано предложение триггера, и ограничение триггера (если оно есть) вычислено как TRUE. Для триггеров строк тело триггера имеет некоторые специальные конструкты, которые могут быть включены в код этого блока PL/SQL: корреляционные имена, опцию REFERENCING, а также условные предикаты INSERTING, DELETING и UPDATING.

Доступ к значениям столбцов в триггерах строки

Внутри тела триггера строк, код PL/SQL и предложения SQL имеют доступ как к старым, так и к новым значениям столбцов текущей строки, затрагиваемой предложением триггера. Для каждого столбца модифицируемой таблицы определены два КОРРЕЛЯЦИОННЫХ ИМЕНИ: одно для старого (old), другое - для нового значения столбца (new). В зависимости от типа предложения триггера, то или иное корреляционное имя может быть лишено смысла.

• Триггер, возбужденный предложением INSERT, имеет осмысленный доступ лишь к новым значениям столбцов. Поскольку строка создается предложением INSERT, старые значения столбцов пусты (NULL).
• Триггер, возбужденный  предложением UPDATE, имеет доступ как к старым, так и к новым значениям столбцов для обоих возможных типов триггера (BEFORE или AFTER).
• Триггер, возбужденный предложением DELETE, имеет осмысленный доступ лишь к старым значениям столбцов. Поскольку строка перестает существовать после ее удаления, новые значения столбцов пусты (NULL). Новые значения столбцов адресуются квалификатором NEW перед именем столбца, старые - квалификатором OLD. Например, если предложение триггера ассоциировано с таблицей EMP, содержащей столбцы SAL, COMM и т.д., то вы можете включить в тело триггера предложения, подобные следующим:

 IF :new.sal > 10000 . . .

IF :new.sal < :old.sal . . .

Старые и новые значения доступны как в триггерах BEFORE, так и в триггерах AFTER. Назначать новое значение столбца можно в триггере строк BEFORE, но не в триггере строк AFTER (потому что предложение триггера уже выполнено, прежде чем триггер AFTER получает управление). Если триггер строк BEFORE изменяет NEW для столбца, то триггер AFTER, возбужденный тем же самым предложением, видит значение, которое было назначено триггером BEFORE.

Корреляционные имена могут также использоваться в булевском выражении фразы WHEN. Заметьте, что перед квалификаторами OLD и NEW должно кодироваться двоеточие, когда они используются в теле триггера, но двоеточие не допускается, когда эти квалификаторы используются в фразе WHEN или опции REFERENCING.

Опция REFERENCING

Опция REFERENCING может специфицироваться в теле триггера строк для того, чтобы избежать конфликтов между корреляционными именами и именами таблиц, в случае, если таблица имеет имя "OLD" или "NEW". Поскольку такая ситуация  редка, эта опция почти никогда не применяется. Например, предположим, что у вас есть таблица с именем NEW, содержащая столбцы FIELD1 (числовой) и FIELD2 (символьный).

Следующее предложение CREATE TRIGGER показывает триггер, ассоциированный с таблицей NEW, который использует опцию REFERENCING, чтобы избежать конфликтов между корреляционными именами и именем таблицы:

CREATE TRIGGER dummy

BEFORE UPDATE ON new

REFERENCING new AS newest

FOR EACH ROW

BEGIN

:newest.field2 := TO_CHAR (:newest.field1);

END;

Заметим, как квалификатор NEW переименован в NEWEST с помощью опции REFERENCING, а затем использован в теле триггера.

Условные предикаты

Если триггер может быть возбужден более чем одним типом предложения DML (например, "INSERT OR DELETE OR UPDATE OF emp"), в теле триггера можно использовать условные предикаты INSERTING, DELETING и UPDATING, для того чтобы выполнять различные участки кода в зависимости от типа предложения, возбудившего триггер. Предположим, что предложение триггера определено следующим образом:

INSERT OR UPDATE ON emp

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

IF INSERTING THEN . . . END IF;

IF UPDATING THEN . . . END IF;

Первое условие будет вычисляться как TRUE  лишь в тех случаях, когда триггер был возбужден предложением INSERT; второе условие будет вычисляться как TRUE лишь в тех случаях, когда триггер был возбужден предложением UPDATE. Кроме того, в триггере UPDATE условный предикат UPDATING можно специфицировать перед именем столбца, чтобы определять, обновляется ли этот столбец текущим предложением, возбудившим триггер. Например, предположим, что триггер определен следующим образом:

CREATE TRIGGER . . .

. . . UPDATE OF sal, comm ON emp . . .

BEGIN

. . . IF UPDATING ('SAL') THEN . . . END IF;

END;

Код в фразе THEN выполняется лишь в том случае, если предложение UPDATE, возбудившее триггер, обновляет столбец SAL. Например, предложение возбудит показанный выше триггер и заставит условный предикат вычислиться как TRUE:

UPDATE emp SET sal = sal + 100;

Условия ошибок и исключения в теле триггера

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

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

Ограничения на создание триггеров

Следующие секции обсуждают ограничения, которые налагаются на триггеры.

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

BEFORE UPDATE строк AFTER UPDATE строк

BEFORE DELETE строк AFTER DELETE строк

BEFORE INSERT предложения AFTER INSERT предложения

BEFORE INSERT строк AFTER INSERT строк

BEFORE UPDATE предложения AFTER UPDATE предложения

BEFORE DELETE предложения AFTER DELETE предложения

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

Замечание: Каждая таблица может иметь до четырех триггеров UPDATE (BEFORE/AFTER, предложения/строки), независимо от того, возбуждаются ли эти триггеры при обновлении специфических столбцов.

Допустимые предложения SQL в теле триггера. Тело триггера может любые  предложения DML, включая предложения SELECT (только SELECT ...  INTO или предложения SELECT в определениях курсоров), INSERT, UPDATE и DELETE; в теле триггера не допускаются предложения DDL. Нельзя также управлять транзакциями в контексте триггера. Поэтому внутри контекста тела триггера не допускаются следующие предложения: ROLLBACK, COMMIT и SAVEPOINT.

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

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

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

• Предложение SQL внутри триггера может вставлять данные в столбец типа данных LONG или LONG RAW.
• Если данные из столбца LONG или LONG RAW могут быть преобразованы в ограниченный тип данных (такой как CHAR или VARCHAR2), то к такому столбцу LONG или LONG RAW можно обращаться в предложении SQL внутри триггера.

Заметим, что максимальная длина для этих типов данных равна 32K.

• Нельзя объявлять переменные с типом данных LONG или LONG RAW.
• Нельзя использовать квалификаторы :NEW и :OLD со столбцами LONG и LONG RAW.

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

Ссылки на пакетированные переменные. Если предложение UPDATE или DELETE обнаруживает конфликт с конкурирующей операцией UPDATE, то ORACLE выполняет прозрачный откат к точке сохранения и повторно запускает обновление. Это может повториться, пока предложение не выполнится успешно. Каждый раз, когда предложение перезапускается, ассоциированный триггер предложения снова возбуждается. Откат к точке сохранения не отменяет изменений в любых пакетированных переменных, адресуемых в этом триггере. Соответствующий  пакет должен включать переменную-счетчик, чтобы распознавать такую ситуацию.

 

Порядок вычисления строк. Реляционная база данных не гарантирует определенности порядка строк, обрабатываемых предложением SQL. Поэтому не создавайте триггеров, которые от того, в каком порядке будут обрабатываться строки. Например, не назначайте значения глобальной пакетированной переменной в триггере строк, если это текущее значение зависит того, какая строка сейчас обрабатывается триггером строк. Кроме того, если в триггере обновляются глобальные пакетированные переменные, лучше всего инициализировать такие переменные в триггере предложения BEFORE.

Если предложение в теле триггера вызывает возбуждение другого, этот эффект называется КАСКАДОМ ТРИГГЕРОВ. ORACLE позволяет иметь одновременно до 32 каскадно возбужденных триггеров. Однако вы можете по существу ограничить количество каскадных триггеров через параметр инициализации MAX_OPEN_CURSORS; для каждого исполнения триггера должен открываться курсор.

Мутирующие и  ограничивающие таблицы. Мутирующая таблица – это таблица, модифицируемая в данный момент предложением UPDATE, или INSERT, или таблица, которая может потребовать обновления в результате действия декларативного ссылочного ограничения целостности DELETE CASCADE. Ограничивающая таблица - это таблица, которую предложение, возбуждающее триггер или ограничение, должно читать, - либо явно (для предложения SQL), неявно (для декларативного ограничения ссылочной). Мутирующая или ограничивающая таблица является таковой лишь для той сессии, которая выдает предложение, возбуждающее триггер или ограничение.

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

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

Для этого правила существует одно исключение: триггер BEFORE ROW, возбужденный предложением INSERT, вставляющим одиночную строку в таблицу с внешним ключом, может модифицировать любые столбцы первичной таблицы, пока эта модификация не нарушает никаких ограничений целостности.

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

Декларативные ограничения целостности проверяются в различные моменты времени по отношению к триггерам строк; обратитесь к документу ORACLE7 Server Concepts Manual за информацией о том, как взаимодействуют триггеры и ограничения целостности.

 

 

Привилегии, требуемые для создания триггеров

Чтобы создать триггер в  своей схеме, вы должны иметь системную привилегию CREATE TRIGGER, а также одно из:

Владеть таблицей, специфицированной в предложении триггера, или иметь привилегию ALTER для таблицы, специфицированной в предложении триггера, или иметь системную привилегию ALTER ANY TABLE