Informix Logo


Informix Magazine Russian Edition Сентябрь 1998г.

Программирование сервера базы данных

Грачев Андрей Юрьевич
консультант, Informix Москва,
тел. (095) 755-87-00
email: andreyg@informix.com

От редакции Informix Magazine/RE: постоянный автор нашего журнала, сотрудник московского офиса Informix, Андрей Грачев заканчивает книгу "Введение в Informix". В этой книге рассмотрены архитектуры - файл-серверная, хост-терминал, клиент-сервер, трехзвенная с сервером приложений; модели данных - иерархическая, сетевая, реляционная, объектно-реляционная; основные понятия реляционных СУБД, реляционные алгебра и исчисление, нормализация; язык SQL и его использование; обзор продуктов Informix и проблем построения информационных систем на их основе.

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

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

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

Динамический SQL

Иногда на этапе написания программы вы еще не знаете, какой именно запрос должен быть отработан сервером. Простейший пример - это порядок сортировки. В вашем приложении, предназначенном для отдела кадров, должна быть возможность отсортировать отчет по фамилии, доходу, стажу работы или числу прогулов. Причем должны быть предусмотрены все возможные комбинации этих сортировок - по доходу и числу прогулов, по фамилии и стажу в обратном порядке и т.д. Простейший подсчет показывает - вариантов раздела ORDER BY оператора SELECT будет 81 (всего 4 поля, каждое поле допускает три разных способа использования при сотрировке - сортировать в порядке возрастания, убывания, вообще не сортировать - три в четвертой степени и есть 81). Согласитесь, что весьма утомительно указывать все 81 вариантов оператора SELECT в программе.

В системах разработки приложений (ESQL/C, NewEra, 4GL и т.д.) имеется возможность формировать и исполнять SQL-запросы в процессе работы прикладной программы, в динамике ее исполнения. Отсюда и название - "динамический SQL" (впрочем, автор не считает это название очень удачным). Фактически, это тот же самый язык - SQL, но SQL-оператор не подвергается синтаксическому разбору на этапе компиляции прикладной программы, а в текстовом виде передается непосредственно серверу базу данных.

Для работы с динамическим SQL предлагаются следующие операторы:

PREPARE <имя оператора> FROM <текстовая строка>

EXECUTE <имя оператора>

FREE <имя оператора>

Оператор PREPARE "подготавливает" SQL-оператор для исполнения. Сам SQL-оператор указывается либо явно в виде текстовой строки, либо через значение текстовой переменной. Оператор PREPARE "связывает" с SQL-оператором имя. Это имя - самое обычное имя в среде разработки. Примеры (Informix-4GL):

PREPARE sel1 FROM "select name from items where price < 1.50"

PREPARE empty_comp FROM
"INSERT INTO companies(name) VALUES ('undef')"

Оператор PREPARE посылает переданный ему текст SQL-серверу. Сервер анализирует переданные ему SQL-операторы и, если нет ошибок, переводит их во внутреннее представление.

После того, как оператор подготовлен, он может быть исполнен оператором EXECUTE. Здесь-то и надо указывать имя, данное подготовленному оператору:

EXECUTE sel1
EXECUTE empty_comp

Один и тот же подготовленный оператор можно исполнять многократно. Оператор FREE освобождает все ресурсы (память), связанные с подготовленным оператором. Выполнять оператор FREE следует тогда, когда подготовленный оператор заведомо больше не потребуется:

FREE sel1

Если не выполнить оператор FREE ничего страшного не произойдет, но выделенная для оператора память будет висеть мертвым грузом.

Если на момент подготовки SQL-оператора не все конкретные значения известны, то имеется возможность подставлять эти значения в момент исполнения. Для этого SQL-оператор, подготовленный с помощью PREPARE, должен быть снабжен параметрами. Параметры, значения которых будут определяться в момент исполнения, задаются символом "?":

PREPARE select2 FROM "SELECT price FROM items WHERE name = ?"
PREPARE new_comp FROM
"INSERT INTO companies(name, address) VALUES (?,?)"

Для задания фактических параметров в оператор EXECUTE надо добавить раздел USING:

EXECUTE new_comp USING "Кооператив 'Эх, ухнем'", "Москва, Арбат, 21"
EXECUTE new_comp USING "ИЧП 'Бумеранг'", "Магадан, п/я 777"

