7. Триггеры

1. Введение в триггеры

Триггер — это PL/SQL блок, который хранится в базе данных и срабатывает (выполняется) в ответ на указанное событие.

Триггер может быть определён на таблицу, представление, схему (владельца схемы) или базу данных (всех пользователей).

Типы событий триггеров:

Пример создания триггера:

CREATE OR REPLACE TRIGGER check_salary_trg
-- перед INSERT в Employees или UPDATE столбцов Salary или Job_ID
BEFORE INSERT OR UPDATE OF Salary, Job_ID ON Employees
[REFERENCING OLD AS old_name | NEW AS new_name] -- можно переименовывать OLD и NEW
FOR EACH ROW -- строковый триггер, для каждой строки (не для всей DML-команды)
BEGIN
  -- NEW — объект типа строки Employees, хранит строку после DML-команды
  -- OLD — то же самое, но до DML-операции
  check_salary(:NEW.Job_ID, :NEW.Salary); -- вызов процедуры check_salary
END;
/

2. Типы триггеров

Триггеры делятся на:

Триггеры можно использовать для:

Допустимые типы триггеров:

Триггеры бывают:

3. Использование условных предикатов

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

В теле триггера можно использовать конструкции (если триггер состоит из нескольких DML-операций):

чтобы определить, какое событие вызвало триггер, который слушает несколько событий.

CREATE OR REPLACE TRIGGER triggerName
[BEFORE | AFTER] INSERT OR UPDATE OR DELETE ON Table_Name
BEGIN
  IF DELETING THEN
    -- ...
  ELSIF UPDATING THEN
    -- ...
  ELSIF INSERTING THEN
    -- ...
  END IF;
END;
/

Можно так же использовать WHEN для строковых триггеров.

CREATE OR REPLACE TRIGGER check_salary_trg
BEFORE INSERT OR UPDATE OF Salary, Job_ID ON Employees
FOR EACH ROW
/*
  проверяем, что мы действительно обновляем столбец Job_ID или Salary
  или же вставляем новую строку
  (без этого триггер сработает на UPDATE, даже если фактическое значение не изменилось).
*/
WHEN (
     OLD.Job_ID IS NULL       OR OLD.Salary IS NULL
  OR OLD.Job_ID != NEW.Job_ID OR OLD.Salary != NEW.Salary
)
-- Заметим, что внутри WHEN пишем «OLD», а внутри BEGIN..END — «:OLD».
BEGIN
  check_salary(:NEW.Job_ID, :NEW.Salary);
END;
/

О NEW и OLD (доступны только в строковых триггерах):

DML-командаOLDNEW
INSERTNULLвведённое значение
UPDATEзначение до обновлениязначение после обновления
DELETEудаляемое значениеNULL

Краткое описание модели выполнения триггера:

  1. Выполняются все BEFORE триггеры уровня команды.
  2. Для каждой строки, затронутой триггером:
    1. Выполняются все строковые BEFORE триггеры
    2. Выполняется DML-команда и проверка ограничений целостности.
    3. Выполняются все строковые AFTER триггеры.
  3. Выполняются все AFTER триггеры уровня команды.

У триггера есть 2 состояния: ENABLE и DISABLE. Синтаксис:

CREATE OR REPLACE TRIGGER triggerName
BEFORE INSERT ON tableName FOR EACH ROW
DISABLE -- создание выключенного триггера
BEGIN
  -- код
END;
/

-- триггер можно выключить/включить, например, так:
ALTER TRIGGER triggerName DISABLE; -- выключаем
ALTER TRIGGER triggerName ENABLE;  -- включаем

4. Тестирование триггеров

Информацию о триггерах можно посмотреть в словарях USER_OBJECTS, USER/ALL/DBA_TRIGGERS. Синтаксис ошибок триггеров можно посмотреть в словаре USER_ERRORS.

5. Составные (compound) триггеры

Составной (compound) триггер — это один триггер на таблицу, позволяющий задать действия для каждой из следующих четырёх точек синхронизации:

  1. Перед вызывающей командой.
  2. Перед каждой строкой, на которую влияет вызывающая команда.
  3. После каждой строки, на которую влияет вызывающая команда.
  4. После вызывающей команды.

Мутирующая таблица — это:

Составные триггеры можно использовать для:

Ограничения составных триггеров:

Ограничение триггеров на мутирующие таблицы:

6. Создание триггеров системных событий

Триггеры LOGON и LOGOFF. Пример:

CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON SCHEMA
BEGIN
  INSERT INTO log_trig_table(user_id, log_date, action)
  VALUES (USER, SYSDATE, 'Logging on');
END;
/

CREATE OR REPLACE TRIGGER logoff_trig
BEFORE LOGOFF ON SCHEMA
BEGIN
  INSERT INTO log_trig_table(user_id, log_date, action)
  VALUES (USER, SYSDATE, 'Logging off');
END;
/

Можно использовать команду CALL внутри триггера:

CREATE OR REPLACE PROCEDURE log_execution IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('log_exection: Employee Inserted');
END;
/
CREATE OR REPLACE TRIGGER log_employee
BEFORE INSERT ON EMPLOYEES
CALL log_execution -- точка с запятой не требуется
/

Преимущества триггеров на события базы данных:

Системные привилегии, необходимые для управления триггерами:

7. Рекомендации по Разработке Триггеров