• Triggeri PL/SQL
• Crearea unui trigger
• Triggeri de tip BEFORE
• Triggeri de tip AFTER
• Restricții în clauza WHEN
• Predicate condiționale
• Triggeri cu opțiunea INSTEAD OF
• Informații din dicționarul bazei de date

Un trigger este un bloc PL/SQL stocat pe server care se execută la apariția unui eveniment care modifică starea anumitor obiecte ale bazei de date. Termenul corespondent în literatura de specialitate românească este declanșator, dar este rar folosit și de aceea în continuare se va folosi termenul în limba engleză. Tipuri de evenimente care pot determina execuția unui trigger sunt:

• Comenzi INSERT, UPDATE, DELETE pe o tabelă
• Comenzi INSERT, UPDATE, DELETE pe un view cu opțiunea INSTEAD OF
• Comenzi CREATE, ALTER, DROP la nivel de schemă sau bază de date
• Comenzi SHUTDOWN, LOGON, LOGOFF la nivel de schemă sau bază de date

În general, triggerii se folosesc pentru:

• Gestionarea restricțiilor complexe de integritate
• Monitorizarea tranzacțiilor
• Efectuarea de replicări de tabele situate în diferite noduri ale unei baze de date distribuite
• Păstrarea semnăturii userilor care au efectuat operații pe baza de date
• Prelucrarea de informații statistice în legătură cu accesul tabelelor
• Jurnalizarea transparentă a evenimentelor

Printre avantajele utilizării triggerilor, se pot menționa:

• Declanșarea automată, la apariția evenimentului monitorizat
• Lansarea în execuție a unor proceduri stocate specifice
• Posibilitatea modificării în cascadă a mai multor obiecte corelate în baza de date
• Transparența față de utilizator

Sintaxa unui trigger este:

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
	{BEFORE | AFTER | INSTEAD OF}
	{DELETE | INSERT | UPDATE [OR {DELETE | INSERT | UPDATE }…] 
		[OF COLUMN[, COLUMN …] ]} 
	ON [schema.]tabel _name
	[referencing_clauses] 
	[FOR EACH ROW] 
	[WHEN (condition) ] 
	DECLARE
		trigger_variables
	BEGIN
		trigger_body
	END

Unde:

• trigger_name – numele triggerului PL/SQL
• schema – specifică schema pe care se definește triggerul sau în care există obiectele, în mod implicit este aleasă schema utilizatorului curent
• tabel _name – numele tabelul/view-ul pe care se monitorizează evenimentul
• column – numele coloanei (coloanelor) din tabelul/view-ul pe care se monitorizează evenimentul
• condition – reprezintă o condiție pentru executarea triggerului, fiind admise corelări dar nu și interogări
• trigger_variables – secțiunea de declarare a variabilelor locale ale triggerului
• trigger_body – reprezintă corpul triggerului
• BEFORE | AFTER – specifică momentul executării triggerului: înainte sau după apariția evenimentului
• INSTEAD OF – specifică că este permisă o operație de inserare, ștergere, modificare pe view-uri, pentru care nu este permisă operația în mod firesc
• INSERT | UPDATE | DELETE – specifică evenimentul pe care se declanșează triggerul
• FOR EACH ROW – specifică dacă execuția triggerului se face pentru fiecare linie afectată, cu respectarea condiției din WHEN

Sintaxa referencing_clauses:

REFERENCING {OLD [AS] old_variable NEW [AS] new_variable | 
			NEW [AS] new_variable OLD [AS] old_variable}

Se folosesc nume corelate pentru a specifica valorile noi și vechi ale rândului curent pentru care se declanșează triggerul. Aceste valori se pot folosi atât în clauza WHEN (old_variable.row_field, new_variable.row_field) cât și în blocul PL/SQL folosindu-se prefixate de două puncte : (de exemplu :old_variable.row_field, :new_variable.row_field).

Sintaxa pentru crearea unui trigger de sistem este următoarea:

	CREATE [OR REPLACE] TRIGGER [schema.]trigger_name 
		{BEFORE | AFTER} 
		{DDL_event_list | DB_event_list} 
		ON {DATABASE | SCHEMA} 
		[WHEN (condition) ] 
	DECLARE
		trigger_variables
	BEGIN
		trigger_body
	END

Unde:

• DDL_event_list - CREATE, DROP, ALTER
• DB_event_list - STARTUP, SHUTDOWN, LOGON, LOGOFF, SERVERERROR, SUSPEND

Există două tipuri de triggeri:

• Triggeri pe o comandă – sunt executați o singură dată pentru evenimentul declanșator. De exemplu dacă se execută o comandă INSERT de mai multe linii, triggerul este executat o singură dată. În acest caz, nu este limitare la numărul de linii afectate de eveniment;
• Triggeri pe o linie – este executat ori de câte ori o linie a unei tabele este afectată de evenimentul declanșator. De exemplu, dacă se execută o comandă UPDATE care actualizează k linii, atunci triggerul este executat de k ori.

