2008 г.

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

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

21.4.3. Выполнение триггеров

При выполнении каждого триггера система устанавливает контекст выполнения триггера. Выполнение любого оператора SQL, обновляющего базовую таблицу базы данных, может привести к срабатыванию одного или нескольких триггеров, а выполнение операторов SQL, содержащихся в триггерах, может привести к обновлению других базовых таблиц. Эти «внутритриггерные» (инициируемые) операторы выполняются в контексте текущего триггера, но их выполнение может привести к срабатыванию других триггеров. Для каждого из «вторичных» триггеров образуется собственный контекст выполнения, позволяющий определить их действия точно и независимо от действий первого набора триггеров. Выполнение вторичных триггеров может привести к срабатыванию «третичных» триггеров и т.д. – допускается произвольная глубина вложенности. Для каждого триггера на каждом уровне образуется собственный контекст.

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

Обсудим понятие контекста триггера немного более подробно. Предположим, что в нашей базе данных EMP-DEPT-PRO должно поддерживаться правило, в соответствии с которым каждый служащий, становящийся руководителем проекта, автоматически получает прибавку к заработной плате в 10 000 руб. (Для простоты будем считать, что снятие служащего с должности руководителя проекта не приводит к автоматическому изменению его зарплаты и что для каждого служащего, являющегося руководителем проекта, определен номер отдела, в котором он работает.) Тогда мы могли бы определить триггер CHANGE_MNG_NO следующим образом:

CREATE TRIGGER CHANGE_MNG_NO AFTER UPDATE OF PRO_MNG ON PRO
   FOR EACH ROW
      UPDATE EMP SET EMP_SAL = EMP_SAL + 10000.00
         WHERE EMP_NO = PRO_MNG;

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

CREATE TRIGGER DEPT_CORRECTION_1 
      AFTER UPDATE OF EMP_SAL ON EMP
   REFERENCING OLD ROW AS OLD_EMP NEW ROW AS NEW_EMP
      FOR EACH ROW
         UPDATE DEPT SET
            DEPT_TOTAL_SAL =
            DEPT_TOTAL_SAL + NEW_EMP.EMP_SAL – 
               OLD_EMP.EMP_SAL
               WHERE EMP.DEPT_NO = DEPT.DEPT_NO;

Пусть теперь выполняется операция

UPDATE PRO SET PRO_MNG = 4455
   WHERE PRO_NO = 554;

Сразу после выполнения этой операции сработает триггер CHANGE_MNG_NO. Этот триггер будет выполняться в контексте, который мы для удобства назовем контекстом CMN. Заметим, что исходный оператор модификации в действительности изменяет только одну строку таблицы PRO, но триггеру CHANGE_MNG_NO это неизвестно, и он будет работать так, как если бы изменялось произвольное число строк таблицы PRO.

Выполнение операции модификации таблицы EMP приведет к срабатыванию триггера DEPT_CORRECTION_1. В этот момент контекст CMN будет «упрятан в стек», образуется и станет активным контекст следующего триггера – контекст DR1. После завершения выполнения этого триггера контекст DR1 больше не требуется, и он ликвидируется, а из стека восстанавливается контекст CMN, в котором и будет завершено выполнение триггера CHANGE_MNG_NO.

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

триггерное событие – INSERT, UPDATE или DELETE;
имя предметной таблицы триггера;
имена столбцов предметной таблицы, специфицированных в определении триггера (только для триггеров по UPDATE);
набор переходов (представление всех строк, вставляемых в предметную таблицу, модифицируемых в ней или удаляемых из нее), список всех триггеров уровня STATEMENT, уже выполненных в некотором (не обязательно активном) контексте выполнения, и список всех триггеров уровня ROW, уже выполненных в некотором (не обязательно активном) контексте выполнения, и строк, над которыми эти триггеры выполнялись.

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

При создании контекста выполнения триггера его набор изменений состояния изначально пуст. В набор изменений состояния добавляется каждое встречающееся «новое» изменение состояния, в котором не дублируются триггерное событие существующего изменения состояния, имя предметной таблицы и имена столбцов предметной таблицы. Набор переходов каждого изменения состояния изначально пуст, и переходы добавляются при каждом обновлении предметной таблицы, ассоциированной с изменением состояния (включая обновления, производимые ссылочными действиями).