Параметры в операторах PREPARE/EXECUTE являются позиционными. То есть, при исполнении на место первого вопросительного знака подставляется первое значение в разделе USING, на место второго вопросительного знака - второе значение из раздела USING и т.д.

Подготовленный оператор может использоваться при описании курсора. Например, если в зависимости от желания пользователя нам надо выполнить сортировку товара либо по названию, либо по цене, то это на Informix-4GL реализуется следующим образом:

DEFINE string1 CHAR(60)
..........
IF flag
THEN { сортируем по названию }
LET string1 = "SELECT name, price FROM items ORDER BY name"
ELSE { сортируем по цене }
LET string1 = "SELECT name, price FROM items ORDER BY price"
END IF
PREPARE select_st FROM string1
DECLARE my_cursor CURSOR FOR select_st
..........

Очевидно, можно еще сократить приведенный выше фрагмент, если использовать оператор конкатенации строк.

Помимо возможности формировать запросы не на этапе написания программы, а на этапе ее иcполнения, операторы PREPARE/EXECUTE/FREE могут быть полезны еще, как минимум, в двух случаях: для повышения эффективности программы и для исполнения SQL-операторов, которых "нет" в среде разработки.

Для того, чтобы понять за счет чего подготовленные операторы могут повысить эффективность, рассмотрим как отрабатываются SQL-запросы. Когда в работе приложения управление передается на SQL-оператор, то происходит следующее. Этот SQL-оператор посылает серверу запрос на исполнение. SQL-сервер исполняет запрос в четыре этапа: (1) анализирует пришедший запрос, (2) выбирает оптимальный способ его исполнения, (3) исполняет и (4) отсылает результаты приложению. Если выполнять несколько одинаковых запросов, то для каждого запроса будут исполняться все эти этапы. Если же мы подготавливаем запрос с помощью оператора PREPARE, а затем несколько раз исполняем его оператором EXECUTE, то анализ запроса и поиск оптимального способа исполнения будет выполняться только раз - при выполнении оператора PREPARE. А на каждое исполнение запроса оператором EXECUTE требуется только два последних этапа - непосредственное исполнение и отсылка результатов.

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

Например, Вы используете среду разработки Informix-4GL старой версии (предположим, 4-й). Она вас вполне устраивает. Но в качестве SQL-сервера используется 7-я версия Informix OnLine Dynamic Scalable Architecture. Этот сервер "понимает" уже значительно более широкий набор SQL-операторов по сравнению с 4-й версией Informix-4GL. В частности, оператор создания триггеров CREATE TRIGGER (о том, что это такое, будет сказано ниже) может быть исполнен сервером, но отсутствует в Informix-4GL версии 4.10. Используя операторы PREPARE/EXECUTE, этот триггер можно создать так:

PREPARE cr_trig FROM "CREATE TRIGGER trig1 ..."
EXECUTE cr_trig
FREE cr_trig

Подготовленные с помощью оператора PREPARE SQL-запросы доступны (видимы) только в данном приложении, а именно между операторами DATABASE ... CLOSE DATABASE. То есть, если вы завершили работу (а точнее, закрыли базу данных оператором CLOSE DATABASE), то подготовленные запросы пропадают. Или, если вы подготовили SQL-запрос, то другой пользователь за другим компьютером не может выполнить подготовленный вами запрос (этот другой пользователь, конечно, может выполнить ту же самую последовательность PREPARE/EXECUTE/FREE, но это будет уже другой SQL-запрос). Однако, существует возможность подготовить для исполнения SQL-запросы так, что эти запросы будут доступны многим пользователям. Но для этого используется уже другой механизм - хранимые процедуры.

Хранимые процедуры

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

Основное назначение хранимых процедур - это функциональное расширение схемы базы данных. Хранимая процедура может содержать некоторый нетривиальный набор операторов, реализующий то или иное логическое действие. Например, администратор банковской системы разработал хранимую процедуру, которая реализует функцию "занести на счет номер X сумму Y". Программист, разрабатывающий приложение пользуется этой процедурой, но не знает КАК именно она выполняется. В результате:

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

  • никто, кроме администратора, не знает о том, как устроена схема базы данных, и меньше шансов, что кто-то использует эти знания в корыстных целях;

  • независимо от типа рабочего места одно и тоже действие выполняется гарантированно одними и теми же операторами - выше надежность разработанной системы;

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

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

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

Языки написания хранимых процедур в настоящее время значительно различаются у разных производителей. Для серверов Informix язык для создания хранимых процедур носит название SPL - Stored Procedure Languages. Именно его (точнее, его подмножество), мы и рассмотрим.

Хранимая процедура создается оператором

CREATE PROCEDURE <имя процедуры> (<формальные параметры>)
<тело хранимой процедуры>
END PROCEDURE

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

CREATE PROCEDURE <имя процедуры> (<формальные параметры>)
RETURNING <тип 1>, <тип 2> ...
<тело хранимой процедуры>
END PROCEDURE

Хранимая процедура может получать и возвращать произвольное число значений разных типов. Типы входных параметров и выходных значений могут быть любыми, доступными в SQL, за исключением SERIAL, BYTE и TEXT. Вместо типа SERIAL надо указывать тип INTEGER.

Примеры описаний хранимых процедур:

CREATE PROCEDURE incr_account (account_no INTEGER,
quantity MONEY(20,2))
.....
END PROCEDURE

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
RETURNING INTEGER {серийный номер нового пользователя}
.....
END PROCEDURE

Язык хранимых процедур будет рассмотрен в отдельном пункте ниже.

Для удаления хранимой процедуры, которая больше не нужна, используется оператор:

DROP PROCEDURE <имя хранимой процедуры>

Например:

DROP PROCEDURE add_new_user

Для исполнения хранимой процедуры используется оператор

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>)

Например:

EXECUTE PROCEDURE incr_account (213917008, 23000000.00)

Если хранимая процедура возвращает какие-то значения (то есть в ее описании есть раздел RETURNING), то при исполнении этой процедуры в оператор EXECUTE PROCEDURE надо добавить раздел INTO с перечислением имен переменных (иногда вместо INTO используется слово RETURNING):

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>)
INTO <имя переменной>, ...

Например:

EXECUTE PROCEDURE add_new_user ("Прутков") INTO user_id

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

EXECUTE PROCEDURE incr_account (123456789, 1500000.00)
EXECUTE PROCEDURE incr_account
(account_no=123456789, quantity=1500000.00)
EXECUTE PROCEDURE incr_account
(quantity=1500000.00, account_no=123456789)

Если при исполнении хранимой процедуры значения каких-либо фактических параметров не указаны, то используются значения по умолчанию. Значения по умолчанию задаются при описании хранимой процедуры с помощью ключевого слова DEFAULT. Например, в описанной выше процедуре add_new_user для параметра name задано значение по умолчанию - NULL.


Язык хранимых процедур

Рассмотрим подробно язык хранимых процедур Informix - SPL. Именно из операторов этого языка конструируется тело хранимой процедуры. Тело хранимой процедуры представляет собой блок операторов. Блок операторов состоит из раздела описаний, раздела реакций на исключительные ситуации и раздела исполняемых операторов. Каждый из этих разделов может быть пустым. Операторы разделяются точкой с запятой. Комментарии помещаются в фигурных скобках или начинаются с двух символов минус и продолжаются до конца строки.

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

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

В разделе исполняемых операторов могут использоваться любые операторы из таблицы 1, а также любой из SQL-операторов, за исключением тех, которые создают, удаляют, открывают или закрывают базу данных. При обращении к хранимой процедуре управление передается первому оператору в ее теле. Исполнение оператора RETURN завершает хранимую процедуру и возвращает исполнение в то место, откуда хранимая процедура была вызвана. Хранимая процедура завершается также и при достижении оператора END PROCEDURE. При этом никаких значений в вызывающую программу не возвращается.

В качестве примера рассмотрим реализацию хранимых процедур, приведенных при рассмотрении оператора CREATE PROCEDURE:

CREATE PROCEDURE incr_account (account_no INTEGER, quantity MONEY(20,2))
UPDATE accounts SET value = value + quantity
WHERE account_id = account_no
END PROCEDURE

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

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
RETURNING INTEGER {серийный номер нового пользователя}
DEFINE user_id INTEGER;
INSERT INTO users(lname) VALUES (name);
LET user_id = SQLCA.SQLERR[2];
RETURN user_id
END PROCEDURE

