Вопрос 27: Триггеры.
Триггер - это механизм, который вызывается, когда в указанной таблице происходит определенное действие. Каждый триггер имеет следующие основные составляющие: имя, действие и исполнение. Имя триггера может содержать максимум 128 символов. Действием триггера может быть или инструкция DML (INSERT, UPDATE или DELETE), или инструкция DDL. Таким образом, существует два типа триггеров: триггеры DML и триггеры DDL. Исполнительная составляющая триггера обычно состоит из хранимой процедуры или пакета.
Создание триггера DML
CREATE TRIGGER [schema_name.]trigger_name
ON {table_name | view_name}
[WITH dml_trigger_option [,…]]
{FOR | AFTER | INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE]}
[WITH APPEND]
{AS sql_statement | EXTERNAL NAME method_name}
Параметры
schema_name - имя схемы, к которой принадлежит триггер.
trigger_name - имя триггера.
table_name - имя таблицы, для которой создается триггер. (Также поддерживаются триггеры для представлений, на что указывает наличие параметра view_name.)
Также можно задать тип триггера с помощью двух дополнительных параметров: FOR, AFTER и INSTEAD OF:
- FOR является синонимом параметра AFTER.
- Триггеры типа AFTER вызываются после выполнения действия, запускающего триггер. Можно создавать только для таблиц.
- Триггеры типа INSTEAD OF выполняются вместо действия, запускающего триггер. Можно создавать как для таблиц, так и для представлений.
Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкция Transact-SQL, которая запускает триггер. Допускается любая комбинация этих трех инструкций. Инструкция DELETE не разрешается, если используется параметр IF UPDATE.
В синтаксисе инструкции CREATE TRIGGER, действие (или действия) триггера указывается в спецификации AS sql_statement.
Только владелец базы данных, администраторы DDL и владелец таблицы, для которой определяется триггер, имеют право создавать триггеры для текущей базы данных.
Изменение структуры триггера
ALTER TRIGGER инструкция обычно применяется для изменения тела триггера. Все предложения и параметры инструкции ALTER TRIGGER имеют такое же значение, как и одноименные предложения и параметры инструкции CREATE TRIGGER.
Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER.
Использование виртуальных таблиц deleted и inserted
При создании действия триггера обычно требуется указать, ссылается ли он на значение столбца до или после его изменения действием, запускающим триггер. По этой причине, для тестирования следствия инструкции, запускающей триггер, используются две специально именованные виртуальные таблицы:
deleted - содержит копии строк, удаленных из таблицы;
inserted - содержит копии строк, вставленных в таблицу.
Структура этих таблиц эквивалентна структуре таблицы, для которой определен триггер.
Таблица deleted используется в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то используется таблица inserted. Это означает, что для каждой инструкции DELETE, выполненной в действии триггера, создается таблица deleted. Подобным образом для каждой инструкции INSERT, выполненной в действии триггера, создается таблица inserted.
Инструкция UPDATE рассматривается, как инструкция DELETE, за которой следует инструкция INSERT. Поэтому для каждой инструкции UPDATE, выполненной в действии триггера, создается как таблица deleted, так и таблица inserted (в указанной последовательности).
Таблицы inserted и deleted реализуются, используя управление версиями строк, которое рассматривалось в предыдущей статье. Когда для таблицы с соответствующими триггерами выполняется инструкция DML (INSERT, UPDATE или DELETE), для всех изменений в этой таблице всегда создаются версии строк. Когда триггеру требуется информация из таблицы deleted, он обращается к данным в хранилище версий строк. В случае таблицы inserted, триггер обращается к самым последним версиям строк.
В качестве хранилища версий строк механизм управления версиями строк использует системную базу данных tempdb. По этой причине, если база данных содержит большое число часто используемых триггеров, следует ожидать значительного увеличения объема этой системной базы данных.
Область применения DML-триггеров
Триггеры AFTER
Триггеры этого типа можно использовать для выполнения, среди прочих, следующих операций:
- создания журнала логов действий в таблицах базы данных;
- реализации бизнес-правил;
- принудительного обеспечения ссылочной целостности.
Триггеры INSTEAD OF
Данный тип триггера всегда использует информацию в таблицах inserted и deleted, созданных для представления, чтобы создать любые инструкции, требуемые для создания запрошенного события.
Значения столбцов, предоставляемые триггером INSTEAD OF, должны удовлетворять определенным требованиям:
- значения не могут задаваться для вычисляемых столбцов;
- значения не могут задаваться для столбцов с типом данных timestamp;
- значения не могут задаваться для столбцов со свойством IDENTITY, если только параметру IDENTITY_INSERT не присвоено значение ON.
Эти требования действительны только для инструкций INSERT и UPDATE, которые ссылаются на базовые таблицы. Инструкция INSERT, которая ссылается на представления с триггером INSTEAD OF, должна предоставлять значения для всех столбцов этого представления, не допускающих пустые значения NULL. (То же самое относится и к инструкции UPDATE. Инструкция UPDATE, ссылающаяся на представление с триггером INSTEAD OF, должна предоставить значения для всех столбцов представления, которое не допускает пустых значений и на которое осуществляется ссылка в предложении SET.)
Триггеры DDL и области их применения
CREATE TRIGGER [schema_name.]trigger_name
ON {ALL SERVER | DATABASE }
[WITH {ENCRYPTION | EXECUTE AS clause_name]
{FOR | AFTER } { event_group | event_type | LOGON}
AS {batch | EXTERNAL NAME method_name}
Параметры
Первым делом при определении триггера DDL нужно указать его область действия. DATABASE указывает в качестве области действия триггера DDL текущую базу данных, ALL SERVER - текущий сервер.
После указания области действия триггера DDL нужно в ответ на выполнение одной или нескольких инструкций DDL указать способ запуска триггера. В параметре event_type указывается инструкция DDL, выполнение которой запускает триггер, а в альтернативном параметре event_group указывается группа событий языка Transact-SQL. Триггер DDL запускается после выполнения любого события языка Transact-SQL, указанного в параметре event_group. Ключевое слово LOGON указывает триггер входа.
Различия DML и DDL
- DDL можно задать в качестве его области действия всю базу данных или даже весь сервер, а не всего лишь отдельный объект.
- Триггеры DDL не поддерживают триггеров INSTEAD OF.
- Для триггеров DDL не требуются таблицы inserted и deleted, поскольку эти триггеры не изменяют содержимого таблиц.