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

Оператор UPDATE для модификации существующих строк в существующих таблицах

Общий синтаксис оператора UPDATE выглядит следующим образом:

UPDATE table_name SET update_assignment_commalist
    WHERE conditional_expression
update_assignment ::= column_name =
    { value_expression | DEFAULT | NULL }

Семантика оператора модификации существующих строк определяется следующим образом:

Приведем примеры операций модификации таблиц.

UPDATE EMP SET DEPT_NO = 632, EMP_SAL = EMP_SAL + 1000.00
   WHERE PRO_NO = 772;

При выполнении данной операции на первом шаге в таблице EMP будут найдены все строки, относящиеся к служащим, которые участвуют в проекте с номером 772. На втором шаге во всех этих строках значение столбца DEPT_NO будет изменено на 632, а к значению столбца EMP_SAL будет прибавлено 1000.00.

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL)
     FROM EMP EMP1
     WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
         + 1000.00, PRO_NO = NULL
     WHERE (SELECT EMP1.EMP_SAL
          FROM EMP EMP1, DEPT
          WHERE EMP.DEPT_NO = DEPT.DEPT_NO
             AND DEPT_MNG = EMP1.EMP_NO) > 30000.00;

Конечно, если вам больше нравится другой стиль, то запрос, фигурирующий в разделе WHERE, можно переформулировать с использованием вложенного подзапроса (пример 17.6a).

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL)
     FROM EMP EMP1
     WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
         + 1000.00, PRO_NO = NULL
     WHERE DEPT.NO IN (SELECT DEPT.DEPT_NO
          FROM EMP, DEPT
          WHERE DEPT_MNG = EMP_NO
          AND EMP_SAL > 30000.00);

Эти примеры позволяют понять, насколько богаты возможности оператора UPDATE . В разделе WHERE может содержаться любое условие, допускаемое в операторе выборки, а в элементах списка раздела SET может присутствовать любой вид value_expression, в том числе любой запрос, вырабатывающий одиночное значение (скалярный подзапрос).

Оператор DELETE для удаления строк в существующих таблицах

Общий синтаксис оператора DELETE выглядит следующим образом:

DELETE FROM table_name
   WHERE conditional_expression

В некотором смысле оператор DELETE является частным случаем оператора UPDATE (или, наоборот, действие оператора UPDATE представляет собой комбинацию действий операторов DELETE и INSERT ).

Семантика оператора модификации существующих строк определяется следующим образом:

С целью иллюстрации приведем два примера операции удаления строк.

DELETE FROM EMP WHERE PRO_NO = 772;

DELETE FROM EMP WHERE EMP_SAL >
     (SELECT EMP1.EMP_SAL
          FROM EMP EMP1, DEPT
          WHERE EMP.DEPT_NO = DEPT.DEPT_NO
             AND DEPT.DEPT.MNG = EMP1.EMP_NO);

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

Представления, над которыми возможны операции обновления

В разделе "Общие синтаксические правила построения скалярных выражений" лекции 13 было введено понятие представления ( VIEW ). Кратко повторим, что представление - это сохраняемое в каталоге базы данных выражение запросов, обладающее собственным именем и, возможно, собственными именами столбцов. Для удобства повторим синтаксические правила определения представления:

create_view ::= CREATE [ RECURSIVE ] VIEW table_name
      [ column_name_comma_list ]
   AS query_expression
   [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

В операциях выборки к любому представлению можно адресоваться таким же образом, как и к любой базовой таблице. Естественно, возникает вопрос: а можно ли использовать имена представлений и в операциях обновления базы данных и если такая возможность допускается, то как это следует понимать?

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

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

Представления, допускающие применение операций обновления, в стандарте SQL/92

Поскольку базовым элементом выражения запросов является спецификация запроса, прежде всего нужно понять, какой класс спецификаций запросов является допускающим операции обновления (термин updatable - обновляемый, используемый в стандарте SQL, кажется не слишком удачным в русском варианте). В стандарте SQL/92 спецификация запроса считалась допускающей операции обновления в том и только в том случае, когда выполнялись следующие условия:

Нетрудно убедиться в том, что эти требования являются достаточными для однозначной интерпретации операций обновления над представлениями. Например, пусть имеется следующая спецификация запроса (пример 17.9):

SELECT EMP_SAL
   FROM (SELECT EMP_SAL, DEPT_NO
         FROM EMP
         WHERE EMP_NAME = (SELECT EMP_NAME
                           FROM EMP
                           WHERE EMP_NO = 4425))
   WHERE DEPT_NO <> 630;

Эту спецификацию можно упростить до эквивалентной формулировкиОбратите внимание, что формально эта формулировка не отвечает требованиям SQL/92 для спецификаций запросов, допускающих применение операций обновления. Но в действительности здесь вложенный подзапрос вычисляется в единственное значение при отсутствии какой-либо корреляции с внешним вхождением таблицы EMP .:

SELECT EMP_SAL
   FROM EMP
   WHERE EMP_NAME = (SELECT EMP_NAME
                     FROM EMP
                     WHERE EMP_NO = 4425 )
                        AND DEPT_NO <> 630;

Предположим, что с данной спецификацией запроса связано представление с именем EMPSAL. Тогда операция

UPDATE EMPSAL SET EMP_SAL = EMP_SAL - 1000.00;

эквивалентна операции

UPDATE EMP SET EMP_SAL = EMP_SAL - 1000.00
   WHERE EMP_NAME = (SELECT EMP_NAME
                     FROM EMP
                     WHERE EMP_NO = 4425 )
                        AND DEPT_NO <> 630;

Операция

DELETE FROM EMPSAL WHERE EMP_SAL > 20000.00;

эквивалентна операции

DELETE EMPSAL
   WHERE EMP_SAL > 20000.00 AND
         EMP_NAME = (SELECT EMP_NAME
                     FROM EMP
                     WHERE EMP_NO = 4425 )
                        AND DEPT_NO <> 630;

Операция вставки над представлением EMPSAL

INSERT INTO EMPSAL 25000.00;

трактуется как

INSERT INTO EMP
   ROW (DEFAULT, DEFAULT, DEFAULT, 25000.00, DEFAULT, DEFAULT);

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

С другой стороны, условия допустимости операций обновления, специфицированные в SQL/92, не являются необходимыми. Например, над представлением EMPMNG, определенным над спецификацией запроса ("выбрать данные о служащих, являющихся руководителями отделов").

SELECT *
   FROM EMP
   WHERE EXISTS (SELECT *
                 FROM DEPT
                 WHERE DEPT_MNG = EMP_NO);

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

Представления, допускающие применение операций обновления, в стандарте SQL:1999

В стандарте SQL:1999 правила применимости операций обновления к спецификации запроса существенно уточнены.

Критерии применимости операций обновления

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

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

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

Выражение запросов удовлетворяет условию применимости операций обновления, если по крайней мере к одному столбцу выражения запросов применимы операции обновления . Выражение запросов удовлетворяет условию простой применимости операций обновления, если в разделе FROM выражения запросов содержится ссылка только на одну таблицу, и все столбцы выражения запросов удовлетворяют условию применимости операций обновления .

Выражение запросов удовлетворит условию применимости операций вставки, если оно удовлетворяет условию применимости операций обновления ; каждая из таблиц, от которых зависит это выражение (т.е. таблиц, на которые имеются ссылки в разделе FROM ), удовлетворяет условию применимости операций вставки и выражение запросов не содержит операций UNION, INTERSECT и EXCEPT .Конечно, это определение базируется на том факте, что для любой базовой таблицы условие применимости операции вставки удовлетворяется (при наличии привилегии INSERT, см. следующую лекцию).

Правила функциональных зависимостей

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

На основе этих определений в стандарте SQL:1999 устанавливаются правила функциональных зависимостей для 11 компонентов языка.

В стандарте определяется несколько правил, на основе которых устанавливаются известные функциональные зависимости соединенных таблиц, но здесь мы приведем только простейшее из этих правил. Если соединенная таблица производится на основе одной из двух указанных выше операций, то в первой таблице-источнике присутствует один или более столбцов, соответствующих одноименным столбцам второй таблицы-источника. Обозначим через SLCC список следующих выражений (элемент списка соответствует общему столбцу):

Определение выражения COALESCE (V1, V2) см. в разделе "Средства определения, изменения и ликвидации базовых таблиц" лекции 12.

Пусть JT обозначает ключевые слова, определяющие тип соединения ( INNER, LEFT, RIGHT, FULL и т.д.), и пусть TN1 и TN2 обозначают имена таблиц или (если они заданы) имена псевдонимов двух таблиц-источников соответственно. Обозначим через IR результат вычисления следующего выражения запросов:

SELECT SLCC, T1*, T2* FROM T1 JT JOIN T2;

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

Обозначим через SL некоторый список выборки. Пусть:

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

SELECT SL FROM IR;

Раздел WITH CHECK OPTION определения представления

Пусть в базе данных имеется упрощенная таблица EMP, содержащая следующее множество строк (как в примере с GROUP BY ROLLUP разделе "Возможности формулирования аналитических запросов" лекции 16).

Предположим, что в базе данных имеется представление RICH_EMP, определенное следующим образом:

CREATE VIEW RICH_EMP AS
   SELECT *
      FROM EMP
      WHERE EMP_SAL > 18000.00;

Понятно, что в соответствии с правилами SQL (и здравым смыслом) над этим представлением можно выполнять операции обновления. Как видно, в таблице EMP содержится строка, которая соответствует служащему с номером 2447, получающему зарплату в размере 20000 руб. Естественно, эта строка будет присутствовать в виртуальной таблице RICH_EMP. Поэтому можно было бы выполнить, например, операцию

UPDATE RICH_EMP
   SET EMP_SAL = EMP_SAL - 3000
   WHERE EMP_NO = 4452;

Но если выполнение такой операции действительно допускается, то в результате строка, соответствующая служащему с номером 2447, исчезнет из виртуальной таблицы RICH_EMP! Аналогичный эффект возникнет при выполнении операции вставки

INSERT INTO RICH_EMP (EMP_NO) 2452;

В базовой таблице EMP появится строка, в которой значением столбца EMP_NO будет 2452, а значения остальных столбцов будут установлены по умолчанию. В частности, значением столбца EMP_SAL будет 10000.00. Тем самым, если подобная операция вставки действительно допустима, то мы вставили в виртуальную таблицу RICH_EMP строку, которую в этой виртуальной таблице увидеть невозможно.

Чтобы избежать такого противоречивого поведения представляемых таблиц, нужно включать в определение представления раздел WITH CHECK OPTION . При наличии этого раздела до реального выполнения операций модификации или вставки строк через представление для каждой строки будет проверяться, что она соответствует условиям представления. Если данное условие не выполняется хотя бы для одной модифицируемой или вставляемой строки, то операция полностью отвергается. В некотором смысле (при наличии раздела WITH CHECK OPTION ) условие выборки, содержащееся в выражении запросов представления, можно считать ограничением целостности этого представления.

Режимы проверки CASCADED и LOCAL

Вспомним теперь, что в полном виде синтаксис раздела WITH CHECK OPTION может включать ключевые слова CASCADED или LOCAL . Обсудим их смысл. Предположим, что представление V2 определяется над представлением V1 следующим образом:

CREATE VIEW V2 AS
   SELECT ...
   FROM V1
   WHERE ...
   [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Пусть над V2 выполняется некоторая операция O обновления базы данных. Тогда:

Примеры результатов действия раздела WITH CHECK OPTION

Чтобы пояснить результаты действия раздела WITH CHECK OPTION , допустим, что в базе данных присутствуют определения двух представлений MIDDLE_RICH_EMP и MORE_RICH_EMP:

CREATE VIEW MIDDLE_RICH_EMP AS
   SELECT *
      FROM EMP
      WHERE EMP_SAL < 20000.00
      [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
CREATE VIEW MORE_RICH_EMP AS
   SELECT *
      FROM MIDDLE_RICH_EMP
      WHERE EMP_SAL > 18000.00
      [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

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

В тело (материализованного) представления MORE_RICH_EMP будут входить следующие строки представляемой таблицы MIDDLE_RICH_EMP:

В каждом из представлений MIDDLE_RICH_EMP и MORE_RICH_EMP может отсутствовать или присутствовать (в одном из двух видов) раздел WITH CHECK OPTION . В совокупности возможен один из девяти случаев:

Чтобы рассмотреть каждый из возможных случаев по отдельности, обсудим, что будет происходить в каждом случае при выполнении следующих двух операций модификации строк (будем называть эти операции U1 и U2 соответственно )Будем считать, что тем, кто пользуется представлением MORE_RICH_EMP, неизвестно ограничение EMP_SAL < 20000.00, на котором основывается представление MIDDLE_RICH_EMP .:

UPDATE MORE_RICH_EMP
   SET EMP_SAL = EMP_SAL + 7000.00;
UPDATE MORE_RICH_EMP
   SET EMP_SAL = EMP_SAL - 7000.00;

Случай 1. Ни в одном из представлений не содержится раздел WITH CHECK OPTION .

Первый неожиданный результат состоит в том, что после выполнения операции U1 тело представления MORE_RICH_EMP оказывается пустым. Действительно, у единственной строки таблицы EMP (со значением EMP_NO, равным 2443 ), одновременно удовлетворяющей условиям обоих представлений, столбец EMP_SAL принимает значение 26000.00. После этого строка перестает удовлетворять условию представления MIDDLE_RICH_EMP и исчезает из результирующей таблицы MORE_RICH_EMP. Этот результат может быть особенно неожиданным для пользователей базы данных, которым известно, что условие представления MORE_RICH_EMP имеет вид EMP_SAL > 18000.00, и соблюдение этого условия должно сохраняться при увеличении размера зарплаты.

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

Случай 2. В определении представления MIDDLE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION, а в определении MORE_RICH_EMP раздел WITH CHECK OPTION отсутствует.

В этом случае, в соответствии с первыми двумя правилами проверки корректности выполнения операций обновления над представлениями, операция U1 должна быть отвергнута системой (поскольку ее выполнение нарушает условие представления MIDDLE_RICH_EMP ). Но заметим, что такое поведение системы будет совершенно неожиданным и непонятным для тех пользователей базы данных, которым известно только определение "верхнего" представления MORE_RICH_EMP, поскольку операция U1 явно не может нарушить видимое ими ограничение.

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

Случай 3. В определении представления MIDDLE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION, а в определении MORE_RICH_EMP раздел WITH CHECK OPTION отсутствует.

В этой ситуации будут проверяться условия, содержащиеся в определении представления MIDDLE_RICH_EMP, а также все ограничения целостности таблицы EMP и всех других представлений, определенных над этой базовой таблицей. В результате операция U1 будет отвергнута системой, а операция U2 будет "успешно" выполнена. Другими словами, повторится Случай 2.

Случай 4. В определении представления MIDDLE_RICH_EMP раздел WITH CHECK OPTION отсутствует, а в определении MORE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION.

Понятно, что в этом варианте операция U2 не сработает (ее выполнение не будет допущено условием "ограничения целостности" представления MORE_RICH_EMP ). Но операция U1 (увеличение размера зарплаты служащих) будет успешно выполнена, поскольку она не противоречит локальным ограничениям представления MORE_RICH_EMP.

Случай 5. В определениях представлений MIDDLE_RICH_EMP и MORE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION.

Выполнение обеих операций U1 и U2 будет справедливо отвергнуто. На первый взгляд все в порядке. Но если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем получить ситуацию Случая 2, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP - роль MORE_RICH_EMP.

Случай 6. В определении представления MIDDLE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION, а в определении MORE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION.

Снова, если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем попасть в ситуацию Случая 2, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP - роль MORE_RICH_EMP.

Случай 7. В определении представления MIDDLE_RICH_EMP раздел WITH CHECK OPTION отсутствует, а в определении MORE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION.

Если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем попасть в ситуацию Случая 3, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP - роль MORE_RICH_EMP.

Случай 8. В определении представления MIDDLE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION, а в определении MORE_RICH_EMP - раздел WITH CASCADED CHECK OPTION.

Если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем получить ситуацию Случая 3, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP - роль MORE_RICH_EMP.

Случай 9. В определениях представлений MIDDLE_RICH_EMP и MORE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION.

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

Очевидный вывод из приведенного анализа заключается в том, что единственным способом обеспечить корректность выполнения операций обновления через представления (допускающие операции обновления) является включение в определение каждого представления раздела WITH CASCADED CHECK OPTION. В этом случае поведение системы будет оставаться корректным при введении дополнительных представлений над представлением MORE_RICH_EMP, между представлениями MORE_RICH_EMP и MIDDLE_RICH_EMP или между представлением MIDDLE_RICH_EMP и базовой таблицей EMP, если в определениях всех этих представлений присутствует раздел WITH CASCADED CHECK OPTION.

Исторический очерк

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

Во-первых, как отмечалось в лекции 3, одной из наиболее привлекательных черт реляционной алгебры является замкнутость относительно понятия отношения. В любой алгебраической операции, операндом которой является отношение, в качестве операнда можно использовать алгебраическое выражение. С другой стороны, имеется явное неравноправие по отношению к операциям обновления. Мы можем вставлять, модифицировать и удалять кортежи в базовых отношениях, но не можем (в общем случае) применять эти операции к алгебраическим выражениям. Хотелось максимальным образом устранить подобное неравноправие.

Во-вторых, на первый взгляд задача не является слишком трудной (по крайней мере, если оставаться в пределах реляционной алгебры). Действительно, базовых операций совсем немного, и каждая базовая операция очень проста.

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

В двух первых международных стандартах (SQL/89 и SQL/92) к виду таких виртуальных таблиц предъявлялись чрезмерно строгие требования. Это показывают даже те простые примеры, которые приводились в начале данного раздела. И конечно, наличие таких ограничений в стандарте языка приводило к тому, что в реализациях SQL появлялось много расширений, которые поддерживались только отдельными компаниями-производителями СУБД. Создается впечатление, что когда более десяти лет назад был инициирован проект нового стандарта SQL-3 (который в конце концов привел к появлению SQL:1999), разработчики находились в состоянии растерянности.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При выполнении каждого триггера система устанавливает контекст выполнения триггера. Выполнение любого оператора 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. Эти данные представляют собой набор изменений состояния, где каждое изменение состояния описывает изменение данных в целевой таблице триггера. Изменение состояния включает следующие данные:

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

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

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

Мы уже продемонстрировали использование старых и новых значений в определении триггера 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-оператора Помимо прочего, этот факт означает, что определение в базе данных нового триггера может привести к неработоспособности существующих приложений, разработчики которых, вообще говоря, могут даже и не знать о появлении нового триггера ..

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

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

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

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

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

Заключение

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

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

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

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

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