Все про тригерах в oracle, savepearlharbor

Загальні відомості про тригерах

Тригер - це іменований 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)

Умовні предикати для визначення операції, на яку спрацював тригер:

• З псевдозапісямі заборонені операції рівня всього запису (: new = null;)
• Не можна змінювати значення полів запису old
• Якщо тригер спрацьовує на delete, не можна змінити значення полів запису new
• У тригері after можна змінити значення полів запису new

Instead of dml triggers

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

• Це завжди тригер рівня запису (row level)
• Має доступ до псевдозапісям old і new, але не може змінювати їх
• Замінює собою dml операцію з поданням (view)

Instead of triggers on Nested Table Columns of Views

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

Приклад такого тригера

Складові DML тригера (compound DML triggers)

Що з'явилися у версії 11G ці тригера включають в одному блоці обробку всіх видів DML тригерів.
Конструкція compound_dml_trigger:

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

В основному використовуються, щоб:

• Збирати в колекцію рядки для вставки в іншу таблицю, щоб періодично вставляти їх пачкою
• Уникнути помилки мутує таблиці (mutating-table error)

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

Може містити змінні, які живуть на всьому протязі виконання оператора, що викликав спрацьовування тригера.
Такий тригер містить наступні секції:

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

У цих триггерах немає секції ініціалізації, але для цих цілей можна використовувати секцію before statement.
Якщо в тригері немає ні before statement секції, ні after statement секції, і оператор не зачіпає жодну запис, такої тригер не спрацьовує.

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

Основні правила визначення DML тригерів

Обмеження 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)

• Завжди спрацьовує, коли користувач-власник схеми запускає подія (виконує операцію), на яку повинен спрацьовувати тригер.
• У разі, якщо будь-який інший користувач запускає процедуру / функцію, яка викликається з правами творця, і в цій процедурі / функції виконується операція, на яку створено системний тригер - цей тригер спрацює.

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

• Такий тригер спрацьовує коли будь-який користувач БД виконує команду, на яку створено тригер.