Oracle Підручник із тригерів PL/SQL: замість складеного [приклад]

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

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

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

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

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

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

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

• Класифікація на основі синхронізація
• Тригер BEFORE: запускається до того, як відбулася зазначена подія.
• Тригер ПІСЛЯ: запускається після того, як сталася зазначена подія.
• ЗАМІСТЬ Тригера: Особливий тип. Ви дізнаєтесь більше про наступні теми. (тільки для DML)
• Класифікація на основі рівень
• Тригер рівня STATEMENT: запускається один раз для вказаного оператора події.
• Тригер рівня ROW: запускається для кожного запису, який постраждав у вказаній події. (тільки для DML)
• Класифікація на основі Event
• Тригер DML: спрацьовує, коли вказано подію DML (INSERT/UPDATE/DELETE)
• Тригер DDL: спрацьовує, коли вказано подію DDL (CREATE/ALTER)
• Тригер БАЗИ ДАНИХ: спрацьовує, коли вказана подія бази даних (ВХІД/ВИХІД/ЗАПУСК/ВИКЛЮЧЕННЯ)

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

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

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

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;

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

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

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

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

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

• :NEW – зберігає нове значення для стовпців базової таблиці/подання під час виконання тригера
• :OLD – зберігає старі значення стовпців базової таблиці/подання під час виконання тригера

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

	INSERT	ОНОВЛЕННЯ	DELETE
:НОВИЙ	ДІЄ	ДІЄ	НЕДІЙСНИЙ. У регістрі видалення немає нового значення.
:СТАРИЙ	НЕДІЙСНИЙ. У вставному регістрі немає старого значення	ДІЄ	ДІЄ

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

«ЗАМІСТЬ тригера» — це спеціальний тип тригера. Він використовується лише в тригерах DML. Він використовується, коли будь-яка подія DML відбудеться у складному поданні.

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

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

• Таблиця_1 є пустою таблицею і

• Таблиця_2 – таблиця відділу.

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

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

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

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

Пояснення коду

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

Вихід

Таблицю створено

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

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,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

Пояснення коду

• Рядок коду 13-19: Вставлення даних у таблицю «dept».
• Рядок коду 20-26: Вставлення даних у таблицю «emp».

Вихід

Процедура PL/SQL завершений

Крок 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;

Пояснення коду

• Рядок коду 27-32: Створення перегляду 'guru99_emp_view'.

• Рядок коду 33: Запит guru99_emp_view.

Вихід

Перегляд створено

ІМ'Я ПРАЦІВНИКА	DEPT_NAME	МІСЦЕ
ZZZ	HR	USA
РРР	ПРОДАЖ	UK
XXX	ФІНАНСОВІ	ЯПОНІЯ

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

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

Пояснення коду

• Рядок коду 34-38: Оновіть розташування «XXX» на «ФРАНЦІЯ». Це викликало виняток, тому що Операції DML не допускаються в складному поданні.

Вихід

ORA-01779: неможливо змінити стовпець, який відповідає таблиці без збереження ключа

ORA-06512: у рядку 2

Крок 5)Щоб уникнути помилок під час оновлення подання на попередньому кроці, на цьому кроці ми будемо використовувати «замість тригера».

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

Пояснення коду

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

Вихід

Тригер створено

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

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

SELECT * FROM guru99_emp_view;

Пояснення коду:

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

вихід:

Процедура PL/SQL успішно завершена

ІМ'Я ПРАЦІВНИКА	DEPT_NAME	МІСЦЕ
ZZZ	HR	USA
РРР	ПРОДАЖ	UK
XXX	ФІНАНСОВІ	ФРАНЦІЯ

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

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

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

Він надає можливість поєднувати дії для різних часових рамок в одному тригері.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
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".
• Декларативний розділ є загальним для всіх блоків виконання в тілі тригера.
• Ці 4 блоки синхронізації можуть бути в будь-якій послідовності. Не обов'язково мати всі ці 4 блоки синхронізації. Ми можемо створити тригер 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;

Пояснення коду:

• Рядок коду 2-10: Створення складеного тригера. Його створено для рівня часу ПЕРЕД РЯДКОМ, щоб заповнити зарплату значенням за замовчуванням 5000. Це змінить зарплату на значення за замовчуванням «5000» перед вставленням запису в таблицю.
• Рядок коду 11-14: Вставте запис у таблицю «emp».
• Рядок коду 16: Перевірка вставленого запису.

вихід:

Тригер створено

Процедура PL/SQL успішно завершена.

EMP_NAME	EMP_NO	СЛУЖБА	МЕНЕДЖЕР	DEPT_NO
CCC	1004	5000	AAA	30

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

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

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

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

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

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

Підсумки

У цій главі ми дізналися про тригери PL/SQL та їхні переваги. Ми також вивчили різні класифікації та обговорили тригер ЗАМІСТЬ і СКЛАДЕНИЙ тригер.