Триггеры INSTEAD OF в PL/SQL на примере

Триггеры INSTEAD OF в PL/SQLТриггеры 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 содержит значения записи родительского представления.

 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4633 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14737 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Типы данных INTERVAL в PL/SQL:...
Типы данных INTERVAL в PL/SQL:... 22997 просмотров Doctor Sat, 19 Oct 2019, 10:56:26
Символьные функции и аргументы...
Символьные функции и аргументы... 18545 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Войдите чтобы комментировать

1dz аватар
1dz ответил в теме #10127 2 года 7 мес. назад
Триггеры INSTEAD OF порой незаменимы. Спасибо за дельные примеры!