29. Работа с триггерами

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

По событию изменения ТБД триггеры различаются на вызываемые при:

• добавлении новой записи;

• изменении существующей записи;

• удалении записи.

По отношению к событию, влекущему их вызов, триггеры различаются на:

• выполняемые до наступления события;

• выполняемые после наступления события. Преимущества использования триггеров:

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

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

ЗАМЕЧАНИЕ. При откате транзакции откатываются также и все изменения, внесенные в БД триггерами.

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

Триггер создается оператором

CREATE TRIGGER ИмяТриггера FOR ИмяТаблицы

[ACTIVE I INACTIVE]

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE}

[POSITION номер]

AS <тело триггера>

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

Структура отела триггера:

[<объявление локальных переменных процедуры>]

BEGIN

< оператор>

END

29.2. Определение заголовка триггера

Заголовок триггера имеет формат

... TRIGGER ИмяТриггера FOR ИмяТаблицы

[ACTIVE | INACTIVE]

{BEFORE | AFTER}

{DELETE | INSERT UPDATE}

[POSITION номер]

ACTIVE | INACTIVE - указывает, активен триггер или нет. Можно определить триггер "про запас", установив для него INACTIVE. В дальнейшем можно переопределить триггер как активный. По умолчанию действует ACTIVE.

BEFORE | AFTER - указывает, будет выполняться триггер до (BEFORE) или после (AFTER) запоминания изменений в БД.

DELETE \ INSERT \ UPDATE - указывает операцию над ТБД, при выполнении которой срабатывает триггер.

POSITION номер - указывает, каким по счету будет выполняться триггер в случае наличия группы триггеров, обладающих одинаковыми характеристиками операции и времени (до, после операции) вызова триггера. Значение номера задается числом в диапазоне 0..32 767. Триггеры с меньшими номерами выполняются раньше.

Например, если определены триггеры

CREATE TRIGGER A FOR RASHOD BEFORE INSERT POSITION 1 ...

CREATE TRIGGER С FOR RASHOD BEFORE INSERT POSITION 0...

CREATE TRIGGER D FOR RASHOD BEFORE INSERT POSITION 44 ...

CREATE TRIGGER В FOR RASHOD AFTER INSERT POSITION 1 ...

CREATE TRIGGER E FOR RASHOD AFTER INSERT POSITION 44 ...

для операции добавления новой записи в таблицу RASHOD они будут выполнены в следующей последовательности С, А, D, В, E.

29.3. Значения OLD и NEW

Значение OLD. Имя Столбца позволяет обратиться к состоянию столбца, имевшему место до внесения возможных изменений, а значение NEW. Имя Столбца - к состоянию столбца после внесения возможных изменений.

В том случае, если значение в столбце не изменилось, OLD ИмяСтолбца будет равно NEW ИмяСтолбца.

Пример. Если в записи таблицы TOVARY изменилось значение столбца TOVAR, соответствующие изменения должны быть внесены в таблицу RASHOD:

CREATE TRIGGER BU_TOVARY FOR TOVARY

ACTIVE

BEFORE UPDATE

AS

BEGIN

IF (OLD.TOVAR 0 NEW.TOVAR) THEN UPDATE RASHOD

SET TOVAR = NEW.TOVAR

WHERE TOVAR = OLD.TOVAR;

END

29.4. Обеспечение каскадных воздействий

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

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

CREATE TABLE TOVARY

(TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL, PRIMARY KEY(TOVAR));

CREATE TABLE RASHOD

(N_RASH INTEGER NOT NULL,

TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,

PRIMARY KEY(N_RASH),

FOREIGN KEY(TOVAR) REFERENCES TOVARY

);

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

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

IF (OLD.ПoлeCвязиPoдитeля <> NEW.ПoлeCвязиPoдитeля) THEN

UPDATE ДочерняяТаблица

SET ПолеСвязиДочернейТаблицы = NEW.ПолеСвязиРодителя

WHERE ПолеСвязиДочернейТаблицы = OLD.ПолеСвязиРодителя ;

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

DELETE FROM ДочерняяТаблица

WHERE ПолеСвязиДочернейТаблицы =ПолеСвязиРодителя ;

Пример. Напишем триггеры, выполняющие каскадные обновления и каскадные удаления в дочерней таблице RASHOD после соответственно изменения значения столбца связи или удаления записи в родительской таблице TOVARY:

CREATE TRIGGER BUJTOVARY FOR TOVARY

ACTIVE

BEFORE UPDATE

AS

BEGIN

IF (OLD.TOVAR 0 NEW.TOVAR) THEN UPDATE RASHOD

SET TOVAR = NEW.TOVAR

WHERE TOVAR = OLD.TOVAR;

END

CREATE TRIGGER AD_TOVARY FOR TOVARY

ACTIVE

AFTER DELETE

AS

BEGIN

DELETE FROM RASHOD

WHERE RASHOD.TOVAR = TOVARY.TOVAR;

END

29.5. Ведение журнала изменений

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

Пример. Определим в базе данных таблицу TOVARY_LOG

CREATE TABLE TOVARYJLOG(

DAT_IZM DATE, /* дата изменения */

DEISTV CHAR(3), /* операция */

OLD_TOVAR VARCHAR(20), /* старое значение TOVAR*/

NEW_TOVAR VARCHAR(20) /* новое значение TOVAR */

) ;

в которую будем автоматически записывать любые изменения, добавления, удаления в таблице ТО VARY. При этом будем фиксировать дату, операцию (INS, UPD, DEL) над таблицей TOVARY, а также старое и новое значение столбца TOVAR. Для операции удаления новое значение столбца TOVAR будет пустым. Для операции добавления пустым будет старое значение столбца TOVAR.

CREATE TRIGGER TOVARY_ADD_LOG FOR TOVARY

ACTIVE

AFTER INSERT

AS

BEGIN

INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_T,OVAR, NEW_TOVAR)

VALUES ("NOW","ADD","",NEW.TOVAR) ;

END

CREATE TRIGGER TOVARY_UPD_LOG FOR TOVARY

ACTIVE

AFTER UPDATE

AS

BEGIN

INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_TOVAR,

NEW_TOVAR)

VALUES ("NOW","UPD",OLD.TOVAR,NEW.TOVAR) ;

END

CREATE TRIGGER TOVARY_DEL_LOG FOR TOVARY

ACTIVE

AFTER UPDATE

AS

BEGIN

INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_TOVAR, NEW_TOVAR)

VALUES ("NOW","DEL",OLD.TOVAR,"");

END

Пусть в таблицу TOVARY внесены некоторые изменения. Тогда, выполнив оператор

SELECT * FROM TOVARY_LOG;

получим историю изменений в таблице TOVARY:

DAT_IZM DEISTV OLD_TOVAR NEW_TOVAR

30-JUN-1997 ADD оГУРЦЫ

30-JUN-1997 UPD оГУРЦЫ Огурцы

30-JUN-1997 DEL оГУРЦЫ

29.6. Использование триггеров для реализации бизнес-правил

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

Покажем на примерах реализацию некоторых бизнес-правил при помощи триггеров.

Пример. Пусть столбец N_RASH в таблице RASHOD должен содержать уникальное значение. Для этой цели определим генератор RASHOD_N_RASH и установим его начальное значение 20.

CREATE GENERATOR RASHOD_N_RASH;

SET GENERATOR RASHOD_N_RASH TO 20;

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

CREATE TRIGGER BI_RASHOD_GEN FOR RASHOD

ACTIVE

BEFORE INSERT

BEGIN

NEW.N_RASH = GEN_ID(RASHOD_N_RASH,1);

END

Пример. Пусть в БД имеется таблица STAT_TOVARY, в которой на каждую дату накапливается количество отпущенного товара:

CREATE TABLE STAT_TOVARY(

DAT_RASH DATE NOT NULL,

TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,

KOLVO INTEGER NOT NULL

) ;

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

CREATE TRIGGER AI_RASHOD FOR RASHOD

ACTIVE

AFTER INSERT

AS

DECLARE VARIABLE CNT INTEGER; DECLARE VARIABLE OLD_KOLVO_VAL INTEGER;

BEGIN

/* выбрать число записей в таблице STAT_TOVARY по данному товару за дату расхода */

SELECT COUNT(*) FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR) INTO :CNT;

/* если число записей = 0, добавить запись в таблицу STAT_TOVARY по данному товару и дате */

IF (:CNT = 0) THEN

INSERT INTO STAT_TOVARY (DAT_RASH, TOVAR, KOLVO)

VALUES(NEW.DAT_RASH, NEW.TOVAR, NEW.KOLVO) ;

ELSE

/* иначе добавить новое количество товара в уже существующей записи для этого товара и этой даты в STAT_TOVARY */

BEGIN

SELECT KOLVO FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR)

INTO :OLD_KOLVO_VAL;

UPDATE STAT_TOVARY

SET KOLVO = :OLD_KOLVO_VAL + NEW.KOLVO

WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR);

END

END

CREATE TRIGGER AU_RASHOD FOR RASHOD

ACTIVE

AFTER UPDATE

AS

DECLARE VARIABLE CNT INTEGER;

DECLARE VARIABLE OST_KOLVO INTEGER;

DECLARE VARIABLE OLD_KOLVO_VAL INTEGER;

BEGIN

/* в таблице статистики STAT_TOVARY найти общее количество расхода старого товара по старой дате */

/* из таблицы RASHOD */

SELECT KOLVO FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR)

INTO :OLD_KOLVO_VAL;

/* в таблице статистики STAT_TOVARY уменьшить общее число прихода старого товара */

/* на старое значение количества расхода из таблицы RASHOD */

UPDATE STAT_TOVARY

SET KOLVO = :OLD_KOLVO_VAL - OLD.KOLVO

WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR) ;

OST_KOLVO = OLD_KOLVO_VAL - OLD.KOLVO;

/* если оставшееся количество расхода за эту дату по этому товару равно 0, удалить */

/* запись из таблицы STAT_TOVARY */

IF (:OST_KOLVO = 0) THEN

DELETE FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR);

/* выбрать число записей в таблице STAT_TOVARY по новому товару за новую дату расхода */

SELECT COUNT (*) FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR)

INTO :CNT;

/* если число записей = 0, добавить запись в таблицу STAT_TOVARY по новому товару и новой дате */

IF (:CNT = 0) THEN

INSERT INTO STAT_TOVARY (DAT_RASH, TOVAR, KOLVO)

VALUES(NEW.DAT_RASH, NEW.TOVAR, NEW.KOLVO) ;

ELSE

/* иначе добавить новое количество товара в уже существующей записи для данного товара*/

/* и новой даты в STAT_TOVARY*/

BEGIN

SELECT KOLVO FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR)

INTO :OLD_KOLVO_VAL;

UPDATE STAT_TOVARY

SET KOLVO = :OLD_KOLVO_VAL + NEW.KOLVO

WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR);

END

END

CREATE TRIGGER ADL_RASHOD FOR RASHOD

ACTIVE

AFTER DELETE

AS

DECLARE VARIABLE OST_KOLVO INTEGER; DECLARE VARIABLE OLD_KOLVO_VAL INTEGER;

BEGIN

/* в таблице статистики STAT_TOVARY найти общее количество расхода товара за дату из таблицы RASHOD */

SELECT KOLVO FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR)

INTO :OLD_KOLVO_VAL;

/* в таблице статистики STAT_TOVARY уменьшить общее число прихода товара */

/* на значение количества расхода товара из удаленной таблицы RASHOD */

UPDATE STAT_TOVARY

SET KOLVO = :OLD_KOLVO_VAL - OLD.KOLVO

WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR);

OST_KOLVO = OLD_KOLVO_VAL - OLD.KOLVO;

/* если оставшееся количество расхода по товару за эту дату равно 0, удалить запись из таблицы STAT TOVARY

IF (:OST_KOLVO = 0) THEN

DELETE FROM STAT_TOVARY

WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR);

END

Покажем состояние таблицы STAT_TOVARY после добавления в таблицу RASHOD новых записей по расходу за 12 и 14 февраля:

SELECT * FROM STAT_TOVARY ORDER BY DAT_RASH, TOVAR

DATRASH

TOVAR

KOLVO

12-JAN-1997

Кока-кола

23

12-JAN-1997

Сахар

100

14-JAN-1997

Кока-кола

3

Пусть после изменения в таблице RASHOD информации о расходе товара за 12 февраля:

• вместо 11 единиц товара "Кока-кола" в действительности пришла 21 единица товара "Ставрида консерв.";

• вместо 100 единиц товара "Сахар" пришло 98 единиц этого товара.

Покажем состояние таблицы STAT_TOVARY после внесения в таблицу RASHOD указанных изменений:

SELECT * FROM STAT_TOVARY ORDER BY DAT_RASH, TOVAR

DATRASH

TOVAR

KOLVO

12-JAN-1997

Кока-кола

12

12-JAN-1997

Сахар

98

12-JAN-1997

Ставрида консерв.

21

14-JAN-1997

Кока-кола

3

Покажем состояние таблицы STAT_TOVARY после удаления расхода за 12 февраля товара "Сахар в размере 98 ед. и за 14 февраля товара "Кока-кола в размере 2 ед. из таблицы RASHOD:

SELECT * FROM STAT_TOVARY ORDER BY DAT_RASH, TOVAR

DATRASH

TOVAR

KOLVO

12-JAN-1997

Кока-кола

12

12-JAN-1997

Ставрида консерв.

21

14-JAN-1997

Кока-кола

1

29.7. Изменение и удаление триггеров

Изменить существующий триггер можно при помощи оператора

ALTER TRIGGER ИмяТриггера FOR ИмяТаблицы

[ACTIVE | INACTIVE]

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE}

[POSITION номер]

AS <тело триггера>

После выполнения этого оператора все старые определения триггера заменяются на определения, указанные в операторе ALTER TRIGGER

Для удаления триггера следует воспользоваться оператором

DROP TRIGGER ИмяТриггера;