Триггеры

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

Таблица 6.1. Сравнение триггеров и хранимых процедур
Хранимые процедуры	Триггеры
Являются самостоятельным объектом базы данных	Привязываются к таблице или виду
Явно вызываются на выполнение командой EXECUTE	Реагируют на события, связанные с таблицей-владельцем
Могут иметь входные и / или выходные параметры, коды возврата	Не имеют входных / выходных параметров и кодов возврата
Могут выполняться в отдельной транзакции	Выполняются в той же транзакции, в которой осуществляются изменения таблицы-владельца

Возможность закрепления триггеров за конкретными операторами определяется тем, что в языке SQL предусмотрены три типа запросов, предназначенных для изменения данных – INSERT, UPDATE и DELETE. В связи с этим выделяют шесть видов триггеров (табл. 6.2).

Таблица 6.2. Виды триггеров
Объект	Действие	Триггер
Таблица / вид	Вставка строк INSERT	Вместо вставки строк INSTEAD OF
После вставки строк FOR (AFTER)
Модификация строк UPDATE	Вместо модификации строк INSTEAD OF
После модификации строк FOR (AFTER)
Удаление строк DELETE	Вместо удаления строк INSTEAD OF
После удаления строк FOR (AFTER)

Примечание: триггеры FOR и AFTER являются синонимами.

Независимо от используемого типа триггера для реализации действий, предусмотренных этим триггером в СУБД SQL Server, используются две виртуальные таблицы.

В одной из них хранятся копии всех вставляемых строк (поэтому данная таблица именуется INSERTED), а во второй хранятся копии всех удаляемых строк (эта таблица именуется DELETED) (табл. 6.3).

Таблица 6.3. Содержимое таблиц INSERTED и DELETED
Вид триггера	Таблицы INSERTED и DELETED
INSTEAD OF INSERT	INSERTED содержит вставляемые в таблицу записи. DELETED пуста
FOR INSERT	INSERTED содержит вставленные в таблицу записи. DELETED пуста
INSTEAD OF UPDATE	INSERTED содержит новые значения модифицируемых записей. DELETED содержит старые значения модифицируемых записей
FOR UPDATE	INSERTED содержит новые значения модифицированных записей. DELETED содержит старые значения модифицированных записей
INSTEAD OF DELETE	INSERTED пуста. DELETED содержит удаляемые записи
FOR DELETE	INSERTED пуста. DELETED содержит удаленные записи

На рис. 6.8 приведена схема запуска триггеров FOR (AFTER) и INSTEAD OF.

Рис. 6.8. Схема запуска триггеров FOR (AFTER) и INSTEAD OF

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

Создавать новые триггеры можно либо в редакторе запросов среды Management Studio, либо воспользовавшись специальным шаблоном (рис. 6.10), который открывается при выборе в «Обозревателе объектов» пункта «Создать триггер» у таблицы, к которой будет привязываться создаваемый триггер (рис. 6.9).

Рис. 6.9. Создание нового триггера

Рис. 6.10. Шаблон создания нового триггера

Общий синтаксис создания триггера:

CREATE TRIGGER <Имя триггера>

ON <Имя таблицы или вида>

{FOR|AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}

AS <SQL-выражения, составляющие тело триггера>

Примеры:

1. Триггер на поддержание актуального количества товаров на складе при добавлении поставок деталей (рис. 6.11).

Рис. 6.11. Текст триггера №1

2. Триггер на поддержание актуального количества товаров на складе при удалении поставок (рис. 6.12).

Рис. 6.12. Текст триггера №2

3. Триггер на поддержание актуального количества товаров на складе при отпуске деталей в цеха (рис. 6.13).

Рис. 6.13. Текст триггера №3

4. Проверка допустимости значения даты поставки при осуществлении новой поставки деталей (рис. 6.14).

Рис. 6.14. Текст триггера №4

5. Проверка допустимости значения даты поставки при редактировании существующей поставки деталей (рис. 6.15).

Рис. 6.15. Текст триггера №5

Задание: создайте два триггера для своей базы, в отчет поместите их описание и код создания.

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

1. Что такое бизнес-логика приложения в теле сервера БД?

2. Охарактеризуйте архитектуры информационных систем?

3. В чем преимущества и недостатки использования бизнес-логики приложения в теле сервера баз данных?

4. Что такое триггер? Как он активизируется в информационной системе?

5. Охарактеризуйте особенности работы с триггерами в СУБД MS SQL Server.

6. Приведите пример триггера на поддержание целостности БД.

7. Чем триггер отличается от хранимой процедуры?

8. Кто может вызывать триггер и хранимую процедуру?

9. Охарактеризуйте синтаксис языка хранимых процедур СУБД MS SQL Server.

10. Как значения параметров передаются в хранимую процедуру?

11. Приведите пример процедуры с возвращаемыми значениями параметров.

12. Приведите пример хранимой процедуры для организации отчета по информации, собранной в созданной БД.

13. Приведите пример хранимой процедуры для поддержания историчности регистрационных сведений в созданной БД.

14. Что такое вид над таблицей? Какой командой он создается?

15. Приведите пример использования вида в созданной БД.

16. Может ли триггер быть связан с видом?