3. Триггеры

Триггер (trigger) – это специальная программа, применяемая к таблице или представлению. Триггер вызывается СУБД, когда пользователь запрашивает вставку, обновление или удаление строки из таблицы или представления, которому принадлежит данный триггер. Триггеры можно писать на языках программирования СУБД, например, триггеры для SQL Server пишутся на собственном языке этой СУБД, который называется T-SQL (Transaction SQL – транзакционный SQL).

Существует три вида триггеров: предваряющие (before), замещающие (instead of) и завершающие (after). Предваряющие триггеры вызываются перед обработкой запроса на вставку, обновление или удаление, замещающие – вместо него, а завершающие – после обработки запроса. Т.о., всего имеется девять возможных типов триггеров; предваряющий вставки, обновления и удаления, замещающий триггер вставки, обновления и удаления и завершающий триггер вставки, обновления и удаления.

SQL Server поддерживает только замещающие и завершающие триггеры, поэтому для данной СУБД имеется только шесть возможных типов триггеров.

При запуске триггера СУБД предоставляет доступ к вставляемым, обновляемым или удаляемым из тела триггера данным. В случае вставки триггеру доступны значения столбцов новой строки, в случае удаления – значения столбцов удаляемой строки, а в случае обновления триггер может оперировать как новыми, так и старыми значениями.

Способ, при помощи которого это делается, зависит от конкретной СУБД. В SQL Server таблица может иметь один или несколько завершающих триггеров вставки, обновления или удаления. Завершающие триггеры нельзя назначать представлениям. Представление или таблица может иметь не более одного замещающего триггера на каждое из возможных действий – вставку, обновление или удаление.

В SQL Server триггеры могут производить откат транзакций, повлекших их запуск. Когда триггер выполняет команду ROLLBACK, все действия, произведенные транзакцией, которая привела к запуску триггера, отменяются. Если в тексте триггера за командой ROLLBACK следуют какие-либо инструкции, эти инструкции будут выполнены. Однако никакие инструкции, следующие за оператором, который вызвал запуск триггера, выполняться не будут.

Для триггеров вставки и обновления новые значения каждого столбца обрабатываемой таблицы хранятся в псевдотаблице под названием inserted. Если, например, в таблицу Проекты добавляется новая строка, то псевдотаблица inserted будет содержать три столбца: Пр№, Имя_П и Гор. Аналогичным образом, в случае обновления или удаления старые значения каждого столбца будут храниться в псевдотаблице с именем deleted.

Триггеры можно создавать, записывая их в текстовые файлы (первым оператором в таких файлах должен быть оператор CREATE TRIGGER или ALTER TRIGGER), или в программе Enterprise Manager, щелкнув правой кнопкой мыши на имени таблицы и выбрав All Tasks/Manager Triggers.

В качестве примера рассмотрим создание триггера, реализующего некоторое бизнес-правило. Магазин ведет список «проблемных» клиентов – тех, кто не оплатил вовремя покупку или создал какие-то другие проблемы. Когда в базу данных вводится покупатель, фигурирующий в списке таких клиентов, необходимо получать об этом сообщение.

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

Завершающий триггер CheckForProblems реализует эту политику (для объявления замещающего триггера в заголовке должно присутствовать ключевое слово INSTEAD OF):

CREATE TRIGGER [CheckForProblems] ON [dbo].[КЛИЕНЫ] FOR INSERT, UPDATE AS

/* Пытаемся соединить данные из inserted со строкой в таблице Проблемные_Клиенты */

SELECT *

FROM Inserted AS I JOIN Проблемные_Клиенты AS P ON I.Name = P.Name AND I.AreaCode = P.AreaCode AND I.PhoneNumber = P.PhoneNumber

*/ В следующем разделе необходимо проверить количество строк @@rowCount в полученной таблице. Если результат соединения не пуст, выполняем откат транзакции и выдаем сообщение */

If @@rowCount > 0 BEGIN ROLLBACK Print 'Клиент принадлежит к числу "проблемных".' Print 'Вставка и обновление не разрешены.' END

Триггер CheckForProblems пытается соединить новые значения в псевдотаблице instead по очереди со всеми строками таблицы Проблемные_Клиенты. Если соединение возвращает какие-либо результаты, значит, данный клиент присутствует в таблице Проблемные_Клиент. В этом случае следует выполнить откат транзакции и вывести предупреждение.

Данный триггер использует системную функцию @@rowCount, которая возвращает количество строк, обработанных предыдущим оператором T/SQL. Значение этой функции необходимо проверять сразу после выполнения оператора или сохранять в переменной для последующей проверки. Здесь триггер выполняет эту проверку сразу.

Если @@rowCount равняется нулю, то данный клиент не присутствует в таблице Проблемные_Клиенты и ничего делать не требуется. Если же @@rowCount имеет положительное значение, значит, информация о данном клиенте содержится в таблице Проблемные_Клиенты, поэтому транзакцию необходимо отменить, а пользователю выдать предупреждающее сообщение.