Oracle Тригер PL/SQL: замість складених типів &

Що таке тригер у PL/SQL?

ТРИГЕРИ зберігаються PL / SQL програми, що запускаються Oracle двигун автоматично, коли Операції DML Такі операції, як вставка, оновлення та видалення, виконуються для таблиці або коли відбуваються певні події. Код, який має виконуватися у випадку тригера, можна визначити відповідно до вимоги. Ви можете вибрати подію, за якою має спрацювати тригер, та час виконання. Мета тригера — підтримувати цілісність інформації в базі даних.

Переваги тригерів

Нижче наведено переваги тригерів.

• Автоматичне генерування деяких похідних значень стовпців
• Забезпечення посилальної цілісності
• Журнал подій і збереження інформації про доступ до таблиці
• Аудит
• Syncхронологічна реплікація таблиць
• Накладення дозволів безпеки
• Запобігання недійсним угодам

Типи тригерів в Oracle

Тригери можна класифікувати на основі таких параметрів.

Класифікація за часом

• ДО запуску тригера: Він спрацьовує до того, як відбудеться зазначена подія.
• ПІСЛЯ тригера: Він спрацьовує після того, як відбулася зазначена подія.
• ЗАМІСТЬ Тригера: Спеціальний тип. Ви дізнаєтесь більше з наступних тем. (лише для DML)

Класифікація за рівнем

• Тригер рівня ОПЕРАТОРА: Він спрацьовує один раз для зазначеного оператора події.
• Тригер рівня ROW: Він спрацьовує для кожного запису, на який вплинула зазначена подія. (лише для DML)

Класифікація на основі події

• Тригер DML: Він спрацьовує, коли вказано подію DML (INSERT/UPDATE/DELETE).
• Тригер DDL: Він спрацьовує, коли вказано подію DDL (CREATE/ALTER).
• Тригер БАЗИ ДАНИХ: Він спрацьовує, коли вказано подію бази даних (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Отже, кожен тригер є комбінацією вищезазначених параметрів.

Як створити тригер

Нижче наведено синтаксис для створення тригера. На знімку екрана нижче показано цей синтаксис створення тригера в Oracle.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Пояснення синтаксису:

• Наведений вище синтаксис показує різні додаткові оператори, які присутні під час створення тригера.
• У полі ДО/ПІСЛЯ буде вказано час проведення події.
• ВСТАВИТИ/ОНОВИТИ/ВХІД/СТВОРИТИ/і т.д. буде вказувати подію, для якої потрібно запустити тригер.
• У реченні ON буде вказано об'єкт, для якого дійсна вищезгадана подія. Наприклад, це буде ім'я таблиці, для якої може відбутися подія DML у випадку тригера DML.
• Команда «ДЛЯ КОЖНОГО РЯДУ» визначатиме тригер рівня РЯДУ.
• Речення WHEN визначатиме додаткову умову, за якої має спрацювати тригер.
• Частина оголошення, частина виконання та частина обробки винятків такі ж, як і в інших Блоки PL/SQLДеклараційна частина та обробка винятків частина є необов'язковою.

Речення :NEW і :OLD

У тригері рівня рядка тригер запускається для кожного пов’язаного рядка. Іноді потрібно знати значення до і після оператора DML.

Oracle передбачив два речення в тригері на рівні рядка для зберігання цих значень. Ми можемо використовувати ці речення для посилання на старі та нові значення всередині тіла тригера.

• :НОВИЙ – Він містить нове значення для стовпців базової таблиці/представлення під час виконання тригера.
• :СТАРИЙ – Зберігає старе значення стовпців базової таблиці/представлення під час виконання тригера.

Цей пункт слід використовувати на основі події DML. У таблиці нижче вказано, який пункт є дійсним для якого оператора DML (INSERT/UPDATE/DELETE).

ЗАМІСТЬ Тригер

Тригер «INSTEAD OF» – це спеціальний тип тригера. Він використовується лише в тригерах DML. Він застосовується, коли будь-яка подія DML має відбутися у складному поданні.

Розглянемо приклад, у якому представлення створено з трьох базових таблиць. Коли будь-яка подія DML видається над цим представленням, вона стає недійсною, оскільки дані беруться з трьох різних таблиць. Тому в цьому випадку використовується тригер INSTEAD OF. Тригер INSTEAD OF використовується для безпосередньої зміни базових таблиць, а не для зміни представлення для заданої події.

Приклад 1: У цьому прикладі ми створимо складне представлення з двох базових таблиць, де Table_1 – це таблиця empty, а Table_2 – таблиця department.

Далі ми розглянемо, як тригер INSTEAD OF використовується для оновлення даних про місцезнаходження в цьому складному поданні. Ми також розглянемо, як :NEW та :OLD корисні в тригерах. Приклад виконується за допомогою таких кроків:

• Крок 1: Створення таблиць 'emp' та 'dept' з відповідними стовпцями
• Крок 2: Заповнення таблиць зразками значень
• Крок 3: Створення представлення для вищезгаданих таблиць
• Крок 4: Оновлення представлення перед тригером INSTEAD OF
• Крок 5: Створення тригера INSTEAD OF
• Крок 6: Оновлення представлення після спрацьовування тригера INSTEAD OF

Крок 1) Створення таблиць 'emp' та 'dept' з відповідними стовпцями.

