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

Триггеры DML

  • Статья

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

Триггеры 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 сравнивается в следующей таблице.

Function Триггер 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. Создание триггеров DML
Описывает, как создать триггер CLR. Создание триггеров CLR
Описывает, как создать триггер DML для выполнения и однострочных, и многострочных операций модификации данных. Создание триггеров DML для обработки нескольких строк данных
Описывает, как вкладывать триггеры. Создание вложенных триггеров
Описывает, как указывать порядок, в котором активируются триггеры AFTER. Указание первого и последнего триггеров
Описывает, как использовать специальные таблицы inserted и deleted в коде триггера. Использование вставленных и удаленных таблиц
Описывает, как изменить или переименовать триггер DML. Изменение или переименование триггеров DML
Описывает, как просматривать сведения о триггерах DML. Получение сведений о триггерах DML
Описывает, как удалять или отключать триггеры DML. Удаление или отключение триггеров DML
Описывает, как управлять безопасностью триггеров. Управление безопасностью триггеров

См. также

CREATE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
Функции триггера (Transact-SQL)