Поделиться через

CREATE TRIGGER (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Создает триггер языка обработки данных, DDL или входа. Триггер — это особая разновидность хранимой процедуры, которая автоматически выполняется при возникновении события на сервере базы данных. Триггеры DML выполняются, когда пользователь пытается изменить данные с помощью событий языка обработки данных (DML). События DML — это INSERTили UPDATEDELETE операторы таблицы или представления. Эти триггеры срабатывают при запуске любого допустимого события независимо от наличия и числа затронутых строк таблицы. Дополнительные сведения см. в разделе 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

Триггер для инструкции INSERTUPDATEили DELETE таблицы или представления (триггер DML):

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

Триггер для INSERTUPDATEтаблицы или DELETE оператора (триггер DML в таблицах, оптимизированных для памяти):

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 ]

Триггер для триггера CREATE, , GRANTDENYREVOKEALTERDROPили UPDATE инструкции (триггер DDL):

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 ]

Триггер события LOGON (триггер входа):

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

Триггер для инструкции INSERTUPDATEили DELETE таблицы или представления (триггер DML):

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 ]

Триггер для триггера CREATE, , GRANTDENYREVOKEALTERDROPили UPDATE STATISTICS инструкции (триггер DDL):

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 ]

Аргументы

ИЛИ ALTER

Область применения: SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) и более поздних версий, а также база данных SQL Azure

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

schema_name

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

trigger_name

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

стол | вид

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

База данных

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

ALL SERVER

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

С ШИФРОВАНИЕМ

Скрывает текст инструкции CREATE TRIGGER . Использование WITH ENCRYPTION предотвращает публикацию триггера в рамках репликации SQL Server. WITH ENCRYPTION Не удается указать для триггеров СРЕДЫ CLR.

ВЫПОЛНИТЬ КАК

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

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

Дополнительные сведения см. в предложении EXECUTE AS.

NATIVE_COMPILATION

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

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

SCHEMABINDING

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

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

FOR | ПОСЛЕ

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

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

ВМЕСТО

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

В большинстве случаев можно определить один INSTEAD OF триггер для каждой INSERTUPDATEили DELETE инструкции в таблице или представлении. Вы также можете определить представления, в которых каждое представление имеет собственный 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.

С ДОБАВЛЕНИЕМ

Применимо: 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 представление каталога.

НЕ ДЛЯ РЕПЛИКАЦИИ

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

sql_statement

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

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

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

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

SELECT * FROM deleted;

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

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

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

Внимание

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

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

<method_specifier>

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

Примечание.

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

Примечания для триггеров DML

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

Вы можете указать первые и последние AFTER триггеры для запуска в таблице с помощью sp_settriggerorder. Для каждой таблицы можно указать только один первый и последний AFTER триггер, а DELETE также операцию в таблице.INSERTUPDATE Если в той же таблице есть другие 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() проверяет или UPDATEINSERT пытается использовать один столбец. 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, он не активирует триггер, так как операция не регистрирует отдельные удаления строк. Однако только те пользователи с разрешениями на выполнение TRUNCATE TABLE инструкции должны быть обеспокоены непреднамеренно обходом DELETE триггера таким образом.

Оператор WRITETEXT , зарегистрированный или не зарегистрированный, не активирует триггер.

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

  • ALTER DATABASE
  • CREATE 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, как и стандартные триггеры, запускают хранимые процедуры в ответ на какое-либо событие. Но, в отличие от стандартных триггеров, они не выполняются в ответ на UPDATEINSERTинструкции или DELETE инструкции в таблице или представлении. Вместо этого они обычно срабатывают в ответ на инструкции языка определения данных (DDL). Типы инструкций включают CREATE, , ALTER, GRANTDROP, DENY, REVOKEи UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

Внимание

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

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

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

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

Примечание.

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

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

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

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

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

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

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

Общие рекомендации по триггерам

Результаты возврата

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

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

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

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

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

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

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

  • Непрямая рекурсия: при косвенном рекурсии приложение обновляет таблицу T1. Этот триггер TR1запускает обновление таблицы T2. Затем активирует T2 и обновляет таблицу T1.

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

В следующем примере используется рекурсия косвенного и прямого триггера предполагается, что два триггера TR1 обновления и TR2определены в таблице T1. Активируйте рекурсивное обновление TR1 таблицы T1 . Оператор UPDATE выполняется каждый TR1 и TR2 один раз. Кроме того, запуск TR1 активирует выполнение TR1 (рекурсивно) и TR2. Вставленные и удаленные таблицы для определенного триггера содержат строки, соответствующие только UPDATE инструкции, которая вызвала триггер.

Примечание.

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

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

Если какой-либо из триггеров выполняется ROLLBACK TRANSACTIONнезависимо от уровня вложенности, больше триггеров не выполняются.

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

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

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

Первый AFTER триггер, вложенный внутри триггера INSTEAD OF , запускается, даже если параметр конфигурации вложенного сервера равен 0. Но в этом параметре более поздние 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

В. Использование триггера DML с сообщением электронной почты напоминания

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

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE 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

В. Использование триггера 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
        INNER 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;
    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

Д. Использование триггера 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".

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 имени входа, если в этом имени входа уже три сеанса пользователя, выполняющихся под этим именем входа. Перейдите <password> на надежный пароль.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' 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создается в примере D. Используйте триггер DDL с областью действия базы данных.

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

Связанный контент