На скріншоті нижче показано створення базових таблиць «emp» та «dept» у Oracle.

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept(
Dept_no NUMBER,
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code Пояснення

• Code рядки 1-7: Створення таблиці 'emp'.
• Code рядки 8-12: Створення таблиці «відділ».

вихід:

Table Created

Крок 2) Тепер, оскільки ми створили таблиці, ми заповнимо їх зразками значень.

На знімку екрана нижче показано приклади рядків, що вставляються в таблиці «dept» та «emp».

BEGIN
INSERT INTO DEPT VALUES(10,'HR','USA');
INSERT INTO DEPT VALUES(20,'SALES','UK');
INSERT INTO DEPT VALUES(30,'FINANCIAL','JAPAN');
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Пояснення

• Code рядки 13-19: Вставка даних у таблицю «відділ».
• Code рядки 20-26: Вставка даних у таблицю 'emp'.

вихід:

PL/SQL procedure completed

Крок 3) Створення представлення для вищезгаданих таблиць.

На скріншоті нижче показано створення та запит складного представлення.

CREATE VIEW guru99_emp_view(
Employee_name,dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code Пояснення

• Code рядки 27-32: Створення представлення 'guru99_emp_view'.
• Code рядок 33: Запит guru99_emp_view.

вихід:

View created

Крок 4) Оновлення представлення перед спрацьовуванням тригера INSTEAD OF.

На знімку екрана нижче показано спробу оновлення складного представлення та результуючу помилку.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code Пояснення

• Code рядки 34-38: Оновіть розташування «XXX» на «FRANCE». Це викликало виняток, оскільки оператори DML не дозволені безпосередньо у складному поданні.

вихід:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Крок 5) Щоб уникнути помилки, яка виникла під час оновлення представлення на попередньому кроці, на цьому кроці ми використовуватимемо тригер «ЗАМІСТЬ тригера».

На скріншоті нижче показано створення тригера INSTEAD OF.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code Пояснення

• Code рядок 39: Створення тригера INSTEAD OF для події 'UPDATE' у представленні 'guru99_emp_view' на рівні ROW. Він містить оператор update для оновлення розташування в базовій таблиці 'dept'.
• Code рядок 44: Оператор оновлення використовує ':NEW' та ':OLD' для знаходження значення стовпців до та після оновлення.

вихід:

Trigger Created

Крок 6) Оновлення представлення після спрацьовування тригера INSTEAD OF. Тепер помилка не з’являтиметься, оскільки операцію оновлення цього складного представлення оброблятиме тригер «INSTEAD OF». Після виконання коду місцезнаходження співробітника XXX буде оновлено з «Японія» на «Франція».

На знімку екрана нижче показано успішне оновлення за допомогою тригера INSTEAD OF та оновлене подання.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code Пояснення:

• Code рядки 49-53: Оновлення розташування «XXX» на «ФРАНЦІЯ». Це успішно, оскільки тригер «INSTEAD OF» зупинив фактичний оператор оновлення у поданні та виконав оновлення базової таблиці.
• Code рядок 55: Перевірка оновленого запису.

вихід:

PL/SQL procedure successfully completed

Складний тригер

Складений тригер – це тригер, який дозволяє вказати дії для кожної з чотирьох точок часу в одному тілі тригера. Чотири різні точки часу, які він підтримує, наведено нижче.

• BEFORE STATEMENT – рівень
• ПЕРЕД РЯДОМ – рівень
• ПІСЛЯ РЯДУ – рівень
• AFTER STATEMENT – рівень

Це надає можливість об'єднувати дії з різним часом спрацьовування в один тригер.

На скріншоті нижче показано синтаксис складеного тригера з чотирма секціями синхронізації.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
ON <name of underlying object>
<Declarative part>
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Пояснення синтаксису:

• Наведений вище синтаксис показує створення тригера 'COMPOUND'.
• Декларативна частина є спільною для всіх блоків виконання в тілі тригера.
• Ці чотири блоки синхронізації можуть бути в будь-якій послідовності. Необов'язково мати всі чотири блоки синхронізації. Ми можемо створити тригер COMPOUND лише для необхідних таймінгів.

Приклад 1: У цьому прикладі ми створимо тригер для автоматичного заповнення стовпця зарплати значенням за замовчуванням 5000.

На скріншоті нижче показано приклад складеного тригера та його результат.

CREATE TRIGGER emp_trig
FOR INSERT
ON emp
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,'CCC',15000,'AAA',30);
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code Пояснення:

• Code рядки 2-10: Створення складеного тригера. Він створюється для рівня BEFORE ROW, щоб заповнити зарплату значенням за замовчуванням 5000. Це змінить зарплату на значення за замовчуванням '5000' перед вставкою запису в таблицю.
• Code рядки 11-14: Вставте запис у таблицю 'emp'.
• Code рядок 16: Перевірка вставленого запису.

вихід:

Trigger created

PL/SQL procedure successfully completed.

Увімкнення та вимкнення тригерів

Тригери можна вмикати або вимикати. Щоб увімкнути або вимкнути тригер, для нього потрібно надіслати оператор ALTER (DDL), який його вмикає або вимикає.

Нижче наведено синтаксис для ввімкнення/вимкнення тригерів.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Пояснення синтаксису:

• Перший синтаксис показує, як увімкнути/вимкнути один тригер.
• Другий оператор показує, як увімкнути/вимкнути всі тригери в певній таблиці.

Поширені запитання