Все о триггерах в Oracle

Традиционно статья написана тезисно. Более подробное содержание можно найти в приложенном внизу статьи видео с записью лекции про триггеры Oracle.

• Общие сведения о триггерах Oracle
• DML triggers
  
  • Псевдозаписи
  • Instead of dml triggers
  • Instead of triggers on Nested Table Columns of Views.
  • Составные DML триггера (compound DML triggers)
    
    • Структура составного триггера
  • Основные правила определения DML триггеров
  • Ограничения DML триггеров
  • Ошибка мутирования таблицы ORA-04091
• Системные триггеры (System triggers)
  
  • Триггеры уровня схемы (schema triggers)
  • Триггеры уровня базы данных (database triggers)
  • Instead of create triggers
  • Атрибуты системных триггеров
  • События срабатывания системных триггеров
• Компиляция триггеров
• Исключения в триггерах
• Порядок выполнения триггеров
• Права для операций с триггерами
• Словари данных с информацией о триггерах

Общие сведения о триггерах

Триггер – это именованный pl/sql блок, который хранится в базе данных.

• Нельзя самому вызвать триггер, он всегда срабатывает только на определенное событие автоматически(если он enable)
• Не стоит создавать рекурсивные триггера. Т.е., например, триггер after update, в котором выполняется update той же таблицы. В этом случае триггер будет срабатывать рекурсивно до тех пор, пока не закончится память.

Классификация триггеров:

• DML trigger (на таблицу или представление)
• System trigger (на схему или базу данных)
• Conditional trigger (те, которые имеют условие when)
• Instead of trigger (dml триггер на представление или system триггер на команду create)

Зачем использовать триггеры:

• Для автоматической генерации значений виртуального поля
• Для логгирования
• Для сбора статистики
• Для изменения данных в таблицах, если в dml операции участвует представление
• Для предотвращения dml операций в какие-то определенные часы
• Для реализации сложных ограничений целостности данных, которые невозможно осуществить через описательные ограничения, установленные при создании таблиц
• Для организации всевозможных видов аудита
• Для оповещения других модулей о том, что делать в случае изменения информации в БД
• Для реализации бизнес логики
• Для организации каскадных воздействий на таблицы БД
• Для отклика на системные события в БД или схеме

где plsql_trigger_source, это такая конструкция:

Конструкции simple_dml_trigger, instead_of_dml_trigger, compound_dml_trigger и system_trigger будут приведены в соответствующих разделах статьи.

DML triggers

• DML триггеры создаются для таблиц или представлений, срабатывают при вставке, обновлении или удалении записей.
• Триггер может быть создан в другой схеме, отличной от той, где определена таблицы. В таком случае текущей схемой при выполнении триггера считается схема самого триггера.
• При операции MERGE срабатывают триггеры на изменение, вставку или удаление записей в зависимости от операции со строкой.
• Триггер – часть транзакции, ошибка в триггере откатывает операцию, изменения таблиц в триггере становятся частью транзакции.
• Если откатывается транзакция, изменения триггера тоже откатываются.
• В триггерах запрещены операторы DDL и управления транзакциями (исключения – автономные транзакции).

Конструкция simple_dml_trigger:

Где, dml_event_clause:

referencing_clause:

trigger_edition_clause:

trigger_body:

По привязанному объекту делятся на:

• На таблице
• На представлении (instead of trigger)

По событиям запуска:

• Вставка записей (insert)
• Обновление записей (update)
• Удаление записей (delete)

По области действия:

• Уровень всей команды (statement level triggers)
• Уровень записи (row level triggers)
• Составные триггеры (compound triggers)

По времени срабатывания:

• Перед выполнением операции (before)
• После выполнения операции (after)

Crossedition triggers — служат для межредакционного взаимодействия, например для переноса и трансформации данных из полей, отсутствующих в новой редакции, в другие поля.

Условные предикаты для определения операции, на которую сработал триггер:

Эти предикаты могут использоваться везде, где можно использовать Boolean выражения.

CREATE OR REPLACE TRIGGER t   BEFORE     INSERT OR     UPDATE OF salary, department_id OR     DELETE   ON employees BEGIN   CASE     WHEN INSERTING THEN       DBMS_OUTPUT.PUT_LINE('Inserting');     WHEN UPDATING('salary') THEN       DBMS_OUTPUT.PUT_LINE('Updating salary');     WHEN UPDATING('department_id') THEN       DBMS_OUTPUT.PUT_LINE('Updating department ID');     WHEN DELETING THEN       DBMS_OUTPUT.PUT_LINE('Deleting');   END CASE; END; 

