Базы данных. Вводный курс

Сергей Кузнецов

Назад Содержание Вперёд

21.4. Операции обновления баз данных и механизм триггеров

Термин триггер в контексте реляционных баз данных был введен в обиход участниками проекта System R (лекции 12, 15). В терминологии этого проекта триггером называлась хранимая в базе данных процедура, автоматически вызываемая СУБД при возникновении соответствующих условий.При определении триггера указывались два условия его применимости – общее условие (имя отношения и тип операции манипулирования данными) и конкретное условие (логическое выражение, построенное по правилам, близким к правилам ограничений целостности), а также действие, которое должно быть выполнено над БД при наличии условий применимости.

Конечно, термин триггер в данном контексте является жаргонным. Но, с другой стороны, он достаточно точно соответствует ситуации: для применения процедуры должны быть произведены «возбуждающие» ее действия. Как отмечалось в лекции 15, после завершения проекта System R на протяжении более десяти лет триггеры не поддерживались ни в одной коммерческой SQL-ориентированной СУБД. Но затем практически во всех ведущих СУБД механизм триггеров в том или ином виде был реализован.

В стандарте же языка SQL спецификации триггеров отсутствовали до принятия стандарта SQL:1999. По словам главного редактора стандартов SQL/92 и SQL:1999 Джима Мелтона, эта спецификация была уже полностью готова к моменту принятия SQL/92 и не вошла в текст стандарта только по причине ограниченности его объема. Однако, как мне кажется, этому препятствовали и расхождения в подходах, существовавшие между основными компаниями-производителями СУБД.

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

21.4.1. Понятие триггера в SQL:1999

В языке обеспечиваются возможности определения триггеров, которые вызываются («срабатывают») при вставке одной или нескольких строк в указанную таблицу, при модификации одной или нескольких строк в указанной таблице или при удалении одной или нескольких строк из указанной таблицы. Вообще говоря, триггер может производить любое действие, необходимое для соответствующего приложения. Можно определить триггеры, срабатывающие по одному разу для операций INSERT, UPDATE или DELETE, но существует и возможность определения триггеров, вызываемых при вставке, модификации или удалении каждой отдельной строки. Таблица, с которой связывается определение триггера, называется предметной таблицей (subject table), а оператор SQL, выполнение которого приводит к срабатыванию триггера, мы будем называть инициирующим (triggering SQL statement).

Триггерымогут срабатывать после и до реального выполнения инициирующего оператора SQL. В теле триггера допускается доступ к значениям вставляемых, модифицируемых и удаляемых строк. В случае операции модификации возможен доступ к значениям строк до модификации и к значениям после модификации. В соответствии со стандартом SQL:1999 любой триггер ассоциируется только с одной базовой таблицей. Не допускается определение триггеров над представлениями¹⁶¹⁾.

Можно придумать различные способы полезного применения механизма триггеров, но принято считать, что основными областями использования этого механизма являются следующие.

• Журнализация и аудит. С помощью триггеров можно отслеживать изменения таблиц, для которых требуется поддержка повышенного уровня безопасности. Данные об изменении таблиц могут сохраняться в других таблицах и включать, например, идентификатор пользователя, от имени которого выполнялась операция обновления; временную метку операции обновления; сами обновляемые данные и т. д.
• Согласование и очистка данных. С любым простым оператором SQL, обновляющим некоторую таблицу, можно связать триггеры, производящие соответствующие обновления других таблиц. Например, с операцией вставки новой строки в таблицу EMP (прием на работу нового служащего) можно было связать триггер, модифицирующий значения столбцов DEPT_EMP_NO и DEPT_TOTAL_SAL¹⁶²⁾ строки таблицы DEPT со значением столбца DEPT_NO, которое соответствует номеру отдела нового служащего.
• Операции, не связанные с изменением базы данных. В триггерах могут выполняться не только операции обновления базы данных. Стандарт SQL позволяет определять хранимые процедуры (которые могут вызываться из триггеров), посылающие электронную почту, печатающие документы и т. д.

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

Для более подробного обсуждения механизма триггеров в 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, то число возможных событий, приводящих к срабатыванию триггера, возрастает. Кроме явных операций модификации строк предметной таблицы или удаления из нее строк к срабатыванию триггера могут привести ссылочные действия (см. раздел 16.2. Средства определения, изменения и ликвидации базовых таблиц лекции 16).

Заметим, что в стандарте 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). Это достаточно большой язык, который мы не будем подробно рассматривать в этом курсе лекций¹⁶³⁾. Тем не менее для понимания синтаксиса определения триггеров необходимо отметить, что: (a) SQL/PSM включает основные операторы SQL, связанные с обновлением данных; (b) язык является вычислительно полным, т.е. включает развитые средства вычислений; (c) в языке содержатся средства определения и вызова функций ипроцедур,¹⁶⁴⁾ и (d) SQL/PSM содержит стандартный комплект управляющих конструкций – циклы, ветвления разных типов и т. д. Тем самым, SQL_procedure_statement – это любая процедура, определенная на языке SQL/PSM.¹⁶⁵⁾ В частности, эта процедура может представлять собой оператор 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, которое соответствует номеру отдела нового служащего (пример 21.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¹⁶⁶⁾:

EMP_DISMISSED

EMP_NO : EMP_NO

EMP_NAME : VARCHAR

DEPT_NO : DEPT_NO

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

---

161   Непонятно, откуда происходит это ограничение. Скорее всего, в будущих версиях стандарта оно будет снято.

162   В примерах этой лекции мы будем считать, что в столбце DEPT_TOTAL_SAL таблицы DEPT хранится суммарное значение заработной платы служащих соответствующего отдела.

163   Для читателей, которые имеют хотя бы минимальный опыт работы с продуктами компании Oracle, заметим, что во многих своих чертах SQL/PSM напоминает PL/SQL. Одной из причин, на основании которых мы отказались от описания SQL/PSM в этой книге, является то, что до сих пор (первый вариант стандарта SQL/PSM был опубликован в 1996 г.) нет ни одной реализации SQL, в которой этот стандарт был бы реализован полностью (точнее, ни одна такая реализация не известна автору).

164   Во многом на этих возможностях основываются механизмы SQL:1999, предназначенные для определения на уровне пользователя новых типов данных и их операций. Эта тематика также выходит за пределы данного курса (хотя мы немного затронем соответствующие вопросы в последней лекции этого курса).

165   На самом деле, для написания процедур, функций и методов допускается использование не только языка SQL/PSM, но и традиционных языков программирования, для которых в стандарте определены правила связывания с SQL. В последней лекции курса мы немного затронем и эту тему.

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

Назад Содержание Вперёд