Имеется несколько достоинств использования триггеров и хранимых процедур. Если Вы сохраняете эти команды на сервере, то Вам нужно создавать код только один раз, а не в каждом приложении, работающем с базой данных. Это экономит время при написании программ. Далее, поскольку коды хранятся на сервере, то их не требуется пересылать по сети из клиентского приложения, что значительно снижает сетевой траффик. И наконец, сохраняя коды на сервере, Вы тем самым гарантируете, что целостность данных и правила (business rules) поддерживаются единым образом, независимо от того, какое именно клиентское приложение обращается к данным.

В этой главе Вы узнаете, как:

Триггером называется именованный набор прекомпилированных команд SQL, хранящийся на сервере, который автоматически выполняется, когда происходит заданное событие. Например, триггер может выполняться при вставке, изменении или удалении строки в существующей таблице. Триггер сообщает СУБД, как нужно выполнять команды SQL INSERT, UPDATE или DELETE, чтобы выполнялись нормальные правила (business rules) организации.

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

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

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

Например, в конце каждого месяца фирма по прокату видеопродукции может захотеть получить список всех фильмов, которые никто не брал в последние 30 дней, и выставить их на распродажу. Чтобы ускорить выполнение этого рутинного запроса, Вам нужно создать хранимую процедуру под названием “sp_norental”, выдающую список всех фильмов, которые никто не брал за последний месяц. В последний день каждого месяца, когда Вы выбираете эту процедуру из меню клиентского приложения, клиент пересылает по сети на сервер только имя хранимой процедуры, и сервер автоматически выполняет все коды, связанные с этим именем. Поскольку весь код процедуры хранится на сервере, Вам не нужно писать все команды SQL, нужные для выполнения запроса, и пересылать их по сети каждый раз, когда Вы хотите найти фильмы, на которые нет спроса, и убрать их со склада.

/* ERwin Builtin %Datetime */
/* %Parent %Verbphrase %Child ON PARENT DELETE RESTRICT */
if exists (
select * from deleted, %Child
where
/* %%JoinFKPK(%Child, deleted, “ = “, “ and”) */
%JoinFKPK(%Child, deleted, “ = “,” and”)
)
begin
select @errno = 30001,
@ errmsg = ‘Cannot DELETE “%Parent” because “%Child” exists. ‘
goto error
end

create trigger tDMOVIE on MOVIE for DELETE as
/* ERwin Builtin Wed Aug 24 20:33:33 1994 */
/* DELETE trigger on MOVIE */
begin
declare @errno int,
@ errmsg varchar(255)
/* ERwin Builtin Wed Aug 24 20:33:33 1994 */
/* MOVIE is in stock as MOVIE_COPY ON PARENT DELETE RESTRICT */
if exists (
select * from deleted, MOVIE_COPY
where
/* %JoinFKPK(MOVIE_COPY,deleted, “ = “,” and”) */
MOVIE_COPY.master_number = deleted.movie_number
)
begin
select @errno = 30001,
@errmsg = ‘Cannot DELETE “MOVIE” because “MOVIE_COPY” exists. ‘
goto error
end
/* ERwin Builtin Wed Aug 24 20:33:33 1994 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

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

Редактор Referential Integrity позволяет Вам изменять правило ссылочной целостности, связанное с конкретной связью. Например, со связью между MOVIE и MOVIE-COPY по умолчанию для случая Parent-Delete устанавливается правило ссылочной целостности RESTRICT. Вы можете изменить правило ссылочной целостности для этой связи на CASCADE, нажав кнопку CASCADE в редакторе Referential Integrity.

Редактор Trigger Template позволяет Вам изменять шаблон, связанный с любым типом RI-триггера, а также просматривать и модифицировать макрокод, используемый конкретным шаблоном. Когда Вы изменяете шаблон триггера, Вы можете использовать другой встроенный шаблон или пользовательский (User Override - переопределеный пользователем) шаблон.

Редактор Trigger Toolbox предоставляет в Ваше распоряжение набор предопределенных макрокоманд, которые помогут Вам адаптировать встроенные шаблоны триггеров, создавать переопределенные шаблоны триггеров или писать новые триггеры и хранимые процедуры на SQL. Предопределенные макрокоманды, которые начинаются с символа процента (%), генерируют псевдокод, который в процессе генерации схемы расширяется в специальный синтаксис SQL, поддерживаемый Вашей СУБД.

Trigger Toolbox состоит из трех частей: слева - набор из трех окон макрокоманд, в центре - окно, в которое выводится подсказка о выбранной макрокоманде, справа - статическая диаграмма-пример. Макрокоманды, относящиеся к сущностям, связям и атрибутам, содержатся в трех разных списках, что позволяет Вам быстрее найти нужную макрокоманду. Обратите внимание на то, что некоторые макрокоманды содержатся более чем в одном списке.

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

Один из способов, как можно это сделать - изменить встроенный триггер Parent-Delete CASCADE так, чтобы он производил архивирование, создавая RI Type Override. Имеется несколько достоинств этого способа:

Создание шаблона RI Type Override облегчает создание собственных триггеров ссылочной целостности, поскольку все связи, которым присваивается переопределенный тип RI-триггера, автоматически используют связанный с ним шаблон User Override.

Если Вы изменяете встроенный триггер так, чтобы он мог выполнить это действие, создавая для этого Relationship Override для триггера Parent-Delete RESTRICT, то этот шаблон будет применяться только к связи между таблицами ORDER и ORDER LINE. Хотя изменение режимов ссылочной целостности для отдельных связей может усложнить работу с базой данных, использование режима Relationship Override позволяет вам поддерживать правила, которые распространяются на какие-то отдельные связи.

Ниже приводится часть кода шаблона Relationship Override для триггера Parent-Delete RESTRICT (вверху), расширенный код (в центре) и завершенный шаблон триггера (внизу).

/* ERwin Built-in %Datetime */
/* %Parent %VerbPhrase %Child ON PARENT DELETE RESTRICT */
update %Parent
set order_status=‘outstanding’
from %Parent, deleted, %Child
where
/* %%JoinPKPK(%Parent, deleted) */
%JoinPKPK(%Parent,deleted) and
/*%%JoinFKPK(%Child,deleted) */
%JoinFKPK(%Child,deleted)

