Триггеры PL/SQL уровня DDL на примерах: CREATE OR REPLACE TRIGGER

База данных Oracle позволяет определять триггеры, срабатывающие при выполнении команды DDL — проще говоря, любых команд SQL, создающих или модифицирующих объекты базы данных (таблицы, индексы и т. д.). Несколько примеров команд DDL: CREATE TABLE, ALTER INDEX, DROP TRIGGER — каждая из них создает, изменяет или удаляет объект базы данных. Синтаксис создания триггеров команд DDL почти не отличается от синтаксиса триггеров PL/SQL для DML. Они различаются лишь перечнем инициирующих событий и тем, что триггеры DDL не связываются с конкретными таблицами.

Весьма специфический триггер INSTEAD OF CREATE TABLE, описанный в конце раздела, позволяет манипулировать со стандартным поведением события CREATE TABLE. Не все аспекты синтаксиса и использования триггеров, описанные далее, применимы к этому типу триггеров.

Создание триггера DDL

Команда создания (или замены) триггера DDL имеет следующий синтаксис:

1   CREATE [OR REPLACE] TRIGGER имя_триггера
2   {BEFORE | AFTER } { событие_DDL} ON {DATABASE | SCHEMA}
3      [WHEN (...)]
4   DECLARE
5      Объявления переменных
6   BEGIN
7      ...код триггера...
8   END;

Элементы триггера описаны в следующей таблице.

Приведем пример простого триггера, оповещающего о создании любых объектов:

SQL> CREATE OR REPLACE TRIGGER town_crier
   2   AFTER CREATE ON SCHEMA
   3   BEGIN
   4   DBMS_OUTPUT.PUT_LINE('I believe you have created something!');
   5   END;
   6   /
Trigger created.

SQL> SET SERVEROUTPUT ON 
SQL> CREATE TABLE a_table 
   2 (col1 NUMBER);
Table created.

SQL> CREATE INDEX an_index ON a_table(col1);
Index created.

SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS
   2   BEGIN
   3   RETURN(TRUE);
   4   END;
   5   /
Function created.

SQL> /* Очистка буфера DBMS_OUTPUT */ 
SQL> BEGIN NULL; END;
   2   /
I believe you have created something! 
I believe you have created something! 
I believe you have created something!

PL/SQL procedure successfully completed.

Текст, возвращаемый встроенным пакетом DBMS_OUTPUT, не будет выводиться триггером DDL до успешного выполнения блока PL/SQL, даже если этот блок не выполняет никаких действий.

Однако сообщая о создании объекта, этот триггер не уточняет, что же именно создается. Но на самом деле триггеры DDL могут предоставлять и более полную информацию, ведь триггер можно переписать и таким образом:

SQL> CREATE OR REPLACE TRIGGER town_crier
   2   AFTER CREATE ON SCHEMA
   3   BEGIN
   4   -- Использование атрибутов события для получения более полной информации
   5   DBMS_OUTPUT.PUT_LINE('I believe you have created a ' ||
   6                        ORA_DICT_OBJ_TYPE || ' called ' ||
   7                        ORA_DICT_OBJ_NAME);
   8	END;
   9	/
Trigger created.

SQL> SET SERVEROUTPUT ON 
SQL> CREATE TABLE a_table 
   2 col1 NUMBER);
Table created.

SQL> CREATE INDEX an_index ON a_table(col1);
Index created.
SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS
   2   BEGIN
   3   RETURN(TRUE);
   4   END;
   5   /
Function created.

SQL> /*-- Очистка буфера DBMS_OUTPUT */

SQL> BEGIN NULL; END;
2   /
I believe   you   have   created   a   TABLE called A_TABLE
I believe   you   have   created   a   INDEX called AN_INDEX
I believe   you   have   created   a   FUNCTION called A_FUNCTION

PL/SQL procedure successfully completed.

В этих примерах представлены два важнейших аспекта триггеров DDL: события, с ко­торыми они связываются, и атрибуты событий, которые в них доступны.

События триггеров

Список событий, которые можно связать с триггерами DDL, приведен в табл. 1. Лю­бой триггер DDL может вызываться как до (BEFORE), так и после (after) наступления указанного события.

Таблица 1. События DLL

