CREATE TRIGGER (Transact-SQL)

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Создает триггер языка обработки данных, DDL или входа. Триггер — это особая разновидность хранимой процедуры, которая автоматически выполняется при возникновении события на сервере базы данных. Триггеры DML выполняются, когда пользователь пытается изменить данные с помощью событий языка обработки данных (DML). Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. Эти триггеры срабатывают при запуске любого допустимого события независимо от наличия и числа затронутых строк таблицы. Дополнительные сведения см. в разделе DML Triggers.

Триггеры DDL активируются в ответ на разные события языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции.

Триггеры входа могут срабатывать в ответ на событие LOGON, которое возникает при создании пользовательского сеанса. Вы можете создавать триггеры непосредственно из инструкций Transact-SQL или методов сборок, созданных в среде CLR платформы Microsoft .NET Framework и переданных в экземпляр SQL Server. SQL Server позволяет создавать несколько триггеров для любой конкретной инструкции.

Важно!

Вредоносный программный код внутри триггеров может быть запущен с расширенными правами доступа. Дополнительные сведения о том, как уменьшить эту угрозу, см. в статье Управление безопасностью триггеров.

Примечание.

В этой статье рассматривается интеграция среды CLR .NET Framework с SQL Server. Интеграция со средой CLR не применяется к базе данных SQL Azure.

Соглашения о синтаксисе Transact-SQL

Синтаксис SQL Server

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] 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 [ ; ] > }  
  
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name  
  

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  
  
<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]  
  

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  
  
CREATE [ OR ALTER ] TRIGGER 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 >  [ ; ] }  
  
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  

-- Trigger on a LOGON event (Logon Trigger)  
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
  
<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

Синтаксис базы данных SQL Azure

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
 [ WITH <dml_trigger_option> [ ,...n ] ]   
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }  
  
<dml_trigger_option> ::=   
        [ EXECUTE AS Clause ]  
  

-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { DATABASE }   
 [ WITH <ddl_trigger_option> [ ,...n ] ]   
{ FOR | AFTER } { event_type | event_group } [ ,...n ]   
AS { sql_statement  [ ; ] [ ,...n ]  [ ; ] }  
  
<ddl_trigger_option> ::=   
    [ EXECUTE AS Clause ]

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

OR ALTER
Применимо к: База данных SQL Azure, SQL Server (начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1).

Условно изменяет триггер только в том случае, если он уже существует.

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

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

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

DATABASE
Применяет область действия триггера DDL к текущей базе данных. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении в базе данных события типа event_type или event_group.

ALL SERVER
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Применяет область действия триггера DDL или триггера входа к текущему серверу. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении на текущем сервере события типа event_type или event_group.

WITH ENCRYPTION
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Маскирует текст инструкции CREATE TRIGGER. Использование WITH ENCRYPTION предотвращает публикацию триггера в рамках реплика sql Server. Параметр WITH ENCRYPTION нельзя указать для триггеров CLR.

EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет контролировать, какую учетную запись пользователя использует экземпляр SQL Server для проверки разрешений на любые объекты базы данных, на которые ссылается триггер.

Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти.

Дополнительные сведения см. в разделе EXECUTE AS (Transact-SQL).

NATIVE_COMPILATION
Указывает, что триггер компилируется в собственном коде.

Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти.

SCHEMABINDING
Гарантирует, что используемые триггером таблицы ну будут удалены или изменены.

Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти, и не поддерживается для триггеров в обычных таблицах.

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

Нельзя определить триггеры AFTER для представлений.

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

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

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

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Определяет инструкции изменения данных, при применении которых к таблице или представлению срабатывает триггер DML. Укажите хотя бы один вариант. В определении триггера разрешены любые сочетания вариантов в любом порядке.

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

WITH APPEND
Применимо: SQL Server 2008 (10.0.x) до SQL Server 2008 R2 (10.50.x).

