Почему вызов триггеров осуществляется автоматически
Перейти к содержимому

Почему вызов триггеров осуществляется автоматически

  • автор:

Триггеры DML

Триггеры DML — это хранимые процедуры особого типа, автоматически вступающие в силу, если происходит событие языка обработки данных DML, которое затрагивает таблицу или представление, определенное в триггере. События DML включают инструкции INSERT, UPDATE или DELETE. Триггеры DML можно использовать для применения бизнес-правил и целостности данных, запроса других таблиц и включения сложных инструкций Transact-SQL. Триггер и инструкция, при выполнении которой он срабатывает, считаются одной транзакцией, которую можно откатить назад внутри триггера. При обнаружении серьезной ошибки (например, нехватки места на диске) вся транзакция автоматически откатывается назад.

Преимущества триггеров DML

Триггеры DML аналогичны ограничениям в том, что могут предписывать целостность сущностей или целостность домена. Вообще говоря, целостность сущностей должна всегда предписываться на самом нижнем уровне с помощью индексов, являющихся частью ограничений PRIMARY KEY и UNIQUE или создаваемых независимо от ограничений. Целостность домена должна быть предписана через ограничения CHECK, а ссылочная целостность — через ограничения FOREIGN KEY. Триггеры DML наиболее полезны в тех случаях, когда функции ограничений не удовлетворяют функциональным потребностям приложения.

В следующем списке приведено сравнение триггеров DML с ограничениями и указано, чем триггеры DML лучше ограничений.

  • Триггеры DML позволяют каскадно проводить изменения через связанные таблицы в базе данных; но эти изменения могут осуществляться более эффективно с использованием каскадных ограничений ссылочной целостности. Ограничения FOREIGN KEY могут проверить значения столбца только на предмет точного совпадения со значениями другого столбца, за исключением случаев, когда с помощью предложения REFERENCES задаются каскадные ссылочные действия.
  • Для предотвращения случайных или неверных операций INSERT, UPDATE и DELETE и реализации других более сложных ограничений, чем те, которые определены при помощи ограничения CHECK. В отличие от ограничений CHECK, DML-триггеры могут ссылаться на столбцы других таблиц. Например, триггер может использовать инструкцию SELECT для сравнения вставленных или обновленных данных и выполнения других действий, например изменения данных или отображения пользовательского сообщения об ошибке.
  • Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия.
  • Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.
  • Ограничения могут сообщать об ошибках только с помощью соответствующих стандартных системных сообщений. Если для пользовательского приложения требуются более сложные методы управления ошибками и, соответственно, пользовательские сообщения, то необходимо использовать триггер.
  • При использовании триггеров DML может произойти откат изменений, нарушающих ссылочную целостность, что приводит к запрету модификации данных. Подобные триггеры могут применяться при изменении внешнего ключа в случаях, когда новое значение не соответствует первичному ключу. Обычно в указанных случаях используются ограничения FOREIGN KEY.
  • Если в таблице триггеров существуют ограничения, то их проверка осуществляется между выполнением триггеров INSTEAD OF и AFTER. В случае нарушения ограничений выполняется откат действий триггеров INSTEAD OF, а триггер AFTER не срабатывает.

Типы триггеров DML

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

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

Функциональность триггеров AFTER и INSTEAD OF сравнивается в следующей таблице.

Функция Триггер AFTER Триггер INSTEAD OF
Применимость Таблицы Таблицы и представления
Количество триггеров на таблицу или представление Несколько триггеров на одно запускающее триггеры действие (UPDATE, DELETE или INSERT). Один триггер на одно запускающее триггеры действие (UPDATE, DELETE или INSERT).
Каскадные ссылки Нет ограничений. Триггеры INSTEAD OF UPDATE и DELETE нельзя определять для таблиц, на которые распространяются каскадные ограничения ссылочной целостности.
Выполнение После:

Декларативные ссылочные действия.

Создание таблицinserted и deleted .

Вместо: действие, запускающее триггер

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

Связанные задачи

