DML-триггеры

В отличие от хранимых процедур и функций DML-триггеры не являются отдельными объектами, и их нельзя исполнить напрямую. DML-триггер привязан к конкретной таблице или представлению и определяется для конкретного события. При возникновении этого события SQL Server автоматически исполняет код триггера, то есть запускает триггер. Событиями, вызывающими запуск триггера, являются операции INSERT, UPDATE и DELETE.

Триггеры можно запускать в двух режимах: AFTER и INSTEAD OF. Триггер AFTER запускается после успешного завершения SQL-сервером всех операций. Например, при вставке строки в таблицу триггер, определенный для операции INSERT, запускается только после того, как строка пройдет все ограничения, наложенные на таблицу. Если любое из наложенных ограничений не проходит проверку, SQL Server не исполнит триггер. Триггеры AFTER могут быть определены только для таблиц. Для представления или таблицы можно создать любое количество триггеров AFTER.

Триггер INSTEAD OF выполняется вместо операции, вызвавшей запуск триггера. Триггеры INSTEAD OF могут создаваться для представлений и таблиц. Чаще всего триггеры INSTEAD OF применяются к представлениям с целью обновления нескольких базовых таблиц через представление. Для каждого события INSERT, UPDATE или DELETE для представления или таблицы можно создать только один триггер INSTEAD OF.

Общий синтаксис Transact-SQL для создания DML-триггера имеет следующий вид:

CREATE TRIGGER [ schema_name. ]trigger_name

ON { table | view }

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }

AS { sql_statement [; ] [,...n ]

Аргументы

schema_name

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

trigger_name

Имя триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов — за исключением того, что trigger_name не может начинаться с символов # или ##.

table | view

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

FOR | AFTER

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

Если единственным заданным ключевым словом является FOR, аргумент AFTER используется по умолчанию.

Триггеры AFTER не могут быть определены на представлениях.

INSTEAD OF

Указывает, что триггер DML срабатывает вместо инструкции SQL, используемой триггером, переопределяя таким образом действия выполняемой инструкции триггера. Аргумент INSTEAD OF не может быть указан для триггеров DDL или триггеров входа.

На каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF. Однако можно определить представления на представлениях, где у каждого представления есть собственный триггер INSTEAD OF.

Триггеры INSTEAD OF не разрешены для обновляемых представлений, использующих параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к обновляемому представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF.

{ [ DELETE ] [, ] [ INSERT ] [, ] [ UPDATE ] }

Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.

Для триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON DELETE. Точно так же параметр UPDATE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON UPDATE.

sql_statement

Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события — DML, DDL или событие входа — вызывают срабатывание триггера.

Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.

Триггеры могут содержать любое количество инструкций языка Transact-SQL любого типа, за некоторыми исключениями. Дополнительные сведения см. в разделе «Примечания». Триггеры разработаны для контроля или изменения данных на основании инструкций модификации или определения данных; они не возвращают пользователю никаких данных. Инструкции языка Transact-SQL в составе триггера часто содержат выражения языка управления потоком.

Триггеры DML используют логические (концептуальные) таблицы deleted и inserted. По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя. Например, для запроса всех значений таблицы deleted можно использовать инструкцию:

SELECT *

FROM deleted

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

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

■ Нельзя создавать, изменять, удалять резервные копии или восстанавливать базы данных.

■ В таблице, которая привела к запуску триггера, не допускаются такие структурные изменения, как CREATE/ALTER/DROP INDEX, ALTER/DROP TABLE и так далее.

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

Триггеры могут использоваться для обеспечения ссылочной целостности. Однако их не следует применять вместо декларативной ссылочной целостности (DRI) посредством ограничения FOREIGN KEY. DRI реализуется при внесении модификации до того, как изменение станет частью таблицы, и это гораздо эффективнее, чем исполнение кода триггера. Но ограничения FOREING KEY нельзя определять применительно к таблицам в разных базах данных. Поэтому для обеспечения ссылочной целостности таблиц в разных баз данных следует использовать триггеры.

Триггеры имеют доступ к двум специальным динамически создаваемым таблицам INSERTED и DELETED. Эти таблицы видны только внутри триггера и недоступны другим конструкциям, таким как хранимые процедуры и функции. Структура таблиц INSERTED и DELETED полностью соответствует определению столбца таблицы, для которой был создан триггер. Поэтому на столбцы можно ссылаться по тому же имени, что и таблица, для которой был определен триггер.

При исполнении инструкции INSERT таблица INSERTED содержит все добавленные в таблицу строки, тогда как в таблице DELETED нет ни одной строки. При исполнении инструкции DELETE таблица DELETED содержит все удаленные из таблицы строки, тогда как в таблице INSERTED нет ни одной строки. При исполнении инструкции UPDATE таблица INSERTED содержит образы строк после обновления, а таблица DELETED — образы строк до обновления. Образ строки до обновления — это просто копия строки до исполнения инструкции UPDATE. Образ строки после обновления отображает данные в строке после изменения соответствующих значений посредством инструкции UPDATE.

У каждого триггера должно быть имя, соответствующее правилам для идентификаторов объектов.

В предложении ON указывается таблица или представление, для которого создается триггер. При удалении таблицы или представления все созданные для нее триггеры также будут удалены.

При желании для триггера можно задать несколько событий.

В предложении AS указывается код, который нужно исполнить при запуске триггера. Рассмотрим применение триггеров на примере.

Задача:

Необходимо создать триггер запрещающий удаление, изменение или добавление нескольких учётных записей администратора.

Этот триггер привязан к таблице dbo.USER

Рис.2. Таблица dbo.USER

CREATE TRIGGER [dbo].[AdminTrigger]

ON [dbo].[USER]

instead of INSERT,DELETE,UPDATE

AS

BEGIN

declare @oldlogin varchar(50)

declare @newlogin varchar(50)

select @newlogin=[login] from inserted

-- узнаём значение поля [login] в таблице inserted и присваиваем

--его переменной @newlogin

if(@newlogin='Administrator')

begin

-- если значение переменной @newlogin='Administrator' выходим из

--триггера

return

end

else

begin

-- узнаём значение поля [login] в таблице deleted и присваиваем

-- его переменной @oldlogin

select @oldlogin=[login] from deleted

if(@oldlogin='Administrator')

begin

-- если значение переменной @oldlogin='Administrator' выходим из

--триггера

return

end

else

begin

if(@newlogin is null and @oldlogin is not null)

begin

-- Т.к. значения логина <> 'Administrator', то его можно

-- удалить, если было удаление

delete from

dbo.[USER] where ID in

(select ID from deleted)

end

if(@oldlogin is null and @newlogin is not null)

begin

--Если была вставка.

insert into dbo.[USER]

([LOGIN],[PASSWORD],

[FIRSTNAME],[MIDDLENAME],[LASTNAME],

[DELETED_STATUS],[FIRM_ID],[DEPARTMENT_ID],

[E_MAIL],[LAST_UPDATE],[PASSPORT_DATA],

[USER_NEAREST_REGION])

select [LOGIN],[PASSWORD],

[FIRSTNAME],[MIDDLENAME],[LASTNAME],

[DELETED_STATUS],[FIRM_ID],[DEPARTMENT_ID],

[E_MAIL],GETDATE(),[PASSPORT_DATA],

[USER_NEAREST_REGION]

from inserted

end

if(@oldlogin is not null and @newlogin is not null)

begin

--Если было обновление

declare @id int

declare @password varbinary(8000)

declare @firstname varchar(50)

declare @middlename varchar(50)

declare @lastname varchar(50)

declare @deleted_status bit

declare @firm_id int

declare @department_id bigint

declare @e_mail varchar(50)

declare @last_update datetime

declare @passport_data varchar(20)

declare @user_nearest_region int

select @id=[ID],

@password=[PASSWORD],

@firstname=[FIRSTNAME],

@middlename=[MIDDLENAME],

@lastname=LASTNAME,

@deleted_status=[DELETED_STATUS],

@firm_id=[FIRM_ID],

@department_id=[DEPARTMENT_ID],

@e_mail=[E_MAIL],

@last_update=[LAST_UPDATE],

@passport_data=[PASSPORT_DATA],

@user_nearest_region=[USER_NEAREST_REGION]

from inserted

update dbo.[USER]

set

[password]=@password,

firstname=@firstname,

middlename=@middlename,

lastname=@lastname,

deleted_status=@deleted_status,

firm_id=@firm_id,

department_id=@department_id,

e_mail=@e_mail,

last_update=@last_update,

passport_data=@passport_data,

user_nearest_region=@user_nearest_region

where ID=@id

end

end

end

END

Чтобы удалить триггер, необходимо выполнить команду:

DROP TRIGGER <TRIGGER_NAME>

Чтобы изменить триггер, необходимо выполнить команду:

ALTER TRIGGER <TRIGGER_NAME>

Дата добавления: 2014-01-04