Псевдозаписи

Существуют псевдозаписи, позволяющие обратиться к полям изменяемой записи и получить значения полей до изменения и значения полей после изменения. Это записи old и new. С помощью конструкции Referencing можно изменить их имена. Структура этих записей tablename%rowtype. Эти записи есть только у триггеров row level или у compound триггеров (с секциями уровня записи).

Restrictions:

• С псевдозаписями запрещены операции уровня всей записи ( :new = null;)
• Нельзя изменять значения полей записи old
• Если триггер срабатывает на delete, нельзя изменить значения полей записи new
• В триггере after нельзя изменить значения полей записи new

Instead of dml triggers

• Создаются для представлений (view) и служат для замещения DML операций своим функционалом.
• Позволяют производить операции вставки/обновления или удаления для необновляемых представлений.

Конструкция instead_of_dml_trigger:

• Это всегда триггер уровня записи (row level)
• Имеет доступ к псевдозаписям old и new, но не может изменять их
• Заменяет собой dml операцию с представлением (view)

CREATE OR REPLACE VIEW order_info AS    SELECT c.customer_id, c.cust_last_name, c.cust_first_name,           o.order_id, o.order_date, o.order_status    FROM customers c, orders o    WHERE c.customer_id = o.customer_id;  CREATE OR REPLACE TRIGGER order_info_insert    INSTEAD OF INSERT ON order_info    DECLARE      duplicate_info EXCEPTION;      PRAGMA EXCEPTION_INIT (duplicate_info, -00001);    BEGIN      INSERT INTO customers        (customer_id, cust_last_name, cust_first_name)      VALUES (      :new.customer_id,      :new.cust_last_name,      :new.cust_first_name);    INSERT INTO orders (order_id, order_date, customer_id)    VALUES (      :new.order_id,      :new.order_date,      :new.customer_id);    EXCEPTION      WHEN duplicate_info THEN        RAISE_APPLICATION_ERROR (          num=> -20107,          msg=> 'Duplicate customer or order ID');    END order_info_insert; 

Instead of triggers on Nested Table Columns of Views

Можно создать триггер для вложенной в представлении таблицы. В таком триггере также присутствует дополнительная псевдозапись – parent, которая ссылается на всю запись представления (стандартные псевдозаписи old и new ссылаются только на записи вложенной таблицы)

-- Create type of nested table element:   CREATE OR REPLACE TYPE nte AUTHID DEFINER IS OBJECT (   emp_id     NUMBER(6),   lastname   VARCHAR2(25),   job        VARCHAR2(10),   sal        NUMBER(8,2) ); /   -- Created type of nested table:   CREATE OR REPLACE TYPE emp_list_ IS   TABLE OF nte; /   -- Create view:  CREATE OR REPLACE VIEW dept_view AS   SELECT d.department_id,           d.department_name,          CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary                          FROM employees e                          WHERE e.department_id = d.department_id                         )                         AS emp_list_               ) emplist   FROM departments d;   -- Create trigger:   CREATE OR REPLACE TRIGGER dept_emplist_tr   INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view   REFERENCING NEW AS Employee               PARENT AS Department   FOR EACH ROW BEGIN   -- Insert on nested table translates to insert on base table:   INSERT INTO employees (     employee_id,     last_name,     email,     hire_date,     job_id,     salary,     department_id   )   VALUES (     :Employee.emp_id,                      -- employee_id     :Employee.lastname,                    -- last_name     :Employee.lastname || '@company.com',  -- email     SYSDATE,                               -- hire_date     :Employee.job,                         -- job_id     :Employee.sal,                         -- salary     :Department.department_id              -- department_id   ); END; 

<spoiler title=«Запускает триггер оператор insert">

INSERT INTO TABLE (   SELECT d.emplist    FROM dept_view d   WHERE department_id = 10 ) VALUES (1001, 'Glenn', 'AC_MGR', 10000); 

Составные DML триггера (compound DML triggers)

Появившиеся в версии 11G эти триггера включают в одном блоке обработку всех видов DML триггеров.
Конструкция compound_dml_trigger:

Где, compound_trigger_block:

timing_point_section:

timing_point:

tps_body:

• Срабатывают такие триггера при разных событиях и в разные моменты времени (на уровне оператора или строки, при вставке/обновлении/удалении, до или после события).
• Не могут быть автономными транзакциями.

В основном используются, чтобы:

• Собирать в коллекцию строки для вставки в другую таблицу, чтобы периодически вставлять их пачкой
• Избежать ошибки мутирующей таблицы (mutating-table error)

Структура составного триггера

Может содержать переменные, которые живут на всем протяжении выполнения оператора, вызвавшего срабатывание триггера.
Такой триггер содержит следующие секции:

• Before statement
• After statement
• Before each row
• After each row

В этих триггерах нет секции инициализации, но для этих целей можно использовать секцию before statement.
Если в триггере нет ни before statement секции, ни after statement секции, и оператор не затрагивает ни одну запись, такой триггер не срабатывает.

Restrictions:

• Нельзя обращаться к псевдозаписям old, new или parent в секциях уровня выражения (before statement и after statement)
• Изменять значения полей псевдозаписи new можно только в секции before each row
• Исключения, сгенерированные в одной секции, нельзя обрабатывать в другой секции
• Если используется оператор goto, он должен указывать на код в той же секции

create or replace trigger tr_table_test_compound   for update or delete or insert on table_test   compound trigger      v_count  pls_integer := 0;          before statement is   begin     dbms_output.put_line ( 'before statement' );   end before statement;      before each row is   begin     dbms_output.put_line ( 'before insert' );   end before each row;      after each row is   begin     dbms_output.put_line ( 'after insert' );     v_count := v_count + 1;   end after each row;      after statement is   begin    dbms_output.put_line ( 'after statement' );   end after statement;   end tr_table_test_compound; 

Основные правила определения DML триггеров

• Update of – позволяет указать список изменяемых полей для запуска триггера
• Все условия в заголовке и When … проверяются без запуска триггера на стадии выполнения SQL
• В операторе When можно использовать только встроенные функции
• Можно делать несколько триггеров одного вида, порядок выполнения не определен по умолчанию, но его можно задать с помощью конструкции FOLLOWS TRIGGER_FIRST
• Ограничения уникальности проверяются при изменении записи, то есть после выполнения триггеров before
• Секция объявления переменных определяется словом DECLARE
• Основной блок триггера подчиняется тем же правилам, что и обычные PL/SQL блоки

Ограничения DML триггеров

• нельзя выполнять DDL statements (только в автономной транзакции)
• нельзя запускать подпрограммы с операторами контроля транзакций
• не имеет доступа к SERIALLY_REUSABLE пакетов
• размер не может превышать 32К
• нельзя декларировать переменные типа LONG и LONG RAW

Ошибка мутирования таблицы ORA-04091

Если в триггере уровня строки попытаться получить или изменить данные в целевой таблицы, то Oracle не позволит это сделать и выкинет ошибку ORA-04091 Таблица TABLE_TEST изменяется, триггер/функция может не заметить это.
Для обхода данной проблемы используются следующие приемы:

• использовать триггеры уровня операции
• автономная транзакция в триггере
• использовать сторонние структуры (коллекции уровня пакета)
• использовать COMPOUND TRIGGER
• изменение самого алгоритма с выносом функционала из триггера

Системные триггеры (System triggers)

Конструкция system_trigger:

Такие триггеры относятся или к схеме, или ко всей базе данных.

Есть несколько вариантов, в какой момент времени срабатывает системный триггер:

• До того, как будет выполнена операция (на которую срабатывает триггер)
• После того, как будет выполнена операция (на которую срабатывает триггер)
• Вместо выполнения оператора Create

Триггеры уровня схемы (schema triggers)

• Срабатывает всегда, когда пользователь-владелец схемы запускает событие (выполняет операцию), на которую должен срабатывать триггер.
• В случае, если любой другой пользователь запускает процедуру/функцию, которая вызывается с правами создателя, и в этой процедуре/функции выполняется операция, на которую создан системный триггер – этот триггер сработает.

CREATE OR REPLACE TRIGGER drop_trigger   BEFORE DROP ON hr.SCHEMA   BEGIN     RAISE_APPLICATION_ERROR (       num => -20000,       msg => 'Cannot drop object');   END; 

Триггеры уровня базы данных (database triggers)

• Такой триггер срабатывает когда любой пользователь БД выполняет команду, на которую создан триггер.

CREATE OR REPLACE TRIGGER check_user   AFTER LOGON ON DATABASE   BEGIN     check_user;   EXCEPTION     WHEN OTHERS THEN       RAISE_APPLICATION_ERROR         (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);  END; 

Instead of create triggers

• Это триггер уровня схемы, который срабатывает на команду create и заменяет собой эту команду (т.е. вместо выполнения команды create выполняется тело триггера).

CREATE OR REPLACE TRIGGER t   INSTEAD OF CREATE ON SCHEMA   BEGIN     EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';   END; 

Атрибуты системных триггеров

ссылка на оригинал статьи http://habrahabr.ru/post/256655/