Рассмотрим основные операторы SPL:

CALL - то же самое, что и EXECUTE PROCEDURE.

CONTINUE - продолжить выполнение цикла. После этого слова надо указать какой именно цикл надо продолжить выполнять -FOR/WHILE/FOREACH. Примеры:

CONTINUE FOR
....
CONTINUE FOREACH

DEFINE - определить внутреннюю переменную. Для каждой переменной надо задать ее имя и ее тип:

DEFINE <имя переменной> <тип>

Примеры:

DEFINE user_id INTEGER
DEFINE comp_name CHAR(40)

EXIT - прекратить выполнение цикла. После этого слова надо указать какой именно цикл надо прекратить выполнять - FOR/WHILE/FOREACH. Примеры:

EXIT WHILE
....
EXIT FOR

FOR - цикл со счетчиком. Аналогичен оператору цикла со счетчиком в других языках программирования. Значения шага, начального и конечного значений вычисляются заранее, до начала выполнения операторов тела цикла:

FOR <переменная> = <целое> TO <целое> STEP <целое>
<операторы>
END FOR

Если шаг (STEP) не указан, то он принимается равным 1 если начальное значение меньше конечного, и -1, если нет. Пример:

FOR i = 1 TO 14
....
END FOR

IF - условный оператор. Проверяет условие и, если оно верно, выполняет операторы после слова THEN. Если не верно, и присутствует раздел ELSE - то выполняются операторы после слова ELSE:

IF <условие> THEN <операторы> END IF
IF <условие>
THEN <операторы>
ELSE <операторы>
END IF

Если после слова ELSE по логике должен следовать другой оператор IF, то такое сочетание ELSE IF ... END IF END IF можно записывать более компактно: ELIF ... END IF. Пример:

IF j > 20
THEN RETURN j
ELIF j > 10
THEN RETURN 10
ELSE RETURN 0
END IF

LET - оператор присваивания. Вычисляет выражение и присваивает его значение указанной переменно:

LET <имя переменной> = <выражение>

Выражение, которое может быть здесь использовано, очень похоже на выражение, которое может использоваться в операторе SELECT. Примеры:

LET i = 0
LET str1 = "абвгдежз"

RAISE EXCEPTION - возбудить ошибку. Если на данную ошибку не стоит реакция (нет соответствующего оператора ON EXCEPTION), то исполнение хранимой процедуры завершается и в вызвавшую программу "передается" данная ошибка:

RAISE EXCEPTION <номер ошибки>

Можно в качестве номера ошибки использовать как предопределенные, стандартные номера, так и свои собственные.

RETURN - завершает исполение хранимой процедуры. Управление передается в вызвавшую программу. После слова RETURN надо указывать возвращаемые значения, если процедура должна возвращать значения:

RETURN <выражение 1>, ....

SYSTEM - позволяет выполнить внешнюю команду. Имя этой внешней команды передается операционной системе, то есть это может быть или встроенная команда ОС, или исполнимый файл:

SYSTEM <символьная строка>
SYSTEM <имя символьной переменной>

Например, можно выполнить командный файл my_cmd:

SYSTEM "/usr/local/bin/my_cmd"

WHILE - цикл с завершением по условию. Аналогичен циклу WHILE в других языках программирования:

WHILE <условие>
<операторы>
END WHILE

Пример:

LET i = 1
WHILE i = 1
EXECUTE PROCEDURE my_proc RETURNING I
END WHILE

BEGIN ... END - блок операторов. В блоке операторов можно определять и использовать локальные переменные. Допустимые любые другие операторы SPL. Пример:

BEGIN
DEFINE i CHAR(20)
LET i = "Ну, погоди!"
IF ... THEN
BEGIN
DEFINE i INTEGER
LET i = 0
....
END
END IF
END

Триггеры

Идея триггеров

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

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

Триггер хранится как объект в базе данных, то есть принадлежит схеме базы данных. )

Создание и удаление триггера

Для создания и удаления триггеров, как и для таблиц, и для хранимых процедур, используются операторы CREATE и DROP.

