Триггеры

Триггеры - это один из видов программных объектов СУБД, поддерживаемых SQL Anywhere. Каждый триггер связан с одной из таблиц данных, входящих состав базы данных. С каждой такой таблицей может быть связано несколько триггеров. Основное назначение триггеров состоит в автоматическом использовании их в качестве реакции на некоторые события, происходящие с таблицами, с которыми связанны триггеры. Это свойство триггеров позволяет использовать их для реализации сложных форм ограничений целостности данных. Кроме того, рассматриваемое свойство превращает сервер из пассивного наблюдателя за происходящими изменениями данных в систему, оперативно реагирующую на такие изменения. Правила, в соответствие с которыми осуществляются активные действия сервера, определяются триггерами. Иногда эти правила или сами триггеры называют бизнес - правилами .

Триггеры, создаются оператором CREATE TRIGGER. Модификация триггеров производится при помощи оператора ALTER TRIGGER, а удаление - оператором DROP TRIGGER. Эти операторы могут использовать:

пользователь - владелец таблицы;

пользователи, владеющие полномочиями администратора базы данных;

пользователи с классом полномочий RESOURCE (), имеющие привилегию ALTER () для таблицы, с которой связанны триггеры.

ПРИМЕЧАНИЕ

Независимо от того, кто создал триггер его владельцем всегда является владелец таблицы.

В СУБД SQL Anywhere триггеры могут быть определены для одного из приведенных ниже событий или сразу на несколько из них:

Добавление новой записи в таблицу. Данное событие возникает при выполнении оператора INSERT. Оно приводит к активизации триггера при добавлении новой записи в связанную таблицу.

Удаление записей. Это событие наступает в результате воздействия оператора DELETE. Данный оператор производит удаление записей из таблиц, с которыми связанны триггеры.

связанной с триггером таблицы, что является причиной активизации триггера.

Модификация записей. Событие этого типа инициируется оператором UPDATE. Оно возникает при изменении значений любого из полей записей таблицы, с которой связан активизируемый триггер.

Изменение значений заданного списка полей таюлицы. Это событие, как и предыдущее, возникает при выполнении оператора UPDATE (UPDATE OF colums-list), но при модификации только заданных полей таблицы данных (colums-list).

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

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

Операторы INSERT, DELETE, UPDATE, которые содержат служебное слово WHERE или подзапросы, как правило, воздействуют (добавляют, удаляют, модифицируют) на несколько записей таблицы. Когда при этом должен выполняться триггер? Каждый раз при изменение очередной записи или один раз после модификации всех записей? Для ответа на этот вопрос в SQL Anywhere реализованы триггеры двух уровней. Триггер первого уровня Statement-level trigger выполняется однократного после полного завершения одного из вышеуказанных операторов (операторный триггер) Триггер второго уровня Row-level trigger предназначен для многократного выполнения после каждого изменения одной из записей (строк) таблицы (строчный триггер).

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

ПРИМЕЧАНИЕ

Следует особо подчеркнуть, что триггеры активизируются только при выполнении операторов INSERT, DELETE, UPDATE и никаких других. Данное обстоятельство необходимо отметить в связи со следующим. Как показал опрос ряда пользователей, многие из них ошибочно считают, что триггеры активизируются и при добавлении новых записей в таблицы в результате импорте данных. Однако импорт производится операторами INPUT и LOAD TABLE, который не входят в число вышеуказанных операторов. В связи с этим, при осуществлении импорта данных механизм триггеров не работает.

Рассмотрим пример использования триггера. В предметной области - в библиотеке, для которой разрабатывается приложение, вполне типична следующая ситуация. В библиотеку периодически поступают новые книги. Каждую из них необходимо учесть и завести карточки для всех ее экземпляров. Применительно к следует для каждой книги добавить одну запись в таблицу Stepanov.Books и несколько записей (по числу экземпляров) в таблицу Stepanov.Copies. Для реализации этих действий следует выполнить операторы INSERT для обеих вышеуказанных таблиц. Если один из таких операторов не выполнить, то произойдет нарушение целостности базы данных. Такое требование должно соблюдаться для всех приложений (допустим их несколько), производящих регистрацию поступления новых книг. В этом случае хорошим решением является применение для таблицы Stepanov.Books триггера, реагирующего на выполнение оператора INSERT. Как и таблица, этот триггер будет в храниться в база данных и можно обеспечить доступ к нему всем приложениям. Тогда один и то же механизм поддержания целостности будет использоваться для всех таких приложений. При изменении механизма (изменении тела триггера), этот факт станет "достоянием гласности" для всех приложений. Реализация механизма поддержания целостности базы данных при пополнения библиотечного фонда представлен в виде триггера Add_copies:

//Определение триггера CREATE TRIGGER Add_copies BEFORE INSERT ORDER 1 ON Stepanov.Books REFERENCING NEW AS New_books FOR EACH ROW /* Предваряющий строчный триггер, активизирую- щийся при наступлении события "Добавление новой записи" в таблицу Stepanov.Books. В этой таблице имеются три поля, для двух из которых определены значения по умолчанию. Поэтому в операторе INSERT достаточно ввести значения только одного поля - Code_book */ BEGIN DECLARE Kol SMALLINT; SET Kol=New_books.number; /* MESSAGE 'Code_book=', New_books.Code_book; */ WHILE Kol>0 LOOP INSERT INTO Stepanov.Copies( Code_book) VALUES( New_books.Code_book); SET Kol=Kol-1 END LOOP END

Триггер Add_copies является предваряющим строчным триггером, о чем указывают служебные слова BEFORE и FOR EACH ROW, соответственно. Данный триггер активизируется при добавлении новой записи (служебное слово INSERT) в таблицу (служебное слово ON) Stepanov.Copies. Триггер Add_copies формирует данные об экземплярах поступившей книги путем добавления новых записей о каждом экземпляре книги в таблицу Stepanov.Copies. В рассматриваемом случае процесс использования этого триггера происходит следующим образом. Данный триггер активизируется при выполнении оператора INSERT. Этот оператор позволяет вставлять сразу несколько записей. Каждый раз перед добавлением очередной записи запускается триггер и после его успешного выполнения производится непосредственное добавление следующей записи в таблицу Stepanov.Books. Далее при попытке вставить еще одну запись происходит повторная активизация триггера, а затем добавление этой записи в таблицу. Так происходит до тех пор пока не завершится выполнение оператор INSERT. Другим условием завершением этого оператора является возникновения ошибки при выполнении самого оператора или триггера. В этом случае происходит аварийное завершение оператора и база данных возвращается в состояние, в котором она была до начала выполнения оператора.

Общий порядок применения триггеров состоит в последовательном выполнении следующих шагов при вызове на выполнение операторов INSERT, DELETE или UPDATE:

1.Активизируются предваряющие триггеры, если они есть.

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

3.Выполняется непосредственно операция, активизирующая триггер.

4.Активизируются завершающие триггеры, в случае если таковые имеются .

Теперь детально проанализируем триггер Add_copies. Как уже было сказано ранее, с одной таблицей может быть связано несколько триггеров. Некоторые из них могут быть настроены на одни и те же события. Для того, чтобы в этом случае установить порядок их выполнения применяется служебное слово ORDER. Для других случаев его применение является не обязательным. В триггере Add_copies это слово используется для указания того, что данный триггер будет выполняться первым при наступлении события Добавление новой записи в таблицу. Это сделано в связи с тем, что далее для этого события будет предложен еще один триггер.

Проверим функционирование триггера Add_copies. Для этого сымитируем факт поступления в библиотеку новой книги:

//Имитация поступления в библиотеку новой книги INSERT Stepanov.Books (Author, Coauthors, Title_book, City_publish, Publisher, Year_publish, Number) VALUES ('Степанов Ю.Л.', 'Карпова Т.С., Гурко А.В.', 'ЭФФЕКТИВНАЯ РАБОТА С БАЗАМИ ДАННЫХ ПО ТЕХНОЛОГИИ КЛИЕНТ-СЕРВЕР', 'Санкт-Петербург','ПИТЕР', 1998, 10)

Данный триггер при попытке добавления новой записи в таблицу Stepanov.Books сначала осуществляет вставку записи в таблицу Stepanov.Copies. Далее производится контроль ограничений целостности. При этом стоит напомнить, что в таблице Stepanov.Copies определен внешний ключ Books_Copies, который ссылается на таблицу Stepanov.Books. Создание этого внешнего ключа , но только для базы данных Dubl_Biblia обсуждалось в п.5.8.

Одним из основных назначений внешних ключей является предотвращение "висячих" ссылок. Однако при использовании триггера Add_copies сначала создаются именно такие ссылки. Это связано с тем, что в таблицу Stepanov.Copies будет добавлена запись с информацией об первом экземпляре i-ой книги, а данных о самой книге в таблице Stepanov.Books еще нет. В связи с этих внешний ключ Books_Copies ссылается на не существующую запись таблицы Stepanov.Books. Результатом этого является возврат базы данных в исходное состояние (состояние до начала выполнения оператора INSERT) и выдача сообщения о возникновении исключительной ситуации "no primary key value for foreign key 'Book_Copies' in table 'Copies'". Оно означает отсутствие значения первичного ключа для внешнего ключа Books_Copies таблицы Copies.

Таким образом, условием наступления данной исключительной ситуации является наличие предваряющего триггера. Другим условием является то, что во внешнем ключе Books_Copies определена проверка ограничений целостности сразу после изменения содержимого таблицы данных. Выходом из создавшегося положения может быть одна из двух альтернатив:

преобразование предваряющего триггера Add_copies в завершающий триггер;

установка во внешнем ключе Books_Copies проверку ограничений целостности только при завершении транзакции, т.е. при выполнении оператора COMMIT.

Использовании первой альтернативы заключается в замене в тексте тела триггера служебного слова BEFORE на AFTER. Тогда первая строчка в определении триггера Add_copies будет выглядеть следующим образом:

/* Преобразование предваряющего триггера в завершающий */ ALTER TRIGGER Add_copies AFTER.

Тексты триггеров в базе данных содержатся в системном представлении SYS.SYSTRIGGERS. Для получения текста триггера Add_copies необходимо выполнить следующие операторы:

//Получение текста триггера SELECT SYS.SYSTRIGGERS.Trigdefn FROM SYS.SYSTRIGGERS WHERE Trigname ='Add_copies' //Указание имени владельца //AND Owner='Stepanov' //Указание наименивания таблицы //AND Tname='Books' ;OUTPUT TO d:\Log_db\trigtext.sql FORMAT ASCII

В утилите SQL Central применение второй альтернативы состоит в установке флажка Check on commit закладки Integrity окна свойств внешнего ключа Books_Copies в утилите SQL Central. Окно свойств внешнего ключа Books_Copies появляется после щелчка правой кнопкой мыши по его пиктограмме (). В утилите ISQL для достижения такого эффекта необходимо выполнить следующие SQL-операторы:

//Изменение свойств внешнего ключа //Сначала удалением внешнего ключа таблицы ALTER TABLE Stepanov.Copies DELETE FOREIGN KEY Books_Copies; //Создание заново внешнего ключа //с новыми свойствами ALTER TABLE Stepanov.Copies ADD "Books_Copies " NOT NULL FOREIGN KEY(Code_book) REFERENCES Stepanov.Books(Code_book) ON UPDATE CASCADE ON DELETE RESTRICT CHECK ON COMMIT /* признак проверки целостности только при выполнении оператора COMMIT */ // комментарий к внешнему ключу COMMENT ON FOREIGN KEY Stepanov.Copies.Books_Copies IS 'Внешний ключ для поддержания ограничений ссылочной целостности между таблицами Copies и Books'

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

Теперь повторим попытку выполнения оператора INSERT для добавления новой записи в таблицу Stepanov.Books. Она должна завершиться успешно и в таблице Stepanov.Books появится одна новая запись. При этом в результате активизации триггера Add_copies таблица Stepanov.Copies пополнится десятком записей. Все они будут связанны с записью появившейся в таблице Stepanov.Books.

В рассматриваемом примере порядок очередности активизации триггера (предваряющий или завершающий) имеет важное значение. В других случаях совершенно безразлично в какой последовательности будут происходить обращение к триггеру. К этому следует добавить, что случае термины "предваряющий" и "завершающий" применятся только для строчных триггеров. Операторные триггеры, как следует из определения их названия, могут быть только завершающими.

При работе триггеров часто возникает необходимость обращаться как к исходным значениям полей таблицы, так и к их обновленным значениям. Для этой цели используются фраза REFERENCING с параметрами NEW AS или OLD AS. С ее помощью вводятся псевдонимы для обращения к исходным и обновленным данным. В триггере Add_copies фраза REFERENCING с параметром NEW AS определяет псевдоним New_books для обращения к значениям полей вновь введенной записи таблицы Stepanov.Books. В конечном же итоге смысл таких псевдонимов зависит от типа триггера и от событий, при возникновении которых активизируются триггеры. Назначение псевдонимов, вводимых фразой REFERENCING, приведено в табл. 24.

Содержание раздела