Указывает, что требуется добавить триггер существующего типа. Аргумент WITH APPEND нельзя использовать для триггеров INSTEAD OF и в тех случаях, когда явно указан триггер AFTER. Для сохранения обратной совместимости аргумент WITH APPEND следует использовать только при указании параметра FOR без INSTEAD OF или AFTER. Нельзя указать WITH APPEND, если используется EXTERNAL NAME (то есть триггер является триггером CLR).

event_type
Имя языкового события Transact-SQL, запуск которого вызывает срабатывание триггера DDL. Список событий, которые могут быть использованы в триггерах DDL, приведен в разделе DDL-события.

event_group
Имя предварительно определенной группы относящихся к языку событий Transact-SQL. Триггер DDL срабатывает после запуска любого языкового события Transact-SQL, которое относится к группе event_group. Список групп событий, которые могут быть использованы в триггерах DDL, приведен в разделе Группы DDL-событий.

После завершения инструкции CREATE TRIGGER параметр event_group работает в режиме макроса, добавляя охватываемые им типы события в представление каталога sys.trigger_events.

NOT FOR REPLICATION
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

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

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

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

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

SELECT * FROM deleted;

Дополнительные сведения см. в разделе Использование таблиц inserted и deleted.

Триггеры DDL и триггеры входа собирают сведения о запускающих событиях с помощью функции EVENTDATA (Transact-SQL). Дополнительные сведения см. в разделе Использование функции EVENTDATA.

SQL Server позволяет обновлять столбцы текста, ntext или изображения с помощью триггера INSTEAD OF в таблицах или представлениях.

Важно!

Типы данных ntext, text и image будут удалены в следующей версии Microsoft SQL Server. Следует избегать использования этих типов данных при новой разработке и запланировать изменение приложений, использующих их в настоящий момент. Вместо них следует использовать типы данных nvarchar(max), varchar(max)и varbinary(max) . Как триггеры AFTER, так и триггеры INSTEAD OF поддерживают данные типов varchar(MAX), nvarchar(MAX) и varbinary(MAX) в таблицах inserted и deleted.

Для триггеров в таблицах, оптимизированных для памяти, единственной инструкцией sql_statement, разрешенной на верхнем уровне, является блок ATOMIC. В блоке ATOMIC допускается только T-SQL, разрешенный в процедурах, компилируемых в собственном коде.

<>method_specifier применимо к SQL Server 2008 (10.0.x) и более поздним версиям.

Указывает метод сборки для связывания с CLR-триггером. Этот метод не должен принимать аргументы и возвращать значения void. class_name должен быть допустимым идентификатором SQL Server и должен существовать как класс в сборке с видимостью сборки. Если класс имеет имя, содержащее точки (.) для разделения частей пространства имен, имя класса должно быть заключено в квадратные скобки ([ ]) или двойные кавычки (" "). Класс не может быть вложенным.

Примечание.

По умолчанию возможность выполнения кода СРЕДЫ CLR в SQL Server отключена. Можно создавать, изменять и удалять объекты базы данных, ссылающиеся на модули управляемого кода, но эти ссылки не выполняются в экземпляре SQL Server, если параметр clr не включен с помощью sp_configure.

Примечания о триггерах DML

Триггеры DML часто используются для применения бизнес-правил и обеспечения целостности данных. SQL Server предоставляет декларативную целостность ссылок (DRI) с помощью инструкций ALTER TABLE и CREATE TABLE. Но декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после выполнения триггера INSTEAD OF, но до выполнения триггера AFTER. Если будет обнаружено нарушение ограничений, для триггера INSTEAD OF выполняется откат, а триггер AFTER не срабатывает.

Вы можете указать, какой триггер AFTER будет выполняться для таблицы первым, а какой последним, с помощью sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если для таблицы определены другие триггеры AFTER, они выполняются в случайном порядке.

Если инструкция ALTER TRIGGER изменяет первый или последний триггер, для него удаляется метка первого или последнего триггера и порядок сортировки нужно установить заново с помощью sp_settriggerorder.

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

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