CREATE TRIGGER <имя> <событие> <действие>
DROP TRIGGER <имя>

Имя триггера - обычное имя в SQL. Главное, что бы оно было уникальным. Событие указывает момент, когда триггер срабатывает. Триггеры бывают трех типов - на вставку нового ряда в таблицу, удаление ряда и модификацию каких-либо полей. Для каждой таблицы могут существовать все три типа триггеров, как, впрочем, и не существовать ни одного. При этом, если для каждой таблицы можно задать только по одному триггеру на вставку и удаление, то триггеров на обновление может быть несколько в зависимости от того, какие поля обновляются (но не может быть двух триггеров на обновление одного и того же поля).

Соответственно, синтаксис на раздел "событие" при создании триггера будет следующим:

INSERT ON <имя таблицы>
DELETE ON <имя таблицы>
UPDATE ON <имя таблицы>
UPDATE OF <поле>, <поле>, ... ON <имя таблицы>

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

{ триггер на вставку нового документа }
CREATE TRIGGER trig1
INSERT ON documents ....

{ триггер на удаление информации о фирме }
CREATE TRIGGER del_trig
DELETE ON companies ....

{ триггер на обновление цены товара }
CREATE TRIGGER upd_price
UPDATE OF price ON items ....

{ триггер на изменение имени или фамилии человека }
CREATE TRIGGER upd_name
UPDATE OF lname, fname ON persons ....

Теперь рассмотрим, как описываются действия триггера. Каждое действие состоит из описания того, что выполняется единожды перед началом исполнения оператора, вызвавшего событие для триггера (раздел BEFORE), для каждого ряда (раздел FOR EACH ROW) и после исполнения оператора (раздел AFTER).

CREATE TRIGGER .... ON ...
BEFORE <операторы>
FOR EACH ROW <операторы>
AFTER <операторы>

Можно использовать произвольное сочетание из разделов BEFORE, FOR EACH ROW и AFTER, главное, что бы был задан хотя бы один раздел. В качестве операторов для триггеров могут быть использованы всего четыре типа SQL-операторов - оператор вставки (INSERT), удаления (DELETE), обновления (UPDATE) и выполнения хранимой процедуры (EXECUTE PROCEDURE). Очевидно, что наличие в этом списке оператора вызова хранимой процедуры позволяет сделать триггер сколь угодно сложным. Эти SQL-операторы должны разделяться запятой и находиться в круглых скобках.

Примеры:

CREATE TRIGGER trig1 DELETE ON persons
AFTER ( UPDATE tab2 SET x=x+1,
UPDATE tab3 SET y=y-1 )

CREATE TRIGGER upd_trig UPDATE OF name ON companies
FOR EACH ROW ( EXECUTE PROCEDURE proc1 )
AFTER ( EXECUTE PROCEDURE proc2 )

Рассмотрим поведение последнего триггера (upd_trig) более подробно. Пусть таблица companies имеет следующую структуру и состоит из следующих записей:

CREATE TABLE companies (  
company_id SERIAL { уникальный идентификатор фирмы }
name CHAR(40) { название фирмы }
address CHAR(60) { адрес фирмы }
)

company_id

name address
101  АО Рога и Копыта Одесса, п/я 13 
105  ТОО Добро пожаловать Энск, 5-я авеню
107 АОЗТ Сделай сам Городская свалка

При такой структуре данной таблицы и при таком ее содержании оператор

UPDATE companies
SET name = "ИЧП Мастер Безенчук"
WHERE company_id = 101

приведет к исполнению триггера upd_trig. Причем, так как модифицироваться будет одна запись, то и процедура proc1, и proc2 будут исполнены по одному разу, причем вначале proc1, а затем proc2. Раздел AFTER и, соответственно, процедура proc2, будут исполнены уже после внесения изменений в базу данных. Но если оператор UPDATE модифицирует несколько записей, то процедура proc1 будет исполнена несколько раз (по одному разу для каждой модифицируемой записи), а процедура proc2 - только один раз - после внесения всех изменений. Так, оператор

UPDATE companies
SET name = "ТОО Льдинка"
WHERE company_id > 103

