Создание и использование триггеров

Триггер - это программа, которая автоматически выполняется при по­пытке изменения содержимого заданной таблицы. Для каждой таблицы можно определить три типа триггеров: триггеры ввода, добавления и удале­ния. Поскольку мы просто знакомим вас с понятием триггера, то в даль­нейшем наложении: будем пользоваться примерами из SQL Server. Триггеры в Oracle несколько сложнее. В конце изложения мы охарактеризуем некото­рые отличия триггеров, существующие в Oracle.

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

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

Прежде чем начать описывать действительные команды определения триггеров, мы должны объяснить, как работают триггеры SQL Server. Каж­дый раз, когда над таблицей выполняются операции добавления, изменения или удаления данных, создаются новые версии управляемых системой таб­лиц. Эти триггерные таблицы называются inserted (введено) и deleted (удалено). Если в таблицу введены строки, то они помещаются в таблицу inserted, а таблица deleted остается пустой. Если из таблицы удаляются строки, то они помещаются в таблицу deleted, а таблица inserted остается пустой. Если таблица обновляется, то deleted состоит из старых версий строк, а таблица inserted состоит из новых версий тех же строк. Эта инфор­мация особенно ценна с учетом того факта, что SQL Server запускает триггер после того, как обновление файла произошло. Таким образом, когда триггер запускается, файлы inserted и deleted уже существуют.

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

Теперь рассмотрим, как реализовать описанный триггер. Мы предпола­гаем, что таблица worker была расширена и в нее включено поле «cumulative_pay»:

worker (worker_id, worker_name, hrly_rate, skill_type, supv_id, cumulative_pay).

Формула подсчета значения атрибута cumulative_pay такова:

cumulative_pay = total_num_day * 8 * hrly_rate,

где total_num_day работника вычисляется по таблице assignment.

Мы будем рассматривать только изменения таблицы assignment. Каж­дый раз, когда к таблице assignment добавляется новая строка или же суще­ствующая строка обновляется или удаляется, мы хотим обновлять значение поля cumulative_pay для соответствующего работника. Таким образом, нам нужен триггер, который реагирует на изменение таблицы assignment изме­нением поля таблицы worker.

Как будет работать такой триггер? Вспомним, что любое обновление, будь то добавление, удаление или собственно изменение, добавляет строки (от нуля и более) в таблицы inserted и deleted. Таким образом, мы можем воспользоваться этими двумя таблицами независимо от типа обновления, и внести нужные поправки в таблицу worker. Обратите внимание, что для сле­дующего решения не имеет значения, одна или несколько строк были добав­лены, удалены или изменены за один раз. Это решение работает в обоих случаях.

Для создания триггера update_assigment с помощью утилиты SQL Server Enterprise Manager необходимо выбрать таблицу assigment в списке объектов базы данных, после чего выполнить команду All tasks / Manage Triggers меню Action. Данные действия приведут к открытию диалогового окна свойств триггера (рис. 10).

 

Рис. 10. Диалоговое окно свойств триггера

В диалоговом окне появится заготовка для создания текста триггера. Необходимо указать имя триггера, имя таблицы на которую он определен, тип триггера и после ключевого слова AS ввести тест триггера (рис. 11). С помощью клавиши Check Syntax можно проверить корректность текста триггера.

Рис. 11. Текст триггера update_assigment

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

create trigger update_assignment

on assignment

for insert, update, delete

Первая строка дает триггеру имя «update_assignment»; вторая означает, что он применяется к таблице assignment. Третья строка задает, что триггер будет запускаться от каждой операции - ввода, обновления или удаления.

Следующая строка триггера, «as», открывает программную часть опре­деления триггера. Все, что следует за этой строкой, выполняется системой при запуске триггера. Теперь рассмотрим эту часть. Она состоит из двух ко­манд обновления, каждая из которых применяется к таблице worker. Первая команда update прибавляет к значению столбца cumulative_pay значение, вычисленное по таблице inserted, а вторая команда update вычитает из зна­чения столбца cumulative_pay значение, вычисленное по таблице deleted.

