Урок за задействане на Oracle PL / SQL: Вместо, Compound (Пример)

Съдържание:

Anonim

Какво представлява Trigger в PL / SQL?

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

В този урок ще научите -

  • Предимства на тригерите
  • Видове задействания в Oracle
  • Как да създадете тригер
  • : НОВО и: СТАРА КЛАУЗА
  • ВМЕСТО Спусък
  • Съставен тригер

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

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

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

Видове задействания в Oracle

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

  • Класификация въз основа на времето
    • ПРЕДИ Тригер: Той се задейства преди да е настъпило посоченото събитие.
    • СЛЕД задействане: Той се задейства, след като е настъпило посоченото събитие.
    • ВМЕСТО Спусък: Специален тип. Ще научите повече за допълнителните теми. (само за DML)
  • Класификация въз основа на нивото
    • Задействане на ниво STATEMENT: Задейства се веднъж за посочения оператор на събитие.
    • Задействане на ниво ROW: Активира се за всеки запис, засегнат в посоченото събитие. (само за DML)
  • Класификация въз основа на събитието
    • DML Trigger: Той се задейства, когато е посочено DML събитие (INSERT / UPDATE / DELETE)
    • DDL Trigger: Активира се, когато е посочено DDL събитието (CREATE / ALTER)
    • Задействане на база данни: Той се задейства, когато е посочено събитие в базата данни (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.
  • Командата "ЗА ВСЕКИ РЕД" ще определи задействането на ниво ROW.
  • Клаузата WHEN ще посочи допълнителното условие, при което спусъка трябва да задейства.
  • Частта за декларация, част за изпълнение, част за обработка на изключения е същата като тази на другите PL / SQL блокове. Частта за декларация и частта за обработка на изключения не са задължителни.

: НОВО и: СТАРА КЛАУЗА

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

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

  • : НОВО - Той съдържа нова стойност за колоните на основната таблица / изглед по време на изпълнението на задействането
  • : 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 : Създаване на таблица „dept“.

Изход

Таблица създадена

Стъпка 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: Създаване на INSTEAD OF спусъка за събитие '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“ до „ФРАНЦИЯ“. Той е успешен, защото задействането „INSTEAD OF“ спря действителния изглед на актуализацията и извърши актуализация на основната таблица.
  • Кодов ред 55: Проверка на актуализирания запис.

Изход:

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

ИМЕ НА СЛУЖИТЕЛЯ DEPT_NAME МЕСТОПОЛОЖЕНИЕ
ZZZ HR САЩ
ГГГ ПРОДАЖБИ Великобритания
XXX ФИНАНСОВИ ФРАНЦИЯ

Съставен тригер

Задействането на Compound е спусък, който ви позволява да задавате действия за всяка от четирите точки на времето в единичното тяло на задействането. Четирите различни точки за синхронизиране, които поддържа, са както по-долу.

  • ПРЕДИ ДЕКЛАРАЦИЯ - ниво
  • ПРЕДИ РЕД - ниво
  • СЛЕД РЕД - ниво
  • СЛЕД СЪОБЩЕНИЕ - ниво

Той предоставя възможност за комбиниране на действията за различно време в един и същ спусък.

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 блока за синхронизация. Можем да създадем СЪЕДИНЕНО задействане само за времето, което се изисква.

Пример 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 : Създаване на комбиниран спусък. Създаден е за синхронизиране ПРЕД ROW- ниво, за да се запълни заплатата със стойност по подразбиране 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 тригерите и техните предимства. Също така научихме различните класификации и обсъдихме ВМЕСТО НА спусъка и СЪЕДИНЕНИЯТ спусък.