Если определенный для представления триггер INSTEAD OF выполняет по отношению к этому представлению какую-либо инструкцию, которая обычно приводит к срабатыванию триггера INSTEAD OF, триггер рекурсивно не вызывается. Вместо этого инструкция выполняет изменение базовых таблиц, на которых основано представление. В данном случае определение представления должно удовлетворять всем ограничениям, установленным для обновляемых представлений. Определение обновляемых представлений см. в разделе Изменение данных через представление.

Для примера предположим, что для представления определен триггер INSTEAD OF UPDATE. Этот триггер выполняет инструкцию UPDATE в том же представлении, и в этом случае выполненная в триггере INSTEAD OF инструкция UPDATE не приводит к новому срабатыванию триггера. Выполняемая в триггере инструкция UPDATE обрабатывает представление так, как если бы у него не было триггера INSTEAD OF. Столбцы, измененные с помощью инструкции UPDATE, должны принадлежать одной базовой таблице. Каждая модификация базовой таблицы вызывает применение последовательности ограничений и взвод триггеров AFTER, определенных для данной таблицы.

Проверка действий инструкций UPDATE или INSERT на указанные столбцы

Триггер Transact-SQL можно настроить для выполнения некоторых действий при изменении определенных столбцов в инструкциях UPDATE или INSERT. Используйте для этих целей в теле триггера конструкции UPDATE() или COLUMNS_UPDATED. Конструкция UPDATE() проверяет действие инструкций UPDATE или INSERT на одном столбце. COLUMNS_UPDATED проверяет выполнение операций UPDATE или INSERT над множеством столбцов. Эта функция возвращает битовый шаблон с информацией о том, какие столбцы были вставлены или обновлены.

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

Инструкция CREATE TRIGGER должна быть первой инструкцией в пакете и может применяться только к одной таблице.

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

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

Одно и то же действие триггера может быть определено более чем для одного действия пользователя (например, INSERT и UPDATE) в одной и той же инструкции CREATE TRIGGER.

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

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

Во время срабатывания триггера результаты возвращаются вызывающему приложению так же, как и в случае с хранимыми процедурами. Чтобы при срабатывании триггера в приложение не возвращались результаты, не включайте в триггер инструкции SELECT, которые возвращают результаты или инструкции присвоения переменных. Если триггер содержит инструкции SELECT, которые возвращают результаты пользователю, либо инструкции присвоения значения переменным, для него требуется особый подход. Возвращаемые результаты нужно будет передать в каждое приложение, которому разрешено изменять таблицу триггера. Если в триггере происходит присвоение переменной, следует использовать инструкцию SET NOCOUNT в начале триггера, чтобы предотвратить возвращение каких-либо результирующих наборов.

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

Инструкция WRITETEXT (с ведением журнала и без него) не запускает триггеры.

