Oracle Tutorial de declanșare PL/SQL: în loc de, compus [Exemplu]

Ce este Trigger în PL/SQL?

DEclanșatoare sunt programe stocate care sunt declanșate de Oracle motor automat atunci când instrucțiunile DML precum inserarea, actualizarea, ștergerea sunt executate pe tabel sau apar anumite evenimente. Codul care trebuie executat în cazul unui declanșator poate fi definit conform cerințelor. Puteți alege evenimentul asupra căruia declanșatorul trebuie declanșat și momentul execuției. Scopul declanșatorului este menținerea integrității informațiilor din baza de date.

Beneficiile declanșatorilor

Următoarele sunt beneficiile declanșatorilor.

• Generarea automată a unor valori derivate de coloană
• Implementarea integrității referențiale
• Înregistrarea evenimentelor și stocarea informațiilor privind accesul la masă
• Audit
• Syncreplicarea armonioasă 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 bazată pe sincronizare
• Î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 loc de declanșare: un tip special. Veți afla mai multe despre subiectele ulterioare. (numai pentru DML)
• Clasificare bazată pe nivel
• Trigger la nivel STATEMENT: Se declanșează o singură dată pentru instrucțiunea de eveniment specificată.
• Declanșare la nivel de RÂND: 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ă când este specificat evenimentul DML (INSERT/UPDATE/DELETE)
• Declanșare DDL: se declanșează când este specificat evenimentul DDL (CREATE/ALTER)
• DATABASE Trigger: 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ă declanșatorul

Mai jos este sintaxa pentru crearea unui declanșator.

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;

Explicația sintaxei:

• Sintaxa de mai sus arată diferitele instrucțiuni opționale care sunt prezente în crearea declanșatorului.
• BEFORE/AFTER va specifica orarul evenimentului.
• INSERT/UPDATE/LOGON/CREATE/etc. va specifica evenimentul pentru care declanșatorul trebuie declanșat.
• Clauza ON va specifica pe ce obiect este valabil evenimentul mai sus mentionat. De exemplu, acesta va fi numele tabelului pe care poate apărea evenimentul DML în cazul declanșării DML.
• Comanda „PENTRU FIECARE RÂND” va specifica declanșarea nivelului RÂND.
• Clauza WHEN va specifica condiția suplimentară în care declanșatorul trebuie să se declanșeze.
• Partea declarație, partea de execuție, partea de gestionare a excepțiilor este aceeași cu cea a celeilalte Blocuri PL/SQL. Partea de declarație și partea de gestionare a excepțiilor sunt opționale.

:NEW și :OLD Clauză

Într-un declanșator la nivel de rând, declanșatorul se declanșează pentru fiecare rând aferent. Și uneori este necesar să cunoașteți valoarea înainte și după instrucțiunea 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 interiorul corpului declanșatorului.

• :NEW – Deține o nouă valoare pentru coloanele tabelului/vizualizării de bază în timpul execuției declanșatorului
• :OLD – Deține valoarea veche a coloanelor din tabelul/vizualizarea de bază în timpul execuției declanșatorului

Această clauză ar trebui utilizată pe baza evenimentului DML. Tabelul de mai jos va specifica ce clauză este validă pentru ce instrucțiune DML (INSERT/UPDATE/DELETE).

	INSERT	UPDATE	DELETE
:NOU	VALABIL	VALABIL	INVALID. Nu există nicio valoare nouă în cazul ștergerii.
:VECHI	INVALID. Nu există o valoare veche în introducerea majusculei	VALABIL	VALABIL

ÎN LOC DE Declanșare

„INSTEAD OF trigger” este tipul special de trigger. Este folosit numai în declanșatoarele DML. Este folosit atunci când orice eveniment DML va avea loc pe vizualizarea complexă.

Luați în considerare un exemplu în care o vedere este realizată din 3 tabele de bază. Când orice eveniment DML este emis pe această vizualizare, acesta va deveni invalid deoarece datele sunt preluate din 3 tabele diferite. Deci, în acest declanșator ÎNLOCUIT DE este folosit. Declanșatorul INSTEAD OF este folosit pentru a modifica direct tabelele de bază, în loc să modifice vizualizarea pentru evenimentul dat.

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

• Tabelul_1 este tabel emp și

• Tabelul_2 este tabelul departamentului.

Apoi vom vedea cum este folosit declanșatorul INSTEAD OF pentru a emite declarația UPDATE privind detaliile locației pe această vizualizare complexă. Vom vedea, de asemenea, cum :NEW și :OLD sunt utile în declanșatoare.

