• Общие сведения о триггерах 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
  • Атрибуты системных триггеров
  • События срабатывания системных триггеров
• Компиляция триггеров
• Исключения в триггерах
• Порядок выполнения триггеров
• Включение/отключение триггеров
• Права для операций с триггерами
• Словари данных с информацией о триггерах

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

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

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

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

DML triggers

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

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

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

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

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

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;

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

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

Instead of dml triggers

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

• Это всегда триггер уровня записи (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

-- 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 INTO TABLE (
  SELECT d.emplist 
  FROM dept_view d
  WHERE department_id = 10
)
VALUES (1001, 'Glenn', 'AC_MGR', 10000);

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

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

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

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

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

• Нельзя обращаться к псевдозаписям 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

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

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

• До того, как будет выполнена операция (на которую срабатывает триггер)
• После того, как будет выполнена операция (на которую срабатывает триггер)
• Вместо выполнения оператора 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;

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

IF (ora_sysevent = 'LOGON') THEN
    v_addr := ora_client_ip_address;
  END IF;

v_db_name := ora_database_name;

IF (ora_dict_obj_type = 'USER') THEN
  INSERT INTO event_table
  VALUES (ora_des_encrypted_password);
END IF;

INSERT INTO event_table 
VALUES ('Changed object is ' ||
        ora_dict_obj_name);

IF (ora_sysevent='ASSOCIATE STATISTICS') THEN
    number_modified :=
     ora_dict_obj_name_list(name_list);
  END IF;

INSERT INTO event_table
VALUES ('object owner is' || 
        ora_dict_obj_owner);

IF (ora_sysevent='ASSOCIATE STATISTICS') THEN
    number_modified :=
      ora_dict_obj_name_list(owner_list);
  END IF;

INSERT INTO event_table
VALUES ('This object is a ' || 
        ora_dict_obj_type);

IF (ora_sysevent = 'GRANT') THEN
    number_of_grantees := 
     ora_grantee(user_list);
  END IF;

IF (ora_instance_num = 1) THEN
  INSERT INTO event_table VALUES ('1');
END IF;

IF (ora_sysevent = 'ALTER' AND
  ora_dict_obj_type = 'TABLE') THEN 
    alter_column := ora_is_alter_column('C');
END IF;

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;

IF (ora_sysevent = 'ALTER' AND
  ora_dict_obj_type = 'TABLE') THEN
    drop_column := ora_is_drop_column('C');
END IF;

IF ora_is_servererror(error_number) THEN
  INSERT INTO event_table
  VALUES ('Server error!!');
END IF;

SELECT ora_login_user FROM DUAL;

-- 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));

IF (ora_sysevent = 'GRANT' OR
      ora_sysevent = 'REVOKE') THEN
    number_of_privileges :=
      ora_privilege_list(privilege_list);
  END IF;

IF (ora_sysevent = 'REVOKE') THEN
    number_of_users := ora_revokee(user_list);
  END IF;

INSERT INTO event_table
VALUES ('top stack error ' || 
        ora_server_error(1));

n := ora_server_error_depth;
-- Use n with functions such as ora_server_error

INSERT INTO event_table
VALUES ('top stack error message' ||
        ora_server_error_msg(1));

n := ora_server_error_num_params(1);

-- Second %s in "Expected %s, found %s":
param := ora_server_error_param(1,2);

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;

INSERT INTO event_table
VALUES (ora_sysevent);

IF (ora_sysevent = 'GRANT' AND
  ora_with_grant_option = TRUE) THEN
    INSERT INTO event_table 
    VALUES ('with grant option');
END IF;

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;

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

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

• Триггер создан в состоянии disabled (или переведен в такое состояние)
• Событие триггера after startup on database
• Событие триггера after logon on database или after logon on schema и происходит попытка залогиниться под пользователем System

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

• Если событие триггера after startup on database или before shutdown on database
• Если событие триггера after logon on database и пользователь имеет привилегию administer database trigger
• Если событие триггера after logon on schema и пользователь или является владельцем схемы, или имеет привилегию alter any trigger

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

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

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

ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };

ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;

alter trigger TRIGGER_NAME compile;

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

grant create trigger to USER;

grant create any trigger to USER;
grant alter any trigger to USER;
grant drop any trigger to USER; 

grant ADMINISTER DATABASE TRIGGER to USER; 

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

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

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