Следующие инструкции языка Transact-SQL не разрешены в триггерах DML:

  • ALTER DATABASE
  • СОЗДАТЬ БАЗУ ДАННЫХ
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

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

  • CREATE INDEX (в т.ч CREATE SPATIAL INDEX и CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER TABLE при использовании для выполнения следующих действий:
    • Добавление, изменение или удаление столбцов.
    • Переключение секций.
    • Добавление или удаление ограничений PRIMARY KEY и UNIQUE.

Примечание.

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

Оптимизация триггеров DML

Триггеры работают в транзакциях (в том числе неявных) и блокируют ресурсы на весь период, в течение которого транзакция открыта. Такая блокировка действует, пока транзакция не будет зафиксирована (COMMIT) или отклонена (ROLLBACK). Чем дольше выполняется триггер, тем выше вероятность блокирования другого процесса. Старайтесь создавать такие триггеры, которые выполняются максимально быстро. Один из способов сократить время выполнения — освободить триггер, если инструкция DML изменяет 0 строк.

Чтобы освободить триггер для команды, которая не изменяет ни одной строки, используйте системную переменную ROWCOUNT_BIG.

В следующем фрагменте кода T-SQL триггер освобождается для команды, которая не изменяет ни одной строки. Этот код нужно добавить в начале каждого триггера DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Примечания о триггерах DDL

Триггеры DDL, как и стандартные триггеры, запускают хранимые процедуры в ответ на какое-либо событие. В отличие от стандартных триггеров, они не срабатывают при выполнении инструкций UPDATE, INSERT или DELETE для таблицы или представления. Вместо этого они обычно срабатывают в ответ на инструкции языка определения данных (DDL). К ним относятся инструкции CREATE, ALTER, DROP, GRANT, DENY, REVOKE и UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

Важно!

Протестируйте триггеры DDL, чтобы получить ответ на выполнение системных хранимых процедур. Например, инструкция CREATE TYPE и хранимые процедуры sp_addtype и sp_rename вызовут срабатывание триггера DDL, созданного для события CREATE_TYPE.

Дополнительные сведения о триггерах DDL см. в разделе Триггеры DDL.

Триггеры DDL не срабатывают в ответ на события, влияющие на локальные или глобальные временные таблицы и хранимые процедуры.

В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Это означает, что для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями, как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога. Дополнительные сведения см. в статье Получение сведений о триггерах DDL.

Примечание.

Триггеры DDL сервера появляются в обозревателе объектов среды SQL Server Management Studio в папке Triggers . Эта папка находится под папкой Объекты сервера . Триггеры DDL, доступные в области базы данных, находятся в папке Триггеры базы данных. Эта папка находится в папке Программирование соответствующей базы данных.

Триггеры входа

Триггеры входа выполняют хранимые процедуры в ответ на событие LOGON. Это событие происходит при установке сеанса пользователя с экземпляром SQL Server. Триггеры входа срабатывают после проверки подлинности при входе, но перед тем, как устанавливается пользовательский сеанс. Таким образом, все сообщения, поступающие внутри триггера, которые обычно будут обращаться к пользователю, например сообщения об ошибках и сообщениях из инструкции PRINT, перенаправляются в журнал ошибок SQL Server. Дополнительные сведения см. в разделе Триггеры входа.

Если проверка подлинности завершается сбоем, триггеры входа не срабатывают.

Распределенные транзакции не поддерживаются в триггерах входа. Если триггер содержит распределенную транзакцию, при его срабатывании возвращается ошибка 3969.

Отключение триггера входа

Триггер входа может эффективно предотвратить успешные подключения к ядро СУБД для всех пользователей, включая членов предопределенных ролей сервера sysadmin. Если триггер входа запрещает подключения, члены предопределенной роли сервера sysadmin могут подключаться с помощью выделенного подключения администратора или запуска ядро СУБД в минимальном режиме конфигурации (-f). Дополнительные сведения см. в разделе Параметры запуска службы Database Engine.

Общие соглашения о триггерах

Возвращаемые результаты

Возможность возвращать результаты из триггеров будет исключена из следующей версии SQL Server. Триггеры, которые возвращают результирующие наборы, могут привести к непредвиденному поведению в приложениях, не предназначенных для работы с ними. Старайтесь не возвращать результирующие наборы из триггеров во всех новых проектах и постепенно исправляйте такое поведение в существующих приложениях. Чтобы триггеры не возвращали результирующие наборы, для параметра disallow results from triggers необходимо установить значение 1.

Триггеры входа всегда запрещают возврат результирующих наборов, и это нельзя изменить. Если триггер входа формирует результирующий набор, его не удастся запустить и любая попытка входа, при которой срабатывает такой триггер, будет запрещена.

Несколько триггеров

SQL Server позволяет создавать несколько триггеров для каждого события DML, DDL или LOGON. Например, если CREATE TRIGGER FOR UPDATE выполняется для таблицы, которая уже имеет триггер UPDATE, будет создан дополнительный триггер для обновлений. В более ранних версиях SQL Server для каждого события изменения данных INSERT, UPDATE или DELETE допускается только один триггер для каждой таблицы.

Рекурсивные триггеры

SQL Server также поддерживает рекурсивное вызов триггеров, если параметр RECURSIVE_TRIGGERS включен с помощью ALTER DATABASE.

В рекурсивных триггерах могут возникать следующие типы рекурсии:

  • Косвенная рекурсия

    При косвенной рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T2. Затем срабатывает триггер T2, который обновляет таблицу T1.

  • Прямая рекурсия

    При прямой рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T1. Поскольку таблица T1 уже была обновлена, триггер TR1 срабатывает снова и т. д.

В следующем примере используются оба типа рекурсий: прямая и косвенная. Допустим, для таблицы T1 определены два триггера: TR1 и TR2. Триггер TR1 рекурсивно обновляет таблицу T1. Инструкция UPDATE выполняет TR1 и TR2 по одному разу. Кроме того, запуск TR1 вызывает выполнение триггеров TR1 (рекурсивно) и TR2. В таблицах inserted и deleted триггера содержатся строки, которые относятся только к инструкции UPDATE, вызвавшей срабатывание триггера.

Примечание.

Описанная ситуация имеет место только в том случае, если настройка RECURSIVE_TRIGGERS включена с помощью инструкции ALTER DATABASE. Не существует определенного порядка для выполнения нескольких триггеров, определенных для одного события. Каждый триггер должен быть самодостаточным.

Отключение настройки RECURSIVE_TRIGGERS предотвращает выполнение только прямых рекурсий. Чтобы отключить косвенную рекурсию, с помощью хранимой процедуры sp_configure присвойте параметру сервера nested triggers значение 0.

Если один из триггеров (независимо от уровня вложенности) выполняет инструкцию ROLLBACK TRANSACTION, никакие другие триггеры не выполняются.

Вложенные триггеры

Для триггеров допускается не более 32 уровней вложенности. Если триггер изменяет таблицу, для которой определен другой триггер, активируется этот второй триггер. Он может, в свою очередь, вызвать третий триггер и так далее. Если любой из триггеров в цепочке отключает бесконечный цикл, то уровень вложенности превышает допустимый предел, и срабатывание триггера отменяется. Когда триггер Transact-SQL запускает управляемый код ссылкой на подпрограмму CLR, тип, или статистическое выражение, такая ссылка считается одним из 32 допустимых уровней вложенности. Это ограничение не распространяется на методы, вызываемые из управляемого кода.

Чтобы отменить вложенные триггеры, присвойте значение 0 параметру nested triggers хранимой процедуры sp_configure. Конфигурация по умолчанию поддерживает вложенные триггеры. Если вложенные триггеры отключены, отключаются и рекурсивные триггеры, независимо от значения RECURSIVE_TRIGGERS, которое установлено с помощью инструкции ALTER DATABASE.

Первый триггер AFTER, вложенный в триггер INSTEAD OF, срабатывает даже в том случае, если для сервера настроен нулевой уровень вложенных триггеров. Но в таком случае остальные триггеры AFTER не сработают. Проверьте все приложения на наличие вложенных триггеров, чтобы определить соблюдение бизнес-правил, прежде чем устанавливать значение 0 для параметра nested triggers (вложенные триггеры). Если правила не соблюдаются, внесите соответствующие изменения.

Отложенная интерпретация имен

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

Разрешения

Чтобы создать триггер DML, ему нужно разрешение ALTER для таблицы или представления, для которых создается этот триггер.

Чтобы создать триггер DDL в области сервера (ON ALL SERVER) или триггера входа, требуется разрешение CONTROL SERVER для этого сервера. Чтобы создать триггер DDL в области базы данных (ON DATABASE), требуется разрешение ALTER ANY DATABASE DDL TRIGGER для текущей базы данных.

Примеры

А. Использование триггера DML с предупреждающим сообщением

Следующий триггер DML выводит сообщение клиенту, когда любой пользователь пытается добавить или изменить данные в таблице в Customer базе данных AdventureWorks2022.

CREATE TRIGGER reminder1  
ON Sales.Customer  
AFTER INSERT, UPDATE   
AS RAISERROR ('Notify Customer Relations', 16, 10);  
GO

B. Использование триггера DML с предупреждающим сообщением, отправляемым по электронной почте

В следующем примере указанному пользователю (MaryM) по электронной почте отправляется сообщение при изменении таблицы Customer.

CREATE TRIGGER reminder2  
ON Sales.Customer  
AFTER INSERT, UPDATE, DELETE   
AS  
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'AdventureWorks2022 Administrator',  
        @recipients = 'danw@Adventure-Works.com',  
        @body = 'Don''t forget to print a report for the sales force.',  
        @subject = 'Reminder';  
GO

C. Использование триггера DML AFTER для принудительного применения бизнес-правил между таблицами PurchaseOrderHeader и Vendor

Так ограничения CHECK ссылаются только на столбцы, для которых определено ограничение на уровне таблицы или столбца, все межтабличные ограничения (в нашем примере это бизнес-правила) следует определять как триггеры.

В следующем примере создается триггер DML в AdventureWorks2022 базе данных. Этот триггер проверяет оценку кредитоспособности для поставщика (оценка не равна 5) при попытке добавить новый заказ на покупку в таблицу PurchaseOrderHeader. Чтобы получить оценку кредитоспособности поставщика, требуется ссылка на таблицу Vendor. Если рейтинг кредитоспособности слишком низок, поступает сообщение об этом и вставка не выполняется.

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO

D. Использование триггера DDL уровня базы данных

В следующем примере триггер DDL используется для предотвращения удаления синонимов в базе данных.

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_SYNONYM  
AS   
IF (@@ROWCOUNT = 0)
RETURN;
   RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1)  
   ROLLBACK  
