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