Un trigger poate fi executat înainte ca un eveniment să aibă loc (opțiunea BEFORE) sau după ce evenimentul s-a consumat (opțiunea AFTER). În general, triggerii de tip BEFORE sunt folosiți pentru:

• A salva valorile coloanelor înaintea executării unei comenzi UPDATE
• A decide dacă acțiunea triggerului trebuie sau nu executată (aceasta poate îmbunătăți performanțele serverului prin eliminarea procesării inutile)

Triggeri de tip AFTER sunt, în general, folosiți atunci când:

• Se dorește ca executarea triggerului să se facă după ce comanda s-a efectuat cu succes
• Nu au apărut erori de procesare care ar impune o comandă ROLLBACK pentru respectiva tranzacție
• Trebuie alterate și alte date corelate cu cele deja afectate

Triggerii de tip BEFORE se declanșează la apariția unui eveniment, dar înainte ca evenimentul să se termine;

Ex. 1 Să se scrie un trigger de tip BEFORE care printează un mesaj ori de câte ori se face un insert în tabela emp.

Să verificăm cum lucrează făcând un insert în tabela EMP:

INSERT INTO EMP(EMPNO, ENAME, SAL)
VALUES(999, 'Preda', 1500);

Se observă că triggerul s-a declanșat și operația s-a făcut cu succes.

Să veden ce se întâmplă dacă forțăm o eroare la inserare(exemplul necesită ca empno să aibă constrângerea not null):

INSERT INTO EMP(ENAME, SAL)
VALUES('Tache', 1500);

Se observă că triggerul s-a declanșat normal, nu a ținut cont că inserarea nu a fost efectuată și a generat o eroare.

Triggerii de tip AFTER se declanșează după ce evenimentul declanșator se termină.

Ex. 2 Să se creeze un trigger de tip AFTER care afișează un mesaj ori de câte ori se face o modificare în tabela EMP.

Să verificăm cum lucrează triggerul făcând o modificare de comision în tabela EMP:

UPDATE EMP
SET COMM = 100
WHERE EMPNO = 7902;

Se observă că triggerul a fost declanșat la apariția evenimentului, în acest caz comanda UPDATE și s-a afișat mesajul de avertizare. Triggerul se declanșează chiar dacă nu este găsită nicio înregistrare care să îndeplinească condițiile din clauza WHERE.

UPDATE EMP
SET COMM = 100
WHERE EMPNO = 9999;

Deoarece nu există niciun angajat cu empno=9999, nu a fost alterată nicio linie, totuși triggerul s-a declanșat, deoarece nu a apărut nicio eroare de execuție.

Dacă însă operația se termină cu o eroare, triggerul nu se mai declanșează.

UPDATE EMP
SET EMPNO = NULL
WHERE EMPNO = 7902;
 
ROLLBACK;

Am încercat să atribuim valoarea NULL pentru empno, ceea ce este interzis prin definirea tabelei, ca urmare s-a generat o eroare de sistem pe constrângerea respectivă și triggerul nu s-a declanșat.

S-a utilizat comanda ROLLBACK pentru ca modificările să nu rămână permanente.

Dacă vrem să introducem o restricție pentru declanșarea triggerului, putem să folosim clauza WHEN. Această clauză se poate folosi numai pentru triggerii de tip linie, deci poate fi folosită doar cu opțiunea FOR EACH ROW. În clauza WHEN se acceptă numai condiționări directe sau corelate, nu se acceptă cereri sau subcereri.

Ex. 3 Să se creeze un trigger de tip AFTER care afișează un mesaj ori de câte ori se face o modificare în tabela emp, dar doar pentru angajații care nu au funcția ‘MANAGER’.

Să se modifice comisionul pentru angajatul cu empn = 7566

UPDATE EMP
SET COMM = 100
WHERE EMPNO = 7566;
 
ROLLBACK;

Se observă că triggerul nu a fost declanșat, cu toate că operația UPDATE s-a efectuat cu succes, deoarece angajatul are funcția ‘MANAGER’.

Ex. 4 Să se scrie un trigger de tip BEFORE care afișează un mesaj ori de câte ori se face un insert în tabela EMP, doar dacă salariul noului angajat este strict mai mare de 500 și strict mai mic de 5000.

Să facem o inserare cu salariul în afara limitei:

INSERT INTO EMP(EMPNO, ENAME, SAL)
VALUES(1111, 'Frunza', 5500);
 
ROLLBACK;

Se observă că inserarea s-a făcut cu succes, dar triggerul nu s-a declanșat.

Ex. 5 Să se scrie un trigger de tip AFTER care se declanșează dacă salariul unui angajat este majorat.

Se observă că în clauza WHEN, variabilele old și new, se apelează fără caracterul ‘:’ , pe când la afișare trebuie introdus.

Dacă se face o majorare de salariu pentru salariatul cu empno = 7566, care este rezultatul?

UPDATE EMP
SET SAL = 3000,
  COMM = 100
WHERE EMPNO = 7566;
 
ROLLBACK;

În cazul în care se execută mai multe comenzi DML, se pot folosi predicate condiționate în corpul triggerului. Predicatele condiționate sunt:

• INSERTING – returnează TRUE dacă triggerul se declanșează pe o comandă INSERT;
• UPDATING – retunează TRUE dacă triggerul se declanșează pe o comandă UPDATE;
• UPDATING (‘column_name’) – retunează TRUE dacă triggerul se declanșează pe o comandă UPDATE care modifică coloana specificată prin column_name;
• DELETING – returnează TRUE dacă triggerul se declanșează pe o comandă DELETE.

Ex. 6 Să se scrie un trigger de tip AFTER care se declanșează în momentul în care se face un insert, delete sau update pe coloanele sal și comm din tabela EMP. Să se insereze mesaje la declanșarea triggerului în tabela MESAJE. Pasul I – tabela mesaje

Pasul II – creare trigger

Pasul III – testare trigger

INSERT INTO EMP(EMPNO, ENAME, SAL, COMM)
VALUES(1111, 'Frunza', 1500, 100);
 
UPDATE EMP
SET COMM = NVL(COMM, 0) + 100
WHERE EMPNO = 1111;
 
UPDATE EMP
SET SAL = NVL(SAL, 0) + 500
WHERE EMPNO = 1111;
 
DELETE FROM EMP
WHERE EMPNO = 1111;
 
SELECT *
FROM MESAJE;
 
ROLLBACK;

Acest trigger se definește numai pe view-uri, nu și pe tabele. Unele view-uri nu pot fi modificate prin comenzi DML, dar folosind un trigger cu opțiunea INSTEAD OF acest lucru este realizabil. View-urile care nu pot fi modificate prin comenzile UPDATE, INSERT sau DELETE sunt cele create printr-o interogare care conține în construcție:

• Un operator SET sau DISTINCT
• O funcție de agregare sau o funcție analitică
• Clauzele GROUP BY, ORDER BY, CONNECT BY sau START WITH
• O expresie tip colecție într-o clauză SELECT
• O subcerere într-o clauză SELECT
• Unele metode de JOIN

Orice view, aflat într-un astfel de caz, se poate face modificabil folosind un trigger cu opțiunea INSTEAD OF. Acest trigger trebuie să determine ce operație trebuie executată pentru modificarea tabelei pe care este creat view-ul respectiv. Dacă view-ul conține pseudocoloane sau expresii , acestea nu pot fi modificate direct printr-o comandă UPDATE, dar pot fi modificate prin trigger.

Ex. 7 Să creăm un view sefi cu următoarea comandă:

Să facem o inserare în view-ul creat

INSERT INTO sefi
VALUES(50, 'Mediu', 1234, 'Preda', sysdate, 2000, 100);

Comanda INSERT a generat o eroare, deoarece nu se acceptă inserarea într-un astfel de view.

Să creăm triggerul manager, cu opțiunea INSTEAD OF, care va face o inserare și o modificare în tabela EMP și inserare în tabela DEPT.

Executăm din nou comanda insert de mai sus.

Se observă că inserarea s-a făcut cu succes, de data aceasta, deoarece s-a declanșat triggerul manager care a executat operațiile echivalente comenzii INSERT, prin inserarea în tabelele DEPT și EMP.

În corpul triggerului nu trebuie să fie cuprinse instrucțiuni care să afecteze starea tabelei sau view-ului pe care se monitorizează evenimentul.

Ex. 8 Să construim un trigger comision care actualizează comisionul la 10% din salariu când se modifică salariul unui angajat:

Să facem o modificare de salariu să vedem ce anume se întâmplă:

UPDATE EMP
SET SAL = 5000
WHERE EMPNO = 7566;

Se observă că în execuția triggerului s-a generat o eroare.

Informațiile despre triggeri se pot obține din dicționarul de date, ca pentru proceduri, funcții și pachete. De exemplu, dacă vrem să vedem toți triggerii creați de userul curent, data când au fost creați, data ultimei utilizări și starea lor, putem executa următoarea cerere SQL:

SELECT OBJECT_NAME,
  CREATED,
  LAST_DDL_TIME,
  STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER';

Un trigger se poate șterge din dicționar folosind comanda DDL :

DROP TRIGGER trigger_name;

Pentru a modifica starea unui trigger se folosească comanda DDL:

ALTER TRIGGER trigger_name {ENABLE | DISABLE}

Ex. 9. Să se scrie un trigger care face o inserare în tabela LOG ori de câte ori se face o operație pe coloana sal din tabela EMP. Tabela LOG are următoarea structură:

Triggerul este folosit pentru a păstra semnătura persoanei care a operat pe baza de date, operația pe care o făcut-o, pentru ce angajat a modificat salariul și la ce dată (în formatul yyyy-mm-dd hh-mi-ss). Crearea triggerului:

Testarea triggerului:

INSERT INTO EMP(EMPNO, ENAME, SAL, COMM)
VALUES(999, 'Preda', 1500, 100);
 
UPDATE EMP
SET SAL = NVL(SAL, 0) + 500
WHERE EMPNO = 999;
 
DELETE FROM EMP
WHERE EMPNO = 999;
 
SELECT *
FROM LOG;
 
ROLLBACK;