Oracle Declanșator PL/SQL: În loc de & tipuri compuse

Ce este Trigger în PL/SQL?

DECLANASOARELE sunt stocate PL / SQL programele declanșate de Oracle motorul automat când Declarații DML Coduri precum inserarea, actualizarea și ștergerea sunt executate în tabel sau atunci când apar anumite evenimente. Codul care trebuie executat în cazul unui declanșator poate fi definit în funcție de cerință. Puteți alege evenimentul la care trebuie să se declanșeze declanșatorul și momentul execuției. Scopul unui declanșator este de a menține integritatea 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 în funcție de moment

• ÎNAINTE de declanșator: Se declanșează înainte ca evenimentul specificat să se fi produs.
• DUPĂ declanșator: Se declanșează după ce s-a produs evenimentul specificat.
• ÎN LOC DE Declanșator: Un tip special. Veți afla mai multe în subiectele următoare. (doar pentru DML)

Clasificare în funcție de nivel

• Nivelul de DECLARAȚIE Declanșator: Se declanșează o singură dată pentru instrucțiunea de eveniment specificată.
• Declanșator la nivel de ROW: Se declanșează pentru fiecare înregistrare afectată de evenimentul specificat. (doar pentru DML)

Clasificare în funcție de eveniment

• Declanșator DML: Se declanșează când este specificat evenimentul DML (INSERT/UPDATE/DELETE).
• Declanșator DDL: Se declanșează când este specificat evenimentul DDL (CREATE/ALTER).
• Declanșator BAZA DE DATE: Se declanșează atunci când este specificat evenimentul bazei de date (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Deci, fiecare declanșator este o combinație a parametrilor de mai sus.

Cum se creează declanșatorul

Mai jos este sintaxa pentru crearea unui declanșator. Captura de ecran de mai jos prezintă această sintaxă de creare a declanșatorului în Oracle.

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.
• ÎNAINTE/DUPĂ va specifica orele evenimentului.
• INSERT/UPDATE/LOGON/CREATE/etc. va specifica evenimentul pentru care declanșatorul trebuie declanșat.
• Clauza ON va specifica obiectul asupra căruia este valid evenimentul menționat mai sus. De exemplu, acesta va fi numele tabelului asupra căruia poate apărea evenimentul DML în cazul unui declanșator DML.
• Comanda „PENTRU FIECARE RÂND” va specifica declanșatorul la nivel de RÂND.
• Clauza WHEN va specifica condiția suplimentară în care declanșatorul trebuie să se declanșeze.
• Partea de declarare, partea de execuție și partea de tratare a excepțiilor sunt aceleași ca cele ale celorlalte Blocuri PL/SQLPartea cu declarația și manipularea excepțiilor o parte 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 de rând pentru a păstra aceste valori. Putem folosi aceste clauze pentru a face referire la valorile vechi și noi din corpul declanșatorului.

• :NOU – Păstrează o nouă valoare pentru coloanele tabelului/vizualizării de bază în timpul execuției declanșatorului.
• :VECHI – Păstrează vechea valoare a coloanelor tabelului/vizualizării de bază în timpul execuției declanșatorului.

Această clauză ar trebui utilizată în funcție de evenimentul DML. Tabelul de mai jos specifică ce clauză este validă pentru ce instrucțiune DML (INSERT/UPDATE/DELETE).

ÎN LOC DE Declanșare

Un declanșator „INSTEAD OF” este un tip special de declanșator. Este utilizat numai în declanșatoarele DML. Este utilizat atunci când un eveniment DML urmează să aibă loc într-o vizualizare complexă.

Să luăm în considerare un exemplu în care o vizualizare este creată din trei tabele de bază. Atunci când un eveniment DML este emis peste această vizualizare, aceasta va deveni invalidă deoarece datele sunt preluate din trei tabele diferite. Așadar, în acest caz, se utilizează un declanșator INSTEAD OF. Declanșatorul INSTEAD OF este utilizat pentru a modifica direct tabelele de bază în loc să modifice vizualizarea pentru evenimentul dat.

Exemplu 1: În acest exemplu, vom crea o vizualizare complexă din două tabele de bază, unde Table_1 este tabelul emp, iar Table_2 este tabelul departamentului.

Apoi vom vedea cum este utilizat declanșatorul INSTEAD OF pentru a emite o ACTUALIZARE a detaliilor locației în această vizualizare complexă. De asemenea, vom vedea cum sunt utile funcțiile :NEW și :OLD în declanșatoare. Exemplul este realizat în următorii pași:

• Pasul 1: Crearea tabelelor „emp” și „dept” cu coloanele corespunzătoare
• Pasul 2: Popularea tabelelor cu valori eșantion
• Pasul 3: Crearea unei vizualizări pentru tabelele create mai sus
• Pasul 4: Actualizarea vizualizării înainte de declanșatorul INSTEAD OF
• Pasul 5: Crearea declanșatorului INSTEAD OF
• Pasul 6: Actualizarea vizualizării după declanșatorul INSTEAD OF

Pasul 1) Crearea tabelelor „emp” și „dept” cu coloanele corespunzătoare.