изменит название у двух компаний, то есть в одном запросе будут модифицированы поля name у двух записей. В результате, процедура proc1 будет исполнена дважды, а затем один раз будет вызвана процедура proc2. Если в операторе UPDATE предполагается изменение поля name, но ни одна запись не была модифицирована (не удовлетворила условию в разделе WHERE, например), то раздел FOR EACH ROW триггера не будет выполнен ни разу, тогда как разделы BEFORE и AFTER все равно сработают. Так, следующий оператор исполнит процедуру proc2 и ни разу не выполнит proc1:

UPDATE companies
SET name = NULL
WHERE company_id < 57

А следующий оператор вообще не приведет к выполнению триггера upd_trig, так как поле name данной таблицы не изменяется и его вообще нет в списке обновляемых полей:

UPDATE companies
SET address = "Москва, Бутырка"
WHERE company_id = 101


Имена для старого и нового значений записи

Вернемся к тем задачам, с которых мы начали разговор о триггерах. А именно, о необходимости удалять информацию о сотрудниках при удалении фирмы и о протоколировании всех изменений в таблице. Для того, чтобы реализовать эти задачи надо из триггера получить значения полей модифицируемой записи. Например, при удалении информации о фирме нам надо знать уникальный ключ удаляемой запии - поле companies_id, иначе мы не сможем определить сотрудников удаляемой фирмы.

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

REFERENCING OLD AS <связанное имя для старого значения>
REFERENCING NEW AS <связанное имя для нового значения>

Указывать связанные имена можно в любом порядке. Если какое-то из имен не нужно (например, нам не требуется имя для старого значения), то его можно не указывать. Слово AS можно опускать. )

Примеры использования связанных имен:

CREATE TRIGGER del_trig DELETE ON items
REFERENCING OLD del_rec
FOR EACH ROW ( UPDATE tab2 SET total=total-del_rec.price )

CREATE TRIGGER upd_trig UPDATE OF name ON companies
REFERENCING NEW newval
REFERENCING OLD oldval
FOR EACH ROW
(EXECUTE PROCEDURE proc4(oldval.name, newval.name) )
AFTER ( EXECUTE PROCEDURE proc2 )

Итак, все-таки, напишем триггер, который будет срабатывать на удаление информации о фирме (таблица companies) и действие которого будет состоять в удалении из таблицы persons информацию о всех сотрудниках этой фирмы:

CREATE TABLE companies (
company_id SERIAL, { первичный ключ фирмы }
....)

CREATE TABLE persons (...,
company INTEGER, { ссылка на фирму, где работает }
....)

CREATE TRIGGER del_comp DELETE ON companies
REFERENCING OLD AS rec
FOR EACH ROW
(DELETE FROM persons
WHERE persons.company = rec.company_id )

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

CREATE TABLE protocol (
company INTEGER, { идентификатор измененной записи }
login CHAR(8), { ситемное имя пользователя }
oldname CHAR(40), { старое имя фирмы }
newname CHAR(40), { новое имя фирмы }
when DATETIME YEAR TO SECOND { когда }
)

Тогда нужный нам триггер будет выглядеть так:

CREATE TRIGGER upd_compname UPDATE OF name ON companies
REFERENNCING NEW AS newcomp
REFERENNCING OLD AS oldcomp
FOR EACH ROW
(INSERT
INTO protocol (company, login, oldname, newname, when)
VALUES (oldcomp.company_id, USER, oldcomp.name,
newcomp.name, CURRENT)

 

Условия в триггере

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

........
WHEN ( <условие> ) ( <оператор>, <оператор>, ...),
WHEN ( <условие> ) ( <оператор>, <оператор>, ...),
........

Для каждого из разделов BEFORE/FOR EACH ROW/AFTER можно указывать произвольное количество таких пар условие-операторы. Пример триггера с дополнительными условиями:

CREATE TRIGGER upd_price UPDATE OF price ON items
REFERENCING OLD AS pre
REFERENCING NEW AS post
FOR EACH ROW
WHEN (post.price < 0)
(EXECUTE PROCEDURE proc1(post.price))
WHEN (post.price > pre.price * 2)
(EXECUTE PROCEDURE proc2(ppost.price, pre.price))

 

Украинская баннерная сеть
 

[Home]

Сайт поддерживается группой пользователей Информикс на Украине.

Hosted by NO-more.