6.5. Триггеры

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

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

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

ПРИМЕЧАНИЕ

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

В СУБД 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, который не входят в число вышеуказанных операторов. В связи с этим, при осуществлении импорта данных механизм триггеров не работает.

Рассмотрим пример использования триггера. В предметной области - в библиотеке, для которой разрабатывается приложение, вполне типична следующая ситуация. В библиотеку периодически поступают новые книги. Каждую из них необходимо учесть и завести карточки для всех ее экземпляров. Применительно к базе данных Biblia следует для каждой книги добавить одну запись в таблицу 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 появляется после щелчка правой кнопкой мыши по его пиктограмме (см. рис. 69). В утилите 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.

ПРИМЕЧАНИЕ

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

Для завершения обсуждения псевдонимов, используемых в триггерах, приведем еще один пример триггера:

//Демонстрация псевдонимов триггера
CREATE TRIGGER Update_Number
/* Строчный завершающий триггер, выдающий
при модификации поля Number старое и новое
его значения */
AFTER UPDATE OF Number
ON Stepanov.Books
REFERENCING OLD AS Old_books
            NEW AS New_books
FOR EACH ROW
BEGIN
        MESSAGE '*** UPDATE ****';
    MESSAGE 'OLD NUMBER=',
            Old_books.number;
    MESSAGE 'NEW NUMBER=',
            New_books.number;
END

Строчный завершающий триггер Update_Number, активизируется при модификации поля Stepanov.Books.Number. В процессе своей работы он выдает на сервер старое и новое значение этого поля. Для проверки его работы выполним следующей SQL-оператор:

//Проверка триггера Update_Number
UPDATE Stepanov.Books
SET Number=7
WHERE Code_book=1

В результате в окне сервера будет выдано следующее сообщение:

*** UPDATE ****
OLD NUMBER=49
NEW NUMBER=7

После этого выполним оператор ROLLBACK для отмены проведенной модификации таблицы Stepanov.Books.

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

// Создание операторного триггера
CREATE TRIGGER Copies_Books 
AFTER INSERT ORDER 2 
ON Stepanov.Books
FOR EACH STATEMENT
/* Операторный завершающий триггер сранивает
число экземпляров книг зафиксированных в 
таблицах Stepanov.Books и Stepanov.Copies.
Если эти данные не совпадают, то значит 
нарушена целостность данных. В этом случае
искусственно генерируется ошибка с
кодом SQLSTATE '99999' */
BEGIN
    DECLARE Err_referencins
        EXCEPTION FOR SQLSTATE '99999';
    DECLARE Count_copies SMALLINT;
    DECLARE Sum_copies SMALLINT;
//Определяем число книг в библиотеке,
//используя таблицу Stepanov.Copies
        SELECT COUNT(*) INTO Count_copies 
            FROM Stepanov.Copies;
//Определяем число книг в библиотеке,
//используя таблицу Stepanov.Books
        SELECT SUM(Books.Number)
            INTO Sum_copies 
             FROM Stepanov.Books;
//Сравниваем результаты подсчетов
        IF Count_copies <> Sum_copies 
        THEN MESSAGE 'Err_referencins';
                SIGNAL Err_referencins;
        END IF;
END

В данном триггере нет обращений отдельно к вновь введенным записям таблицы Stepanov.Books, обращение происходит целиком к таблице. По этой причине фраза REFERENCING в триггере не используется. Рассматриваемый триггер представляет собой еще один пример механизма поддержания сложных форм ограничений целостности данных. Оба триггера взаимно дополняют друг друга. Предваряющий строчный триггер Add_copies согласованно добавляет записи в таблицы Stepanov.Copies и Stepanov.Books. Завершающий операторный триггер Copies_Books проверяет согласованность содержимого этих таблиц.

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

По аналогии с ранее рассмотренными программными объектами - хранимыми процедурами и функциями, любой триггер содержится в базе данных. Там он хранится до тех пор пока не будет удален из нее посредством оператора DROP TRIGGER. Таким образом для удаления триггера Add_copies этот оператор должен быть использован так:

//Удаление триггера
DROP TRIGGER Add_copies.

Триггеры всегда выполняются с полномочиями владельца таблицы, с которой связаны триггеры. Привилегии пользователя, активизировавшего триггер, или пользователя, создавшего триггер, на выполнение самого триггера не оказывают никакого влияния. Допустим, что библиотекари не имеют вообще никаких привилегий на использование таблиц Stepanov.Copies и Stepanov.Books. Однако они имеют право вызывать процедуру Ins_absent, которая осуществляет пополнение состава записей таблицы Stepanov.Books. Тогда библиотекари при обращении к процедуре Ins_absent, добавляющей новые записи в таблицу Stepanov.Books, активизируется триггер Add_copies. Этот триггер выполняется с привилегиями пользователя Stepanov. - владельца таблицы Stepanov.Copies. Следовательно этот триггер имеет возможность вводить новые записи в эту таблицу. В связи с этим библиотекари, не имея право работать с таблицей Stepanov.Copies-напрямую, тем не менее имеют возможность взаимодействовать с этой таблицей косвенно. Данное свойство позволяет разработать для пользователей единый способ манипулирования с конкретной таблицей, что служит еще одним средством поддержания целостности данных.

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