Підручник із запуску Oracle PL / SQL: Замість, Compound (Приклад)

Зміст:

Anonim

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

TRIGGERS - це збережені програми, які запускаються двигуном Oracle автоматично, коли в таблиці виконуються DML-оператори, такі як вставка, оновлення, видалення або якісь події. Код, який слід виконати у випадку тригера, можна визначити відповідно до вимоги. Ви можете вибрати подію, після якої потрібно активувати тригер, і час виконання. Призначення тригера - підтримувати цілісність інформації в базі даних.

У цьому підручнику ви дізнаєтесь-

  • Переваги тригерів
  • Типи тригерів в Oracle
  • Як створити тригер
  • : NEW та: СТАРЕ застереження
  • ЗАМІСТО тригера
  • Складений тригер

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

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

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

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

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

  • Класифікація на основі часу
    • ПЕРЕД тригером: спрацьовує до того, як відбулася вказана подія.
    • ПІСЛЯ тригера: спрацьовує після того, як відбулася вказана подія.
    • ЗАМЕСТО тригера: особливий тип. Ви дізнаєтесь більше про подальші теми. (лише для DML)
  • Класифікація на основі рівня
    • Тригер рівня STATEMENT: спрацьовує один раз для зазначеного оператора події.
    • Тригер рівня ROW: спрацьовує для кожного запису, який зазнав впливу у зазначеній події. (лише для DML)
  • Класифікація на основі події
    • DML Trigger: спрацьовує, коли вказана подія DML (INSERT / UPDATE / DELETE)
    • DDL Trigger: спрацьовує, коли вказана подія DDL (CREATE / ALTER)
    • Тригер DATABASE: спрацьовує, коли вказана подія бази даних (LOGON / LOGOFF / STARTUP / SHUTDOWN)

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

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

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

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

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

  • Вищезазначений синтаксис показує різні необов’язкові оператори, які є при створенні тригера.
  • BEFORE / AFTER визначає терміни подій.
  • INSERT / UPDATE / LOGON / CREATE / тощо. вкаже подію, для якої потрібно активувати тригер.
  • У реченні ON буде вказано, для якого об’єкта є дійсною згадана подія. Наприклад, це буде ім'я таблиці, для якої може відбуватися подія DML у випадку DML Trigger.
  • Команда "ДЛЯ КОЖНОГО РЯДУ" задасть тригер рівня РЯДОК.
  • У пункті WHEN буде вказано додаткову умову, при якій тригер повинен спрацьовувати.
  • Частина декларації, частина виконання, частина обробки винятків така ж, як і у інших блоків PL / SQL. Частина декларації та частина обробки винятків необов’язкові.

: NEW та: СТАРЕ застереження

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

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

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

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

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

ЗАМІСТО тригера

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

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

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

  • Table_1 - це таблиця emp і
  • Таблиця_2 - таблиця відділу.

Потім ми побачимо, як тригер INSTEAD OF використовується для видачі ОНОВЛЕННЯ відомості про місце розташування в цьому складному поданні. Ми також побачимо, як: 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) Тепер, коли ми створили таблицю, ми заповнимо цю таблицю зразковими значеннями та Створення переглядів для вищезазначених таблиць. 

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;

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

  • Рядок коду 27-32: Створення подання 'guru99_emp_view'.
  • Код, рядок 33: Запит guru99_emp_view.

Вихідні дані

Вид створений

ІМ'Я ПРАЦІВНИКА DEPT_NAME РОЗТАШУВАННЯ
ZZZ HR США
РРР ПРОДАЖ Великобританія
XXX ФІНАНСОВА ЯПОНІЯ

Крок 4) Оновлення подання перед активатором.

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

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

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

Вихідні дані

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

ORA-06512: у рядку 2

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

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

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

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

Вихідні дані

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

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

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

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

  • Рядок коду 49-53: Оновлення місцезнаходження "XXX" на "FRANCE". Він успішний, оскільки тригер "INSTEAD OF" зупинив фактичний оператор оновлення у поданні та здійснив оновлення базової таблиці.
  • Кодовий рядок 55: Перевірка оновленого запису.

Вихід:

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

ІМ'Я ПРАЦІВНИКА DEPT_NAME РОЗТАШУВАННЯ
ZZZ HR США
РРР ПРОДАЖ Великобританія
XXX ФІНАНСОВА ФРАНЦІЯ

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

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

  • ДО ЗАЯВИ - рівень
  • ДО РЯДКУ - рівень
  • ПІСЛЯ РЯДКУ - рівень
  • ПІСЛЯ ЗАЯВИ - рівень

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

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

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

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

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

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT 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 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

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

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

Резюме

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