Triggerele

Triggerele

Trigger-ul reprezinta o functionalitate prezenta in mai multe versiuni SQL care executa un bloc de cod procedural asupra bazei de date atunci cand intr-o tabela are loc un anumit eveniment. Desi a fost propus ca mecanismul de trigger sa fie inclus in standardul SQL3 ANSI-ISO actual, nici un produs nu 1-a implementat in totalitate, datorita complexitatii.

Sintaxa care sta la baza triggerelor este urmatoarea:

CREATE TRIGGER <nume>

ON <tabela> [ORDER <valoare>]

REFERENCING  AS <nume>

(WHEN  (<conditie_de_cautare_SQL) <procedura__SQL3>

[FOR EACH  ] ) ;

Unde: For Each - precizeaza la cine se aplica trigger-u!; Row - linia; Statement - interogare

Un trigger asociat unei tabele confera dinamism bazei de date deoarece lansarea in executie a programelor se face in functie de anumite evenimente.

Este vorba de modificarea bazei de date prin INSERT, UPDATE sau DELETE. in contextul obiect, triggerele ajuta la programarea restrangerilor de integritate a datelor. Astfel, triggerul se compune din doua parti: una care descrie natura evenimentului si alta care descrie prelucrarile de efectuat.

Un trigger poate fi creat sau modificat (CREATE OR REPLACE TRIGGER), suprimat (DROP T1GGER), activat sau dezactivat (ALTER TRIGGER cu clauzele ENABLED sau DISABLED).

Triggerele pot avea acces la valorile vechi si noi ale atributelor din inregistrarea afectata de evenimentul declansator. Pentru a specifica accesul este necesara precizarea printr-o metoda simpla :new sau :old. Desigur, specificarea ambelor metode va fi acceptata doar in cazul unui UPDATE; pentru INSERT e valabil doar :NEW, iar pentru DELETE doar :OLD.

In practica se recomanda inlocuirea acestor metode cu nume utilizator introduse prin clauza REFERENCING AS.

Triggerele pot fi programate a se declansa inainte (BEFORE) sau dupa (AFTER) o actiune de tip INSERT, UPDATE, DELETE.

In exemplele care urmeaza a fost utilizata sintaxa SQL implementata in SGBD Oracle 8.0.

Exemple:

declararea tipurilor si tabelelor :

CREATE OR REPLACE TYPE tip_sala AS OBJECT

(cod CHAR(2), denumire CHAR(15), nr_locuri NUMBER);

CREATE OR REPLACE TYPE tip_pl AS OBJECT

(serie CHAR(10), tip_post CHAR(10), amplasament CHAR(2));

CREATE TABLE sala OF tip_sala

(CONSTRAINT pk_sala PRIMARY KEY(cod));

CREATE TABLE post_lucru OF tip_pl

(CONSTRAINT pk_pl PRIMARY KEY(serie),

CONSTRAINT fk_psala FOREIGN KEY(amplasament) REFERENCES sala(cod));

presupunem urmatoarea stare a tabelelor:

SQL > SELECT * FROM sala;

SQL > SELECT p.serie, p.tip_posf, p.amplasament 'Sala' FROM post_lucru p; 

Trigger care controleaza adaugarea de noi posturi de lucru. El va incrementa valoarea coloanei nr_locuri din tabela Sala dupa inserarea unui post de lucru intr-o sala:

CREATE OR REPLACE TRIGGER T_I_pl

AFTER INSERT ON post_lucru

FOR EACH ROW

BEG1N

UPDATE sala SET nr_locuri = nr_locuri + 1

WHERE cod = :NEW.amplasament;

END;

• Trigger care controleaza integritatea in cazul disparitiei unui post de lucru. El va decrementa valoarea coloanei nr_locuri din tabela Sala dupa suprimarea unui post de lucru dintr-o sala.

CREATE OR REPLACE TRIGGER T_D_pl

AFTER DELETE ON post_lucru

FOR EACH ROW

BEGIN

UPDATE sala SET nr_locuri = nr_locuri -1

WHERE cod = :OLD,amplasament;

END;

Trigger care actualizeaza intr-o maniera coerenta coloana nr_locuri din tabela Sala dupa mutarea unui post de lucru dintr-o sala in alta. Acest trigger va combina codul celor precedente, astfel:

CREATE OR REPLACE TRIGGER T_U_pl

AFTER UPDATE OF amplasament ON post_lucru

FOR EACH ROW

BEGIN

UPDATE sala SET nr_locuri = nr_locuri -1

WHERE cod = :OLD.amplasament;

UPDATE sala SET nr_locuri = nr_locuri +1

WHERE cod = :NEW.amplasament;

END;

Pe o tabela se pot defini mai multe triggere. Acestea se pot asambla intr-un grup de triggere. Pentru a le putea trata functie de evenimentul care declanseaza mecanismul este nevoie de utilizarea clauzelor INSERTING, UPDATING si DELETING.

Exemplu de grup de triggere:

CREATE OR REPLACE TRIGGER T_IDU_pl

AFTER INSERT OR DELETE OR UPDATE

OF amplasament ON post_lucru

FOR EACH ROW

BEGIN

IF(INSERTING) THEN

UPDATE sala SET nr_locuri = nr_locuri + 1

WHERE cod = :NEW, amplasament;

END IF;

IF(DELETING)THEN

UPDATE sala SET nr_locuri = nr_locuri -1

WHERE cod = :OLD. amplasament;

END IF;

IF(UPDATING('amplasament')) THEN

UPDATE sala SET nr_locuri = nr_locuri -1

WHERE cod = :OLD.amplasament;

UPDATE sala SET nr_locuri = nr_locuri + 1

WHERE cod = :NEW.amplasamerit;

END IF;

END;

Trigger care controleaza integritatea temporala

Fie tabela Caddid(Id, NumePren, SalariuIncadrare). Se construieste un Trigger care sa controleze ca noul salariu sa nu fie mai mic decat vechiul salariu.

CREATE TRIGGER SalariuCrescator

BEFORE UPDATE OF SalariuIncadrare On Caddid

REFERENCING OLD AS V New As N

(WHEN V.salariuIncadrare>N.SalariuIncadrare Signal.SQLState('Salariul nu poate sa scada') FOR EACH RQW);

Triggere asociate View-urilor

Aceste triggere permit inserarea, modificarea si suprimarea inregistrarilor dintr-un view de tip relational sau obiect-relational multitabela. Ele pot fi de tipul INSTEAD OF INSERT, INSTEAD OF UPDATE sau INSTEAD OF DELETE.

Principiu. In loc sa se insereze, sa se modifice sau sa se stearga una sau mai multe inregistrari dintr-un view, se programeaza actiunile (instructiuni SQL asociate eventual la structuri de control fundamentale).

Scopul este de a supraveghea actualizarile unui view si de a le repercuta corect la nivelul tabelelor sursa. Daca ne gandim la baze de date distribuite, cand tabelele pot fi situate la distanta, pe situri separate care, fiecare, poseda o legatura cu baza de date ne dam seama ce avantaj prezinta faptul ca un view bazat pe tabelele respective poate fi definit si manipulat pe un alt site.

Exemplu: Fie tabelele Studenti (nr_matr, Nume, Pren, Grupa ..) si Licenta (nrmatr, tema_lucrare, indrumator) cu urmatoarea stare:

STUDENTI

LICENTA

Si view-ul L_S creat pe baza acestor tabele, astfel:

SQL > CREATE VIEW L_S AS

SELECT s.nr_matr, s.nume, s.pren, s.grupa, l.tema, l.indrumator

FROM studenti s, licenta l

WHERE s.nr_matr=l.nrmatr;

Executand aceasta interogare se obtine urmatoarea tabela virtuala:

Sa se defineasca un trigger care prin intermediul clauzei INSTEAD OF INSERT sa gestioneze (via tabela virtuala) inserarea corecta de inregistrari in cele doua tabele sursa (tabela Studenti si tabela Licenta),

CREATE TRIGGER I_L_S

INSTEAD OF INSERT ON L_S

BEGIN

INSERT INTO STUDENTI

VALUES(:NEW.nr_matr, :NEW.nume, :NEW.pren, :NEW.grupa);

INSERT INTO LICENTA

VALUES(:NEW.nr_matr, :NEW,nume, :tema, :NEW.indrumator);

END;

SQL3/PSM (Persistent Store Modules) reprezinta un limbaj de programare de module persistente si stocate in baze de date.