Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

ACTIVE BEFORE INSERT OR UPDATE

POSITION 0

AS

BEGIN

. . .

NEW. PROXY_LAST_NAME = UPPER(NEW.LAST_HAME) ;

. . .

END ^

Возможны любые виды преобразований. Предположим, мы хотим получить количество элементов (MEMBERSHIP_NUM), собранное из MEMBER_TYPE, за которым следует строка из десяти цифр, заполненная слева нулями и полученная из сгенерированного первичного ключа таблицы MEMBER. С помощью автоматической генерации в триггере BEFORE INSERT мы можем это осуществить[123]:

CREATE TRIGGER BI_MEMBER2 FOR MEMBER

ACTIVE BEFORE

INSERT POSITION 2

AS

DECLARE VARIABLE ID AS STRING VARCHAR (10);

BEGIN

ID_AS_STRING = CAST (NEW. ID AS VARCHAR (10)) ;

WHILE (NOT (ID_AS_STRING LIKE ' %'))

/* 10-символьная маска */

DO

ID_AS_STRING = '0' || ID_AS_STRING;

NEW.MEMBERSHIP_NUM = NEW.MEMBER_TYPE || ID_AS_STRING;

END ^

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

SQL предоставляет ограничения CHECK для гарантии того, что будут сохраняться только "хорошие" данные. Например, значения столбцов, создаваемых на основе следующего домена, ограничены символами в верхнем регистре и цифрами:

CREATE DOMAIN TYPECODE CHAR(3)

CHECK(VALUE IS NULL OR VALUE = UPPER(VALUE));

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

CREATE TRIGGER BA_ATABLE FOR ATABLE

ACTIVE BEFORE INSERT OR UPDATE

AS

BEGIN

NEW.ATYPECODE = UPPER(NEW.ATYPECODE);

END ^

! ! !

ПРИМЕЧАНИЕ. В настоящий момент Firebird поддерживает триггеры только для таблиц и просмотров. Невозможно создать триггер для домена, однако это было бы элегантным улучшением.

. ! .

В определениях доменов и столбцов вы можете указать значение по умолчанию: DEFAULT. Кажется хорошей идеей устанавливать значение столбца, не допускающего значение NULL В некоторое значение по умолчанию, однако SQL-атрибут DEFAULT на деле оказывается беззубым зверем. Он работает только при двух условиях:

* при использовании операции INSERT;

* если этот столбец не включен во входной список оператора.

Поскольку многие современные интерфейсы приложений автоматически создают оператор INSERT, используя выходные столбцы оператора SELECT В качестве входного

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

Вторая проблема связана, конечно, с тем, что значения по умолчанию никогда не применяются, если используется операция изменения.

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

CREATE DOMAIN MONEY NUMERIC (18, 0)

NOT NULL DEFAULT 0.00;

Триггер BEFORE INSERT OR UPDATE для любого столбца, использующего домен MONEY, реализует значение по умолчанию:

CREATE TRIGGER BI_ACCOUNT FOR ACCOUNT

ACTIVE BEFORE INSERT OR UPDATE

AS

BEGIN

IF (NEW.BALANCE IS NULL) THEN

NEW.BALANCE = 0.00;

END ^

! ! !

СОВЕТ. Вы можете обеспечить поддержание всех значений по умолчанию для таблицы в едином триггерном модуле (версия 1.5 и выше) или в двух параллельных модулях: один для BEFORE INSERT, а другой для BEFORE UPDATE (версия 1.0.x).

. ! .

Триггеры полезны для "автоматического заполнения" контекстной информацией столбцов, созданных для подобных целей. Firebird предоставляет множество контекстных переменных, которые вы можете использовать в операциях такого рода. Вы можете также использовать ваши собственные "флаги", которые вы вычисляете или просто поставляете в виде констант в процессе выполнения триггера.

В следующем примере мы используем триггер AFTER для множества событий с целью автоматического заполнения имени пользователя, даты, времени и идентификатора транзакции для помещения в файл протокола вместе с некоторой информацией о событии. Поскольку регистрируется процесс, то, скорее всего, мы захотим выполнить это в самом конце; присвоим триггеру высокий последовательный номер:

CREATE TRIGGER AA_MEMBER FOR MEMBER

ACTIVE AFTER INSERT OR UPDATE OR DELETE

POSITION 99

AS

DECLARE VARIABLE MEM_ID INTEGER;

DECLARE VARIABLE DML_EVENT CHAR(4);

BEGIN

IF (DELETING) THEN

BEGIN

MEM_ID = OLD.MEMBER_ID;

DML_EVENT = 'DEL ';

END

ELSE

BEGIN

MEM_ID = NEW.MEMBER_ID;

IF (UPDATING) THEN

DML_EVENT = 'EDIT';

ELSE

DML_EVENT = 'NEW ';

END

INSERT INTO PROCESS_LOG (

TRANS_ID,

USER_ID,

MEMBER_ID,

DML_EVENT,

TIME_STAMP)

VALUES (

CURRENT_TRANSACTION,

CURRENT_USER,

:MEM_ID,

:DML_EVENT,

CURRENT_TIMESTAMP) ;

END ^

Конечно, вы также можете заполнять ваши новые или редактируемые строки непосредственно в триггере BEFORE.

Изменение других таблиц

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

Поддержание обязательного отношения

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

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

Во-первых, создадим две таблицы:

CREATE TABLE MASTER (

ID INTEGER NOT NULL PRIMARY KEY,

DATA VARCHAR(10));

COMMIT;

CREATE TABLE DETAIL (

ID INTEGER NOT NULL PRIMARY KEY,

MASTER_ID INTEGER,

/* Столбец внешнего ключа сознательно сделан в виде, допускающем пустое значение */

DATA VARCHAR(10),

TEMP_FK INTEGER,

CONSTRAINT FK_MASTER FOREIGN KEY(MASTER_ID)

REFERENCES MASTER

ON DELETE CASCADE);

COMMIT;

Когда приложение посылает (post) строки главной и подчиненной таблиц, оно вначале будет передавать подчиненные строки со значением NULL в столбце внешнего ключа и со значением первичного ключа главной таблицы в столбце TEMP FK.

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

CREATE EXCEPTION CANNOT_DEL_DETAIL

'This is the only detail record: it can not be deleted.';

/* Это единственная подчиненная запись: она не может быть удалена */

CREATE GENERATOR GEN_DETAIL;

COMMIT;

Следующий триггер проверяет подчиненную таблицу после добавления новой версии главной записи. Он может "видеть" подчиненные строки, ранее посланные в той же транзакции, которые имеют значение первичного ключа (NEW.ID) В столбце TEMP FK. В случае изменения, в отличие от добавления, он также может определить подчиненные строки, которыми уже "владеет" главная строка. Любые строки, которые соответствуют условию TEMP_FK, получают значение их внешнего ключа, а TEMP_FK устанавливается в NULL.

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

SET TERM ^;

CREATE TRIGGER AI_MASTER FOR MASTER

ACTIVE AFTER INSERT OR UPDATE POSITION 1

AS

BEGIN

IF (NOT (EXISTS (

SELECT 1 FROM DETAIL WHERE MASTER_ID = NEW.ID

OR TEMP_FK = NEW.ID))) THEN

INSERT INTO DETAIL (MASTER_ID)

VALUES (NEW.ID);

ELSE

IF (NOT (EXISTS (

SELECT 1 FROM DETAIL WHERE MASTER_ID = NEW.ID))) THEN

UPDATE DETAIL SET

MASTER_ID = NEW.ID,

TEMP_FK = NULL

WHERE TEMP_FK = NEW.ID;

END ^

Подчиненная таблица получает автоматически сгенерированный ключ:

CREATE TRIGGER BI_DETAIL FOR DETAIL

ACTIVE BEFORE INSERT AS

BEGIN

IF (NEW.ID IS NULL) THEN

NEW.ID = GEN_ID(GEN_DETAIL, 1);

END ^

Следующий триггер BEFORE DELETE для подчиненной таблицы не позволит удалить строку, если она единственная:

CREATE TRIGGER BD_DETAIL FOR DETAIL

ACTIVE BEFORE DELETE POSITION 0

AS

BEGIN

IF (NOT (EXISTS (

SELECT 1 FROM DETAIL

WHERE MASTER_ID = OLD.MASTER_ID

AND ID <> OLD.ID)))

THEN

EXCEPTION CANNOT_DEL_DETAIL;

END ^

Сейчас у нас ситуация, когда обязательное отношение защищено настолько хорошо, что при попытке удалить главную строку этот триггер не позволит выполнить каскадное удаление. Нам нужно еще два триггера для главной таблицы, расширяющие действия триггеров, созданных системой для каскадного удаления. В триггере BEFORE DELETE для главной таблицы мы заполняем пустым значением внешний ключ подчиненной таблицы и устанавливаем значение столбца TEMP FK. После того как будет