Возможности использования старых и новых значений

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

Сначала немного поговорим о синтаксисе. Итак, в определении триггера может присутствовать раздел REFERENCING old_or_new_values_alias_list, причем список определений псевдонимов может включать следующие элементы:

OLD [ ROW ] [ AS ] correlation_name
NEW [ ROW ] [ AS ] correlation_name
OLD TABLE [ AS ] identifier
NEW TABLE [ AS ] identifier

Каждая из этих конструкций может входить в список определений псевдонимов не более одного раза, и спецификации OLD ROW и NEW ROW могут присутствовать только в определении триггеров уровня ROW. Определяемые корреляционные имена и псевдонимы можно использовать внутри триггера для ссылок на значения предметной таблицы. Если определяется корреляционное имя для новых значений (NEW ROW) или псевдоним для нового содержимого таблицы (NEW TABLE), то эти имена можно использовать для ссылок на значения, которые будут существовать в предметной таблице после выполнения операций INSERT или UPDATE. Если же определяется корреляционное имя для старых значений (OLD ROW) или псевдоним для старого содержимого таблицы (OLD TABLE), то данные имена можно использовать для ссылок на значения, которые существовали в предметной таблице до выполнения операций UPDATE или DELETE. Конечно, нельзя использовать NEW ROW или NEW TABLE в триггерах DELETE, поскольку никакие новые значения не создаются. Аналогично, нельзя использовать OLD ROW или OLD TABLE в триггерах INSERT, поскольку никакие старые значения не существовали.

Таблицы, на которые указывают корреляционные имена или псевдонимы, называются переходными.Эти таблицы не сохраняются в базе данных долговременно; они создаются и уничтожаются динамически, по мере надобности в контексте выполнения триггера. В триггерах уровня ROW можно использовать корреляционное имя, определенное в конструкции OLD ROW, для ссылки на значения строки, удаляемой или модифицируемой инициирующим оператором, в том виде, в котором данная строка существовала в предметной таблице до того, как была удалена или модифицирована при выполнении инициирующего оператора. В триггерах этого уровня можно также использовать псевдоним, определенный в конструкции OLD TABLE, для ссылки на любое значение переходной таблицы в том виде, в котором она находилась до удаления или модификации очередной строки при выполнении инициирующего оператора. Аналогично обстоят дела с использованием корреляционных имен и псевдонимов, определенных в конструкциях NEW ROW и NEW TABLE.

Для триггеров категории BEFORE имеется существенное ограничение: в них не разрешается использовать конструкции OLD TABLE и NEW TABLE, а внутритриггерный SQL-оператор не может производить какие-либо изменения в базе данных. Основанием для такого ограничения является то, что на переходные таблицы, порождаемые OLD TABLE и NEW TABLE, могут существенно влиять ссылочные действия, которые активизируются в результате изменений базы данных при выполнении внутритриггерного SQL-оператора. Поэтому значения строк в таких таблицах могут оказаться нестабильными и недостаточно предсказуемыми, если триггер срабатывает раньше действия триггерного оператора SQL.

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

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

Решение, принятое в SQL, является предельно простым, хотя и несколько странным. При определении каждого триггера фиксируется временная метка выполнения оператора CREATE TRIGGER, и все триггеры, ассоциированные с одной предметной таблицей, относящиеся к одной и той же категории (BEFORE или AFTER) и срабатывающие по одному и тому же событию, упорядочиваются в соответствии со своими временными метками. Тогда при возникновении условия срабатывания всех триггеров одной группы сначала выполняется первый триггер, затем второй и т.д. В стандарте не специфицируется точность временной метки, связываемой с триггером, и если в одной группе обнаруживаются два или более триггеров с неразличимыми временными метками, то порядок их выполнения должен определяться в реализации.

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

