Tutorial Oracle PL / SQL Trigger: în loc de, compus (exemplu)

Ce este Trigger în PL / SQL?

TRIGGERS sunt programe stocate care sunt declanșate automat de motorul Oracle atunci când instrucțiunile DML precum inserarea, actualizarea, ștergerea sunt executate pe masă sau au loc unele evenimente. Codul care trebuie executat în cazul unui declanșator poate fi definit conform cerinței. Puteți alege evenimentul pe care trebuie declanșat declanșatorul și momentul executării. Scopul declanșatorului este menținerea integrității informațiilor din baza de date.

În acest tutorial, veți învăța-

Avantajele declanșatoarelor
Tipuri de declanșatoare în Oracle
Cum se creează un declanșator
: NOU și: Vechi Clauză
ÎN loc de Trigger
Declanșator compus

Avantajele declanșatoarelor

Următoarele sunt avantajele declanșatoarelor.

Generarea automată a unor valori de coloană derivate
Consolidarea integrității referențiale
Înregistrarea evenimentelor și stocarea informațiilor despre accesul la masă
Audit
Replicarea sincronă a tabelelor
Impunerea autorizațiilor de securitate
Prevenirea tranzacțiilor nevalide

Tipuri de declanșatoare în Oracle

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

Clasificare pe baza calendarului
- ÎNAINTE de declanșare: se declanșează înainte ca evenimentul specificat să aibă loc.
- După declanșare: se declanșează după ce a avut loc evenimentul specificat.
- În locul declanșatorului: un tip special. Veți afla mai multe despre alte subiecte. (numai pentru DML)
Clasificare bazată pe nivel
- Declarație nivel DECLARAȚIE: Se declanșează o singură dată pentru declarația de eveniment specificată.
- Declanșator nivel ROW: Se declanșează pentru fiecare înregistrare care a fost afectată în evenimentul specificat. (numai pentru DML)
Clasificare bazată pe eveniment
- Declanșator DML: se declanșează atunci când este specificat evenimentul DML (INSERT / UPDATE / DELETE)
- DDL Trigger: Se declanșează atunci când este specificat evenimentul DDL (CREATE / ALTER)
- Declanșator BAZĂ DE DATE: Se declanșează atunci când este specificat evenimentul bazei de date (LOGON / LOGOFF / STARTUP / SHUTDOWN)

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

Cum se creează un declanșator

Mai jos este sintaxa pentru crearea unui declanșator.

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

Sintaxă Explicație:

Sintaxa de mai sus arată diferitele instrucțiuni opționale care sunt prezente în crearea declanșatorului.
BEFORE / AFTER va specifica calendarul evenimentelor.
INSERT / UPDATE / LOGON / CREATE / etc. va specifica evenimentul pentru care trebuie declanșat declanșatorul.
Clauza ON va specifica pe ce obiect este valid evenimentul menționat mai sus. De exemplu, acesta va fi numele tabelului pe care poate apărea evenimentul DML în cazul declanșatorului DML.
Comanda „FOR EACH ROW” va specifica declanșatorul nivelului ROW.
Clauza WHEN va specifica condiția suplimentară în care declanșatorul trebuie să se declanșeze.
Partea de declarație, partea de execuție, partea de gestionare a excepțiilor este aceeași cu cea a celorlalte blocuri PL / SQL. Partea de declarație și partea de tratare a excepțiilor sunt opționale.

: NOU și: Vechi Clauză

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

Oracle a furnizat două clauze în declanșatorul la nivel RECORD pentru a păstra aceste valori. Putem folosi aceste clauze pentru a ne referi la valorile vechi și noi din corpul declanșator.

: NOU - Deține o nouă valoare pentru coloanele tabelului / vizualizării de bază în timpul executării declanșatorului
: VECHI - Deține valoarea veche a coloanelor tabelului de bază / vizualizării în timpul executării declanșatorului

Această clauză trebuie utilizată pe baza evenimentului DML. Tabelul de mai jos va specifica care este clauza valabilă pentru care instrucțiune DML (INSERT / UPDATE / DELETE).

	INTRODUCE	ACTUALIZAȚI	ȘTERGE
:NOU	VALABIL	VALABIL	INVALID. Nu există o valoare nouă în cazul ștergerii.
:VECHI	INVALID. Nu există o valoare veche în cazul inserării	VALABIL	VALABIL

ÎN loc de Trigger

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

Luați în considerare un exemplu în care o vizualizare este realizată din 3 tabele de bază. Când este emis un eveniment DML peste această vizualizare, acest lucru va deveni invalid deoarece datele sunt preluate din 3 tabele diferite. Deci, în acest loc se folosește declanșatorul INSTEAD OF. Declanșatorul INSTEAD OF este utilizat pentru a modifica direct tabelele de bază în loc să modificați vizualizarea pentru evenimentul dat.

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

Table_1 este emp table și

Table_2 este tabelul departamentului.

