Table of Contents

Laboratorul 07 - PL/SQL Triggers

Conținut

Triggeri PL/SQL

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:

În general, triggerii se folosesc pentru:

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

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:

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).

Numele implicite sunt OLD și NEW.

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:

Crearea unui trigger

Există două tipuri de triggeri:

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:

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

Triggeri de tip BEFORE

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.

Dacă compilarea unui trigger se face cu succes, apare mesajul: “Trigger created”

În caz că apar erori la crearea unui trigger apare mesajul de avertizare: “Warning: Trigger created with compilation errors.”

Pentru a vedea erorile de compilare se va folosi comanda: “show errors;”

Triggeri de tip AFTER

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.

Putem trage concluzia că un trigger de tip BEFORE se declanșează necondiționat de rezultatul comenzii SQL (chiar dacă se generează o eroare), pe când cel de tip AFTER nu se declanșează dacă comanda SQL generează o eroare.

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

Restricții în clauza WHEN

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;

Predicate condiționate

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

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;

Triggere cu opțiunea INSTEAD OF

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:

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ții din dicționarul bazei de date

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;