И еще одно интересное свойство триггеров в SQL:1999. Как уже говорилось ранее в этом разделе, каждый инициируемый SQL-оператор должен являться атомарным, т. е. если его выполнение завершается неуспешно, то в базе данных не должно остаться никаких следов подобного выполнения. Но в стандарте говорится больше: неуспешное выполнение хотя бы одного триггера из группы с одинаковым условием срабатывания должно приводить к отмене результатов выполнения инициируемых SQL-операторов всех триггеров этой группы, а также к отмене результатов выполнения самого инициирующего SQL-оператора¹⁶⁷⁾.

21.4.4. Триггеры и ссылочные действия

В подразделе 16.2.1 Определение базовой таблицы лекции 16 мы достаточно подробно обсуждали механизм определения ссылочных действий, служащий для автоматической поддержки ссылочной целостности. Напомним, что ссылочные действия автоматически модифицируют значения внешнего ключа соответствующей таблицы при удалении или модификации строк таблицы, на которую указывают ссылки.

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

Однако даже в тех СУБД, где не смешиваются механизмы ссылочных действий и триггеров, неминуемо возникает взаимосвязь между ссылочными действиями, изменяющими некоторую таблицу, и триггерами, которые определены в этой таблице или также изменяют ее. В SQL:1999 эта взаимосвязь немного упрощается за счет того, что контроль всех ограничений целостности (включая ссылочные ограничения) и выполнение всех ссылочных действий должны производиться до срабатывания триггеров категории AFTER. Если выполняется некоторая операция обновления таблицы T, то после ее выполнения и срабатывания всех ссылочных действий инициируются все триггеры, ассоциированные с таблицей T и видом произведенной операции, а также соответствующие триггеры, ассоциированные с любой таблицей, которая затрагивалась ссылочным действием, если в этой таблице была изменена хотя бы одна строка. Конечно, срабатывание триггера может привести к новым ссылочным действиям, которые повлекут за собой срабатывание других триггеров ит.д.¹⁶⁸⁾

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

21.5. Заключение

В этой лекции мы обсудили важные аспекты языка SQL, относящиеся к механизмам обновления данных. В разделе 21.2. Базовые средства манипулирования данными были рассмотрены операторы прямого SQL, предназначенные для вставки, модификации и удаления данных из существующих таблиц. Операторы UPDATE и DELETE этой категории иногда называют поисковыми, поскольку в них включаются условия на строки таблицы, которые должны быть модифицированы или удалены. В языке SQL определены так-же позиционные операторы модификации и удаления строк, а также динамические позиционные варианты данных операторов, но для их обсуждения требуется общее рассмотрение встраиваемого и динамического SQL, что выходит за рамки данного курса. На мой взгляд, поисковые версии операторов модификации и удаления хорошо характеризуют соответствующие возможности языка SQL. Кроме того, оператор INSERT, представленный в этой лекции, специфицирован в языке SQL только в таком варианте.

Раздел 21.3. Представления, над которыми возможны операции обновления посвящен обсуждению возможностей языка SQL, связанных с применимостью операций обновления базы данных через виртуальные таблицы, в том числе через представления. Мы рассмотрели ограничения языка SQL/92, накладываемые на виртуальные таблицы, к которым применимы операции обновления. Отмечалось, что эти ограничения являются достаточными, но не необходимыми для применения операций обновления. Был описан подход стандарта SQL:1999, где предлагаются рекомендации, но не требования, которых следует придерживаться реализациям SQL, чтобы соответствовать стандарту.

Наконец, в разделе 21.4. Операции обновления баз данных и механизм триггеров рассматривался механизм триггеров. В первом подразделе упоминались основные понятия триггеров, которые были введены при выполнении проекта System R. Далее приводились основные синтаксические конструкции, предназначенные для определения триггеров, а также была описана их базовая семантика. В следующем подразделе обсуждались принципы выполнения триггеров, заложенные в стандарт SQL:1999. Наконец, в заключение раздела были рассмотрены имеющиеся взаимосвязи между ссылочными действиями и триггерами.

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

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

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

168 Здесь мы опять честно пересказали стандарт SQL:1999. И снова предложенное решение выглядит простым, но не убедительным.

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