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

Просмотров: 6936
Doc

Doc

АйТишник со стажем... Профиль автора.

Триггеры PL/SQL для событий базы ОраклТриггеры событий  базы данных в PL/SQL запускаются при возникновении событий уровня базы данных Oracle. Далее перечислены восемь таких событий (два из них появились в Oracle Database 12c) с указанием условия запуска:

Оглавление статьи[Показать]

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

 

Создание триггера события базы данных

Синтаксис создания этих триггеров в PL/SQL очень похож на синтаксис триггеров DDL :

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

Не все события поддерживают оба атрибута, BEFORE и AFTER. Некоторые комбинации просто не имеют смысла:

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 генерирует какую- либо ошибку. Исключение составляют следующие ошибки:

Кроме того, триггеры AFTER SERVERERROR не запускаются, когда исключение инициируется внутри триггера (для предотвращения бесконечной рекурсии).

Триггеры AFTER SERVERERROR не предоставляют средств для исправления ошибки — они предназначены только для сохранения информации о ней. На этой базе можно построить достаточно мощные механизмы регистрации ошибок.

Oracle также предоставляет встроенные функции (также определенные в пакете DBMS_STANDARD) для получения информации о стеке ошибок, формируемом при воз­никновении исключения:

 

Примеры триггеров 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 для предоставления следующих возможностей:

Реализация такого централизованного пакета обработки ошибок содержится в файле 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...
Встроенные методы коллекций PL... 14849 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4651 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 12355 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Использование записей (records...
Использование записей (records... 19767 просмотров Алексей Вятский Thu, 05 Jul 2018, 07:49:43
Печать
Войдите чтобы комментировать