Средства манипулирования данными

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

Для более подробного обсуждения механизма триггеров в SQL:1999 необходимо ввести набор синтаксических правил:

trigger_definition ::=
   CREATE TRIGGER trigger_name
   { BEFORE | AFTER }
   { INSERT | DELETE | UPDATE [ OF column_commalist ] }
   ON table_name [ REFERENCING
old_or_new_values_alias_list ]
   triggered_action
triggered_action ::=
   [ FOR EACH { ROW | STATEMENT } ]
   [ WHEN left_paren conditional_expression right_paren ]
   triggered_SQL_statement
triggered_SQL_statement ::= SQL_procedure_statement
   | BEGIN ATOMIC
      SQL_procedure_statement_semicolonlist
     END
old_or_new_values_alias ::= OLD [ ROW ] [ AS ] correlation_name
   | NEW [ ROW ] [ AS ] correlation_name
   | OLD TABLE [ AS ] identifier
   | NEW TABLE [ AS ] identifier

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

DROP TRIGGER trigger_name.

(Конструкция ALTER TRIGGER в языке SQL не поддерживается.)

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

Триггеры BEFORE и AFTER

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

Триггеры INSERT, UPDATE и DELETE

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

Заметим, что в стандарте SQL:1999 отсутствует возможность определения триггеров, для которых событием было бы выполнение операции выборки из предметной таблицы. Разработчики стандарта сочли, что область применения триггеров такого рода чересчур узка (трудно придумать какое-либо применение, кроме как для журнализации и аудита).

Триггеры ROW и STATEMENT

Если в определении триггера присутствует конструкция FOR EACH ROW , то триггер будет вызываться для каждой строки предметной таблицы, обновляемой инициирующим SQL-оператором. Если же задано FOR EACH STATEMENT (или явная спецификация FOR EACH отсутствует), то триггер сработает один раз на всем протяжении процесса выполнения инициирующего SQL-оператора.

Раздел WHEN

Включение в определение триггера раздела WHEN с соответствующим условным выражением позволяет более точно специфицировать условие применимости триггера. Вычисление условного выражения производится над строками предметной таблицы, и триггер срабатывает только в том случае, когда значением условного выражения является true. Понятно, что виды и интерпретация логических выражений, допускаемых в разделе WHEN , различаются у триггеров с FOR EACH ROW и у триггеров с FOR EACH STATEMENT . В первом случае условное выражение вычисляется для одной строки, которая должна быть обновлена инициирующим SQL-оператором. Во втором - условное выражение вычисляется для всей предметной таблицы целиком и, по всей видимости, должно базироваться на "кванторных" предикатах. Следует также понимать, что вычисление условия раздела WHEN данного триггера производится только в том случае, если произошло событие срабатывания триггера.

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

Операции, которые должны быть выполнены при срабатывании триггера, специфицируются в синтаксической конструкции triggered_SQL_statement (будем называть ее инициируемым SQL-оператором ).Как видно из синтаксических правил, возможны два вида построения этой конструкции: в виде одиночного оператора SQL и в виде списка операторов со скобками BEGIN ATOMIC и END.

Недоумение читателей может вызвать неуточненная конструкция SQL_procedure_statement. Постараемся объяснить ее происхождение и смысл. Дело в том, что в стандарте SQL:1999 определено процедурное расширение SQL, называемое SQL/PSM (от Persistent Stored Modules ). Это достаточно большой язык, который мы не будем подробно рассматривать в этом курсе лекций^{12Для читателей, которые имеют хотя бы минимальный опыт работы с продуктами компании Oracle, заметим, что во многих своих чертах SQL/PSM напоминает PL/SQL. Одной из причин, на основании которых мы отказались от описания SQL/PSM в этой книге, является то, что до сих пор (первый вариант стандарта SQL/PSM был опубликован в 1996 г.) нет ни одной реализации SQL, в которой этот стандарт был бы реализован полностью (точнее, ни одна такая реализация не известна автору).}. Тем не менее для понимания синтаксиса определения триггеров необходимо отметить, что: (a) SQL/PSM включает основные операторы SQL, связанные с обновлением данных; (b) язык является вычислительно полным, т.е. включает развитые средства вычислений; (c) в языке содержатся средства определения и вызова функций и процедур^{13Во многом на этих возможностях основываются механизмы SQL:1999, предназначенные для определения на уровне пользователя новых типов данных и их операций. Эта тематика также выходит за пределы данного курса (хотя мы немного затронем соответствующие вопросы в последней лекции этого курса).} и (d) SQL/PSM содержит стандартный комплект управляющих конструкций - циклы, ветвления разных типов и т. д. Тем самым, SQL_procedure_statement - это любая процедура, определенная на языке SQL/PSM ^{14На самом деле, для написания процедур, функций и методов допускается использование не только языка SQL/PSM, но и традиционных языков программирования, для которых в стандарте определены правила связывания с SQL. В последней лекции курса мы немного затронем и эту тему.} В частности, эта процедура может представлять собой оператор SQL обновления базы данных.

Обсудим теперь, откуда возникает потребность в составном инициируемом SQL-операторе. Дело в том, что на практике при определении триггеров в качестве SQL_procedure_statement чаще всего используются операторы SQL обновления базы данных. Иногда (и мы покажем это на примере) для корректного определения функциональности триггера одного оператора не хватает, а в SQL отсутствует возможность определения составных операторов. Поэтому допускается использование средств определения составных операторов, присутствующих в SQL/PSM ( BEGIN ATOMIC и END ).

Для иллюстрации случая, когда при определении триггера достаточно специфицировать один оператор SQL, приведем пример определения триггера, условием срабатывания которого является выполнение операции вставки новой строки в таблицу EMP (прием на работу нового служащего). Если значение столбца DEPT_NO в очередной вставляемой строке отлично от NULL, то триггер должным образом модифицирует значения столбцов DEPT_EMP_NO и DEPT_TOTAL_SAL строки таблицы DEPT со значением столбца DEPT_NO, которое соответствует номеру отдела нового служащего (пример 17.10):

CREATE TRIGGER DEPT_CORRECTION AFTER INSERT ON EMP
   FOR EACH ROW
   WHEN (EMP.DEPT_NO IS NOT NULL)
        UPDATE DEPT SET
        DEPT_EMP_NO = DEPT_EMP_NO + 1,
        DEPT_TOTAL_SAL = DEPT_TOTAL_SAL + EMP_SAL
        WHERE DEPT.DEPT_NO = EMP.DEPT_NO;

Теперь предположим, что при увольнении служащего (удалении строки из таблицы EMP ) мы хотим не только должным образом модифицировать таблицу DEPT, но и сохранять (с целью аудита) данные об уволенном служащем в таблице EMP_DISMISSED ^{15Для упрощения будем считать, что идентификаторы уволенных служащих не используются повторно.}:

Определение соответствующего триггера могло бы выглядеть следующим образом (пример 17.11):

CREATE TRIGGER EMP_DISMISSION AFTER DELETE ON EMP
   FOR EACH ROW
      BEGIN ATOMIC
         INSERT INTO EMP_DISMISSED
            ROW (EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO);
      UPDATE DEPT SET
            DEPT_EMP_NO = DEPT_EMP_NO - 1,
            DEPT_TOTAL_SAL = DEPT_TOTAL_SAL - EMP_SAL
         WHERE DEPT.DEPT_NO = EMP.DEPT_NO
   END;