Oracle PL / SQL Trigger Tutorial: în loc de, compus [exemplu]

în acest tutorial, veți învăța –

• beneficiile declanșatoarelor
• tipuri de declanșatoare în Oracle
• cum se creează declanșatorul
• :nou și :Clauză veche
• în loc de declanșator
• declanșator compus

beneficiile declanșatoarelor

următoarele sunt beneficiile declanșatoarelor.

• generarea automată a unor valori de coloană derivate
• impunerea integrității referențiale
• înregistrarea evenimentelor și stocarea informațiilor privind accesul la tabel
• auditarea
• replicarea sincronă a tabelelor
• impunerea autorizațiilor de securitate
• prevenirea tranzacțiilor nevalide

tipurile de declanșatoare din Oracle

declanșatoarele pot fi clasificate pe baza următorilor parametri.

• clasificare bazată pe calendarul
  • înainte de declanșare: se declanșează înainte de evenimentul specificat a avut loc.
  • după declanșare: se declanșează după ce a avut loc evenimentul specificat.
  • în loc de declanșare: un tip special. Veți afla mai multe despre subiectele ulterioare. (numai pentru LMD )
• clasificare bazată pe nivelul
  • declarație nivel de declanșare: se declanșează o singură dată pentru declarația eveniment specificat.
  • Row Level Trigger: se declanșează pentru fiecare înregistrare care a fost afectată în evenimentul specificat. (numai pentru LMD)
• clasificare bazată pe eveniment
  • DML Trigger: se declanșează atunci când evenimentul LMD este specificat (INSERT / UPDATE / DELETE)
  • DDL Trigger: Se declanșează atunci când evenimentul DDL este specificat (CREATE/ALTER)
  • baza de date Trigger: se declanșează atunci când evenimentul bazei de date este specificat (LOGON/LOGOFF/STARTUP/SHUTDOWN)

deci fiecare declanșator este combinația parametrilor de mai sus.

cum se creează declanșatorul

mai jos este sintaxa pentru crearea unui declanșator.

CREATE TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

explicație sintaxă:

• sintaxa de mai sus arată diferitele instrucțiuni opționale care sunt prezente în crearea declanșatorului.
• Înainte / După va specifica timpii evenimentului.
• inserare / actualizare/conectare/creare / etc. va specifica evenimentul pentru care declanșatorul trebuie să fie tras.
• clauza ON va specifica pe ce obiect este valabil evenimentul menționat mai sus. De exemplu, acesta va fi numele tabelului pe care poate apărea evenimentul LMD în cazul declanșării LMD.
• comanda „pentru fiecare rând” va specifica declanșatorul nivelului rândului.
• când clauza va specifica condiția suplimentară în care declanșatorul trebuie să tragă.
• partea de declarație, partea de execuție, partea de manipulare a excepțiilor este aceeași cu cea a celorlalte blocuri PL/SQL. Partea de declarație și partea de manipulare a excepțiilor sunt opționale.

:nou și :clauza veche

într-un declanșator de nivel rând, declanșatorul se declanșează pentru fiecare rând aferent. Și uneori este necesar să cunoaștem valoarea înainte și după declarația DML.

Oracle a furnizat două clauze în declanșatorul de nivel RECORD pentru a menține aceste valori. Putem folosi aceste clauze pentru a ne referi la valorile vechi și noi din interiorul corpului declanșator.

• :Nou-deține o nouă valoare pentru coloanele tabelului de bază/vizualizare în timpul executării declanșatorului
• :vechi – deține o valoare veche a coloanelor tabelului de bază/vizualizare în timpul executării declanșatorului

această clauză ar trebui utilizată pe baza evenimentului DML. Tabelul de mai jos va specifica care clauză este valabilă pentru care declarație DML (INSERT/UPDATE/DELETE).

	INSERT	UPDATE	DELETE
:nou	VALID	valid	INVALID. Nu există nicio valoare nouă în cazul de ștergere.
:Vechi	nevalid. Nu există nicio valoare veche în cazul Inserare	VALID	VALID

în loc de declanșator

„în loc de declanșator” este tipul special de declanșator. Se utilizează numai în declanșatoarele DML. Acesta este utilizat atunci când orice eveniment LMD va avea loc pe vizualizarea complexă.

luați în considerare un exemplu în care se face o vizualizare din 3 tabele de bază. Atunci când orice eveniment LMD este emis peste această vizualizare, care va deveni invalid, deoarece datele sunt preluate din 3 tabele diferite. Deci, în acest loc de declanșare este folosit. În loc de declanșare este folosit pentru a modifica tabelele de bază direct în loc de a modifica vizualizarea pentru evenimentul dat.

Exemplul 1: în acest exemplu, vom crea o Vizualizare complexă din două tabele de bază.

• Table_1 este tabelul emp și

• Table_2 este tabelul Departamentului.

apoi vom vedea cum în loc de declanșare este folosit pentru a emite actualiza instrucțiunea detaliu locație pe această Vizualizare complexă. De asemenea, vom vedea cum :Nou și :vechi este util în declanșatoare.

• Pasul 1: Crearea tabelului ‘emp’ și ‘dept’ cu coloane corespunzătoare
• Pasul 2: popularea tabelului cu valori de probă
• Pasul 3: Crearea vizualizării pentru tabelul creat mai sus
• Pasul 4: Actualizarea vizualizării înainte de declanșarea în loc de declanșare
• Pasul 5: Crearea declanșatorului în loc de declanșare
• Pasul 6: actualizarea vizualizării după declanșarea în loc de declanșare

