Триггеры. Одним из базовых требований к проектируемой базе данных является требование ее

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

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

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

базы данных. Ранее отмечалось, что по способам реализации ограничения целостности

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

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

важная цель механизма триггеров - обеспечение целостности базы данных и обеспечение

логической независимости базы данных.

133

 

или

Триггер - это выполняемый модуль, привязанный к объекту базы данных – чаще

всего к таблицам и событию, связанному с этим объектом (insert, update или delete). Триггер

вызывается неявно при возникновении события над этим объектом. Триггер получает всю

информацию о выполняемых пользователем изменениях в таблице. Разработчик

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

базы данных. Когда пользователь начинает изменение данных, сервер автоматически

начинает транзакцию, в которой и выполняется триггер. В теле транзакции разработчик

может реализовывать произвольные алгоритмы, которые могут выполнять как проверку, так

и изменения данных. В конце концов, работа триггера сводится либо к фиксации, либо к

откату транзакции, которая осуществляет изменение данных. Если выполняется откат

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

отменяются все исправления, сделанные самим триггером в различных таблицах (если они

выполнялись). При фиксации транзакции производится как фиксирование изменений,

выполненных пользователем, так и изменений, сделанных самим триггером. Триггер

хранится в базе данных наряду с таблицами, представлениями, хранимыми процедурами.

При вызове триггеров используются две специальные таблицы: таблица удаления

(deleted table) и таблица добавления (inserted table). Они используются для проверки

операторов модификации данных и создания условий для работы триггеров. В таблице

deleted сохраняются копии строк, которые удаляются операторами update или delete. В

таблице inserted сохраняются копии строк, которые вставляются операторами insert или

update. Пользователь не может непосредственно изменять данные в этих таблицах, но может

использовать находящуюся в них информацию для проверки последствий выполнения

операторов insert, update или delete.

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

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

срабатывании триггера (операторы Insert, Delete, Update, Execute procedure).

Пример 14.18. Разработаем триггер для контроля количества студентов в группе: при

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

если их более 5 (это для примера!), то запись не вводится – производится откат транзакции.

В этом примере используется глобальная системная переменная @@rowcount, которая

возвращает количество строк, подвергшихся воздействию последнего оператора, т.е. в

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

CREATE TRIGGER tr_ins ON [dbo].[Студенты]

FOR INSERT

AS

DECLARE @k int, @p int

IF @@rowcount=1

BEGIN

SELECT @p=Группа

FROM inserted

SELECT @k=count(Фамилия) FROM Студенты

WHERE Группа=@p

IF @k>5

BEGIN

ROLLBACK tran

PRINT ’в группе более 5 студентов’

END

END

Для создания триггера для таблицы Студенты в дереве ее объектов выбирают папку

Triggers, через контекстное меню выбирают New Trigger и в окне вставки кода, где задаются

шаблоны основных команд, вводят программу, представленную выше и на рисунке 14.23.

Набранный код компилируют (Execute), если нет ошибок - объект Triggers, по контекстному

меню, обновляют (Refresh). И тогда в списке триггеров для таблицы Студенты мы увидим

разработанный нами триггер. Заметим, что для одной таблицы можно разработать несколько

триггеров.

Видим разработанный триггер

после компиляции и обновления

объектов базы данных

Рис. 14.23. Разработка триггера для таблицы «Студенты» в базе данных в SQL Server 2005

Для проверки работоспособности триггера в таблицу Студенты надо добавить

записи, в которых номер группы будет повторяться. При попытке ввести шестую запись с

одинаковым номером группы транзакция не будет выполняться. На экран выдается

соответствующее сообщение (рис.14.24) и последняя запись в таблицу не добавляется.

Рис. 14.24. Сообщение об откате транзакции в триггере для таблицы «Студенты»

Аналогично можно контролировать и обновление данных.

135

 

 

Пример 14.19. Разработаем «каскадирующий» триггер: при изменении кода

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

значение в таблице Предмет. На рисунке 14.25 показаны таблицы и ниже приведен код

триггера, работающего при обновлении данных в таблице-справочнике Специальность.

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

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

Специальность. Программно за корректностью данных будет следить триггер, который

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

Рис. 14.25. Таблицы без связей для демонстрации поддержки целостности

программно через триггер

CREATE TRIGGER tr_up ON Специальность

FOR UPDATE

AS

DECLARE @ss char(20), @dd char(20)

SELECT @dd=Спец FROM deleted

SELECT @ss=Спец FROM inserted

UPDATE Предметы SET Спец=@ss

WHERE Спец=@dd

Практические задания

Задание 14.1. Преобразуйте базу данных «Студенты» к SQL Server.

Задание 14.2. Выполните примеры данной темы и самостоятельно разработайте хранимые

процедуры и функции:

a. разработайте функцию, которая вернет таблицу со списком старост или список

отличников;

b. разработайте функцию, которая вернет таблицу со списком отличников или

двоечников (оценка вводится как параметр);

c. разработайте хранимую процедуру для подсчета численности студентов в группе

(группу задать как параметр);

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

месяце (месяц задать как параметр);

e. разработайте хранимую процедуру для вычисления среднего балла успеваемости

каждого студента или каждой группы.

Задание 14.3. В режиме Конструктора добавьте в таблицу Группа поле Куратор (тип

данных – nvarchar) и создайте новую таблицу Куратор с полем Куратор (тип nvarchar).

Введите фамилии кураторов и внесите их в таблицу Группа (один куратор может быть у

нескольких групп). Для обеспечения целостности данных напишите триггер на обновление

строк в таблице Куратор с соответствующими изменениями в таблице Группа. Проверьте

работу триггера.

136

 

Контрольные вопросы

1. В чем отличие локальной идеологии, в рамках которой мы проводили

разработку учебной базы данных «Студенты», от модели клиент-сервер, к

которой преобразовали нашу базу данных?

2. Какие типы функций используются в SQL Server 2005?

3. Какие типы пользовательских функций можно разрабатывать в SQL Server

2005 и в чем их отличия?

4. Почему в теле функции Inline не разрешается создавать циклы, использовать

команды ветвления, работать с транзакциями, объявлять переменные и т. д.?

5. Зачем нужны временные таблицы? Как долго и где хранятся определенные в

них данные?

6. Зачем нужны переменные и какими они могут быть? Как долго и где хранятся

определенные в них данные? Как объявляются переменные? Назовите

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

7. Зачем пишут хранимые процедуры и как их запускают на выполнение?

8. Каково назначение управляющих конструкций?

9. Каково назначение триггера? Какие специальные таблицы как они

используются при разработке триггера?

10. Что означает команда ROLLBACK TRAN?

Литература

Дата добавления: 2015-05-09