Задача Раздел
Описывает, как создать триггер DML. Создание триггеров DML
Описывает, как создать триггер CLR. Создание триггеров CLR
Описывает, как создать триггер DML для выполнения и однострочных, и многострочных операций модификации данных. Создание триггеров DML для обработки нескольких строк данных
Описывает, как вкладывать триггеры. Создание вложенных триггеров
Описывает, как указывать порядок, в котором активируются триггеры AFTER. Указание первого и последнего триггеров
Описывает, как использовать специальные таблицы inserted и deleted в коде триггера. Использование вставленных и удаленных таблиц
Описывает, как изменить или переименовать триггер DML. Изменение или переименование триггеров DML
Описывает, как просматривать сведения о триггерах DML. Получение сведений о триггерах DML
Описывает, как удалять или отключать триггеры DML. Удаление или отключение триггеров DML
Описывает, как управлять безопасностью триггеров. Управление безопасностью триггеров

Триггеры

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

Формальное определение триггера:

CREATE TRIGGER имя_триггера ON  [INSERT | UPDATE | DELETE] AS выражения_sql

Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.

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

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

  • AFTER : выполняется после выполнения действия. Определяется только для таблиц.
  • INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .

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

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );

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

USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id FROM inserted)

Допустим, в таблице Products хранятся данные о товарах. Но цена товара нередко содержит различные надбавки типа налога на добавленную стоимость, налога на добавленную коррупцию и так далее. Человек, добавляющий данные, может не знать все эти тонкости с налоговой базой, и он определяет чистую цену. С помощью триггера мы можем поправить цену товара на некоторую величину.

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.

И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:

Триггеры в MS SQL Server

Удаление триггера

Для удаления триггера необходимо применить команду DROP TRIGGER :

DROP TRIGGER Products_INSERT_UPDATE

Отключение триггера

Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :

DISABLE TRIGGER Products_INSERT_UPDATE ON Products

А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :

ENABLE TRIGGER Products_INSERT_UPDATE ON Products

CREATE TRIGGER (Transact-SQL)

Создает триггер языка обработки данных, 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.

