01112010 (Собственноручно набранные лекции по БД)

SeregaProMai.Narod.ru ©

Лекция от 01.11.2010. (на основе фото с лекции-презентации)

Триггеры.

Специальный вид хранимых процедур, которые прикрепляются к таблице и автоматически выполняются при выполнении определенных операции: INSERT, UPDATE, DELETE с этой таблицей.

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

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

Триггеры создаются, модифицируются и удаляются при помощи команд CREATE, ALTER и DROP языка определения данных.

Для создания триггера используется команда CREATE, которая имеет следующий формат:

CREATE TRIGGER ИмяТриггера ON ИмяТаблицы

тип (AFTER, INSTEAD OF) событие (INSERT, UPDATE, DELETE)

AS

Код триггера

В Microsoft SQL Server 2008 используется два типа триггеров. Триггер типа AFTER, который выполняется после события, для которого определен триггер. И триггер типа INSTEAD OF, который выполняется вместо операции.

Триггер может содержать оператор ROLLBACK TRANSACTION. Если выполняется этот оператор, то все изменения в триггере и все изменения, которые вызвали срабатывание триггера, отменяются.

При выполнении операции INSERT, в триггере можно использовать логическую таблицу inserted, которая имеет такую же структуру, что и таблица, на которую установлен триггер и содержит копии строк, которые должны быть добавлены в таблицу. При выполнении операции DELETE создается аналогичная таблица deleted.

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

Пример создания триггера типа AFTER, показан на рисунке.

Рассмотрим, как используемое ограничение осуществить, используя триггер типа INSTEAD OF.

Так как не рекомендуется для одного и того же ограничения использовать несколько триггеров, то временно отключим триггер ВСТАВКА.

Для временного отключения триггеров используется команда:

ALTER TABLE имя_таблицы DISABLE TRIGGER имя_триггера

А для его включения - команда:

ALTER TABLE имя_таблицы ENABLE TRIGGER имя_триггера

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

Посмотрим как, используя триггер типа INSTEAD OF, можно решить эту проблему.

Проверим работу созданного триггера:

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

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

Проверим работу триггера:

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

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

Наличие этого триггера не отменяет операцию проверки статуса. Рассмотрим взаимодействие этих триггеров.

Список запрещенных деталей поставщика с номером 4, поэтому операция успешно выполнена.

Так как статус детали номер 1 удовлетворяет ограничению, то триггер ВСТАВКА2 добавляет новую поставку. Деталь 1 входит в список запрещенных деталей для поставщика 4, поэтому триггер Вставка0 отменяет ввод поставки.

Курсоры.

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

Необходимость использования курсоров можно объяснить на следующем примере:

Есть таблица ТОВАРЫ, необходимо уменьшить цену на 10 рублей у всех товаров, цена которых > 50 и увеличить цену на 10 рублей, если она < 50.

Транзакции.

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

Есть фирма, сотрудники которой занимаются оформлением заказов. Если заказ выполнен, то сотруднику начисляются комиссионные % от суммы заказа. На рисунке приведена схема базы данных.

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

1. Добавить запись в таблицу “Заказы”.

2. Увеличить сумму комиссионных менеджера, оформившего заказ.

3. Откорректировать количество товара на складе.

Очевидно, если количество товара на складе недостаточно для выполнения заказа, то предыдущие операции (1 и 2) необходимо отменить.

В Microsoft SQL Server транзакция начинается оператором.

BEGIN TRAN[SACTION]

И может завершаться операторами:

1) COMMIT TRAN[SACTION]

2) ROLLBACK TRAN[SACTION]

Если транзакция завершается COMMIT TRAN, то все изменения запоминаются, а если ROLLBACK TRAN, то - отменяются.

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

Пример 1. Транзакция включает выполнение следующих операций:

1. Добавление нового товара.

2. Изменение цены товара в одной из предыдущих поставок.

С помощью оператора

SAVE TRAN <point1>

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

При наличии точки сохранения оператором ROLLBACK TRAN <point1> отменяются все действия после точки сохранения.

Проблемы параллельного выполнения транзакций.

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

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

Пример.

Изменение количества товара.

Транзакция А.

1. Считать с внешнего носителя количество товара [Картофель].

2. Изменить количество [Картофель].

3. Записать количество [Картофель].

Транзакция В.

1. Считать количество товара [Морковь].

2. Изменить количество [Морковь].

3. Записать количество [Морковь].

Обработка транзакций.

1А. Считать количество товара [Картофель].

1В. Считать количество товара [Морковь].

2А. Изменить количество [Картофель].

3А. Записать количество [Картофель].

2В. Изменить количество [Морковь].

3В. Записать количество [Морковь].

Теперь рассмотрим параллельное выполнение транзакций, которые работают с одними данными:

количество [Картофель] = 30

Транзакция А.

1. Считать количество товара [Картофель].

2. Изменить количество [Картофель], (+5)

3. Записать количество [Картофель].

Транзакция В.

1. Считать количество товара [Картофель].

2. Изменить количество [Картофель], (+10)

3. Записать количество [Картофель].

Обработка транзакций:

1А. Считать количество товара [Картофель], (30)

1В. Считать количество товара [Картофель], (30)

2А. Изменить количество [Картофель], (30+5=35)

3А. Записать количество [Картофель], (35)

2В. Изменить количество [Картофель], (30-10=20)

3В. Записать количество [Картофель], (20)

В результате получили 20, хотя должны были получить 25.

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

1. Заблокировать [Картофель] для А.

2. Считать количество [Картофель] для А.

3. При попытке считать [Картофель] для В обнаруживается блокировка и выполнение транзакции В приостанавливается.

4. Изменить количество [Картофель], (А) (30+5=35)

5. Записать количество [Картофель], (35)

6. Снять блокировку.

7. Заблокировать [Картофель] для В.

8. Считать количество [Картофель] для В, (35)

9. Изменить количество [Картофель], (В) (35-10=25)

10. Записать количество [Картофель], (25)

11. Снять блокировку [Картофель].

Возможна взаимная блокировка.

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

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

Транзакция А.

1. Заблокировать [Картофель].

2. Изменить количество [Картофель].

3. Заблокировать [Морковь].

4. Изменить количество [Морковь].

5. Снять блокировку [Картофель].

6. Снять блокировку [Морковь].

Транзакция В.

1. Заблокировать [Морковь].

2. Изменить количество [Морковь].

3. Заблокировать [Картофель].

4. Изменить количество [Картофель].

5. Снять блокировку количества [Морковь].

6. Снять блокировку количества [Картофель].

Обработка транзакций:

1А. Заблокировать [Картофель].

1В. Заблокировать [Морковь].

2А. Изменить количество [Картофель].

2В. Изменить количество [Морковь].

Дальше наступает взаимная блокировка, так как [Картофель] заблокирован…

8