Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 7916 / 387 | Оценка: 3.85 / 3.50 | Длительность: 22:03:00
Лекция 7:

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

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

Для более подробного обсуждения механизма триггеров в 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;
Пример 17.10.

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

EMP_DISMISSED:
EMP_NO : EMP_NO
EMP_NAME : VARCHAR
DEPT_NO : DEPT_NO

Определение соответствующего триггера могло бы выглядеть следующим образом (пример 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;
Пример 17.11.
Александра Каева
Александра Каева
Антон Честиков
Антон Честиков

Не вижу список тем из плана занятий. Проблема известна?

Семен Дядькин
Семен Дядькин
Беларусь, Минск, БГУ, 2003
Светлана Кудинова
Светлана Кудинова
Россия