 |
|
 |  |
|
|
| |
 | Biologie | Chimie | Didactica | Fizica | Geografie | Informatica |
| Istorie | Literatura | Matematica | Psihologie |
Un declansator este un bloc PL/SQL ce se executa implicit ori de cate ori un eveniment are loc. Un declansator poate fi fie un declansator al bazei de date fie un declansator al aplicatiei. Declansatorii bazei de date se executa implicit cand este folosita o instructiune INSERT, UPDATE sau DELETE. Declansatorii aplicatiei se executa implicit ori de cate ori un eveniment particular apare intr-o aplicatie.
Declansatorii bazei de date pot fi definiti numai pe tabele si nu pe view-uri.
Folosirea excesiva a declansatorilor poate duce la interdependente complexe, fiind indicata folosirea lor numai atunci cand sunt necesari.
Un exemplu de aplicatie care utilizeaza foarte des declansatorii este una creata cu Developer/2000 Form Builder.
Inainte de a scrie instructiunile care alcatuiesc corpul unui declansator trebuie sa decidem urmatoarele componentele ale unui declansator: timpul cand va actiona, evenimentul declansator si tipul declansatorului.
|
Parti componente |
Descriere |
Valori posibile |
|
Stabilirea timpului de declansare a triggerului |
Cand triggerul actioneaza fata de evenimentul declansator |
BEFORE AFTER |
|
Evenimentul declansator |
Ce operatie cauzeaza activarea triggerului |
INSERT UPDATE DELETE |
|
Tipul triggerului |
De cate ori se executa corpul triggerului |
Statement Row |
|
Corpul triggerului |
Ce actiune executa triggerul |
Executa blocul scris in PL/SQL |
Ordinea in care triggerii de acelasi tip se executa este arbitrara. Pentru a fi siguri ca triggerii de acelasi tip se executa intr-o anumita ordine restrangeti triggerii intr-unul singur care apeleaza proceduri separate in ordinea dorita.
Sintaxa pentru crearea declansatorilor este:
CREATE [OR REPLACE] TRIGGER nume_trigger
timing event1 [OR event2 OR event3]
ON nume_tabela
PL/SQL block;
unde:
nume_trigger - este numele triggerului;
timing - indica timpul cand se declanseaza triggerul in relatie cu evenimentul de declansare BEFORE sau AFTER;
event - identifica operatia de manipulare a datelor (DML) ce determina declansarea triggerului. Poate fi: INSERT, UPDATE , DELETE [OF column];
nume_tabela - indica numele tabelei asociata triggerului;
PL/SQL block - este corpul triggerului, defineste actiunea executata de trigger, incepe fie cu DECLARE sau BEGIN si se termina cu END.
Stabilirea timpului cand actioneaza declansatorul se face prin optiunile BEFORE si AFTER.
BEFORE: Corpul triggerului se va executa inaintea evenimentului DML.
AFTER: Corpul triggerului se va executa dupa declansarea evenimentului DML.
Instructiunea sau evenimentul declansator poate fi o instructiune INSERT, UPDATE sau DELETE pe tabela.
Cand evenimentul declansator este un UPDATE, se poate include o lista de coloane pentru a identifica care coloane trebuie schimbate pentru a declansa triggerul. Nu se poate specifica o lista de coloane cu comanda INSERT sau DELETE pentru ca acestea intotdeauna actioneaza asupra unei linii intregi.
Evenimentul declansator poate contine multiple instructiuni DML. In acest fel se poate diferentia ce cod sa execute in functie de instructiunea care declanseaza triggerul.
Exista doua tipri de declansatori:
statement - corpul triggerului se executa o data pentru evenimentul decansator. Este implicit.
row - corpul triggerului se executa o data pentru fiecare rand afectat de evenimentul declansator.
Se poate specifica de cate ori se va executa actiunea declansatorului: O data pentru fiecare rand afectat de instructiunea declansatoare (cum ar fi o comanda UPDATE pentru mai multe randuri) sau o data pentru situatia in care avem un trigger de tipul statement indiferent de cate randuri afecteaza instructiunea.
Un statement trigger este declansat o data in numele evenimentului declansator, chiar daca nici un rand nu este afectat. Sunt folositi daca actiunea declansatoare nu depinde de informatiile din liniile care sunt afectate sau de informatiile oferite de evenimentul declansator. Exemplu: un trigger ce efectueaza o verificaree de securitate pe utilizatorul curent.
Un row trigger se declanseaza de fiecare data cand tabela este afectata de evenimentul declansator. Cand evenimentul nu afecteaza nici o linie nu se executa nici un row trigger.
Actiunea declansatorului defineste ce trebuie facut cand apare un eveniment declansator. Poate contine instructiuni SQL si PL/SQL, se pot defini constructii PL/SQL ca variabile, cursori, exceptii, etc. Pe langa aceasta row triggerii au acces la valorile vechi si noi ale coloanelor, folosind nume de corelatie.
Exemplu
SQL>INSERT INTO dept (depno, dname, loc)
2 VALUES (50,'ANALISTI', 'ORADEA');
Comanda SQL INSERT nu face diferenta intre triggerii statement si triggerii row, deoarece este inserat in tabel un singur rand folosind aceasta forma a comenzii. Cand comanda de declansare afecteaza mai multe randuri, triggerul statement se executa o data si triggerul row se executa o data pentru fiecare rand afectat de comanda.
Exemplu
SQL>UPDATE emp
2 SET sal = sal * 1.1
3 WHERE depno = 30;
Comanda SQL UPDATE declanseaza triggerul pentru fiecare rand ori de cate ori in tabela campul depno=30, pentru a satisface clauza WHERE.
Crearea unui trigger Statement folosind Procedure Builder se realizeaza astfel:
Se conecteaza la baza de date.
Alegeti Database Object din Object Navigator.
Selecteaza Database Trigger Editor din meniul Program.
Se alege o tabela apasand pe butoanele corespunzatoare.
Apasati New pentru crearea triggerului.
Selectati unul din butoanele radio (Before sau After) pentru a alege componenta timing.
Alegeti evenimentul (INSERT, UPDATE sau DELETE).
Introduceti comenzile in sectiunea Trigger Body.
Salvati. Codul va fi compilat. Odata compilat cu succes, declansatorul este inmagazinat in baza de date si activat.
Exemplu: Triggerul Statement BEFORE.
SQL> CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON emp
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))
5 OR TO_CHAR(sysdate,'HH24')NOTBETWEEN
6 '8' AND '18'
7 THEN RAISE_APPLICATION_ERROR (-20500,
8 'Inserati in EMP doar intre orele normale.');
9 END IF;
10 END;
11 /
S-a creat un trigger pentru a restrictiona inserarile in tabela EMP la anumite ore, de luni pana vineri. Daca un utilizator a incearcat sa insereze o inregistrare in tabela EMP sambata, de exemplu, utilizatorul va vedea mesajul, triggerul va esua si comanda care a declansat triggerul va face un rollback.
RAISE_APPLICATION_ERROR este o procedura care tipareste mesajul pentru utilizator si declanseaza esuarea blocului PL/SQL.
Cand un trigger (declansator) esueaza, comanda de declansare este anulata automat de catre serverul Oracle.
Exemplu: O incercare de a se insera o linie in tabela EMP in timpul orelor nelucratoare.
SQL> INSERT INTO emp (empno, ename, deptno)
2 VALUES (7777, 'BAUWENS',40);
INSERT INTO emp (empno, ename, deptno)
*
ERROR at line 1:
ORA-20500: Inserati in EMP doar intre orele normale.
ORA-06512: at "SCOTT.SECURE_EMP", line 4
ORA-04088: error during execution of trigger
'SCOTT.SECURE_EMP'
Exemplu: Folosirea predicatelor conditionale.
In urmatorul exemplu s-a creat un trigger pentru a restrictiona toate evenimentele de manipulare a informatiilor de pe tabela EMP la anumite ore de luni pina vineri.
SQL> CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT OR UPDATE OR DELETE ON emp
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR
5 (TO_CHAR(sysdate,'HH24')NOT BETWEEN '08' AND '18' THEN
6 IF DELETING THEN
7 RAISE_APPLICATION_ERROR (-20502,
8 'Stergeti din EMP doar intre orele normale.');
9 ELS IF INSERTING THEN
10 RAISE_APPLICATION_ERROR (-20500,
11 'Inserati in EMP doar intre orele normale.');
12 ELSIF UPDATING ('SAL') THEN
13 RAISE_APPLICATION_ERROR (-20503,
14 'Inlocuiti SAL doar intre orele normale.');
15 ELSE
16 RAISE_APPLICATION_ERROR (-20504,
17 'Inlocuiti in EMP doar intre orele normale.');
18 END IF;
19 END IF;
20 END;
21 /
Exemplu: In exemplu de mai jos s-a creat un trigger AFTER pentru a urmari operatiunea de declansare sau pentru a efectua o operatie de calcul dupa ce a fost executata o operatie.
SQL> CREATE OR REPLACE TRIGGER check_sal_count
2 AFTER UPDATE OF sal ON emp
3 DECLARE
4 v_sal_changes NUMBER;
5 v_max_changes NUMBER;
6 BEGIN
7 SELECT upd, max_upd
8 INTO v_sal_changes, v_max_changes
9 FROM audit_table
10 WHERE user_name = user
11 AND table_name = 'EMP'
12 AND column_name = 'SAL';
13 IF v_sal_changes > v_max_changes THEN
14 RAISE_APPLICATION_ERROR (-20501,
15 'Puteti face cel mult' ||
16 TO_CHAR(v_max_changes) ||
17 'inlocuiri in coloana SAL.');
18 END IF;
19 END;
20 /
Se presupune ca aveti definita deja o tabela audit_table care listeaza utilizatorii si numara operatiunile de manipulare a datelor. Dupa ce orice utilizator a actualizat coloana SAL din EMP, folositi audit_table pentru a va asigura ca numarul schimbarilor salariale nu depaseste maximul permis pentru acel utilizator.
Crearea unui trigger Row se face folosind urmatoarea sintaxa:
CREATE [OR REPLACE] TRIGGER nume_trigger
timing event1 [OR event2 OR event3]
ON nume_tabela
[REFERENCING OLD AS vechi | NEW AS nou
FOR EACH ROW
[WHEN conditie]
PL/SQL block
unde:
nume_trigger - numele triggerului;
timing - indica timpul cand actioneaza triggerul in relatie cu momentul declansator BEFORE sau AFTER.
event - identifica operatiunea de manipulare a operatiilor ce declanseaza triggerul. INSERT, UPDATE [OF column], DELETE.
nume_tabela - indica tabela asociata cu triggerul;
REFERENCING - specifica corelarea numelor pentru valorile vechi si noi ale randului sau liniei curente. Implicit sunt OLD si NEW.
FOR EACH ROW - desemneaza triggerul ca fiind Row trigger.
WHEN - specifica restrictiile pentru trigger (acest predicat conditional este evaluat pentru fiecare rand pentru a determina daca corpul triggerului se executa sau nu)
PL/SQL block - este corpul triggerului care defineste actiunea executata de trigger, incepand fie cu DECLARE sau BEGIN si sfarsind cu END;
Crearea triggerilor de tip Row folosind Procedure Builder se face in urmatorii pasi:
Conectare la baza de date.
Selecteaza Database Trigger din Object Navigator.
Selecteaza Database Trigger Editor din meniul Program.
Selecteaza un Table Owner si o tabela.
Selectati New pentru a incepe crearea triggerului.
Alegeti unul din butoanele radio AFTER sau BEFORE (de sub Triggering).
Alegeti evenimentul de declansare (UPDATE, INSERT, DELETE) de sub Statement.
Selecteaza check box-ul For Each Row pentru a desemna triggerul ca fiind trigger Row.
Introduceti Referencing OLD as si NEW as daca vreti sa modificati corelarea numelor. Introduceti o conditie WHEN pentru a restrictiona executarea triggerului. Aceste componente sunt obtionale si disponibile doar cu triggerii Row.
Introduceti comenzile ce alcatuiesc corpul triggerului
Salvati. Codul triggerului va fi compilat si depus in baza de date pe server si activat automat.
Exemplu: Se creaza un trigger Row pentru a memora un contor al operatiilor de manipulare a datelor pentru utilizatori diferiti pe anumite tabele de date.
SQL> CREATE OR REPLACE TRIGGER audit_emp
2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 IF DELETING THEN
6 UPDATE audit_table SET del = del +1
7 WHERE user_name = user AND table_name = 'EMP'
8 AND column_name IS NULL;
9 ELSIF INSERTING THEN
10 UPDATE audit_table SET ins = ins +1
11 WHERE user_name = user AND table_name = 'EMP'
12 AND column_name IS NULL;
13 ELSIF UPDATING ('SAL') THEN
14 UPDATE audit_table SET upd = upd +1
15 WHERE user_name = user AND table_name = 'EMP'
16 AND column_name = 'SAL';
17 ELSE
18 UPDATE audit_table SET upd = upd + 1
19 WHERE user_name = user AND table_name = 'EMP'
20 AND column_name IS NULL;
21 END IF;
22 END;
Exemplu: Folosirea calificatorilor OLD si NEW.
Se creaza un trigger pe tabela EMP pentru a adauga linii la tabela AUDIT_EMP_VALUES, urmarind activitatea utilizatorului pe tabela EMP. Triggerul inregistreaza valorile mai multor coloane atat inainte cat si dupa modificari folosind calificatori OLD si NEW cu numele coloanei respective.
SQL> CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp_values (user_name,
6 timestamp, id, old_last_name, new_last_name,
7 old_title, new_title, old_salary, new_salary)
8 VALUES (USER, SYSDATE, :old.empno, :old.ename,
9 :new.ename, :old.job, :new.job,
10 :old.sal, :new.sal);
11 END;
Intr-un triggerde tip Row, referirea la valorile noi respectiv vechi ale unei coloane se face prin prefixare cu calificatorul Old respectiv New.
|
Operatie asupra datelor |
Valori vechi |
Valori noi |
|
INSERT |
NULL |
Valoarea inserata |
|
UPDATE |
Valoarea dinaintea actualizarii |
Valoarea dupa modificare |
|
DELETE |
Valoarea dinaintea stergerii |
NULL |
Cateva observatii privind calificatorii OLD si NEW:
OLD si NEW sunt disponibili doar in triggerii Row.
Calificatorii sunt prefixati cu : in orice instructiune SQL si PL/SQL.
Nu exista prefixare cu : daca apar in conditia de restrictionare WHEN.
Exemplu: Restrictionarea unui trigger Row.
SQL> CREATE OR REPLACE TRIGGER derive_commision_pct
2 BEFORE INSERT OR UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN (new.job = 'SALESMAN')
5 BEGIN
6 IF INSERTING THEN :new.comm := 0
7 ELSE /*UPDATE of salary */
8 IF :old.comm IS NULL THEN
9 :new.comm := 0;
10 ELSE
11 :new.comm := :old.comm * (:new.sal/:old.sal);
12 END IF;
13 END IF;
14 END;
S-a creat un trigger pe tabela EMP pentru a calcula comisionul unui angajat cand o linie este adaugata la tabela EMP sau cand salariul unui angajat este modificat.
Calificatorul New nu are nevoie de a fi prefixat cu o virgula in clauza WHEN.
|
Triggeri |
Proceduri |
|
Se foloseste CREATE TRIGGER |
Se foloseste CREATE PROCEDURE |
|
Dictionarul de date contine sursa si cod-p |
Dictionarul de date contine sursa si cod-p |
|
Apelat implicit |
Apelat explicit |
|
Nu sunt permise COMMIT, SAVEPOINT si ROLLBACK |
Sunt permise COMMIT, SAVEPOINT si ROLLBACK |
Triggerii sunt compilati in totalitate cand comanda CREATE TRIGGER apare si codul-p este stocat in dictionarul de date. De aici, declansarea triggerului nu mai necesita deschiderea unui cursor partajat pentru a rula corpul triggerului. In schimb, triggerul se executa direct.
|
Trigger database |
Trigger Form Builder |
|
Executat prin actiuni din orice aplicatie sau unealta din baza de date |
Executat doar intr-o aplicatie particulara Form Builder |
|
Intotdeauna declansat de o instructiune de manipulare a datelor SQL |
Poate fi declansat prin trecerea dintr-un domeniu in altul prin apasarea unei taste sau prin mai multe alte actiuni |
|
Poate fi Statement sau Row |
Nu se face diferenta intre trigger Row sau Statement |
|
In caz de esec se realizeza un rollback (restaurarea instructiunii de declansare). |
In caz de esec determina blocarea cursorului si poate cauza restaurarea intregii tranzactii |
|
Se declanseaza independent de, si in completare la triggerii Form Builder |
Se declanseaza independent de, si in completare la triggerii database |
|
Se executa sub domeniul de securitate al autorului triggerului |
Se executa sub domeniul de securitate ale userului Form Builder |
OBS: Nu confundati triggerii database cu triggerii Developer/2000 Form Builder
Dezactivarea sau reactivarea unui trigger database se face folosind sintaxa:
ALTER TRIGGER nume_trigger DISABLE | ENABLE
Dezactivarea sau reactivarea tuturor triggerilor pentru o tabela se realizeaza cu urmatoarea sintaxa:
ALTER TRIGGER nume_trigger DISABLE | ENABLE ALL TRIGGERS
Recompilarea unui trigger pentru o tabela:
ALTER TRIGGER nume_trigger COMPILE
Cand un trigger este creat este activat automat.
Pentru triggerii activati serverul Oracle verifica constrangerile de integritate si garanteaza ca triggerii nu pot sa compromita constrangerile de integritate. In plus serverul Oracle in permanenta citeste tabelele view pentru interogari si constrangerile, administreza dependentele si permite efectuarea in doua faze daca un declansator actualizeaza tabele indepartate intr-o baza de date distribuita.
Dezactiveaza un trigger specific prin folosirea sintaxei ALTER TRIGGER, sau dezactiveaza toti triggerii de pe o tabela prin folosirea sintaxei ALTER TABLE
Dezactiveaza un trigger pentru a imbunatati performata sau pentru a evita verificarile de integritate a datelor cand se incarca masiv cantitati de informatii, prin utilitati cum ar fi SQL *Loader. Se poate dezactiva triggerul cand face referire la un obiect din baza de date care nu este disponibil momentan, datorita unei erori de conectare la retea, distrugerea diskului sau alte erori.
Folositi o comanda ALTER TRIGGER pentru a recompila cu exactitate un trigger care este invalid. Cand folositi un ALTER TRIGGER cu optiunea RECOMPILE, triggerul este recompilat indiferent daca este valid sau nu.
Pentru stergerea unui database trigger folosim sintaxa DROP TRIGGER:
DROP TRIGGER nume_trigger
Situatii de testare a triggerilor:
Testati fiecare din operatiile de declansare cat si operatiile de nedeclansare.
Testati fiecare conditie a clauzei WHEN.
Faceti ca triggerul sa actioneze direct dintr-o operatie de baza cu date, cat si indirect dintr-o procedura.
Testati efectul triggerului asupra altor triggeri.
Testati efectul altor triggeri asupra triggerului
Asigurati-va ca triggerul actioneaza corect prin testarea separata a mai multor cazuri. Profitati de procedurile DBMS_OUTPUT pentru depanarea triggerilor.
Se executa toti triggerii BEFORE Statement.
Saltul pentru randul afectat
a) Se executa toti triggerii BEFORE Row.
b) Se executa instructiunea DML si se efectueaza verificarea constrangerii de integritate.
c) Se executa toti triggerii AFTER Row.
Se termina verificarea constrangerii de integritate aminate.
Se executa toti triggerii AFTER Statement.
O singura instructiune DML poate declansa pana la patru tipuri de triggeri: triggerii Statement si Row, BEFORE si AFTER. Un eveniment declansator sau o instructiune din trigger poate declansa verificarea uneia sau mai multor constrangeri de integritate. Triggerii pot de asemenea declansa alti triggeri. Toate actiunile si verificarile efectuate ca rezultat al unei instructiuni SQL trebuie sa reuseasca. Daca apare o exceptie in trigger si aceasta nu este solutionata corect, toate actiunile efectuate datorita instructiunii SQL originale sunt restaurate, inclusiv actiunile efectuate de triggerii declansati deja. Aceasta garanteaza ca constrangerile de integritate nu pot fi niciodata compromise de triggeri. Cand un trigger este declansat, tabelele la care s-a facut referire in actiunea triggerului pot cauza modificari de catre alte tranzactii ale utilizatorilor. In toate cazurile se asigura o permanenta vizualizare pentru valorile modificate pe care trebuie sa le citeasca sau sa le scrie.
Citirea si scrierea informatiilor folosind triggerii este supusa anumitor reguli:
Regula 1: Nu schimba informatiile din coloanele ce refera cheia primara, cheia externa sau cheia unica a unei tabele.
Regula 2: Nu citeste informatiile dintr-o tabela care se schimba.
Exemplu: Schimbarea datelor intr-o tabela cu constrangeri.
SQL> UPDATE dept
2 SET empno = 1
3 WHERE empno=30;
O tabela cu constrangeri este o tabela pe care evenimentul declansator ar putea fi obligat sa o citeasca fie direct printr-o instructiune SQL sau indirect, printr-o constrangere de integritate referentiala declarativa.
In exemplu de mai sus s-a incercat schimbarea datelor intr-o tabela cu constrangeri. Cand valoarea in campul DEPTNO s-a modificat din tabela parinte DEPT, incercarea de a actualiza in cascada randurile corespunzatare din tabela copil EMP produce o eroare runtime error.
Exemplu: Acest trigger incearca sa actualizeze in cascada cheia parinte din DEPT referita de o cheie externa pentru inregistrarea copil din tabela EMP.
SQL> CREATE OR REPLACE TRIGGER cascade_updates
2 AFTER UPDATE OF deptno ON DEPT
3 FOR EACH ROW
4 BEGIN
5 UPDATE emp
6 SET emp.deptno = :new.deptno
7 WHERE emp.deptno= :old.deptno;
8 END;
9 /
SQL> UPDATE dept
2 SET deptno = 1
3 WHERE deptno = 30;
*
ERROR at line 1:
ORA-04091: table DEPT is mutating, trigger/function
May not see it
Apare o eroare cand userul incearca sa modifice tabela DEPT. Tabela declansata DEPT, refera tabela EMP printr-o constrangere FOREIGN KEY. De aceea, se spune ca EMP este o tabela de constrangere. Triggerul CASCADE_UPDATES incearca sa schimbe informatii din tabela de constrangere ceea ce nu este permis.
Exemplu: Citirea informatiilor dintr-o tabela modificabila.
SQL> UPDATE emp
2 SET sal = 1500
3 WHERE ename = 'SMITH'
O tabela modificabila este o tabela care este modificata de o instructiune UPDATE, DELETE sau INSERT, sau o tabela care ar putea fi actualizata de catre efectele unei actiuni de integritate referentiala DELETE CASCADE declarativa. O tabela nu este considerata ca fiind modificabila pentru triggerii Statement. Tabela declansata este ea insasi o tabela modificabila, cat si orice alta tabela ce face referire la ea prin constrangerea de integritate FOREIGN KEY.
Exemplu: Trigger-ul CHECK_SAL asigura ca oricand este adaugat un nou angajat pe tabela EMP sau este schimbat salariul sau functia unui anagajat existent, salariul angajatului se incadreaza in gama de salarii stabilita pentru slujba angajatului.
SQL> CREATE OR REPLACE TRIGGER check_sal
2 BEFORE INSERT OR UPDATE OF sal, job ON emp
3 FOR EACH ROW
4 WHEN (new.job <> 'PRESIDENT')
5 DECLARE
6 v_minsalary emp.sal%TYPE;
7 v_maxsalary emp.sal%TYPE;
8 BEGIN
9 SELECT MIN(sal), MAX(sal)
10 INTO v_minsalary, v_maxsalary
11 FROM emp
12 WHERE job = :new.job;
13 IF :new.sal < v_minsalary OR
14 :new.sal > v_maxsalary THEN
15 RAISE_APPLICATION_ERROR (-20505,
16 'Salar in afara domeniului');
17 END IF;
18 END;
19 /
Implementarea triggerilor asigura imbunatatirea urmatoarelor caracteristici:
Securitatea datelor.
Verificarea datelor.
Integritatea datelor.
Integritatea referentiala.
Replicarea tabelelor.
Informatii derivate.
Urmarirea evenimentelor.
Folosim triggeri pentru a spori calitati care nu pot fi implementate de catre serverul Oracle.
Exemplu: Controlul securitatii pe server
SQL> GRANT SELECT, INSERT, UPDATE, DELETE
2 ON emp
3 TO CLERK; --database role
SQL> GRANT CLERK TO SCOTT;
Se folosesc scheme si roluri in Oracle pentru a controla securitatea operatiilor de date pe tabele conform cu identitatea utilizatorului.
Se stabilesc privilegii asupra utilizatorului cand utilizatorul se conecteaza la o baza de date. Se determina accesul la tabela, tabele view, sinonime si secvente si deasemenea se determina privilegiile de definire a datelor.
Exemplu: Controlarea securitatii folosind un trigger database
SQL> CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT OR UPDATE OR DELETE ON emp
3 DECLARE
4 v_dummy VARCHAR2(1);
5 BEGIN
6 IF TO_CHAR(sysdate, 'DY' IN ('SAT','SUN'))
7 THEN RAISE_APPLICATION_ERROR(-20506,
8 'Puteti schimba date doar in timpul normal de lucru.');
9 END IF;
10 SELECT COUNT(*) INTO v_dummy FROM holiday
11 WHERE holiday_date = TRUNC(sysdate);
12 IF v_dummy>0 THEN RAISE_APPLICATION_ERROR(-20507,
13 'Nu puteti schimba date in vacanta.');
14 END IF;
15 END;
16 /
Folosim triggerii pentru a rezolva cerintele de securitate mai complexe.
Stabilim privilegii pentru valorile din baza de date, cum ar fi: ora din zi , ziua din saptamana, etc.
Determinam accesul doar la tabele.
Determinam privilegiile manipularii datelor.
Exemplu: Verificarea operatiilor folosind facilitatea serverului.
SQL> AUDIT INSERT, UPDATE, DELETE
2 ON emp
3 BY ACCESS
4 WHENEVER SUCCESSFUL;
Operatiile de date verificate de serverul Oracle8 sunt:
Verificarea refacerilor de informatii, verificarea manipularii datelor si verificarea instructiunilor de definire a datelor.
Scrie ultima verificare intr-o tabela centralizata de verificare.
Genereaza inregistrarile de verificare o data pe sesiune sau o data pe incercarea de acces.
Captureaza incercarile reusite, pe cele nereusite sau pe amandoua.
Activeaza sau dezactiveaza in mod dinamic.
Exemplu: Verificarea operatiilor folosind un trigger.
SQL> CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 IF audit_emp_package.g_reason IS NULL THEN
6 RAISE_APPLICATION_ERROR(-20059, 'Specificati un motiv
7 pentru operatiile cu date cu procedura
8 SET_REASON inainte de lucru cu date.');
9 ELSE
10 INSERT INTO audit_emp_values (user_name, timestamp, id,
11 old_last_name, new_last_name, old_title, new_title,
12 old_salary, new_salary, comments)
13 VALUES (user, sysdate, :old.empno, :old.ename,
14 :new.ename, :old.job, :new.job, :old_sal,
15 :new.sal, :audit_emp_package.g_reason);
16 END IF;
17 END;
18 /
SQL> CREATE TRIGGER clean_audit_emp
2 AFTER INSERT OR UPDATE OR DELETE ON emp
3 BEGIN
4 audit_emp_package.g_reason := NULL;
5 END;
6 /
Valorile de date actuale verificate cu triggeri sunt:
Doar instructiunile de manipulare a datelor specificate in trigger.
Genereaza inregistrari de verificare pentru fiecare instructiune sau pentru fiecare rand.
Captureaza doar incercari reusite
Activeaza si dezactiveaza dinamic.
Exemplu: Protejarea integritatii datelor folosind un trigger.
In exemplu urmator s-a asigurat ca salariul nu descreste si nici nu creste niciodata cu mai mult de 10% o data.
SQL> CREATE OR REPLACE TRIGGER check_sal
2 BEFORE UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN (new.sal < old.sal) OR
5 (new.sal > old.sal * 1.1)
6 BEGIN
7 RAISE_APPLICATION_ERROR(-20508,
8 'Nu descresteti salariul nici nu-l mariti cu mai mult de 10% ');
9 END;
Exemplu: Protejarea integritatii referentiale folosind un trigger.
SQL> CREATE TRIGGER cascade_updates
2 AFTER UPDATE OF deptno ON dept
3 FOR EACH ROW
4 BEGIN
5 UPDATE emp
6 SET emp.deptno = :new.deptno
7 WHERE emp.deptno = :old.deptno;
8 END;
9 /
In exemplu de mai sus s-a intarit integritatea referentiala cu un trigger. Cind valoarea campului DEPTNO se schimba in tabela parinte DEPT, se declanseaza actualizarea in cascada a randurilor corespunzatoare din tabela EMP. Functioneaza doar daca nu exista nici o alta integritate referentiala intre DEPT si EMP in definitiile tabelei.
Triggeri se folosesc pentru a implementa integritatea referentiala nonstandard cum ar fi:
Declansarea in cascada a actualizarilor.
Punerea pe NULL a actualizarilor si stergerilor.
Punerea pe o valoare implicita a actualizarilor si stergerilor.
Intarirea datelor referentiale intr-un sistem distribuit.
Activarea si dezactivarea dinamica.
Incorporarea constrangerilor de integritate referentiala in definitia unei tabele pentru a preveni inconsistenta datelor.
Exemplu: Calcularea valorilor derivate folosind un trigger.
SQL> CREATE OR REPLACE PROCEDURE increment_salary
2 (v_id IN dept.deptno%TYPE,
3 v_salary IN dept.total_salary%TYPE)
4 IS
5 BEGIN
6 UPDATE dept
7 SET total_sal = NVL (total_sal,0) + v_salary
8 WHERE deptno = v_id;
9 END increment_salary;
10 /
SQL> CREATE OR REPLACE TRIGGER compute_salary
2 AFTER INSERT OR UPDATE OF sal OR DELETE ON emp
3 FOR EACH ROW
4 BEGIN
5 IF DELETING THEN increment_salary(:old.deptno, -1*:old.sal);
6 ELSIF UPDATING THEN increment_salary(:new.deptno,
7 :new.sal-:old.sal);
8 ELSE increment_salary(:new.deptno, :new.sal);
9 END IF;
10 END;
11 /
In exemplu de mai sus se continua totalizarea salariului pentru fiecare departament in cadrul coloanei speciale TOTAL_SALARY din tabela DEPT.
Exemplu: Inregistrarea evenimentelor utilizand un trigger.
SQL> CREATE OR REPLACE TRIGGER notify_reorder_rep
2 AFTER UPDATE OF amount_in_stock, reorder_point ON inventory
3 FOR EACH ROW
4 WHEN new.amount_in_stock <= new.reorder_point
5 DECLARE
6 v_descrip product.descrip%TYPE;
7 v_msg_text VARCHAR2(2000);
8 BEGIN
9 SELECT descrip INTO v_descrip
10 FROM PRODUCT WHERE prodid = :new.product_id;
11 v_msg_text := 'Mi s-a adus la cunostinta ca datorita tranzactiilor din ultima vreme'
12 CHR(10) || 'inventarul nostru pentru produsul # '|| TO_CHAR(:new.product_id) || '--'
13 || v_name || 'a cazut' || CHR(10) || CHR(10) ||
14 'Al dumnevoastra,' || CHR(10) || user || '.';
15 dbms_mail.send('Inventory', user, null, null,'Low Inventory', null, v_msg_text);
16 END;
In interiorul serverului, inregistrarea evenimentelor are loc prin interogarea datelor si efectuarea unor operatii manuale, cu scopul de a trimite mesaje electronice cand inventarul pentru un anumit produs a cazut sub limita acceptabila. Acest trigger foloseste package-ul din Oracle DBMS_MAIL pentru a trimite mesalul electronic (e-mail).
Beneficiile declansatorilor sunt:
Imbunatatirea securitatii datelor.
Verificari ale securitatii bazata pe valori.
Imbunatatirea integritatii datelor.
Imbunatateste constrangerile de integritate dinamice ale datelor.
Intareste constrangerile complexe de integritate referentiala.
Se asigura ca operatiile legate sunt efectuate in mod implicit impreuna.
Folosind tabelele create in lucrarea 1 si anume DEPT, EMP si SALGRADE sa se scrie folosind limbajul PL/SQL urmatorii triggeri:
a) Sa se scrie un trigger care nu va permite operatii de modificare si stergere pe tabela EMP.
b) Sa se scrie un trigger care nu va permite inserarea pe tabele EMP in zilele de Sambata si Duminica.
c) Sa se dezactiveze triggeri definiti mai sus dupa testarea acestora.
Copyright © 2024 - Toate drepturile rezervate