Captura de ecran de mai jos arată crearea tabelelor de bază „emp” și „dept” în Oracle.

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

Code Explicație

• Code liniile 1-7: Crearea tabelului „emp”.
• Code liniile 8-12: Crearea tabelului „dept”.

ieșire:

Table Created

Pas 2) Acum, din moment ce am creat tabelele, le vom popula cu valori eșantion.

Captura de ecran de mai jos prezintă rândurile de exemplu inserate în tabelele „dept” și „emp”.

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Explicație

• Code liniile 13-19: Introducerea datelor în tabelul „dept”.
• Code liniile 20-26: Introducerea datelor în tabelul „emp”.

ieșire:

PL/SQL procedure completed

Pas 3) Crearea unei vizualizări pentru tabelele create mai sus.

Captura de ecran de mai jos arată vizualizarea complexă care este creată și apoi interogată.

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;

Code Explicație

• Code liniile 27-32: Crearea vizualizării „guru99_emp_view”.
• Code linia 33: Se interogează guru99_emp_view.

ieșire:

View created

Pas 4) Actualizarea vizualizării înainte de declanșatorul INSTEAD OF.

Captura de ecran de mai jos arată încercarea de actualizare a vizualizării complexe și eroarea rezultată.

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

Code Explicație

• Code liniile 34-38: Actualizați locația lui „XXX” la „FRANCE”. A apărut o excepție deoarece instrucțiunile DML nu sunt permise direct în vizualizarea complexă.

ieșire:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Pas 5) Pentru a evita eroarea întâlnită la actualizarea vizualizării în pasul anterior, în acest pas vom folosi un declanșator „INSTEAD OF”.

Captura de ecran de mai jos arată crearea declanșatorului INSTEAD OF.

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

Code Explicație

• Code linia 39: Crearea declanșatorului INSTEAD OF pentru evenimentul „UPDATE” în ​​vizualizarea „guru99_emp_view” la nivel de ROW. Acesta conține instrucțiunea de actualizare pentru actualizarea locației în tabelul de bază „dept”.
• Code linia 44: Instrucțiunea de actualizare folosește „:NEW” și „:OLD” pentru a găsi valoarea coloanelor înainte și după actualizare.

ieșire:

Trigger Created

Pas 6) Actualizarea vizualizării după declanșatorul INSTEAD OF. Acum eroarea nu va mai apărea, deoarece „declanșatorul INSTEAD OF” va gestiona operațiunea de actualizare a acestei vizualizări complexe. Când codul este executat, locația angajatului XXX va fi actualizată din „Japonia” în „Franța”.

Captura de ecran de mai jos arată actualizarea reușită prin declanșatorul INSTEAD OF și vizualizarea reîmprospătată.

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

SELECT * FROM guru99_emp_view;

Code Explicaţie:

• Code liniile 49-53: Actualizarea locației „XXX” la „FRANCE”. Aceasta a reușit deoarece declanșatorul „INSTEAD OF” a oprit instrucțiunea de actualizare propriu-zisă din vizualizare și a efectuat actualizarea tabelului de bază.
• Code linia 55: Verificarea înregistrării actualizate.

ieșire:

PL/SQL procedure successfully completed

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 temporizare dintr-un singur corp de declanșator. Cele patru puncte de temporizare diferite pe care le acceptă sunt prezentate mai jos.

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

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

Captura de ecran de mai jos prezintă sintaxa declanșatorului compus cu cele patru secțiuni de temporizare ale sale.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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 ilustrează crearea unui declanșator „COMPOUND”.
• Secțiunea declarativă este comună pentru toate blocurile de execuție din corpul declanșatorului.
• Aceste patru blocuri de temporizare pot fi în orice secvență. Nu este obligatoriu să aveți toate cele patru blocuri de temporizare. Putem crea un declanșator COMPOUND doar pentru temporizările necesare.

Exemplu 1: În acest exemplu, vom crea un declanșator pentru a completa automat coloana salariu cu valoarea implicită 5000.

Captura de ecran de mai jos prezintă exemplul de declanșator compus și rezultatul acestuia.

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;

Code Explicaţie:

• Code liniile 2-10: Crearea declanșatorului compus. Acesta este creat pentru ca nivelul BEFORE ROW să populeze salariul cu valoarea implicită 5000. Aceasta va schimba salariul la valoarea implicită „5000” înainte de inserarea înregistrării în tabel.
• Code liniile 11-14: Introduceți înregistrarea în tabelul „emp”.
• Code linia 16: Verificarea înregistrării introduse.

ieșire:

Trigger created

PL/SQL procedure successfully completed.

Activarea și dezactivarea declanșatorilor

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

Mai jos este sintaxa pentru activarea/dezactivarea declanșatoarelor.

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

Explicația sintaxei:

• Prima sintaxă arată cum se activează/dezactivează un singur declanșator.
• A doua declarație arată cum să activați/dezactivați toate declanșatoarele de pe un anumit tabel.

Întrebări frecvente