Триггеры PL/SQL для событий базы данных Oracle на примере

Триггеры PL/SQL для событий базы ОраклТриггеры событий  базы данных в 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

 

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

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1519 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 2321 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 1835 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 3692 просмотров Анатолий Wed, 23 May 2018, 18:54:01

Comments on Триггеры PL/SQL для событий базы данных Oracle на примере

Будьте первым прокомментировавшим
Пожалуйста, авторизуйтесь, для комментирования