Триггеры INSTEAD OF
предназначены в PL/SQL для выполнения операций вставки, обновления и удаления элементов представлений базы данных Oracle, но не таблиц. С их помощью можно сделать необновляемое представление обновляемым и изменить поведение обновляемого представления по умолчанию.
Создание триггера INSTEAD OF
Команда создания (или замены) триггера INSTEAD OF
имеет следующий синтаксис:
1 CREATE [OR REPLACE] TRIGGER имя_триггера
2 INSTEAD OF операция
3 ON имя_представления
4 FOR EACH ROW
5 BEGIN
6 ... Код ...
7 END;
Основные элементы определения представлены в следующей таблице.
строки | описание |
1 | Создание триггера с заданным именем. Если триггер существует, а предложение REPLACE отсутствует, попытка создания триггера приведет к ошибке ORA-4081 |
2 | В этой строке проявляются различия между триггерами INSTEAD OF и всеми остальными типами триггеров. Поскольку триггеры INSTEAD OF запускаются не при наступлении определенных событий, а выполняются вместо команд SQL, для них не нужно указывать ни ключевое слово AFTER или BEFORE , ни имя события. Задается только команда (INSERT , UPDATE , MERGE или DELETE ), вместо которой должен выполняться триггер |
3 | Строка аналогична соответствующей строке триггеров DDL и событий базы данных, однако вместо ключевого слова DATABASE или SCHEMA в ней задается имя представления, с которым связан триггер |
4-7 | Стандартный код PL/SQL |
Принцип действия триггеров INSTEAD OF
проще всего объяснить на конкретном примере. Допустим, в системе доставки пиццы используются три таблицы: для учета доставленной продукции, для хранения списка районов доставки и для информации о курьерах:
CREATE TABLE delivery
(delivery_id NUMBER,
delivery_start DATE,
delivery_end DATE,
area_id NUMBER,
driver_id NUMBER);
CREATE TABLE area
(area_id NUMBER, area_desc VARCHAR2(30));
CREATE TABLE driver
(driver_id NUMBER, driver_name VARCHAR2(30));
Для простоты в этом примере не будут использоваться ни первичные, ни внешние ключи. Нам понадобятся три последовательности, обеспечивающие уникальные идентификаторы для таблиц:
CREATE SEQUENCE delivery_id_seq;
CREATE SEQUENCE area_id_seq;
CREATE SEQUENCE driver_id_seq;
Чтобы не объяснять работникам фирмы тонкости нормализации и проектирования реляционных баз данных, мы объединим всю информацию о доставках, районах и курьерах в одно представление:
VIEW delivery_info AS
SELECT d.delivery_id,
d.delivery_start,
d.delivery_end,
a.area_desc,
dr.driver_name
FROM delivery d,
area a,
driver dr
WHERE a.area_id = d.area_id
AND dr.driver_id = d.driver_id
Поскольку вся система запросов строится на основе этого представления, почему бы не использовать его для вставки, обновления и удаления данных? Мы не можем напрямую применять команды DML к этому представлению, так как оно является объединением нескольких таблиц. Как база данных узнает, что следует делать при получении команды INSERT? Необходимо предельно четко объяснить ей, как выполнять операции вставки, обновления и удаления для представления delivery_info; иначе говоря, нужно указать, что делать вместо обычной вставки, обновления или удаления. В этом нам помогут замещающие триггеры INSTEAD OF. Начнем с триггера INSERT.
Триггер INSTEAD OF INSERT
Триггер INSERT
должен выполнить четыре основные операции:
- убедиться в том, что столбец delivery_end содержит
NULL
(информация о доставках должна вноситься только посредством обновления записи); - определить идентификатор курьера по заданному имени. Если имя в таблице отсутствует, триггер присваивает курьеру другой идентификатор и добавляет строку в таблицу курьеров, записав в нее заданное имя и новый идентификатор;
- определить идентификатор района по заданному названию. Если имя в таблице отсутствует, триггер присваивает району другой идентификатор и добавляет строку в таблицу районов, записав в нее заданное имя и новый идентификатор;
- добавить строку в таблицу доставки.
Имейте в виду, что этот пример всего лишь демонстрирует вариант использования триггеров, а не эффективные приемы построения бизнес-систем! Через некоторое время в таблицах скопятся повторяющиеся строки с идентификаторами. Однако это представление ускоряет работу, не требуя обязательного предварительного определения списков курьеров и районов.
TRIGGER delivery_info_insert
INSTEAD OF INSERT
ON delivery_info
DECLARE
-- Курсор для получения идентификатора курьера по имени
CURSOR curs_get_driver_id (cp_driver_name VARCHAR2)
IS
SELECT driver_id
FROM driver
WHERE driver_name = cp_driver_name;
v_driver_id NUMBER;
-- курсор для получения идентификатора района по названию
CURSOR curs_get_area_id (cp_area_desc VARCHAR2)
IS
SELECT area_id
FROM area
WHERE area_desc = cp_area_desc;
v_area_id NUMBER;
BEGIN
/* Значение столбца delivery_end должно быть равно NULL
*/
IF :NEW.delivery_end IS NOT NULL
THEN
raise_application_error
(-20000
, 'Delivery end date value must be NULL when delivery created'
);
END IF;
/*
|| Попытка получить идентификатор курьера по имени.
|| Если имя не найдено, создаем новый идентификатор
|| на основе последовательности.
*/
OPEN curs_get_driver_id (UPPER (:NEW.driver_name));
FETCH curs_get_driver_id
INTO v_driver_id;
IF curs_get_driver_id%NOTFOUND
THEN
SELECT driver_id_seq.NEXTVAL
INTO v_driver_id
FROM DUAL;
INSERT INTO driver
(driver_id, driver_name
)
VALUES (v_driver_id, UPPER (:NEW.driver_name)
);
END IF;
CLOSE curs_get_driver_id;
/*
|| Попытка получить идентификатор района по названию.
|| Если название не найдено, создаем новый идентификатор
|| на основе последовательности.
*/
OPEN curs_get_area_id (UPPER (:NEW.area_desc));
FETCH curs_get_area_id
INTO v_area_id;
IF curs_get_area_id%NOTFOUND
THEN
SELECT area_id_seq.NEXTVAL
INTO v_area_id
FROM DUAL;
INSERT INTO area
(area_id, area_desc
)
VALUES (v_area_id, UPPER (:NEW.area_desc)
);
END IF;
CLOSE curs_get_area_id;
/*
|| Добавление строки с информацией о доставке
*/
INSERT INTO delivery
(delivery_id, delivery_start ,
delivery_end, area_id, driver_id
)
VALUES (delivery_id_seq.NEXTVAL, NVL (:NEW.delivery_start, SYSDATE)
, NULL, v_area_id, v_driver_id
);
END;
Триггер INSTEAD OF UPDATE
Перейдем к триггеру UPDATE
. Чтобы упростить код, мы будем обновлять только поле delivery_end и только в том случае, если оно содержит значение NULL
(нельзя допускать, чтобы курьеры могли изменить время доставки):
TRIGGER delivery_info_update
INSTEAD OF UPDATE
ON delivery_info
DECLARE
-- курсор для получения строки доставки
CURSOR curs_get_delivery (cp_delivery_id NUMBER)
IS
SELECT delivery_end
FROM delivery
WHERE delivery_id = cp_delivery_id
FOR UPDATE OF delivery_end;
v_delivery_end DATE;
BEGIN
OPEN curs_get_delivery (:NEW.delivery_id);
FETCH curs_get_delivery INTO v_delivery_end;
IF v_delivery_end IS NOT NULL
THEN
RAISE_APPLICATION_ERROR (
-20000, 'The delivery end date has already been set');
ELSE
UPDATE delivery
SET delivery_end = :NEW.delivery_end
WHERE CURRENT OF curs_get_delivery;
END IF;
CLOSE curs_get_delivery;
END;
Триггер INSTEAD OF DELETE
Триггер DELETE
в рассматриваемом примере имеет самую простую структуру. Его основная задача — следить за тем, чтобы никто не удалил заполненную строку, а затем самому удалить указанную строку доставки. Строки в таблицах курьеров и районов остаются неизменными.
TRIGGER delivery_info_delete
INSTEAD OF DELETE
ON delivery_info
BEGIN
IF :OLD.delivery_end IS NOT NULL
THEN
RAISE_APPLICATION_ERROR (
-20000,'Completed deliveries cannot be deleted');
END IF;
DELETE delivery
WHERE delivery_id = :OLD.delivery_id;
END;
Заполнение таблиц
Теперь для заполнения всех трех таблиц достаточно одной команды INSERT
, которая содержит известную информацию о доставке (курьер и район):
SQL> INSERT INTO delivery_info(delivery_id,
2 delivery_start,
3 delivery_end,
4 area_desc,
5 driver_name)
6 VALUES
7 NULL, NULL, NULL, 'LOCAL COLLEGE', 'BIG TED');
1 row created.
SQL> SELECT * FROM delivery;
DELIVERY_ID DELIVERY_ DELIVERY_ AREA_ID DRIVER_ID
----------- --------- --------- ---------- ----------
1 13-JAN-02 1 1
SQL> SELECT * FROM area;
AREA_ID AREA_DESC
---------- ------------------------------
1 LOCAL COLLEGE
SQL> SELECT * FROM driver;
DRIVER_ID DRIVER_NAME
---------- ------------------------------
1 BIG TED
Триггеры INSTEAD OF для вложенных таблиц
В Oracle существует много способов хранения сложных структур данных в виде столбцов таблиц или представлений. На логическом уровне такие решения эффективны, поскольку связь между таблицей или представлением и его столбцами предельно очевидна. С технической точки зрения поддержка даже самых простых операций вроде вставки записи в эти сложные структуры потребует не столь очевидных ухищрений. Одна из сложных ситуаций такого рода решается при помощи особой разновидности триггера INSTEAD OF
.
Возьмем следующее представление, объединяющее главы книги со строками главы:
VIEW book_chapter_view AS
SELECT chapter_number,
chapter_title,
CAST(MULTISET(SELECT *
FROM book_line
WHERE chapter_number = book_chapter.chapter_number)
AS book_line_t) lines
FROM book_chapter;
Пожалуй, смысл такого представления понять довольно трудно (почему бы просто не объединить таблицы напрямую?), но оно наглядно демонстрирует применение триггеров INSTEAD OF
для столбцов вложенных таблиц, а также для любых объектов или столбцов- коллекций в представлениях.
После создания записи в таблице BOOK_CHAPTER
и запроса к представлению мы видим, что глава еще не содержит строк:
CHAPTER NUMBER CHAPTER TITLE
------------------------------
LINES(CHAPTER_NUMBER, LINE_NUMBER, LINE_TEXT)
--------------------------------------------------
18 Triggers
BOOK_LINE_T()
Соответственно мы пытаемся создать первую строку:
SQL> INSERT INTO TABLE(SELECT lines
2 FROM book_chapter_view
3 WHERE chapter_number = 18)
4 VALUES(18,1j'Triggers are...');
INSERT INTO TABLE(SELECT lines
*
ERROR at line 1:
ORA-25015: cannot perform DML on this nested table view column
База данных определила, что для простой вставки значений в таблицу BOOK_LINE
, замаскированную под столбец LINES
представления, не хватает информации. Проблема решается с помощью триггера INSTEAD OF
:
TRIGGER lines_ins
INSTEAD OF INSERT ON NESTED TABLE lines OF book_chapter_view
BEGIN
INSERT INTO book_line
(chapter_number,
line_number,
line_text)
VALUES(:PARENT.chapter_number,
:NEW.line_number,
:NEW.line_text);
END;
Теперь мы можем добавить первую строку :
SQL> INSERT INTO TABLE ( SELECT lines
2 FROM book_chapter_view
3 WHERE chapter_number = 18 )
4 VALUES(18,1,'Triggers Are...');
1 row created.
SQL> SELECT *
2 FROM book_chapter_view;
CHAPTER NUMBER CHAPTER TITLE
---------------------------------------------
LINES(CHAPTER_NUMBER, LINE_NUMBER, LINE_TEXT)
---------------------------------------------
18 Triggers
BOOK_LINE_T(BOOK_LINE_O(18, 1, 'Triggers Are...'))
Код SQL, используемый для создания этих триггеров, очень похож на аналогичный код для других триггеров INSTEAD OF
, если не считать пары различий:
- Для обозначения столбца используется предложение
ON NESTED TABLE COLUMN OF
. - Новая псевдозапись
PARENT
содержит значения записи родительского представления.