Триггеры 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.
Действие, запускающее триггер.
Перед следующей операцией.
Обработка ограничений.
Вместо следующей операции.
Действие, запускающее триггер.
После следующих операций.
Создание таблиц inserted и deleted.
Порядок выполнения
Можно задать выполнение в первую и в последнюю очередь.
Неприменимо
Ссылки на столбцы varchar(max), nvarchar(max) и varbinary(max) в таблицах inserted и deleted
Разрешены.
Разрешены.
Ссылки на столбцы text, ntext и image в таблицах inserted и deleted
Не допускается
Разрешены
Триггеры CLR
Триггер CLR может быть либо триггером AFTER, либо триггером INSTEAD OF. Триггер CLR может также являться триггером DDL. Вместо вызова хранимой процедуры на языке Transact-SQL триггер CLR вызывает один или несколько методов управляемого кода, являющихся членами сборки, созданной с помощью среды .NET Framework и загружены в SQL Server.
Связанные задачи
Задача |
Раздел |
|---|---|
Описывает, как создать триггер DML. |
|
Описывает, как создать триггер CLR. |
|
Описывает, как создать триггер DML для выполнения и однострочных, и многострочных операций модификации данных. |
Создание триггеров DML для обработки нескольких строк данных |
Описывает, как вкладывать триггеры. |
|
Описывает, как указывать порядок, в котором активируются триггеры AFTER. |
|
Описывает, как использовать специальные таблицы inserted и deleted в коде триггера. |
|
Описывает, как изменить или переименовать триггер DML. |
|
Описывает, как просматривать сведения о триггерах DML. |
|
Описывает, как удалять или отключать триггеры DML. |
|
Описывает, как управлять безопасностью триггеров. |
См. также
Справочник
DISABLE TRIGGER (Transact-SQL)