GO  
DROP TRIGGER safety  
ON DATABASE;  
GO

Д. Использование триггера DDL уровня сервера

В следующем примере триггер DDL используется для вывода сообщения при возникновении на данном экземпляре сервера любого из событий CREATE DATABASE, а функция EVENTDATA используется для получения текста соответствующей инструкции на языке Transact-SQL. Примеры использования функции EVENTDATA в триггерах DDL см. в разделе Использование функции EVENTDATA.

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

CREATE TRIGGER ddl_trig_database   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
    PRINT 'Database Created.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
GO  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  
GO

F. Использование триггера входа

В следующем примере триггера входа запрещается попытка войти в SQL Server в качестве члена имени входа login_test , если под этим именем входа уже три сеанса пользователя.

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

USE master;  
GO  
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,  
    CHECK_EXPIRATION = ON;  
GO  
GRANT VIEW SERVER STATE TO login_test;  
GO  
CREATE TRIGGER connection_limit_trigger  
ON ALL SERVER WITH EXECUTE AS 'login_test'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'login_test' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'login_test') > 3  
    ROLLBACK;  
END;

G. Просмотр событий, вызвавших срабатывание триггера

В следующем примере выполняются запросы к представлениям каталога sys.triggers и sys.trigger_events с целью определения, какие события языка Transact-SQL вызывали срабатывание триггера safety. Триггер safety, созданный в примере Г, приведен выше.

SELECT TE.*  
FROM sys.trigger_events AS TE  
JOIN sys.triggers AS T ON T.object_id = TE.object_id  
WHERE T.parent_class = 0 AND T.name = 'safety';  
GO

См. также

Инструкция ALTER TABLE (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL)
CREATE TABLE (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
TRIGGER_NESTLEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sp_helptext (Transact-SQL)
sp_rename (Transact-SQL)
sp_settriggerorder (Transact-SQL)
UPDATE() (Transact-SQL)
Получение сведений о триггерах DML
Получение сведений о триггерах DDL
sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)