База данных 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;
Элементы триггера описаны в следующей таблице.
Строки | Описание |
1 | Создание триггера с заданным именем. Если триггер существует, а секция REPLACE отсутствует, попытка создания триггера приведет к ошибке ORA-4081 |
2 | Строка определяет, должен ли триггер запускаться до или после наступления заданного события DDL , а также должен ли он запускаться для всех операций в базе данных или только в текущей схеме. Секция INSTEAD OF поддерживается только в Oracle9i Release 1 и последующих версиях |
3 | Необязательное условие WHEN , позволяющее задать логику для предотвращения ненужного выполнения триггера |
4-7 | Образец тела триггера |
Приведем пример простого триггера, оповещающего о создании любых объектов:
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
Событие DDL | описание |
ALTER | Создание объекта базы данных командой SQL ALTER |
ANALYZE | Анализ состояния объекта базы данных командой SQL |
ASSOCIATE STATISTICS | Связывание статистики с объектом базы данных |
AUDIT | Включение аудита базы данных командой SQL AUDIT |
COMMENT | Создание комментария для объекта базы данных |
CREATE | Создание объекта базы данных командой SQL CREATE |
DDL | Любое из перечисленных событий |
DISASSOCIATE STATISTICS | Удаление статистики,связанной с объектом баз данных |
DROP | Удаление объекта базы данных командой SQL DROP |
GRANT | Назначение прав командой SQL GRANT |
NOAUDIT | Отключение аудита базы данных командой SQL NOAUDIT |
RENAME | Переименование объекта базы данных командой SQL RENAME |
REVOKE | Отмена прав командой SQL REVOKE |
TRUNCATE | Очистка таблицы командой SQL TRUNCATE |
Как и триггеры DML, триггеры DDL запускаются, когда в заданной базе данных или схеме происходят связанные с ними события. Количество типов триггеров, которые могут быть определены в базе данных или схеме, не ограничено.
Атрибутные функции
Oracle предоставляет набор функций (определенных в пакете DBMS_STANDARD
) для получения информации о причине запуска триггера DDL и других связанных с ним параметрах (например, имя удаляемой таблицы). Перечень этих атрибутных функций приведен в табл. 2, а примеры их использования — в следующих разделах.
Таблица 2. События триггеров DDL и атрибутные функции
Функция | Что возвращает |
ORA_CLIENT_IP_ADDRESS | IP-адрес клиента |
ORA_DATABASE_NAME | Имя базы данных |
ORA_DES_ENCRYPTED_PASSWORD | Пароль текущего пользователя, зашифрованный с использованием алгоритма DES |
ORA_DICT_OBJ_NAME | Имя объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера |
ORA_DICT_OBJ_NAME_LIST | Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
ORA_DICT_OBJ_OWNER | Имя владельца объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера |
ORA_DICT_OBJ_OWNER_LIST | Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список имен этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
ORA_DICT_OBJ_TYPE | Тип объекта базы данных, связанного с командой DDL, вызвавшей запуск триггера (например, TABLE или INDEX ) |
ORA_GRANTEE | Количество пользователей, получивших привилегии. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
ORA_INSTANCE_NUM | Номер экземпляра базы данных |
ORA_IS_ALTER_COLUMN | TRUE , если изменяется столбец, заданный параметром COLUMN_NAME ; FALSE в противном случае |
ORA_IS_CREATING_NESTED_TABLE | TRUE , если создается вложенная таблица; FALSE в противном случае |
ORA_IS_DROP_COLUMN | TRUE , если удаляется столбец, заданный параметром COLUMN _NAME; FALSE в противном случае |
ORA_LOGIN_USER | Имя пользователя Oracle , для которого запущен триггер |
ORA_PARTITION_POS | Позиция команды SQL для корректной вставки секции PARTITION |
ORA_PRIVILEGE_LIST | Количество предоставленных или отмененных привилегий. В аргументе PRIVILEGE_LIST содержится полный список привилегий в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
ORA_REVOKEE | Количество пользователей, лишенных привилегий. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T |
ORA_SQL_TXT | Количество строк в команде SQL, которая вызвала запуск триггера. Аргумент SQL_TXT возвращает каждую строку команды в виде аргумента типа DBMS_STANDARD.ORA_NAME_LIST_T |
ORA_SYSEVENT | Тип события, вызвавшего запуск триггера DDL (например, CREATE , DROP или ALTER ) |
ORA_WITH_GRANT_OPTION | TRUE , если привилегии предоставлены конструкцией GRANT ; FALSE в противном случае |
Об атрибутных функциях необходимо дополнительно сказать следующее:
- Тип данных 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
для таблиц, но этим сходство между ними ограничивается.