Apoi vom vedea cum este utilizat declanșatorul INSTEAD OF pentru a emite UPDATE declarația cu detalii despre locație în această vizualizare complexă. De asemenea, vom vedea cum: NOU și: VECHI sunt utile în declanșatoare.

Pasul 1: Crearea tabelelor „emp” și „dept” cu coloane adecvate
Pasul 2: popularea tabelului cu valori eșantion
Pasul 3: Crearea vizualizării pentru tabelul creat mai sus
Pasul 4: Actualizarea vizualizării înainte de declanșatorul în loc de declanșare
Pasul 5: crearea declanșatorului în loc de
Pasul 6: Actualizarea vizualizării după declanșare în loc de declanșare

Pasul 1) Crearea tabelelor „emp” și „dept” cu coloane adecvate

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

Explicarea codului

Linia 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 completa acest tabel cu valori eșantion ș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;/

Explicarea codului

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;

Explicarea codului

Linia de cod 27-32: crearea vizualizării „guru99_emp_view”.

Linia de cod 33: Interogarea guru99_emp_view.

Ieșire

Vizualizare creată

NUMELE ANGAJATULUI	DEPT_NAME	LOCAȚIE
ZZZ	HR	Statele Unite ale Americii
AAAA	VÂNZĂRI	Regatul Unit
XXX	FINANCIAR	JAPONIA

Pasul 4) Actualizarea vizualizării înainte de declanșare în loc de declanșare.

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

Explicarea codului

Linia de cod 34-38: Actualizați locația „XXX” la „FRANȚA”. A ridicat excepția, deoarece instrucțiunile DML nu sunt permise în vizualizarea complexă.

Ieșire

ORA-01779: nu poate modifica o coloană care se mapează la un tabel care nu este păstrat prin cheie

ORA-06512: la linia 2

Pasul 5) Pentru a evita întâlnirea cu erori în timpul actualizării vizualizării în pasul anterior, în acest pas vom folosi „în loc de declanșator”.

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

Explicarea codului

Linia de cod 39: Crearea declanșatorului INSTEAD OF pentru evenimentul „UPDATE” în vizualizarea „guru99_emp_view” la nivelul ROW. Conține declarația de actualizare pentru a actualiza locația în tabelul de bază „dept”.
Linia de cod 44: Instrucțiunea de actualizare folosește „: NOU” și „: VECHI” pentru a găsi valoarea coloanelor înainte și după actualizare.

Ieșire

Declanșator creat

Pasul 6) Actualizarea vizualizării după declanșare în loc de declanșare. Acum, eroarea nu va apărea deoarece „în loc de declanșator” va gestiona operațiunea de actualizare a acestei vizualizări complexe. Și când codul a fost executat, locația angajatului XXX va fi actualizată la „Franța” din „Japonia”.

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

SELECT * FROM guru99_emp_view;

Explicatie cod:

Linia de cod 49-53: Actualizarea locației „XXX” în „FRANȚA”. Este reușit, deoarece declanșatorul „INSTEAD OF” a oprit declarația de actualizare actuală și a efectuat actualizarea tabelului de bază.
Linia de cod 55: Verificarea înregistrării actualizate.

Ieșire:

Procedura PL / SQL a fost finalizată cu succes

NUMELE ANGAJATULUI	DEPT_NAME	LOCAȚIE
ZZZ	HR	Statele Unite ale Americii
AAAA	VÂNZĂRI	Regatul Unit
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 declanșatorului unic. Cele patru puncte de sincronizare diferite pe care le suportă sunt cele de mai jos.

ÎNAINTE DE DECLARAȚIE - nivel
ÎNAINTE DE RÂND - nivel
DUPĂ RÂND - nivel
DUPĂ DECLARAȚIE - nivel

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

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;

Sintaxă Explicație:

Sintaxa de mai sus arată crearea declanșatorului „COMPOUND”.
Secțiunea declarativă este comună pentru toate blocurile de execuție din corpul declanșatorului.
Aceste 4 blocuri de sincronizare pot fi în orice ordine. Nu este obligatoriu să aveți toate aceste 4 blocuri de sincronizare. Putem crea un declanșator COMPOUND numai pentru momentele necesare.

Exemplul 1 : În acest exemplu, vom crea un declanșator pentru a popula automat coloana de salariu cu valoarea implicită 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;

Explicatie cod:

Linia de cod 2-10 : Crearea declanșatorului compus. Este creat pentru sincronizarea înaintea nivelului ROW pentru a completa salariul cu valoarea implicită 5000. Aceasta va schimba salariul la valoarea implicită „5000” înainte de a insera î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 a fost finalizată cu succes.

EMP_NAME	EMP_NO	SALARIU	ADMINISTRATOR	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 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Sintaxă Explicație:

Prima sintaxă arată cum să activați / dezactivați declanșatorul unic.
A doua afirmație arată cum să activați / dezactivați toate declanșatoarele de pe o anumită masă.

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 locul declanșatorului și al declanșatorului COMPUS.