Триггеры событий базы данных в PL/SQL запускаются при возникновении событий уровня базы данных Oracle. Далее перечислены восемь таких событий (два из них появились в Oracle Database 12c) с указанием условия запуска:
- STARTUP — при открытии базы данных;
- SHUTDOWN — при нормальном закрытии базы данных;
- SERVERERROR — при возникновении ошибки Oracle;
- LOGON — при запуске сеанса Oracle;
- LOGOFF — при нормальном завершении сеанса Oracle;
- DB_ROLE_CHANGE — при назначении резервной базы данных первичной, или наоборот.
- AFTER_CLONE (Oracle Database 12c) — может указываться только в сочетании с
PLUGGABLE DATABASE
. После копирования (клонирования) подключаемой базы данных (PDB, Pluggable DataBase) база данных инициирует триггер в новой PDB и удаляет его. Если при выполнении триггера происходит ошибка, то операция копирования завершается неудачей. - BEFOREUNPLUG (Oracle Database 12c) — может указываться только в сочетании с
PLUGGABLEDATABASE
. Перед отключением PDB база данных активизирует триггер в новой PDB и удаляет его. Если при выполнении триггера происходит ошибка, то операция отключения завершается неудачей.
Эти триггеры предоставляют полезные возможности для автоматизации процесса администрирования базы данных и точного управления ее функционированием.
Создание триггера события базы данных
Синтаксис создания этих триггеров в PL/SQL очень похож на синтаксис триггеров DDL :
1 CREATE [OR REPLACE] TRIGGER имя_триггера
2 {BEFORE | AFTER} {событие} ON {DATABASE | SCHEMA}
3 DECLARE
4 Объявления переменных
5 BEGIN
6 ...Код...
7 END;
Не все события поддерживают оба атрибута, BEFORE
и AFTER
. Некоторые комбинации просто не имеют смысла:
- BEFORE STARTUP. Даже если такие триггеры можно было бы создавать, когда они должны запускаться? При попытке создания триггеров такого типа выводится сообщение об ошибке (
ORA-30500
). - AFTER SHUTDOWN. Опять же, когда должны запускаться такие триггеры? При попытке создания таких триггеров выводится сообщение об ошибке (
ORA-30501
). - BEFORE LOGON. Эти триггеры уж слишком предусмотрительны: «Кажется, кто-то собирается подключиться — давайте сделаем что-нибудь!» Но Oracle подходит к делу более реалистично и выводит сообщение об ошибке (
ORA-30508
). - AFTER LOGOFF. «Пожалуйста, вернитесь! Не отключайтесь!» При попытке создания таких триггеров выводится сообщение об ошибке (
ORA-30509
). - BEFORE SERVERERROR. Мечта каждого программиста! Только представьте:
CREATE OR REPLACE TRIGGER BEFORE_SERVERERROR
BEFORE SERVERERROR ON DATABASE
BEGIN
diagnose_impending_error; -- обнаруживаем будущую ошибку
fix_error_condition; -- исправляем ее причину
continue_as_if_nothing_happened; -- продолжаем работу
END;
К сожалению, так не бывает. Мечты разбиваются сообщением об ошибке (ORA-30500
).
Триггер STARTUP
Триггеры типа STARTUP
запускаются при загрузке базы данных. Это прекрасный момент для выполнения подготовительных работ — например, для фиксации объектов в общем пуле, чтобы они не удалялись в связи с продолжительным бездействием.
Чтобы создать триггер события STARTUP
, пользователь должен обладать привилегией ADMINISTER DATABASE TRIGGER
.
Пример создания триггера STARTUP
:
CREATE OR REPLACE TRIGGER startup_pinner
AFTER STARTUP ON DATABASE
BEGIN
pin_plsql_packages;
pin_application_packages;
END;
Триггеры SHUTDOWN
Триггеры BEFORE SHUTDOWN
запускаются перед закрытием базы данных. Они отлично подходят для сбора статистики о системе. Приведем пример создания триггера события shutdown
:
CREATE OR REPLACE TRIGGER before_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
gather_system_stats;
END;
Триггеры SHUTDOWN
запускаются только при остановке базы данных в режиме NORMAL
или IMMEDIATE
. Они не выполняются при завершении работы в режиме ABORT
и в результате сбоев.
Триггер LOGON
Триггеры AFTER LOGON
запускаются в начале сеанса Oracle. Их удобно использовать для формирования контекста сеанса и выполнения других задач предварительной настройки. Пример триггера события LOGON
:
TRIGGER after_logon
AFTER LOGON ON SCHEMA
DECLARE
v_sql VARCHAR2(100) := 'ALTER SESSION ENABLE RESUMABLE ' ||
'TIMEOUT 10 NAME ' || '''' ||
'OLAP Session ' || '''';
BEGIN
EXECUTE IMMEDIATE v_sql;
DBMS_SESSION.SET_CONTEXT('OLAP Namespace',
'Customer ID',
load_user_customer_id);
END;
Триггеры LOGOFF
Триггеры BEFORE LOGOFF
запускаются при нормальном отсоединении пользователя от базы данных; в них удобно собирать статистику уровня сеанса. Пример создания триггера для события LOGOFF
:
TRIGGER before_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
gather_session_stats;
END;
Триггеры SERVERERROR
Триггеры AFTER SERVERERROR
запускаются в тот момент, когда Oracle генерирует какую- либо ошибку. Исключение составляют следующие ошибки:
- ORA-00600 — внутренняя ошибка Oracle;
- ORA-01034 — нет доступа к Oracle;
- ORA-01403 — данные не найдены;
- ORA-01422 — при выборке возвращается больше строк, чем указано в запросе;
- ORA-01423 — при попытке выборки дополнительных строк произошла ошибка;
- ORA-04030 — нехватка памяти.
Кроме того, триггеры AFTER SERVERERROR
не запускаются, когда исключение инициируется внутри триггера (для предотвращения бесконечной рекурсии).
Триггеры AFTER SERVERERROR
не предоставляют средств для исправления ошибки — они предназначены только для сохранения информации о ней. На этой базе можно построить достаточно мощные механизмы регистрации ошибок.
Oracle также предоставляет встроенные функции (также определенные в пакете DBMS_STANDARD
) для получения информации о стеке ошибок, формируемом при возникновении исключения:
- ORA_SERVER_ERROR — возвращает номер ошибки Oracle в заданной позиции стека. Если в этой позиции ошибки нет, возвращает 0.
- ORA_lS_SERVERERROR — возвращает
TRUE
, если в стеке ошибок текущего исключения имеется ошибка с заданным номером. - ORA_SERVER_ERROR_DEPTH — возвращает количество ошибок в стеке.
- ORA_SERVER_ERROR_MSG — возвращает полный текст сообщения об ошибке для заданной позиции. Если в этой позиции ошибки нет, возвращает
NULL
. - ORA_SERVER_ERROR_NUM_PARAMS — возвращает количество параметров, связанных с сообщением об ошибке в заданной позиции. Если в этой позиции ошибки нет, возвращает 0.
- ORA_SERVER_ERROR_PARAM — возвращает значение параметра в заданной позиции. Если параметр не найден, возвращает
NULL
.
Примеры триггеров SERVERERROR
Рассмотрим несколько примеров использования функций SERVERERROR
. Начнем с очень простого триггера, который просто сообщает о возникновении ошибки:
TRIGGER error_echo
AFTER SERVERERROR
ON SCHEMA
BEGIN
DBMS_OUTPUT.PUT_LINE ('You experienced an error');
END;
При возникновении ошибки Oracle (и при условии, что параметр SERVEROUTPUT
имеет значение ON
) на экран будет выведено такое сообщение:
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));
You experienced an error
BEGIN DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A')); END ;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1
Обратите внимание: сообщение об ошибке Oracle выводится после сообщения триггера. Благодаря этому информацию об ошибке можно получить и сохранить еще до того, как Oracle предпримет какие-либо действия.
Триггеры SERVERERROR
автоматически изолируются в собственных автономных транзакциях. В частности, это означает, что можно записать информацию об ошибке в таблицу-журнал и сохранить ее командой COMMIT
без изменения состоянии транзакции сеанса, в которой произошла ошибка.
Триггер error_logger
гарантирует, что информация обо всех ошибках (кроме перечисленных ранее) будет автоматически записана в журнал. При этом не имеет значения, кем или чем была вызвана ошибка — пользователем, самими приложением или какой- то программой.
TRIGGER error_logger
AFTER SERVERERROR
ON SCHEMA
DECLARE
v_errnum NUMBER; -- Номер ошибки Oracle
v_now DATE := SYSDATE; -- текущее время
BEGIN
-- Для каждой ошибки в стеке ...
FOR e counter IN 1..ORA SERVER ERROR DEPTH LOOP
-- Записать информацию об ошибке в журнал; фиксация транзакции
-- не требуется, поскольку данная транзакция автономна.
INSERT INTO error_log(error_id,
username,
error_number,
sequence,
timestamp)
VALUES(error_seq.nextval,
USER,
ORA_SERVER_ERROR(e_counter),
e_counter,
v_now);
END LOOP; -- для каждой ошибки в стеке
END;
Помните, что все новые строки таблицы error_log
будут зафиксированы к моменту выполнения оператора END
, поскольку триггер PL/SQL выполняется в автономной транзакции. Пример использования этого триггера:
SQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
SQL> SELECT * FROM error_log;
USERNAME ERROR_NUMBER SEQUENCE TIMESTAMP
------------------------------ ------------ ---------- ---------
BOOK 6502 1 04-JAN-02
BOOK 6512 2 04-JAN-02
Почему в таблице хранятся две ошибки, когда была инициирована всего одна ошибка? Дело в том, что стек ошибок, генерируемый базой данных, содержит ошибки ORA-06502
и ORA-06512
, которые регистрируются в порядке их возникновения.
Если вы хотите быстро определить, присутствует ли в стеке определенная ошибка, без ручного разбора его содержимого, используйте вспомогательную функцию 0RA_IS_ SERVERERROR
. Эта функция чрезвычайно полезна для контроля за конкретными ошибками, которые могут потребовать дополнительной обработки — например, пользовательских исключений. Код может выглядеть примерно так:
-- Специальная обработка пользовательских ошибок.
-- Ошибки с 20000 по 20010 инициируются вызовами
-- RAISE APPLICATION ERROR
FOR errnum IN 20000 .. 20010
LOOP
IF ORA_IS_SERVERERROR (errnum)
THEN
log_user_defined_error (errnum);
END IF;
END LOOP;
Все номера ошибок Oracle отрицательны, кроме 1
(исключение, определяемое пользователем) и 100
(синоним 1403
, NO_DATA_FOUND
). Однако номер ошибки, передаваемый при вызове ORA_IS_SERVERERROR
, должен быть положительным, как в приведенном примере.
Центральный обработчик ошибок
Реализовать раздельные триггеры SERVERERROR
в каждой схеме базы данных возможно, но я рекомендую создать единый центральный триггер с сопроводительным пакетом PL/SQL
для предоставления следующих возможностей:
- Централизованная обработка ошибок — всего один триггер и пакет, которые хранятся в памяти Oracle (централизация также упрощает сопровождение).
- Сеансовый журнал ошибок с возможностью поиска — журнал ошибок может накапливаться во время сеанса. Поиск по журналу позволяет получить такую информацию, как количество вхождений ошибки, временные метки первого и последнего вхождения и т. д. Также возможна очистка журнала по требованию.
- Возможность сохранения журнала ошибок — при необходимости журнал можно сохранить в таблице.
- Возможность просмотра текущего журнала — текущий журнал ошибок может просматриваться по конкретному номеру ошибки и/или диапазону дат.
Реализация такого централизованного пакета обработки ошибок содержится в файле error_log.sql на сайте github. Когда пакет будет создан, триггер SERVERERROR
создается так:
CREATE OR REPLACE TRIGGER error_log
AFTER SERVERERROR
ON DATABASE
BEGIN
central_error_log.log_error;
END;
Рассмотрим несколько примеров использования. Для начала сгенерируем ошибку:
SQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Теперь проведем поиск по номеру ошибки и прочитаем информацию в запись:
DECLARE
v_find_record central_error_log.v_find_record;
BEGIN
central_error_log.find_error(6502,v_find_record);
DBMS_OUTPUT.PUT_LINE('Total Found = ' || v_find_record.total_found);
DBMS_OUTPUT.PUT_LINE('Min Timestamp = ' || v_find_record.min_timestamp);
DBMS_OUTPUT.PUT_LINE('Max Timestamp = ' || v_find_record.max_timestamp);
END;
Результат:
Total Found = 1
Min Timestamp = 04-JAN-02
Max Timestamp = 04-JAN-02