Как и триггеры DML, триггеры DDL запускаются, когда в заданной базе данных или схеме происходят связанные с ними события. Количество типов триггеров, которые могут быть определены в базе данных или схеме, не ограничено.

Атрибутные функции

Oracle предоставляет набор функций (определенных в пакете DBMS_STANDARD) для получения информации о причине запуска триггера DDL и других связанных с ним параметрах (например, имя удаляемой таблицы). Перечень этих атрибутных функций приведен в табл. 2, а примеры их использования — в следующих разделах.

Таблица 2. События триггеров DDL и атрибутные функции

Об атрибутных функциях необходимо дополнительно сказать следующее:

•  Тип данных 0RA_NAME_LIST_T определен в пакете DBMS_STANDARD так:

TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);

Иными словами, это вложенная таблица строк, каждая из которых может содержать до 64 символов.

•  События триггеров DDL и атрибутные функции также определены в пакете DBMS_ STANDARD. Для каждой из функций этого пакета Oracle создает независимую функцию, добавляя к ее имени префикс 0RA_, для чего при создании базы данных выполняется сценарий  $ORACLE_HOME/rdbms/dbmstrig.sql. В некоторых версиях Oracle этот сценарий содержит ошибки, из-за которых независимые функции не видны или не выполняют­ся. Если вы сомневаетесь в правильности определения этих элементов, попросите ад­министратора базы данных проверить сценарий и внести необходимые исправления.
•  Представление словаря данных USER_S0URCE  не обновляется до срабатывания обо­их триггеров DDL, BEFORE и AFTER. Иначе говоря, вы не сможете использовать эти функции для реализации системы контроля версий «до и после», построенной ис­ключительно в границах базы данных и основанной на триггерах.

Применение событий и атрибутов

Возможности триггеров DDL лучше всего продемонстрировать на примерах. Для на­чала рассмотрим триггер, который блокирует создание любых объектов базы данных:

TRIGGER no_create
   AFTER CREATE ON SCHEMA 
BEGIN
   RAISE_APPLICATI0N_ERR0R (
      -20000,
      'ERROR : Objects cannot be created in the production database.'
   );
END;

После его создания в базе данных не удастся создать ни один объект:

SQL> CREATE TABLE demo (coll NUMBER);
*
ERROR at line 1:
ORA-20000: Objects cannot be created in the production database.

Сообщение об ошибке получилось кратким и не слишком содержательным. Произошел сбой, но какой именно? Хорошо бы включить в сообщение дополнительную информа­цию, например указать тип объекта, который пытался создать пользователь:

TRIGGER no_create 
AFTER CREATE ON SCHEMA 
BEGIN
   RAISE_APPLICATION_ERROR (-20000,
         'Cannot create the '   || ORA_DICT_OBJ_TYPE   ||
         ' named'               || ORA_DICT_OBJ_NAME   ||
         ' as requested by'     || ORA_DICT_OBJ_OWNER  ||
         ' in production.');
END;

С таким триггером попытка создать таблицу в базе данных приведет к выводу более подробного сообщения об ошибке:

SQL> CREATE TABLE demo (coll NUMBER);
*
ERROR at line 1:
ORA-20000: Cannot create the TABLE named DEMO as requested by SCOTT in production

Можно было бы даже реализовать эту логику в виде триггера BEFORE и воспользоваться событием ora_sysevent:

TRIGGER no_create 
BEFORE DDL ON SCHEMA 
BEGIN
   IF ORA_SYSEVENT = 'CREATE'
   THEN
      RAISE_APPLICATION_ERROR (-20000,
         'Cannot create the '      || ORA_DICT_OBJ_TYPE ||
         ' named '                 ||ORA_DICT_OBJ_NAME ||
         ' as requested by '       || ORA_DICT_OBJ_OWNER);
   ELSIF ORA_SYSEVENT = 'DROP'
   THEN
      -- Логика операций DROP
      ...
   END IF;
END;

Какой столбец был изменен?

Для получения информации о том, какой столбец был изменен командой ALTER TABLE, можно воспользоваться функцией ORA_IS_ALTER_COLUMN. Пример:

TRIGGER preserve_app_cols 
   AFTER ALTER ON SCHEMA 
