Использование команды CREATE TRIGGER

Урок: Триггеры

Триггер – это хранимая процедура, которая выполняется автоматически при изменении таблицы SQL Server с использованием инструкции UPDATE, INSERT или DELETE. Так же как и хранимая процедура, триггер содержит набор инструкций T-SQL. Триггеры обычно используются, чтобы задать для базы данных правила ссылочной целостности. Рассматриваются различные виды триггеров, их применение и возможные ошибки. Особое внимание уделено триггерам AFTER и INSTEAD OF. Много сценариев с примерами на различное использование триггеров.

Вы научитесь:

• создавать триггер AFTER;
• создавать триггер INSTEAD OF;
• использовать функцию UPDATE;
• использовать таблицы вставки и удаления.

Триггер – это особый вид хранимой процедуры, которую Microsoft SQL Server выполняет автоматически, когда вы модифицируете строку с помощью команд INSERT, UPDATE или DELETE. Мы рассмотрим различные виды триггеров, в том числе обладающий богатыми возможностями триггер INSTEAD OF, который является новинкой для SQL Server 2000.

Понятие о триггерах

Преимущество триггерной процедуры состоит в том, что SQL Server автоматически вызывает ее при обращении к команде, для которой она определена. Это повышает устойчивость базы данных к внешним воздействиям (робастность). Вам нет нужды беспокоиться о том, чтобы клиенты базы данных – пользователи или приложения – разбирались и пользовались всеми зависимостями данных и установленными для них правилами.

Предположим, в торговой организации существует правило, в соответствии с которым заказ на общую сумму 10000$ или выше требует проверки кредитоспособности. Если Credit Approved является столбцом таблицы Customer, а заказ добавляется в таблицу Order, вы не сможете использовать проверочное ограничение типа CHECK для реализации правила, не следует возлагать ответственность за его соблюдение на клиентов базы данных. Триггер предоставляет механизм, обеспечивающий исполнение правила внутри базы данных.

Триггеры также могут изолировать клиентов от изменений в правилах для базы данных. В продолжение предыдущего примера, если бизнес-правило будет изменено таким образом, что проверка кредитоспособности требуется только при покупках на сумму 15000$ и более, новое правило может быть применено на уровне базы данных, без необходимости проверки и модификации клиентских приложений.

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

Совет. Хотя производительность триггеров обычно не представляет проблемы (в отличие от курсоров), следует всегда применять правила по возможности на самом низком уровне. Не используйте триггеры, если можно применить проверочное ограничение CHECK, а ограничение CHECK не следует использовать, если можно обойтись ограничением UNIQUE.

Триггеры AFTER

SQL Server поддерживает два различных типа триггеров: триггеры AFTER и триггеры INSTEAD OF. Триггеры AFTER вызываются после выполнения команды, которой они назначены, а триггеры INSTEAD OF вызываются вместо команды.

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

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

Триггер AFTER вызывается после того, как выполнены все операции по обработке ограничений низкого уровня, и не будут вызваны в случае нарушения ограничения. Например, если осуществляется попытка вставить строку, которая нарушает ограничение PRIMARY KEY для таблицы, оператор INSERT не будет выполнен до того, как произойдет вызов триггера.

Триггеры INSTEAD OF

Триггеры INSTEAD OF заменяют команду, для которой они объявлены. Подобно триггерам AFTER, вы можете определять триггеры INSTEAD OF для команд INSERT, UPDATE или DELETE. Один триггер может быть применен к нескольким командам.

Однако, в отличие от триггеров AFTER, вы можете создавать триггеры INSTEAD OF как для таблиц, так и для представлений, но для каждого действия над этой таблицей или представлением может быть создан только один триггер INSTEAD OF.

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

Поскольку триггеры INSTEAD OF могут быть объявлены для представлений, они чрезвычайно полезны для получения функциональных возможностей представления, которые не могут быть доступны иным способом. Например, SQL Server не дает возможности применить для представления оператор INSERT, содержащий фразу GROUP BY, но позволяет вам определить триггер INSTEAD OF INSERT для представления. Вы можете воспользоваться триггером для вставки записей в таблицы, лежащие в основе представления, тем самым давая знать пользователю, что новая строка была вставлена в представление.

Триггеры "BEFORE"

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

Например, если вы хотите проверить некое условие до выполнения команды INSERT, вы можете объявить триггер INSTEAD OF INSERT. Триггер INSTEAD OF будет выполнять проверку, а затем выполнять команду INSERT для таблицы. Оператор INSERT будет выполняться обычным образом, не порождая рекурсивных вызовов триггера INSTEAD OF.

Создание триггеров

SQL Server накладывает ряд ограничений на действия, выполняемые триггерами. Вы не можете создавать (командой CREATE), изменять (командой ALTER) или удалять (командой DROP) базу данных с использованием триггера; вы не можете восстанавливать базу данных или файл журнала; и вы не можете выполнять определенные операции, которые изменяют конфигурацию SQL Server. (Если вас интересует полный перечень "запрещенных" команд, обратитесь к документации SQL Server Books Online.)

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

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

Использование команды CREATE TRIGGER

Как и любые другие объекты базы данных, триггер определяется с помощью соответствующей формы оператора CREATE. Базовый синтаксис оператора создания триггера следующий:

Имя_триггера должно удовлетворять правилам, принятым для идентификаторов. Таблица_или_представление может быть именем представления, только если тип_триггера есть INSTEAD OF, поскольку только этот тип триггера вы можете определять для представлений. Триггеры не могут быть созданы для временных таблиц или системных таблиц, но они могут ссылаться на временные таблицы.

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

Примечание. Ранние версии SQL Server поддерживали только триггеры AFTER и использовали ключевое слово FOR в качестве типа_триггера. Этот синтаксис по-прежнему поддерживается SQL Server 2000, но это будет означать то же самое, что триггер AFTER.

Операторы_SQL, следующие за ключевым словом AS, определяют действия, выполняемые триггером. Здесь имеется аналогия с хранимыми процедурами, за исключением того, что триггеры не имеют параметров.

Создайте триггер AFTER

1. Нажмите кнопку New Query (Новый запрос) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит новое окно Query (Запрос).
2. Нажмите кнопку Load Script (Загрузить сценарий) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

1. Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий afterUpdate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

1. Нажмите кнопку Execute Query (Выполнить запрос) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст триггер.
2. Нажмите кнопку New Query (Новый запрос) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
3. Нажмите кнопку Load Script (Загрузить сценарий) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

1. Выделите сценарий TestAfterUpdate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.

1. Нажмите кнопку Execute Query (Выполнить запрос) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит сценарий и отобразит результат.