• Pasul 1: Crearea tabelului „emp” și „dept” cu coloanele corespunzătoare
• Pasul 2: Popularea tabelului cu valorile eșantionului
• Pasul 3: Crearea vizualizării pentru tabelul creat mai sus
• Pasul 4: Actualizați vizualizarea înainte de declanșarea în loc de
• Pasul 5: Crearea declanșatorului în loc de
• Pasul 6: Actualizarea vizualizării după declanșare

Pas 1) Crearea tabelului „emp” și „dept” cu coloanele 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));
/

Explicarea codului

• Linia de cod 1-7: Crearea tabelului „emp”.
• Linia de cod 8-12: Crearea tabelului „dept”.

producție

Tabelul creat

Pas 2) Acum, din moment ce am creat tabelul, vom completa acest tabel cu valori eșantion și crearea de vizualizări pentru tabelele de mai sus.

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

Explicarea codului

• Linia de cod 13-19: Inserarea datelor în tabelul „dept”.
• Linia de cod 20-26: Inserarea datelor în tabelul „emp”.

producție

Procedura PL/SQL terminat

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

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;

Explicarea codului

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

• Linia de cod 33: Se interogează guru99_emp_view.

producție

Vedere creată

NUMELE ANGAJATULUI	DEPT_NAME	LOCAȚIE
ZZZ	HR	USA
AAAA	SALES	UK
XXX	FINANCIARĂ	JAPONIA

Pas 4) Actualizare a vizualizării înainte de declanșare.

BEGIN
UPDATE 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 „FRANCE”. A ridicat excepția deoarece Declarații DML nu sunt permise în vederea complexă.

producție

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

ORA-06512: la linia 2

Pas 5)Pentru a evita întâlnirea erorilor în timpul actualizării vizualizării din pasul anterior, în acest pas vom folosi „în loc de declanșare”.

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

Explicarea codului

• Linia de cod 39: Crearea declanșatorului INSTEAD OF pentru evenimentul „UPDATE” în ​​vizualizarea „guru99_emp_view” la nivel ROW. Conține instrucțiunea de actualizare pentru a actualiza locația din tabelul de bază „dept”.
• Linia de cod 44: Declarația de actualizare folosește „:NEW” și „: OLD” pentru a găsi valoarea coloanelor înainte și după actualizare.

producție

Declanșator creat

Pas 6) Actualizare a vizualizării după declanșare în 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 fost executat, locația angajatului XXX va fi actualizată la „Franța” din „Japonia”.

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

SELECT * FROM guru99_emp_view;

Explicația codului:

• Linia de cod 49-53: Actualizare a locației „XXX” în „FRANȚA”. Are succes deoarece declanșatorul „INSTEAD OF” a oprit instrucțiunea de actualizare reală și a efectuat actualizarea tabelului de bază.
• Linia de cod 55: Verificarea înregistrării actualizate.

ieșire:

Procedura PL/SQL finalizată cu succes

NUMELE ANGAJATULUI	DEPT_NAME	LOCAȚIE
ZZZ	HR	USA
AAAA	SALES	UK
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 de declanșare unic. Cele patru puncte de sincronizare diferite pe care le suportă sunt cele de mai jos.

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

Oferă posibilitatea de a combina acțiunile pentru timpi diferite în același declanșator.

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;

Explicația sintaxei:

• 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 secvență. Nu este obligatoriu să aveți toate aceste 4 blocuri de cronometrare. Putem crea un declanșator COMPOUND numai pentru momentele care sunt necesare.

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

Explicația codului:

• Linia de cod 2-10: Crearea declanșatorului compus. Este creat pentru cronometrare BEFORE ROW- level 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: Inserați înregistrarea în tabelul „emp”.
• Linia de cod 16: Verificarea înregistrării introduse.

ieșire:

Declanșatorul a fost creat

Procedura PL/SQL finalizată cu succes.

EMP_NAME	EMP_NR	SALARIU	MANAGER	DEPT_NR
CVC	1004	5000	AAA	30

Activarea și dezactivarea declanșatorilor

Declanșatoarele pot fi activate sau dezactivate. Pentru a activa sau dezactiva declanșatorul, trebuie dată o instrucțiune ALTER (DDL) pentru declanșatorul care îl dezactivează sau îl activează.

Mai jos sunt sintaxa pentru activarea/dezactivarea declanșatorilor.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Explicația sintaxei:

• Prima sintaxă arată cum să activați/dezactivați declanșatorul unic.
• A doua declarație arată cum să activați/dezactivați toate declanșatoarele de pe un anumit tabel.

Rezumat

În acest capitol, am aflat despre declanșatorii PL/SQL și despre avantajele acestora. Am învățat, de asemenea, diferitele clasificări și am discutat în locul declanșatorului și a declanșatorului COMPOUND.