DECLARE
   -- Курсор для получения информации о столбцах таблицы 
   CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2)
   IS
      SELECT column_name 
         FROM all_tab_columns
      WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
   -- Если была изменена таблица ...
   IF ora_dict_obj_type = 'TABLE'
   THEN
      -- Для каждого столбца в таблице...
      FOR v_column_rec IN curs_get_columns (
                           ora_dict_obj_owner,
                           ora_dict_obj_name
                        )
   LOOP
      -- Является ли текущий столбец измененным?
      IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name)
      THEN
         -- Отклонить изменения в "критическом" столбце 
         IF mycheck.is_application_column ( 
               ora_dict_obj_owner, 
               ora_dict_obj_name, 
               v_column_rec.column_name
         )
      THEN
            CENTRAL_ERROR_HANDLER (
               'FAIL',
               'Cannot alter core application attributes'
            );
            END IF; -- критическая таблица/столбец 
         END IF; -- текущий столбец был изменен 
      END LOOP; -- для каждого столбца в таблице 
   END IF; -- таблица была изменена 
END;

Попытки изменения критических атрибутов приложения становятся невозможными. Помните, что эта логика не будет работать, если триггер срабатывает при добавлении новых столбцов. При срабатывании триггера DDL информация о столбцах еще не видна в словаре данных.

Попытки удаления конкретных столбцов можно проверять следующим образом:

IF ORA_IS_DROP_COLUMN ('COL2')
THEN
   что-то сделать 
ELSE
   сделать что-то другое!
END IF;

Функции ORA_IS_DROP_COLUMN и ORA_IS_ALTER_COLUMN не обращают внимания на то, к какой таблице присоединен столбец; они работают исключи­тельно по имени столбца.

Списки, возвращаемые атрибутными функциями

Некоторые атрибутные функции возвращают данные двух типов: список элементов и количество элементов. Например, функция ORA_GRANTEE возвращает список и ко­личество пользователей, которым предоставлены определенные права, а функция 0RA_PRIVILEGE_LIST — список и количество предоставленных прав. Обычно обе эти функции применяются в триггерах AFTER GRANT. Пример использования этих функций представлен в файле privs.sql на сайте github. Фрагмент кода этого примера:

TRIGGER what_privs
   AFTER GRANT ON SCHEMA
DECLARE
   v_grant_type      VARCHAR2 (30);
   v_num_grantees    BINARY_INTEGER;
   v_grantee_list    ora_name_list_t;
   v_num_privs       BINARY_INTEGER;
   v_priv_list       ora_name_list_t;
BEGIN
   -- Получение информации о типе с последующей выборкой списков.
   v_grant_type := ORA_DICT_OBJ_TYPE;
   v_num_grantees := ORA_GRANTEE (v_grantee_list);
   v_num_privs := ORA_PRIVILEGE_LIST (v_priv_list);

   IF v_grant_type = 'ROLE PRIVILEGE'
   THEN
      DBMS_OUTPUT.put_line (
         'The following roles/privileges were granted');

      -- Вывод привилегии для каждого элемента списка.
      FOR counter IN 1 .. v_num_privs
      LOOP
         DBMS_OUTPUT.put_line ('Privilege ' || v_priv_list (counter));
      END LOOP;

Триггер прекрасно подходит для получения подробной информации о правах пользовате­лей и объектах базы данных, указанных в командах GRANT. Эту информацию также можно сохранить в базе данных, добавив журнал с подробными сведениями об изменениях.

SQL> GRANT DBA TO book WITH ADMIN OPTION;
Grant succeeded.
SQL> EXEC DBMS_OUTPUT.PUT_LINE('Flush buffer');
         The following roles/privileges were granted 
                  Privilege UNLIMITED TABLESPACE 
                  Privilege DBA 
         Grant Recipient BOOK 
Flush buffer

SQL> GRANT SELECT ON x TO system WITH GRANT OPTION;
Grant succeeded.

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Flush buffer');
         The following object privileges were granted 
                  Privilege SELECT 
         On X with grant option 
         Grant Recipient SYSTEM 
Flush buffer

Можно ли удалить неудаляемое?

