Oracle Урок за задействане на PL/SQL: Вместо съединение [Пример]

Какво е тригер в PL/SQL?

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

Предимства на тригерите

Следват предимствата на тригерите.

  • Автоматично генериране на някои производни стойности на колони
  • Налагане на референтна цялост
  • Регистриране на събития и съхраняване на информация за достъп до таблица
  • Одиторски
  • Syncхронична репликация на таблици
  • Налагане на разрешения за сигурност
  • Предотвратяване на невалидни транзакции

Видове тригери в Oracle

Тригерите могат да бъдат класифицирани въз основа на следните параметри.

  • Класификация въз основа на времето
  • ПРЕДИ Тригер: Задейства се преди да е настъпило определеното събитие.
  • AFTER Тригер: Задейства се след настъпване на определеното събитие.
  • ВМЕСТО Спусък: Специален тип. Ще научите повече за следващите теми. (само за DML)
  • Класификация въз основа на ниво
  • Тригер на ниво STATEMENT: Задейства се веднъж за указания оператор за събитие.
  • Тригер на ниво ROW: Задейства се за всеки запис, който е засегнат в определеното събитие. (само за DML)
  • Класификация въз основа на събитие
  • DML Trigger: Задейства се, когато е указано DML събитие (INSERT/UPDATE/DELETE)
  • DDL тригер: Задейства се, когато е указано DDL събитие (CREATE/ALTER)
  • DATABASE Trigger: Задейства се, когато е посочено събитието на базата данни (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Така че всеки тригер е комбинация от горните параметри.

Как да създадете тригер

По-долу е синтаксисът за създаване на тригер.

Създайте Trigger

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;

Обяснение на синтаксиса:

  • Горният синтаксис показва различните незадължителни изрази, които присъстват при създаването на тригер.
  • ПРЕДИ/СЛЕД ще уточни времето на събитието.
  • INSERT/UPDATE/LOGON/CREATE/и др. ще посочи събитието, за което тригерът трябва да бъде задействан.
  • Клаузата ON ще посочи за кой обект е валидно гореспоменатото събитие. Например, това ще бъде името на таблицата, на която може да възникне DML събитието в случай на DML Trigger.
  • Командата „ЗА ВСЕКИ РЕД“ ще укаже тригера на ниво РЕД.
  • Клаузата WHEN ще посочи допълнителното условие, при което тригерът трябва да се задейства.
  • Частта за деклариране, частта за изпълнение, частта за обработка на изключения е същата като тази на другата PL/SQL блокове. Частта за деклариране и частта за обработка на изключения не са задължителни.

Клауза :NEW и :OLD

В тригер на ниво ред, тригерът се задейства за всеки свързан ред. И понякога се изисква да се знае стойността преди и след DML оператора.

Oracle предостави две клаузи в тригера на ниво RECORD за задържане на тези стойности. Можем да използваме тези клаузи, за да се позоваваме на старите и новите стойности в тялото на тригера.

  • :NEW – Съдържа нова стойност за колоните на базовата таблица/изглед по време на изпълнението на тригера
  • :OLD – Съдържа старата стойност на колоните на базовата таблица/изглед по време на изпълнение на тригера

Тази клауза трябва да се използва въз основа на DML събитието. Таблицата по-долу ще посочи коя клауза е валидна за кой DML оператор (INSERT/UPDATE/DELETE).

INSERT АКТУАЛИЗАЦИЯ ИЗТРИЙ
:НОВО ВАЛИДНА ВАЛИДНА НЕВАЛИДЕН. Няма нова стойност в регистъра на буквите за изтриване.
:СТАР НЕВАЛИДЕН. Няма стара стойност във вмъкнатия регистър ВАЛИДНА ВАЛИДНА

ВМЕСТО Trigger

„ВМЕСТО тригер“ е специалният тип тригер. Използва се само в DML тригери. Използва се, когато всяко DML събитие ще се случи в сложния изглед.

Помислете за пример, в който изглед е направен от 3 базови таблици. Когато всяко DML събитие е издадено върху този изглед, това ще стане невалидно, тъй като данните са взети от 3 различни таблици. Така че в това ВМЕСТО се използва тригер. Тригерът INSTEAD OF се използва за директно модифициране на базовите таблици, вместо за модифициране на изгледа за даденото събитие.

Пример 1: В този пример ще създадем сложен изглед от две базови таблици.

  • Table_1 е emp таблица и
  • Таблица_2 е таблица на отдел.

След това ще видим как тригерът INSTEAD OF се използва за издаване на UPDATE на оператора за подробности за местоположението в този сложен изглед. Също така ще видим как :NEW и :OLD са полезни при задействания.

  • Стъпка 1: Създаване на таблица „emp“ и „dept“ с подходящи колони
  • Стъпка 2: Попълване на таблицата с примерни стойности
  • Стъпка 3: Създаване на изглед за създадената по-горе таблица
  • Стъпка 4: Актуализиране на изгледа преди тригера вместо
  • Стъпка 5: Създаване на тригера вместо
  • Стъпка 6: Актуализиране на изглед след задействане вместо вместо

Стъпка 1) Създаване на таблица 'emp' и 'dept' с подходящи колони

ВМЕСТО Trigger

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) След като създадохме таблицата, ще попълним тази таблица с примерни стойности и създаване на изгледи за горните таблици.

ВМЕСТО Trigger

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) Създаване на изглед за създадената по-горе таблица.

ВМЕСТО Trigger

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.

Продукция

Изгледът е създаден

EMPLOYEE_NAME DEPT_NAME МЕСТОПОЛОЖЕНИЕ
ЗЗЗ HR САЩ
ГГГ ПРОДАЖБИТЕ UK
XXX ФИНАНСОВА ЯПОНИЯ

Стъпка 4) Актуализация на изгледа преди вместо задействане.

ВМЕСТО Trigger

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

Обяснение на кода

  • Кодов ред 34-38: Актуализирайте местоположението на „XXX“ на „ФРАНЦИЯ“. Той повдигна изключението, защото DML отчети не са разрешени в комплексния изглед.

Продукция

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

ORA-06512: на ред 2

Стъпка 5)За да избегнем възникването на грешка по време на актуализиране на изгледа в предишната стъпка, в тази стъпка ще използваме „вместо тригер“.

ВМЕСТО Trigger

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 ще бъде актуализирано на „Франция“ от „Япония“.

ВМЕСТО Trigger

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

Обяснение на кода:

  • Кодов ред 49-53: Актуализация на местоположението на „XXX“ на „ФРАНЦИЯ“. Успешен е, защото тригерът „ВМЕСТО ОТ“ е спрял действителния оператор за актуализиране при изглед и е изпълнил актуализацията на базовата таблица.
  • Кодов ред 55: Проверка на актуализирания запис.

Изход:

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

EMPLOYEE_NAME DEPT_NAME МЕСТОПОЛОЖЕНИЕ
ЗЗЗ HR САЩ
ГГГ ПРОДАЖБИТЕ 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;

Обяснение на синтаксиса:

  • Първият синтаксис показва как да активирате/деактивирате единичния тригер.
  • Вторият оператор показва как да активирате/деактивирате всички тригери на определена таблица.

Oбобщение

В тази глава научихме за тригерите на PL/SQL и техните предимства. Също така научихме различните класификации и обсъдихме ВМЕСТО задействане и СЪЕДИНЕН задействане.