update worker

set cumulative_pay = cumulative_pay + 8 * hrly_rate *

(select sum (num_day) from inserted

where inserted.worker_id = worker.worker_id)

update worker

set cumulative_pay = cumulative_pay - 8 * hrly_rate *

(select sum (num_day) from deleted

where deleted.worker_id = worker.worker_id)

Эти две команды заставляют систему проходить таблицу worker дважды. Первая команда update рассматривает строки, которые были добавлены к таблице assignment. Если какие-либо строки были добавлены, то есть в слу­чае ввода данных в таблицу assignment или ее обновления, атрибут num_days (число дней) добавленных кортежей учитывается в соответствую­щем кортеже таблицы worker. Аналогично, вторая команда рассматривает строки, которые были удалены из таблицы assignment. Если удаленные строки есть, то есть в случае удаления данных из таблицы assignment или ее обновления, атрибут num_day удаленных кортежей учитывается (путем вы­читания) в соответствующем кортеже таблицы worker. Если обе таблицы inserted и deleted пусты, то команды update просто никак не отразятся на таблице worker. Таким образом, этот триггер будет работать именно так, как нам нужно.

Для проверки действия триггера откроем Query Analyzer и выполним команду на обновление одной из строк таблицы Assignment. После этого можно посмотреть содержимое таблицы Worker. Значения поля cumulative_pay этой таблицы изменились автоматически (рис. 12).

Рис. 12. Проверка действия триггера с помощью Query Analyzer

Применение триггеров в SQL Server и Oracle. Триггеры запускаются тогда, когда к таблице применяется заданная команда модификации данных (insert (ввод), delete (удаление) или update (обновление)). Не имеет значения, какой пользователь, или какая программа вносит изменения. Если триггер определен для этой команды, он запускается. Следовательно, важно пользо­ваться триггерами именно для тех операций, которые должны всегда выпол­няться при заданном типе изменения. В приведенных выше примерах вы встретились с ситуациями, в которых триггеры могут использоваться доста­точно эффективно. Но существует и множество других.

Одно важное применение триггеров связано с поддержанием целостно­сти. Например, в SQL Server триггеры используются для поддержания един­ственности значений первичных ключей и целостности на уровне ссылок. В Oracle, с другой стороны, эти возможности встроены в язык определения данных. Так, в Oracle мы просто объявляем некоторые атрибуты первич­ными или внешними ключами, и единственность их значений и целостность ссылок поддерживается автоматически. Но в SQL Server для обеспечения выполнения этих ограничений требуется определять триггеры. Таким обра­зом, в SQL Server триггеры являются важным инструментом поддержания целостности базы данных.

В последней версии SQL Server поддержание целостности также как и в Oracle целостность на уровне ссылок поддерживается автоматически. Однако триггеры можно использовать для операций каскадного удаления или обновления записей.

В базах данных Oracle триггеры нужны по аналогичным причи­нам. Хотя первичные и внешние ключи поддерживаются автоматически, любое бизнес-правило, требующее ссылки на другую таблицу базы данных, может поддерживаться в Oracle только при помощи триггера. Вы скажете, что в определении схемы базы данных Oracle позволяет задавать СНЕСК- ограничения, обеспечивающие выполнение некоторых правил в базе данных. Вспомните, однако, что СНЕСК- ограничения не могут содержать подза­просы, ссылающиеся на другие таблицы или даже на другие кортежи той же самой таблицы. Таким образом, СНЕСК- ограничение может рассматривать только один кортеж за раз. Рассмотрим такое правило:

«Расписание работника не может быть составлено более чем на 100 дней»

Для этого правила требуется запрос к таблице assignment, подсчиты­вающий общее число дней (num_days), расписанных для данного работника.

СНЕСК- ограничением это правило задать нельзя. Однако триггер прекрасно справится с ним.

ЗАДАНИЕ.

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

2. Предположим, что в таблице building есть поле tot_num_days, содер­жащее суммарное число дней работы разных работников на этом здании. Создайте триггер, который будет обновлять это поле при каждом обновлении таблицы assignment.