Я показал, что триггеры DDL могут использоваться для предотвращения выполнения определенного типа операций DDL с конкретными объектами или типами объектов. А если я создам триггер, который предотвращает DDL-операции DROP, а затем попытаюсь удалить сам триггер? Не появится ли триггер, который по сути невозможно удалить? К счастью, в Oracle этот сценарий был предусмотрен:

SQL> CREATE OR REPLACE TRIGGER undroppable
   2   BEFORE DROP ON SCHEMA
   3   BEGIN
   4      RAISE_APPLICATION_ERROR(-20000,'You cannot drop me! I am invincible!');
   5   END;

SQL> DROP TABLE employee;
*
ERROR at line 1:
ORA-20000: You cannot drop me! I am invincible!

SQL> DROP TRIGGER undroppable;
Trigger dropped.

При работе с подключаемыми базами данных (Oracle Database 12c и выше) можно вставить ключевое слово PLUGGABLE перед DATABASE в определении триггера. DATABASE (без PLUGGABLE) определяет триггер на корневом уровне. В мультиарендной (multitenant) контейнерной базе данных (CDB) только пользователь, подключившийся к корневому уровню, может создать триггер для всей базы данных. PLUGGABLE DATABASE определяет триггер для подключаемой базы данных, к которой вы подключены. Триггер срабатывает каждый раз, когда любой пользователь заданной базы данных или PDB инициирует активизирующее событие.

Триггер INSTEAD OF CREATE

Oracle предоставляет триггер INSTEAD OF CREATE для автоматической группировки данных таблиц. Для этого триггер должен перехватить выполняемую команду SQL, вставить в нее условие группировки, а затем выполнить при помощи функции ORA_SQL_TXT. Сле­дующий пример показывает, как это делается.

TRIGGER io_create
   INSTEAD OF CREATE ON DATABASE WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
DECLARE
   v_sql VARCHAR2 (32767);	-- Генерируемый код sql
   v_sql_t ora_name_list_t; -- таблица sql 
BEGIN
   -- Получение выполняемой команды SQL 
   FOR counter IN 1 .. ora_sql_txt (v_sql_t)
   LOOP
   v_sql := v_sql || v_sql_t (counter);
   END LOOP;
   -- Для определения условия PARTITION будет вызвана 
   -- функция magic_partition. 
   v_sql :=
         SUBSTR (v_sql, 1, ora_partition_pos)
      || magic_partition_function
      || SUBSTR (v_sql, ora_partition_pos + 1);

   /* Вставить перед именем таблицы имя пользователя.
   | Комбинации CRLF заменяются пробелами.
   | Операция требует наличия явной привилегии CREATE ANY TABLE,
   | если только вы не переключились на модель AUTHID CURRENT_USER.
   */
   v_sql :=
      REPLACE (UPPER (REPLACE (v_sql, CHR (10), ' '))
            , 'CREATE TABLE '
            , 'CREATE TABLE ' || ora_login_user || '.'
             );
   -- Выполнение сгенерированной команды SQL 
   EXECUTE IMMEDIATE v_sql;
END;

Теперь группировка таблиц будет осуществляться автоматически в соответствии с ло­гикой функции my_partition.

Oracle предоставляет несколько способов группировки (диапазоны, хеширование) и ло­гических моделей группировки (например, по первичному ключу или по уникальному ключу). Вы должны решить, какие из них должны использоваться в вашей функции группировки.

Если не включить только что приведенную секцию WHEN, при попытке создания объектов, отличных от таблиц, происходит ошибка:

ORA-00604: error occurred at recursive SQL level 1 
ORA-30511: invalid DDL operation in system triggers

Кроме того, при попытке создания триггера INSTEAD OF для любой другой операции DDL, кроме CREATE, компилятор выдает сообщение об ошибке (ORA-30513).

Триггеры INSTEAD OF для операций DML (вставка, обновление и удаление) будут рас­сматриваться мною далее в блоге. Эти триггеры используют некоторые элементы синтаксиса триггера INSTEAD OF CREATE для таблиц, но этим сходство между ними ограничивается.

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

Встроенные методы коллекций PL... 14859 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Управление приложениями PL/SQL... 4654 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48

Тип данных RAW в PL/SQL 12367 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Символьные функции и аргументы... 18600 просмотров Анатолий Wed, 23 May 2018, 18:54:01