Триггеры являются наиболее эффективным инструментом сохранения целостности баз данных, так как позволяют подробно проанализировать события, происходящие в системе. Все триггеры можно разделить на два класса: триггеры DML – перехват команд insert, update, delete и триггеры DDL – перехват команд DDL. В свою очередь триггеры DML делятся на триггеры After – выполняющиеся после выполнения команды и триггеры Instead of – триггеры выполняются вместо соответствующих команд SQL.
Формат команды создания триггера следующий
create trigger [ schema_name
. ]trigger_name
on { table | view }
[ with <dml_trigger_option>
[ ,...n ] ]
{ for | after | instead of }
{ [ insert ] [ , ] [ update ] [ , ] [ delete ] }
[ with append ]
[ not for replication ]
as
{ sql_statement [ ; ] [ ...n ]
|
external name <method specifier [ ; ] > }
Здесь
■ [ schema_name . ]trigger_name – имя создаваемого триггера. Должно удовлетворять требованиям, предъявляемым к идентификаторам. Имя триггера не может начинаться с символа '#', поскольку триггер не может быть временным объектом.
■ on { table | view } – опция указывает к какой таблице или представлению (имя таблицы или представления) триггер будет относиться. Только триггеры типа instead of могут создаваться для представлений.
■
<dml_trigger_option> - опции триггера. Можно использовать два вида опций encryption
(шифровать) и execution as clause (задать контекст), но с
ними мы уже знакомы.
■
for | after | instead of –
определяется тип триггера. Опции и for и after являются синонимами.
■ [ delete ] [ , ] [ insert ] [ , ] [ update ] - указываются команды DML, на которые будет реагировать данный триггер. Следует указать, по крайней мере, одну команду.
■ with append - данная опция устарела и используется для совместимости.
■ not for replication – опция показывает, что триггер не будет срабатывать, когда во время репликации будут происходить изменения в таблице.
■ sql_statement [ ; ] [ ...n ] – последовательность команд языка Transact SQL, которые будут выполняться, при запуске триггера.
■ external name <method specifier >– опция используется при создании триггера на основе технологии .NET.
Структура команды alter trigger несколько отличается от структуры команды, которую мы только
что разобрали, но все опции в ней те же самые. Вот эта команда.
alter trigger schema_name.trigger_name
on ( table | view )
[ with <dml_trigger_option>
[ ,...n ] ]
( for | after | instead of )
{ [ delete ] [ , ] [ insert ] [ , ] [ update ] }
[ not for replication ]
as { sql_statement [ ; ]
[ ...n ] |
external name <method specifier> [ ; ] }
Удаление триггера DDL осуществляется командой drop trigger schema_name.trigger_name [ ,...n ]. Т.е. одной командой можно удалить сразу несколько триггеров.
Триггеры after выполняются после успешного выполнения команд, вызвавших его. Если команда, по каким либо причинам не выполняется, то не выполняется и триггер. И команда и триггер выполняется в одной транзакции. Поэтому откат при выполнении триггера приведет и к откату команды, вызвавшей его. Триггеры after могут быть определены только для таблиц и не могут быть для представлений. Для таблицы можно определить несколько after-триггеров.
Триггеры instead of выполняются вместо операций DML и по одному для каждой операции. Эти триггеры могут создаваться и для представлений.
В триггерах after используются специальный инструментарий для определения того, что произошло с таблицей (чем вызван запуск триггера). Функция update (column) определяет, был или не был модифицирован данный столбец. Относиться к командам update и insert. Если столбец был модифицирован, то функция возвращает TRUE. Функция columns_updated() позволяет определить, какой столбец был изменении. Функция возвращает двоичное число, каждый бит которого относиться к конкретному столбцу. Если бит равен 1 то это значит, что столбец был изменен командой update или insert.
Перед выполнением для триггера автоматически создаются две временные таблицы: inserted и deleted. Их содержимое зависит от того, какая операция была выполнена:
■ При выполнении команды insert таблица inserted будет содержать новые строки. Таблица deleted будет пуста.
■ При выполнении команды delete таблица deleted будет содержать удаляемые строки, таблица inserted будет пустой.
■ При выполнении команды update таблица deleted будет содержать старые значения строк, таблица inserted – новые.
В случае триггера instead of таблицы deleted и inserted будут содержать строки, которые соответственно должны быть удалены или должны быть вставлены.
Во временные таблицы (deleted и inserted) нельзя вносить какие-либо изменения. Но из триггера можно менять содержимое любых других таблиц. Из триггера нельзя выполнять следующие команды языка Transact SQL: reconfigure, create database, alter database, drop database, restore database, restore log, load database, load log.
На время выполнения триггера происходит блокирование данных, с которыми он работает, так что из других процедур нельзя к ним обратиться. Поэтому триггер должен планироваться таким образом, чтобы его выполнение не занимало слишком много времени.
Если триггер обращается к таблице, которая защищена другим
триггером, то возникает ситуация вложенности. Всего по умолчанию возможно до 32
вложений, как для хранимых процедур и пользовательских функций. Если уровень
вложенности превосходит допустимый, то возникает ошибка. Вложенные триггеры
можно отключить, если использовать системную хранимую процедуру sp_configure.
Возможен и рекурсивный вызов триггера при обращении триггера к той же таблице.
По умолчанию рекурсивный вызов триггеров отключен. Так же как и вложенные триггеры,
рекурсивные триггеры лучше не использовать.
Как уже было сказано, для одной таблицы можно создать
несколько триггеров, причем настроить на одну операцию. Для того чтобы внести
порядок в выполнение таких триггеров можно использовать хранимую процедуру sp_settriggerorder, имеющую следующую
структуру вызова.
sp_settriggerorder[@triggername
= ] 'triggername'
, [@order = ] 'value'
, [@stmttype = ] 'statement_type'
Здесь
■ [@triggername = ] 'triggername' – определяет имя триггера, которое может содержаться и в переменной.
■ [@order = ] 'value' – порядок следования триггера. Который может быть:
o first – выполняется первый.
o last – выполняется второй.
o none – порядок не определен.
■
[@stmttype = ] 'statement_type' – тип инструкции.
Таким образом, очевидно, что можно определить только первый и последний триггер. Т.е. порядок может быть определен при наличии максимум трех триггеров.
Наконец триггер не возвращать никаких наборов строк. Эта возможность считается устаревшей и будет удалена в будущих версиях SQL Server.
В Листинге 3.83 представлен простой триггер after, который запрещает обновлять значение столбца t1 для таблицы table1. При этом в вызывающий модуль с помощью функции raiserror будет возвращено сообщение о причине отказа. Поскольку триггер расположен в одной транзакции с командой, которая вызвала данный триггер, то команда rollback transaction возвращает состояние таблицы в исходное положение.
Листинг
3.83
create trigger no_update on
table1
after update
as
if update(t1)
begin
raiserror ('Обновлять нельзя', 15,1)
rollback transaction
end
Рассмотрим
еще один пример (см. Листинг 3.84).
Листинг
3.84
create trigger dt_del on
dbo.students
instead of delete
as
begin
--удалить из таблицы marks
delete from dbo.marks
where id_student in (select id from deleted)
if(@@error>0)
begin
rollback transaction
raiserror('Ошибка удаления из таблицы marks',16,3)
return
end
--удалить из таблицы students
delete from dbo.students
where id in (select id from deleted)
if(@@error>0)
begin
rollback transaction
raiserror('Ошибка удаления из таблицы students',16,3)
return
end
return
end
Триггер из Листинга
3.84 срабатывает на попытку удаления из таблицы dbo.students. Триггер в начале удаляет
строки из связанной таблицы dbo.marks, а затем уже из таблицы dbo.students (каскадное удаление). При этом
в триггере обрабатываются и возможные ошибки с откатом тразнакции и
возвращаением в приложение сообщения об ошибке.
Триггеры DDL запускаются в ответ на команды DDL.
Вот формат команды создания триггера DDL
create trigger_name
on { all server | database }
[ with <ddl_trigger_option>
[ ,...n ] ]
{ for | after } { event_type | event_group } [ ,...n ]
as { sql_statement [ ; ] [ ...n ]
| external name < method specifier > [ ; ] }
Здесь
■ on { all server | database } – данная опция показывает, будет ли триггер действовать в пределах текущей базы данных или в пределах всего сервера SQL.
■ <ddl_trigger_option> - данная опция аналогична такой же опции для триггеров DML.
■ event_type – имя события, при наступлении которого должен быть запущен триггер. Список событий можно найти в документации. Например, ALTER_INDEX означает, что триггер будет запускаться при попытке изменить индекс.
■ event_group – можно указать целую группу событий, при наступлении которых должен быть запущен данный триггер. Список имен групп событий можно найти в документации. Например, DDL_TABLE_EVENTS означает группу событий, связанных с таблицами.
Поскольку остальные опции нам
уже знакомы, перейдем сразу к примеру (см. Листинг 3.85).
Листинг
3.85
create trigger tr1
on database
for DDL_TABLE_EVENTS –триггер на операции с таблицами
as
begin
rollback
transaction
raiserror('Операции над таблицами запрещены',16,3)
return
end
При работе с DDL триггерами удобно использовать функцию eventdata(). Данная функция, запущенная внутри триггера возвращает полную информацию о происшедшем событии. Особенностью функции является то, что информация, которую она возвращает, имеет структуру xml-документа.