Понятие триггера

Триггер – это подпрограмма, похожая на процедуру БД, автоматически вызываемая СУБД при изменении, удалении или добавлении записи в таблице. К триггерам невозможно обратиться из программы, передать им параметры или получить от них результат. Наиболее часто триггеры применяются для поддержания ссылочной целостности и каскадных операций в БД. Ссылочные спецификации, определяющие каскадные действия при удалении и обновлении и создаваемые при объявлении таблиц, также реализуются через триггеры, однако текст этих триггеров не редактируется.

Назначение триггеров

• Предотвращение изменения (например, предотвратить счета от изменений после того как они разосланы).
• Журналирование изменения (например, хранить копии старых данных).
• Аудит изменений (например, вести лог пользователей и ролей, участвующих в изменениях).
• Фиксация изменений (например, обеспечить, чтобы все изменения датировались по часам сервера, а не клиента).
• Реализация бизнес-правил.
• Репликация данных (например, хранить записи всех изменений, которые будут отправлены в другой базе данных более поздней версии).
• Повышение производительности (например, обновление сальдо после каждой детали сделки, для ускорения запросов).

Объявление триггеров

CREATE TRIGGER <имя триггера> {BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF <столбцы>]} ON <имя таблицы> REFERENCING {OLD {[ROW]|TABLE [AS] <старое имя>} NEW {ROW|TABLE} [AS] <новое имя>}] [FOR EACH {STATEMENT|ROW [WHEN <предикат>]}]
[BEGIN ATOMIC]
<SQL-операторы>
[END]

Ключевые слова

• BEFORE|AFTER – время запуска триггера – до | после операции обновления.
• DELETE|INSERT|UPDATE [OF <столбцы>] = событие срабатывания триггера.
• FOR EACH ROW – для каждой строки (строчный триггер, тогда и WHEN).
• FOR EACH STATEMENT – для всей команды (действует по умолчанию).
• REFERENCING – позволяет присваивать до 4-х псевдонимов старым и | или новым строкам и | или таблицам, к которым могут обращаться триггера.

Ограничения триггеров

Тело триггера не может содержать операторов:
• Определения, удаления и изменения объектов БД (таблиц, доменов и т.п.)
• Обработки транзакций (COMMIT, ROLLBACK)
• Подключения и отключения к БД (CONNECT, DISCONNECT)

Особенности применения
• Триггер выполняется после применения всех других (декларативны) проверок целостности и целесообразен тогда, когда критерий проверки достаточно сложен. Если декларативные проверки отклоняют операцию обновления, то до выполнения триггеров дело не доходит. Триггер работает в контексте транзакции, а ограничение FK нет.
• Если триггер вызывает дополнительную модификацию своей базовой таблицы, то чаще всего это не приводит к его рекурсивному выполнению, однако это следует уточнять. В СУБД SQL Server 2005 предусмотрена возможность указания рекурсии до 255 уровней с помощью ключевого слова OPTION (MAXRECURSIV 3).
• Триггеры обычно не выполняются при обработке больших двоичных столбцов (BLOB).
• Следует помнить, что всякий раз при обновлении данных СУБД автоматически создает так называемые триггерные виртуальные таблицы, которые в различных СУБД носят разные название. В InterBase и Oracle – Это New и Old. В SQL Server – Inserted и Deleted. Причем при изменении данных создаются обе. Эти таблицы имеют то же количество столбцов, с теми же именами и доменами, что и обновляемая таблица. В СУБД SQL Server 2005 предусмотрена возможность указания таблицы, включая временную, в которую следует вставить данные с помощью ключевого слова OUTPUT Inserted.ID,… INTO @.
• В ряде СУБД допустимо объявлять триггеры для нескольких действий одновременно. Для реализации разных реакций на различные действия в Oracle предусмотрены предикаты Deleting, Inserting, Updating, возвращающие True для соответствующего вида обновления.
• В СУБД Oracle можно для триггеров Update указать список столбцов (After Update Of), что обеспечит вызов триггера только при изменении значений только этих столбцов.
• Для каждого триггерного события может быть объявлено несколько триггеров (в Oracle 12 триггеров на таблицу) и обычно порядок их запуска определяется порядком создания. В некоторых СУБД, например, InterBase, порядок запуска указывается с помощью дополнительного ключевого слова POSITION . В общем случае считается, что первоначально должны выполняться триггеры для каждой команды, а затем – для каждой строки.
• Триггеры можно встраивать друг в друга. Так SQL Server допускает 32 уровня вложения (с помощью глобальной переменной @@NextLevel можно определить уровень вложения).

Недостатки триггеров

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

Изменение и удаление триггеров

• Для удаление триггера используется оператор DROP TRIGGER <имя триггера>
• Для изменения триггера используется оператор ALTER TRIGGER <имя триггера>…
• Отключение триггеров
В ряде случаев, например, при пакетной загрузке, триггеры требуется отключать. В ряде СУБД предусмотрены соответствующие возможности. В Oracle и SQL Server ключевые слова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE в операторе ALTER TRIGGER.

Особенности промышленных серверов

1) InterBase/Firebird

CREATE TRIGGER <имя триггера> FOR <имя таблицы> {ACTIVE|INACTIVE} {BEFORE|AFTER} {INSERT|DELETE|UPDATE} [POSITION <номер>]
AS [DECLARE VARIABLE <имя переменной> <тип данных> [(<размер>)]]
BEGIN
<SQL-операторы>
END

Пример:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER <имя триггера> ON <имя {таблицы | представления}> [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} {INSERT|UPDATE|DELETE}
AS <SQL-операторы>

Пример:

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR(‘Изменена таблица Customer’);

Дополнительные виды триггеров

• В СУБД Oracle и SQL Server есть возможность создания (замещающих) триггеров для не обновляемых представлений. Для этого предусмотрены ключевые слова INSTEAD OF:

CREATE TRIGGER <имя триггера> ON <имя представления> INSTEAD OF INSERT AS …

Можно отслеживать попытки клиента обновлять данные с помощью представлений и выполнять какие-либо действия, обрабатывать не обновляемые представления и т.п.
• В СУБД SQL Server предусмотрен триггер отката, фактически прекращающий все действия с выдачей сообщения:

ROLLBACK TRIGGER [WITH RAISEERROR <номер ошибки> [<сообщение>]]

Комментарии запрещены.