Pasul 1) crearea tabelului ‘emp’ și ” cu coloane corespunzătoare

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

explicație Cod

• linie de cod 1-7: Crearea tabelului emp.
• linia de cod 8-12: crearea tabelului ‘dept’.

ieșire

tabel creat

Pasul 2) acum, de când am creat tabelul, vom popula acest tabel cu valori de probă și crearea de vizualizări pentru tabelele de mai sus.

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

explicație Cod

• linia de cod 13-19: introducerea datelor în tabelul ‘dept’.
• linia de cod 20-26: introducerea datelor în tabelul’ emp’.

ieșire

procedura PL/SQL finalizată

Pasul 3) Crearea unei vizualizări pentru tabelul creat mai sus.

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;

explicație Cod

• linie de cod 27-32: crearea vizualizării ‘guru99_emp_view’.

• linia de cod 33: interogarea guru99_emp_view.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of „XXX” to ‘FRANCE’. A ridicat excepția, deoarece declarațiile DML nu sunt permise în vizualizarea complexă.

ieșire

ORA-01779: nu se poate modifica o coloană care mapează la un tabel non-cheie conservate

ORA-06512: la linia 2

Pasul 5)pentru a evita întâlnirea eroare în timpul actualizării vedere în pasul anterior, în acest pas vom folosi „în loc de declanșare.”

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

explicație Cod

• linie de cod 39: Crearea în loc de declanșare pentru evenimentul ‘UPDATE’ pe vizualizarea ‘guru99_emp_view’ la nivelul rândului. Acesta conține instrucțiunea de actualizare pentru a actualiza locația în tabelul de bază’dept’.
• linia de cod 44: instrucțiunea Update folosește ‘:NEW’ și ‘: OLD’ pentru a găsi valoarea coloanelor înainte și după actualizare.

ieșire

Trigger creat

Pasul 6) actualizarea de vedere după loc-de declanșare. Acum, eroarea nu va veni, deoarece „în loc de declanșare” se va ocupa de operația de actualizare a acestei vizualizări complexe. Și când codul a executat locația angajatului XXX va fi actualizat la ” Franța „de la” Japonia.”

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

SELECT * FROM guru99_emp_view;

explicație Cod:

• linie de cod 49-53: actualizarea locației” XXX ” la „Franța”. Este de succes, deoarece ‘în loc de’ de declanșare a oprit instrucțiunea actualizare reală pe Vizualizare și a efectuat actualizarea tabelului de bază.
• linia de cod 55: verificarea înregistrării actualizate.

ieșire:

procedura PL/SQL finalizată cu succes

EMPLOYEE_NAME	DEPT_NAME	locație
ZZZ	HR	SUA
AAAA	vânzări	Marea Britanie
xxx	financiar	Franța

declanșator compus

declanșatorul compus este un declanșator care vă permite să specificați acțiuni pentru fiecare dintre cele patru puncte de sincronizare din corpul unic de declanșare. Cele patru puncte de sincronizare diferite pe care le suportă sunt cele de mai jos.

• înainte de nivelul declarației
• înainte de nivelul rândului
• după nivelul rândului
• după nivelul declarației

acesta oferă facilitatea de a combina acțiunile pentru sincronizare diferită în același declanșator.

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

explicație sintaxă:

• sintaxa de mai sus arată crearea declanșatorului „compus”.
• secțiunea declarativă este comună pentru toate blocurile de execuție din corpul declanșatorului.
• aceste 4 blocuri de sincronizare pot fi în orice secvență. Nu este obligatoriu să aveți toate aceste 4 blocuri de sincronizare. Putem crea un declanșator compus numai pentru temporizările care sunt necesare.

Exemplul 1: în acest exemplu, vom crea un declanșator pentru a popula automat coloana salarială cu valoarea implicită 5000.

CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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;

explicație Cod:

• linia de cod 2-10: crearea de declanșare compus. Acesta este creat pentru calendarul înainte de rând-nivel pentru a popula salariul cu valoarea implicită 5000. Aceasta va schimba salariul la valoarea implicită ‘5000’ înainte de a introduce înregistrarea în tabel.
• linia de cod 11-14: introduceți înregistrarea în tabelul ‘emp’.
• linia de cod 16: verificarea înregistrării inserate.

ieșire:

declanșator creat

procedura PL / SQL finalizată cu succes.

EMP_NAME	EMP_NO	salariu	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

activarea și dezactivarea declanșatoarelor

declanșatoarele pot fi activate sau dezactivate. Pentru a activa sau dezactiva declanșatorul, trebuie să se dea o declarație ALTER (DDL) pentru declanșatorul care îl dezactivează sau îl activează.

mai jos sunt sintaxa pentru activarea / dezactivarea declanșatoarelor.

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;

explicație sintaxă:

• prima sintaxă arată cum să activați / dezactivați declanșatorul unic.
• a doua instrucțiune arată cum să activați / dezactivați toate declanșatoarele de pe un anumit tabel.

rezumat

În acest capitol, am aflat despre declanșatoarele PL / SQL și avantajele acestora. De asemenea, am învățat diferitele clasificări și am discutat în loc de declanșator și declanșator compus.