Синтаксис 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 [ . n ] ] < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >[ WITH APPEND ] [ NOT FOR REPLICATION ] AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= 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 [ . n ] ] < FOR | AFTER > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >AS < sql_statement [ ; ] [ . n ] > ::= [ 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 [ . n ] ] < FOR | AFTER > < event_type | event_group >[ . n ] AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME < method specifier >[ ; ] > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] 
-- Trigger on a LOGON event (Logon Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH [ . n ] ] < FOR| AFTER >LOGON AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME < method specifier >[ ; ] > ::= [ 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 [ . n ] ] < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >AS < sql_statement [ ; ] [ . n ] [ ; ] >> ::= [ 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 [ . n ] ] < FOR | AFTER > < event_type | event_group >[ . n ] AS < sql_statement [ ; ] [ . n ] [ ; ] > ::= [ 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; 

Триггеры 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 

    36.1. Обзор поведения триггеров#

    Триггер — это спецификация, согласно которой база данных должна автоматически выполнять определенную функцию при выполнении определенного типа операции. Триггеры могут быть привязаны к таблицам (разделенным или нет), представлениям и внешним таблицам. На таблицах и внешних таблицах можно определить триггеры для выполнения операций INSERT , UPDATE или DELETE либо перед, либо после операции, либо один раз для каждой измененной строки, либо один раз для SQL оператора. Триггеры UPDATE могут также быть настроены на срабатывание только в случае, если определенные столбцы указаны в SET части оператора UPDATE . Триггеры также могут срабатывать для операторов TRUNCATE . Если происходит событие триггера, вызывается функция триггера в нужное время для обработки события. На представлениях можно определить триггеры для выполнения вместо операций INSERT , UPDATE или DELETE . Такие триггеры INSTEAD OF срабатывают один раз для каждой строки, которую необходимо изменить в представлении. Обязанностью функции триггера является выполнение необходимых изменений в базовой таблице(ах) представления и, при необходимости, возврат измененной строки в виде, в котором она будет отображаться в представлении. Триггеры на представлениях также могут быть определены для выполнения один раз для каждого SQL оператора, перед или после операций INSERT , UPDATE или DELETE . Однако такие триггеры срабатывают только в том случае, если на представлении также определен триггер INSTEAD OF . В противном случае любой оператор, направленный на представление, должен быть переписан в оператор, воздействующий на его базовую таблицу(ы), и затем триггеры, которые будут срабатывать, будут привязаны к базовой таблице(ам). Функция триггера должна быть определена до создания самого триггера. Функция триггера должна быть объявлена как функция, не принимающая аргументов и возвращающая тип trigger . (Функция триггера получает свои входные данные через специально передаваемую структуру TriggerData , а не в виде обычных аргументов функции). После создания подходящей функции триггера, триггер устанавливается с помощью CREATE TRIGGER . Одну и ту же функцию триггера можно использовать для нескольких триггеров. Tantor BE предлагает как триггеры на строку, так и триггеры на операцию. С триггером на строку, функция триггера вызывается один раз для каждой строки, которая затрагивается операцией, вызвавшей триггер. В отличие от этого, триггер на операцию вызывается только один раз при выполнении соответствующей операции, независимо от количества строк, затронутых этой операцией. В частности, операция, которая не затрагивает ни одной строки, все равно приведет к выполнению всех применимых триггеров на операцию. Эти два типа триггеров иногда называются триггеры на уровне строки и триггеры на уровне операции соответственно. Триггеры на TRUNCATE могут быть определены только на уровне операции, а не на уровне строки. Триггеры также классифицируются в зависимости от того, срабатывают ли они перед, после или вместо операции. Они называются соответственно триггерами BEFORE , AFTER и INSTEAD OF . Триггеры BEFORE на уровне оператора срабатывают перед началом выполнения оператора, а триггеры AFTER на уровне оператора срабатывают в самом конце оператора. Эти типы триггеров могут быть определены для таблиц, представлений или внешних таблиц. Триггеры BEFORE на уровне строки срабатывают непосредственно перед выполнением операции над определенной строкой, а триггеры AFTER на уровне строки срабатывают в конце оператора (но перед любыми триггерами AFTER на уровне оператора). Эти типы триггеров могут быть определены только для таблиц и внешних таблиц, но не для представлений. Триггеры INSTEAD OF могут быть определены только для представлений и только на уровне строки; они срабатывают непосредственно при идентификации каждой строки в представлении, которая должна быть обработана. Выполнение триггера AFTER может быть отложено до конца транзакции, а не до конца оператора, если он был определен как триггер-ограничение. Во всех случаях триггер выполняется в рамках той же транзакции, что и оператор, вызвавший его, поэтому если либо оператор, либо триггер вызывают ошибку, изменения обоих будут отменены. Выполнение оператора, который нацелен на родительскую таблицу в иерархии наследования или разделения, не приводит к запуску триггеров на уровне оператора для затронутых дочерних таблиц; запускаются только триггеры на уровне оператора родительской таблицы. Однако, будут запущены триггеры на уровне строки для любых затронутых дочерних таблиц. Если INSERT содержит фразу ON CONFLICT DO UPDATE , то возможно, что эффекты триггеров BEFORE INSERT на уровне строки и триггеров BEFORE UPDATE на уровне строки могут быть применены таким образом, что это будет видно из конечного состояния обновленной строки, если есть ссылка на столбец EXCLUDED . Для выполнения обоих наборов триггеров на уровне строки не обязательно иметь ссылку на столбец EXCLUDED , однако следует учитывать возможность неожиданных результатов, когда есть и триггеры BEFORE INSERT , и триггеры BEFORE UPDATE , которые изменяют вставляемую/обновляемую строку (это может быть проблематично, даже если модификации более или менее эквивалентны, если они не являются идемпотентными). Обратите внимание, что триггеры UPDATE на уровне оператора выполняются при указании фразы ON CONFLICT DO UPDATE , независимо от того, были ли какие-либо строки затронуты UPDATE (и независимо от того, был ли выбран альтернативный путь UPDATE ). INSERT с фразой ON CONFLICT DO UPDATE будет выполнять триггеры INSERT на уровне оператора BEFORE сначала, затем триггеры UPDATE на уровне оператора BEFORE , за которыми следуют триггеры UPDATE на уровне оператора AFTER и, наконец, триггеры INSERT на уровне оператора AFTER . Если UPDATE на разделенной таблице приводит к перемещению строки в другой раздел, это будет выполнено как DELETE из исходного раздела, за которым следует INSERT в новый раздел. В этом случае все триггеры BEFORE UPDATE на уровне строки и все триггеры на уровне строки BEFORE DELETE будут запущены на исходном разделе. Затем все триггеры BEFORE INSERT на уровне строки будут запущены на целевом разделе. Необходимо учитывать возможность неожиданных результатов, когда все эти триггеры влияют на перемещаемую строку. Что касается триггеров AFTER ROW , триггеры AFTER DELETE и AFTER INSERT применяются; но триггеры AFTER UPDATE не применяются, потому что UPDATE был преобразован в DELETE и INSERT . Что касается триггеров на уровне оператора, ни один из триггеров DELETE или INSERT не запускается, даже если происходит перемещение строк; будут запущены только триггеры UPDATE , определенные на целевой таблице, используемой в операторе UPDATE . Вместо этого не определены отдельные триггеры для MERGE . Вместо этого срабатывают триггеры на уровне оператора или строки для UPDATE , DELETE и INSERT в зависимости от (для триггеров на уровне оператора) указанных действий в запросе MERGE и (для триггеров на уровне строки) выполненных действий. Во время выполнения команды MERGE запускаются триггеры уровня оператора BEFORE и AFTER для событий, указанных в действиях команды MERGE , независимо от того, выполняется ли действие в конечном итоге или нет. Это аналогично оператору UPDATE , который не обновляет ни одной строки, но все равно запускает триггеры уровня оператора. Триггеры уровня строки запускаются только при фактическом обновлении, вставке или удалении строки. Поэтому вполне допустимо, что триггеры уровня оператора запускаются для определенных типов действий, но триггеры уровня строки не запускаются для того же типа действий. Функции триггеров, вызываемые триггерами на уровне оператора, всегда должны возвращать NULL . Функции триггеров, вызываемые триггерами на уровне строки, могут вернуть строку таблицы (значение типа HeapTuple ) вызывающему исполнителю, если они выберут. Триггер на уровне строки, сработавший перед операцией, имеет следующие варианты выбора:

    • Он может вернуть NULL , чтобы пропустить операцию для текущей строки. Это указывает исполнителю не выполнять операцию на уровне строки, которая вызвала триггер (вставку, изменение или удаление определенной строки таблицы).
    • Для триггеров на уровне строк, выполняющих команды INSERT и UPDATE , возвращаемая строка становится строкой, которая будет вставлена или заменит обновляемую строку. Это позволяет функции триггера изменять вставляемую или обновляемую строку.

    Триггер BEFORE на уровне строки, который не предполагает вызова ни одного из этих поведений, должен быть осторожным и возвращать в качестве результата ту же самую строку, которая была передана (то есть строку NEW для триггеров INSERT и UPDATE , строку OLD для триггеров DELETE ).

    Триггер на уровне строки INSTEAD OF должен либо возвращать NULL , чтобы указать, что он не изменял данные из базовых таблиц представления, либо возвращать строку представления, которая была передана (строку NEW для операций INSERT и UPDATE , или строку OLD для операций DELETE ). Ненулевое значение используется для сигнализации о том, что триггер выполнил необходимые изменения данных в представлении. Это приведет к увеличению количества затронутых командой строк. Только для операций INSERT и UPDATE триггер может изменять строку NEW перед ее возвратом. Это изменит данные, возвращаемые при использовании INSERT RETURNING или UPDATE RETURNING , и полезно, когда представление не будет показывать точно те же данные, которые были предоставлены.

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

    Некоторые соображения применяются к сгенерированным столбцам. Хранимые вычисляемые столбцы вычисляются после триггеров BEFORE и перед триггерами AFTER . Поэтому сгенерированное значение можно проверить в триггерах AFTER . В триггерах BEFORE старое сгенерированное значение содержится в строке OLD , как и ожидается, но строка NEW еще не содержит нового сгенерированного значения и к ней не следует обращаться. В интерфейсе на языке C содержимое столбца в этот момент не определено; язык программирования более высокого уровня должен предотвращать доступ к хранимому вычисляемому столбцу в строке NEW в триггере BEFORE . Изменения значения сгенерированного столбца в триггере BEFORE игнорируются и будут перезаписаны.

    Если для одного и того же события на одной и той же связи определено более одного триггера, триггеры будут запускаться в алфавитном порядке по имени триггера. В случае триггеров BEFORE и INSTEAD OF измененная, возможно, строка, возвращаемая каждым триггером, становится входными данными для следующего триггера. Если какой-либо триггер BEFORE или INSTEAD OF возвращает NULL , операция отменяется для этой строки, и последующие триггеры не запускаются (для этой строки).

    Определение триггера также может указывать логическое условие WHEN , которое будет проверяться для определения, должен ли триггер срабатывать. В триггерах на уровне строк условие WHEN может проверять старые и/или новые значения столбцов строки. (Триггеры на уровне операторов также могут иметь условия WHEN , хотя эта функция для них не так полезна). В триггере BEFORE условие WHEN вычисляется непосредственно перед выполнением или планируемым выполнением функции, поэтому использование WHEN не отличается от проверки того же условия в начале функции триггера. Однако в триггере AFTER условие WHEN вычисляется сразу после обновления строки и определяет, должно ли событие быть поставлено в очередь для срабатывания триггера в конце оператора. Поэтому, когда условие WHEN триггера AFTER не возвращает истинное значение, необходимо не ставить событие в очередь и не повторно извлекать строку в конце оператора. Это может привести к значительному увеличению скорости выполнения операторов, модифицирующих много строк, если триггер должен срабатывать только для нескольких строк. Триггеры INSTEAD OF не поддерживают условия WHEN .

    Обычно, триггеры на уровне строки BEFORE используются для проверки или изменения данных, которые будут вставлены или обновлены. Например, триггер BEFORE может использоваться для вставки текущего времени в столбец timestamp или для проверки согласованности двух элементов строки. Триггеры на уровне строки AFTER наиболее разумно использовать для передачи обновлений в другие таблицы или для проверки согласованности с другими таблицами. Причина такого разделения труда заключается в том, что триггер AFTER может быть уверен, что видит окончательное значение строки, в то время как триггер BEFORE не может быть уверен; после него могут сработать другие триггеры BEFORE . Если у вас нет конкретной причины сделать триггер BEFORE или AFTER , то вариант BEFORE более эффективен, так как информация о операции не должна быть сохранена до конца оператора.

    Если функция триггера выполняет SQL-команды, то эти команды могут снова запускать триггеры. Это известно как каскадные триггеры. Нет прямого ограничения на количество уровней каскада. Возможно, каскады могут вызывать рекурсивное выполнение одного и того же триггера; например, триггер INSERT может выполнить команду, которая вставляет дополнительную строку в ту же таблицу, вызывая срабатывание триггера INSERT снова. Ответственность за избегание бесконечной рекурсии в таких сценариях лежит на программисте триггера.

    При определении триггера можно указать аргументы для него. Цель включения аргументов в определение триггера заключается в том, чтобы позволить различным триггерам с похожими требованиями вызывать одну и ту же функцию. Например, может быть обобщенная функция триггера, которая принимает в качестве аргументов два имена столбцов и помещает текущего пользователя в один столбец и текущую метку времени в другой. Правильно написанная такая функция триггера будет независима от конкретной таблицы, на которой она срабатывает. Таким образом, одну и ту же функцию можно использовать для событий INSERT на любой таблице с подходящими столбцами, чтобы автоматически отслеживать создание записей в таблице транзакций, например. Она также может использоваться для отслеживания событий последнего обновления, если определена как триггер UPDATE .

    Каждый язык программирования, поддерживающий триггеры, имеет свой собственный метод для предоставления входных данных триггера функции триггера. Эти входные данные включают тип события триггера (например, INSERT или UPDATE ), а также любые аргументы, указанные в CREATE TRIGGER . Для триггера на уровне строки входные данные также включают строку NEW для триггеров INSERT и UPDATE , а также строку OLD для триггеров UPDATE и DELETE .

    По умолчанию, триггеры на уровне оператора не имеют возможности анализировать отдельные измененные строки. Однако, триггер AFTER STATEMENT может запросить создание таблиц перехода, чтобы сделать наборы затронутых строк доступными для триггера. Триггеры AFTER ROW также могут запросить таблицы перехода, чтобы видеть общие изменения в таблице, а также изменения в отдельной строке, для которой они в данный момент запускаются. Метод анализа таблиц перехода снова зависит от используемого языка программирования, но типичным подходом является использование таблиц перехода как временных таблиц только для чтения, к которым можно обращаться с помощью SQL-команд, выполняемых внутри функции триггера.

    Назад Наверх Далее
    Глава 36. Триггеры Начало 36.2. Видимость изменений данных

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *