Триггер -
это процедура БД, автоматически вызываемая SQL-сервером при обновлении, удалении или добавлении новой записи в ТБД. Непосредственно из программы к триггерам обратиться нельзя. Нельзя и передавать им входные параметры и получать от них значения выходных параметров. Триггеры всегда реализуют действие.По событию изменения ТБД триггеры различаются на вызываемые при:
• добавлении новой записи;
• изменении существующей записи;
• удалении записи.
По отношению к событию, влекущему их вызов, триггеры различаются на:
• выполняемые до наступления события;
• выполняемые после наступления события. Преимущества использования триггеров:
• автоматическое обеспечение каскадных воздействий в дочерних таблицах при изменении, удалении записи в родительской таблице выполняется на сервере. Пользователю нет необходимости заботиться о программной реализации каскадных воздействий. Поскольку каскадные воздействия выполняет сервер, нет необходимости пересылать изменения в таблицах БД из приложения на сервер, что снижает загрузку сетевого трафика;
• изменения в триггерах не влекут необходимости изменения программного кода в клиентских приложениях и не требуют распространения новых версий клиентских приложений у пользователей.
ЗАМЕЧАНИЕ. При откате транзакции откатываются также и все изменения, внесенные в БД триггерами.
Триггер создается оператором
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. 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ля) THENUPDATE
ДочерняяТаблица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_LOGCREATE 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
оГУРЦЫ Использование триггеров для реализации бизнес-правилТриггеры активно используются для реализации бизнес-правил. В частности, это может быть установка с помощью генераторов уникальных значений индексных полей, накапливание статистики в других таблицах и многое другое. К сложностям, возникающим при реализации бизнес-правил при помощи триггеров, следует отнести неразвитость средств отладки логики кода, составляющего тело триггеров.
Покажем на примерах реализацию некоторых бизнес-правил при помощи триггеров.
Пример. Пусть столбец
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
ИмяТриггера;