/* ERwin Built-in Mon Apr 5 07:52:30 1993 */
/*ORDER contains ORDER_LINE ON PARENT DELETE RESTRICT */
update ORDER
set order_status=‘outstanding’
from ORDER,deleted,ORDER_LINE
where
/*%JoinPKPK(ORDER,deleted) */
ORDER.order_id = deleted.order_id and
/*JoinFKPK(ORDER_LINE,deleted) */
ORDER_LINE.order_id = deleted.order_id

create trigger tD_ORDER on ORDER for DELETE as
/* ERwin Builtin Mon Apr 5 07:49:49 1993 */
/* DELETE trigger on ORDER */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Mon Apr 5 07:49:49 1993 */
/* ORDER contains ORDER_LINE ON PARENT DELETE RESTRICT */
update ORDER
set order_status=‘outstanding’
from ORDER,deleted,ORDER_LINE
where
/*%JoinPKPK(ORDER,deleted) */
ORDER.order_id = deleted.order_id and
/* JoinFKPK(ORDER_LINE,deleted) */
ORDER_LINE.order_id = deleted.order_id
/* ERwin Builtin Mon Apr 5 07:49:49 1993 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

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

Код измененного шаблона, включающий в себя специальный код (он выделен), нужный для того, чтобы изменять значение product-sold-quantity в таблице PRODUCT каждый раз при добавлении новой строки в таблицу ORDER LINE, показан ниже. Для того чтобы просмотреть расширенный код после того, как шаблон, заданный по умолчанию, изменен, щелкните по окну Expanded Code.

Далее приводится расширенный код триггера, где Вы можете увидеть реальные значения, которые заменили макропеременные, использованные в коде шаблона. Обратите внимание, что специальный код, изменяющий таблицу PRODUCT, дублируется в каждом триггере Child-Insert, связанном с сущностью ORDER-LINE.

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

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

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

Для того чтобы приступить к созданию хранимой процедуры, дайте команду “<DB> Table Property”, а затем - команду каскадного меню “Stored Procedure”. Вы войдете в редактор Table Property.

Редактор Table Property позволяет Вам просматривать все хранимые процедуры, физические характеристики и правила валидации, связанные с таблицей базы данных. Если Ваша СУБД поддерживает хранимые процедуры, то внизу редактора появляется закладка Stored Procedure, а также появляется лист связей хранимых процедур Stored Procedure Attachment Sheet. Если Ваша СУБД не поддерживает хранимые процедуры, то закладка Stored Procedure не появляется. См. документацию по Вашей СУБД о том, поддерживает ли она хранимые процедуры. Лист связей хранимых процедур - Stored Procedure Attachment Sheet редактора Table Property содержит информацию о том, какая таблица связана с каждой из сущностей ERwin и какие хранимые процедуры в настоящий момент связаны с каждой из сущностей и таблиц. В этом редакторе Вы можете просмотреть код шаблона каждой из хранимых процедур, а также связать хранимую процедуру с определенной сущностью или отсоединить ее от сущности.

Для того чтобы создать шаблон для новой процедуры, нажмите кнопку “SP Template...” для входа в редактор Stored Procedure Template.

Редактор Stored Procedure Template позволяет Вам создавать, изменять или удалять хранимую процедуру.

Список “Template Name”, расположенный в верхней части редактора, содержит имена всех хранимых процедур, связанных с выбранной Вами сущностью.

Для того чтобы изменить существующую хранимую процедуру, щелкните по имени шаблона, который Вы хотите изменить, в списке “Template Name”. Когда ERwin поккажет на экране код, щелкните по окну Template Code и измените коды, пользуясь встроенными макрокомандами и стандартными клавишами редактирования. Нажмите кнопку “Update” для сохранения изменений.

Для того чтобы удалить ранее определенную процедуру, щелкните по имени удаляемого шаблона в списке “Template Name”, а затем нажмите кнопку “Delete”.

Для того чтобы создать новую хранимую процедуру, нажмите кнопку “New” и введите “говорящее” имя, например “sp_overdue” для хранимой процедуры, выбирающей все просроченные фильмы на сегодняшнее число. После этого установите курсор в окно Template Code и создайте код шаблона, пользуясь встроенными макрокомандами и клавишами редактирования. Заккончив создание кода шаблона, нажмите “ОК” для выхода из текущего редактора в редактор Table Property, в котором Вы можете связать новый шаблон с одной или несколькими сущностями.

Два списка в верхней части листа позволяют Вам выбрать сущность или соответствующую ей таблицу, с которой Вы хотите связать хранимую процедуру. Список “Attached Template” содержит все процедуры, связанные с выбранной сущностью или таблицей. Список “Un-Attached Template” содержит все шаблоны хранимых процедур, который не связаны с выбранной сущностью.

Для того чтобы связать шаблон хранимой процедуры с сущностью, выберите сущность, затем выделите имя шаблона процедуры, которую Вы хотите связать с этой сущностью, из списка “Un-Attached Template” и нажмите кнопку “Attach”.

Для того чтобы отменить связь между шаблоном хранимой процедуры и сущностью, выберите сущность, затем выделите имя шаблона процедуры, для которой Вы хотите отменить связь с этой сущностью, из списка “Attached Template” и нажмите кнопку “Detach”.

Для того чтобы просмотреть список всех сущностей, связанных с каким-то шаблоном хранимой процедуры, выделите имя шаблона и нажмите кнопку “SP Browser...”.

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

Редактор Schema Properties позволяет Вам просматривать все хранимые процедуры, а также скрипты “до и после генерации схемы”, которые связаны со схемой. Если Ваша СУБД поддерживает хранимые процедуры, то в нижней части редактора появляется закладка Stored Procedure и на экран выводится лист Stored Procedure Attachment Sheet. Если Ваша СУБД не поддерживает хранимые процедуры, то закладка Stored Procedure не появляется. О поддержке хранимых процедур в Вашей СУБД см. документацию по ней.

Лист Stored Procedure Attachment Shеet редактора Schema Properties показывает, какие хранимые процедуры связаны со схемой в настоящий момент. В этом редакторе Вы можете просмотреть код шаблона любой процедуры, а также связать процедуру со схемой и отсоединить от нее.

Список хранимых процедур, которые связаны в настоящий момент со схемой, приводится в окне “Attached SP Template” наверху редактора. Список хранимых процедур, которые не связаны в настоящий момент со схемой, приводится в окне “Un-attached SP Template” в центре редактора. Редактор Schema Properties позволяет Вам также просматривать код шаблона для выбранной процедуры в окне Template Code внизу редактора.

Для того чтобы связать процедуру со схемой, выделите имя шаблона процедуры в списке “Un-attached SP Template” и нажмите кнопку “Attach”. Для того чтобы отсоединить процедуру от схемы, выделите имя шаблона процедуры в списке “Un-attached SP Template” и нажмите кнопку “Detach”.

Для создания нового шаблона процедуры нажмите кнопку “Schema SP Template...” для входа в редактор Stored Procedure Template.

Редактор Schema Generation Report позволяет Вам выбирать объекты, которые Вы хотите влючить в физическую схему, включая RI-триггеры, хранимые процедуры, связанные со всей схемой, хранимые процедуры, связанные с сущностями и скрипты, выполняемые до (пре-скрипты) или после (пост-скрипты) того, как будет сгенерирована оставшаяся часть схемы.

Для генерации группы объектов, таких как триггеры, щелкните по check box для соответствующей группы объектов, а затем включите любой из связанных с ней режимов генерации, по Вашему желанию, например User Override Trigger Template.

Более подробно о редакторе Schema Generation Report см. Гл.18, Прямое и обратное проектирование, разд. “Генерация схемы базы данных”.

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

Таблица содержит: