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

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


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


    Триггер – это именованный 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 — служат для межредакционного взаимодействия, например для переноса и трансформации данных из полей, отсутствующих в новой редакции, в другие поля.

    Условные предикаты для определения операции, на которую сработал триггер:
    Предикат Описание
    Inserting True, если триггер сработал на операцию Insert
    Updating True, если триггер сработал на операцию Update
    Updating(‘colum’) True, если триггер сработал на операцию Update, которая затрагивает определенное поле
    Deleting True, если триггер сработал на операцию Delete


    Эти предикаты могут использоваться везде, где можно использовать 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 триггеров (с секциями уровня записи).
    Операция срабатывания триггера OLD.column NEW.column
    Insert Null Новое значение
    Update Старое значение Новое значение
    Delete Старое значение Null


    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;
    



    Запускает триггер оператор 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;
    



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


    Атрибут Возвращаемое значение и тип
    ora_client_ip_address Varchar2
    ip-адрес клиента
    Пример:
    IF (ora_sysevent = 'LOGON') THEN
        v_addr := ora_client_ip_address;
      END IF;
    
    ora_database_name Varchar2(50)
    имя базы данных
    Пример:
    v_db_name := ora_database_name;
    ora_des_encrypted_password Varchar2
    зашифрованный по стандарту DES пароль пользователя, который создается или изменяется
    Пример:
    IF (ora_dict_obj_type = 'USER') THEN
      INSERT INTO event_table
      VALUES (ora_des_encrypted_password);
    END IF;
    
    ora_dict_obj_name Varchar2(30)
    имя объекта, над которым совершается операция DDL

    Пример:
    INSERT INTO event_table 
    VALUES ('Changed object is ' ||
            ora_dict_obj_name);
    
    ora_dict_obj_name_list (
    name_list OUT ora_name_list_t
    )
    Pls_integer
    количество изменненых командой объектов
    Name_list – список измененных командой объектов

    Пример:
    IF (ora_sysevent='ASSOCIATE STATISTICS') THEN
        number_modified :=
         ora_dict_obj_name_list(name_list);
      END IF;
    
    ora_dict_obj_owner Varchar2(30)
    владелец объекта, над которым совершается операция DDL

    Пример:
    INSERT INTO event_table
    VALUES ('object owner is' || 
            ora_dict_obj_owner);
    
    ora_dict_obj_owner_list (
    owner_list OUT ora_name_list_t
    )
    Pls_integer
    количество владельцев измененных командой объектов
    Owner_list – список владельцев изменных командой объектов

    Пример:
    IF (ora_sysevent='ASSOCIATE STATISTICS') THEN
        number_modified :=
          ora_dict_obj_name_list(owner_list);
      END IF;
    
    ora_dict_obj_type Varchar2(20)
    тип объекта, над которым совершается операция ddl

    Пример:
    INSERT INTO event_table
    VALUES ('This object is a ' || 
            ora_dict_obj_type);
    
    ora_grantee (
    user_list OUT ora_name_list_t
    )
    Pls_integer
    количество пользователей, участвующих в операции grant
    User_list – список этих пользователей

    Пример:
    IF (ora_sysevent = 'GRANT') THEN
        number_of_grantees := 
         ora_grantee(user_list);
      END IF;
    
    ora_instance_num Number
    номер инстанса

    Пример:
    IF (ora_instance_num = 1) THEN
      INSERT INTO event_table VALUES ('1');
    END IF;
    
    ora_is_alter_column (
    column_name IN VARCHAR2
    )
    Boolean
    True, если указанное поле было изменено операцией alter. Иначе false

    Пример:
    IF (ora_sysevent = 'ALTER' AND
      ora_dict_obj_type = 'TABLE') THEN 
        alter_column := ora_is_alter_column('C');
    END IF;
    
    ora_is_creating_nested_table Boolean
    true, если текущее событие – это создание nested table. Иначе false

    Пример:
    IF (ora_sysevent = 'CREATE' AND
      ora_dict_obj_type = 'TABLE' AND
      ora_is_creating_nested_table) THEN
        INSERT INTO event_table
        VALUES ('A nested table is created');
    END IF;
    
    ora_is_drop_column (
    column_name IN VARCHAR2
    )
    Boolean
    true, если указанное поле удалено. Иначе false

    Пример:
    IF (ora_sysevent = 'ALTER' AND
      ora_dict_obj_type = 'TABLE') THEN
        drop_column := ora_is_drop_column('C');
    END IF;
    
    ora_is_servererror (
    error_number IN VARCHAR2
    )
    Boolean
    true, если сгенерированно исключение с номером error_number. Иначе false

    Пример:
    IF ora_is_servererror(error_number) THEN
      INSERT INTO event_table
      VALUES ('Server error!!');
    END IF;
    
    ora_login_user Varchar2(30)
    имя текущего пользователя

    Пример:
    SELECT ora_login_user FROM DUAL;
    ora_partition_pos Pls_integer
    в instead of trigger для create table позиция в тексте sql команды, где может быть вставлена конструкция partition

    Пример:
    -- Retrieve ora_sql_txt into  sql_text variable
    v_n := ora_partition_pos;
    v_new_stmt := SUBSTR(sql_text,1,v_n - 1)
                  || ' ' || my_partition_clause
                  || ' ' || SUBSTR(sql_text, v_n));
    
    ora_privilege_list (
    privilege_list OUT ora_name_list_t
    )
    Pls_integer
    количество привилегий, участвующее в операции grant или revoke
    Privilege_list – список этих привилегий

    Пример:
    IF (ora_sysevent = 'GRANT' OR
          ora_sysevent = 'REVOKE') THEN
        number_of_privileges :=
          ora_privilege_list(privilege_list);
      END IF;
    
    ora_revokee (
    user_list OUT ora_name_list_t
    )
    Pls_integer
    количество пользователей, участвующих в операции revoke
    User_list – список этих пользователей

    Пример:
    IF (ora_sysevent = 'REVOKE') THEN
        number_of_users := ora_revokee(user_list);
      END IF;
    
    ora_server_error (
    position IN PLS_INTEGER
    )
    Number
    код ошибки в указанной позиции error stack, где 1 – это вершина стека

    Пример:
    INSERT INTO event_table
    VALUES ('top stack error ' || 
            ora_server_error(1));
    
    ora_server_error_depth Pls_integer
    количество сообщений об ошибка в error stack

    Пример:
    n := ora_server_error_depth;
    -- Use n with functions such as ora_server_error
    
    ora_server_error_msg (
    position IN PLS_INTEGER
    )
    Varchar2
    сообщение об ошибке в указанном месте error stack

    Пример:
    INSERT INTO event_table
    VALUES ('top stack error message' ||
            ora_server_error_msg(1));
    
    ora_server_error_num_params (
    position IN PLS_INTEGER
    )
    Pls_integer
    количество замещенных строк (с помощью формата %s) в указанной позиции error stack

    Пример:
    n := ora_server_error_num_params(1);
    ora_server_error_param (
    position IN PLS_INTEGER,
    param IN PLS_INTEGER
    )
    Varchar2
    замещенный текст в сообщении об ошибке в указанной позиции error stack (возвращается param по счету замещенный текст)

    Пример:
    -- Second %s in "Expected %s, found %s":
    param := ora_server_error_param(1,2);
    
    ora_sql_txt (
    sql_text OUT ora_name_list_t
    )
    Pls_integer
    количество элементов в pl/sql коллекции sql_text.
    Сам параметр sql_text возвращает текст команды, на которую сработал триггер

    Пример:
    CREATE TABLE event_table (col VARCHAR2(2030));
    
    DECLARE
      sql_text ora_name_list_t;
      n PLS_INTEGER;
      v_stmt VARCHAR2(2000);
    BEGIN
      n := ora_sql_txt(sql_text);
    
      FOR i IN 1..n LOOP
        v_stmt := v_stmt || sql_text(i);
      END LOOP;
    
      INSERT INTO event_table VALUES ('text of
        triggering statement: ' || v_stmt);
    END;
    
    ora_sysevent Varchar2(20)
    название команды, на которую срабатывает триггер

    Пример:
    INSERT INTO event_table
    VALUES (ora_sysevent);
    
    ora_with_grant_option Boolean
    true, если привилегии выдаются with grant option. Иначе false.

    Пример:
    IF (ora_sysevent = 'GRANT' AND
      ora_with_grant_option = TRUE) THEN
        INSERT INTO event_table 
        VALUES ('with grant option');
    END IF;
    
    ora_space_error_info (
    error_number OUT NUMBER,
    error_type OUT VARCHAR2,
    object_owner OUT VARCHAR2,
    table_space_name OUT VARCHAR2,
    object_name OUT VARCHAR2,
    sub_object_name OUT VARCHAR2
    )
    Boolean
    true, если ошибка возникает из-за нехватки места. В выходных параметрах информация об объекте.

    Пример:
    IF (ora_space_error_info (
         eno,typ,owner,ts,obj,subobj) = TRUE) THEN
      DBMS_OUTPUT.PUT_LINE('The object '|| obj
         || ' owned by ' || owner ||
         ' has run out of space.');
    END IF;
    


    События срабатывания системных триггеров


    Событие Описание Доступные атрибуты
    AFTER STARTUP При запуске БД. Бывает только уровня БД. При ошибке пишет в системный лог. ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    BEFORE SHUTDOWN Перед тем, как сервер начнет процесс останова. Бывает только уровня БД. При ошибке пишет в системный лог. ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    AFTER DB_ROLE_CHANGE При запуске БД в первый раз после смены ролей from standby to primary or from primary to standby.
    используется только в конфигурации Data Guard,, бывает только уровня БД.
    ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    AFTER SERVERERROR Если случается любая ошибка (если с условием, то срабатывает только на ошибку, указанную в условии). При ошибке в теле триггера не вызывает себя рекурсивно. ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_server_error
    ora_is_servererror
    ora_space_error_info
    BEFORE ALTER

    AFTER ALTER
    Если объект изменяется командой alter ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_type
    ora_dict_obj_name
    ora_dict_obj_owner
    ora_des_encrypted_password
    (for ALTER USER events)
    ora_is_alter_column
    (for ALTER TABLE events)
    ora_is_drop_column
    (for ALTER TABLE events)
    BEFORE DROP

    AFTER DROP
    При удалении объекта ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_type
    ora_dict_obj_name
    ora_dict_obj_owner
    BEFORE ANALYZE

    AFTER ANALYZE
    При срабатывании команды analyze ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner
    BEFORE ASSOCIATE STATISTICS

    AFTER ASSOCIATE STATISTICS
    При выполнении команды associate statistics ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner
    ora_dict_obj_name_list
    ora_dict_obj_owner_list
    BEFORE AUDIT

    AFTER AUDIT

    BEFORE NOAUDIT

    AFTER NOAUDIT
    При выполнении команды audit или noaudit ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    BEFORE COMMENT

    AFTER COMMENT
    При добавлении комментария к объекту ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner
    BEFORE CREATE

    AFTER CREATE
    При создании объекта ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_type
    ora_dict_obj_name
    ora_dict_obj_owner
    ora_is_creating_nested_table
    (for CREATE TABLE events)
    BEFORE DDL

    AFTER DDL
    Срабатывает на большинство команд DDL, кроме: alter database, create control file, create database. ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner
    BEFORE DISASSOCIATE STATISTICS

    AFTER DISASSOCIATE STATISTICS
    При выполнении команды disassociate statistics ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner
    ora_dict_obj_name_list
    ora_dict_obj_owner_list
    BEFORE GRANT

    AFTER GRANT
    При выполнении команды grant ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner
    ora_grantee
    ora_with_grant_option
    ora_privilege_list
    BEFORE LOGOFF Срабатывает перед дисконнеком пользователя, бывает уровня схемы или БД ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    AFTER LOGON Срабатывает после того, как пользователь успешно установил соединение с БД. При ошибке запрещает пользователю вход. Не действует на SYS. ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_client_ip_address
    BEFORE RENAME

    AFTER RENAME
    При выполнении команды rename ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_owner
    ora_dict_obj_type
    BEFORE REVOKE

    AFTER REVOKE
    При выполнении команды revoke ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner
    ora_revokee
    ora_privilege_list
    AFTER SUSPEND Срабатывает в случае, если sql команда приостанавливается по причине серверной ошибки (нехватки памяти).
    При этом триггер должен изменить условия таким образом, чтобы выполнение команды было возобновлено)
    ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_server_error
    ora_is_servererror
    ora_space_error_info
    BEFORE TRUNCATE

    AFTER TRUNCATE
    При выполнении команды truncate ora_sysevent
    ora_login_user
    ora_instance_num
    ora_database_name
    ora_dict_obj_name
    ora_dict_obj_type
    ora_dict_obj_owner


    Компиляция триггеров


    Если во время выполнения команды create trigger произошла ошибка, триггер все равно будет создан, но будет в невалидном состоянии. При этом все попытки выполнить операцию(на которую должен срабатывать триггер) над объектом, на котором висит такой триггер, будут завершаться ошибкой. Это не относится к случаям, когда:
    • Триггер создан в состоянии disabled (или переведен в такое состояние)
    • Событие триггера after startup on database
    • Событие триггера after logon on database или after logon on schema и происходит попытка залогиниться под пользователем System

    Чтобы перекомпилировать триггер, используйте команду alter trigger.

    Исключения в триггерах


    В случае, если в триггере возникает исключение, вся операция откатывается (включая любые изменения, сделанные внутри триггера). Исключения из этого:
    • Если событие триггера after startup on database или before shutdown on database
    • Если событие триггера after logon on database и пользователь имеет привилегию administer database trigger
    • Если событие триггера after logon on schema и пользователь или является владельцем схемы, или имеет привилегию alter any trigger


    Порядок выполнения триггеров


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


    1. Сначала выполняются все before statement триггера
    2. Потом все before each row триггера
    3. После все after each row триггера
    4. И в конце все after statement триггера

    Чтобы задать явно порядок выполнения триггеров, срабатывающих в одинаковый момент времени (потому что по умолчанию такой порядок не определен), используйте конструкции follows и precedes.

    Включение/отключение триггеров


    Это может понадобиться, например, для загрузки большого объема информации в таблицу.
    Выполнить включение/отключение триггера можно с помощью команды:
    ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };
    

    Чтобы включить/отключить сразу все триггеры на таблице:
    ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;
    


    Для изменения триггера можно или воспользоваться командой Create or replace trigger, или сначала удалить триггер drop trigger, а потом создать заново create trigger.
    Операция alter trigger позволяет только включить/отключить триггер, скомпилировать его или переименовать.
    Компиляция триггера:
    alter trigger TRIGGER_NAME compile;
    


    Права для операций с триггерами



    Для работы с триггерами даже в своей схеме необходима привилегия create trigger, она дает права на создание, изменение и удаление.
    grant create trigger to USER;
    

    Для работы с триггерами во всех других схемах необходима привилегия * any trigger. Обратите внимание, что права даются отдельно на создание, изменение и удаление.
    grant create any trigger to USER;
    grant alter any trigger to USER;
    grant drop any trigger to USER; 
    

    Для работы с системными триггерами уровня DATABASE необходима привилегия ADMINISTER DATABASE TRIGGER.
    grant ADMINISTER DATABASE TRIGGER to USER; 
    


    Словари данных с информацией о триггерах:


    • dba_triggers – информация о триггерах
    • dba_source — код тела триггера
    • dba_objects – валидность триггера


    Видео-запись лекции, по материалам которой и была написана эта статья:



    Множество других видео по темам Oracle можно найти на этом канале: www.youtube.com/c/MoscowDevelopmentTeam

    Другие статьи по Oracle


    Все о коллекциях в Oracle
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 10

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

      Ух ты страсти какие

      Всё нормально с памятью
      SQL> create table test(a number not null);
      
      Table created.
      
      SQL> insert into test(a) values (0);
      
      1 row created.
      
      SQL> create or replace trigger test_aru
        2  after update
        3  on test
        4  for each row
        5  begin
        6    update test set a = :old.a + 1;
        7  end;
        8  /
      
      Trigger created.
      
      SQL> update test set a = 1;
      update test set a = 1
             *
      ERROR at line 1:
      ORA-04091: table MCHS_TEST.TEST is mutating, trigger/function may not see it
      ORA-06512: at "MCHS_TEST.TEST_ARU", line 2
      ORA-04088: error during execution of trigger 'MCHS_TEST.TEST_ARU'
      


      Или имелся в виду какой-то другой триггер? Статья полезная, разумеется.
        0
        :)
        Имелся в виду такой же триггер, но объявленный как автономная транзакция.
          +1
          Ну, с автономной транзакцией можно и самого себя задидлочить.
          Этот момент стоит уточнить.
            +1
            Ну есть и другие способы сделать рекурсию: в триггере на одну таблицу можно апдейтить другую, а в триггере на ту таблицу апдейтить первую.
            Об этом подробнее говорится (с примером) в видео-версии статьи.
        0
        (не туда коммент отправил)
          +1
          И запрос на триггер After Commit висит с 98-го года если не ошибаюсь. Это так, жизнь-боль :)
            0
            :)
            «жизнь-боль» — это самый популярный мем в нашей команде

            0
            Скажите, планируется ли написать такую же статью «Всё о материализованных представлениях»?
            0
            То что нужно!

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

            Самое читаемое