Diferente intre proceduri si declansatoare consta in aceea ca o procedura este executata explicit dintr-un alt bloc printr-un apel care ii furnizeaza parametri in timp ce un declansator este executat implicit oridecate ori apare evenimentul pentru care a fost definit (INSERT, UPDATE, DELETE) si nu accepta argumante.
Pentru ce se utilizeaza declansatoarele?
pentrua mentine constrangeri de integritate complexe sau reguli de
afacere
pentru auditarea informatiilor intr-un tabel prin
inregistrarea modificarilor
pentru activarea automata a altor programe care trebuie sa
ruleze la efectuarea anumitor modificari in tabel
pentru colectarea si intretinerea datelor statistice.
CREATE [OR REPLACE] TRIGGER nume_declansator
{BEFORE | AFTER} {INSERT|UPDATE|DELETE} [OF coloana]
ON tabel_referit
[FOR EACH ROW [WHEN conditii]]
[DECLARE] -- optional, pentru declararea variabilelor locale
corpul_declansatorului
Denumirea declansatoarelor
Declansatoarele exista intr-un spatiu de nume( namespace) separat de proceduri, pachete, tabele (care partajeaza acelasi spatiu de nume), ceea ce inseamna ca un declansator poate avea acelasi nume cu un tabel sau procedura.
Tipuri de declansatoare
In Oracle exista doua tipuri de declansatoare:
- la nivel de inregistrare (row-level triggers)
- la nivel de declaratie (statement-level triggers)
Declansatoare la nivel de inregistrare pentru actiuni relativ
la date
se executa cate o data pentru fiecare rand dintr-o tranzactie.
sunt cele mai comune tipuri de declansatoare si sunt adesea
folosite in aplicatii de audit pentru date.
sunt identificate prin clauza FOR EACH ROW din comanda
CREATE TRIGGER .
Declansatoare la nivel de declaratie pentru actiuni relativ
la tranzactii
se executa o singura data pentru fiecare tranzactie De exemplu,
daca printr-o singura tranzactie se insereaza 500 de randuri
intr-un tabel atunci declansatorul pentru tabelul respectiv
se va executa o singura data.
se utilizeaza pentru a forta masuri suplimentare
de securitate asupra anumitor tipuri de tranzactii care se
pot executa asupra unui tabel.
sunt declansatoarecreate implicit - se omite clauza FOR
EACH ROW din comanda CREATE TRIGGER.
Tipuri de declansatoare valide (combinatii posibile de declansatoare)
Declaratii (INSERT, DELETE, UPDATE), Moment (BEFORE,
AFTER), Nivel (Row-level, Statement-level)
Valorile pentru cele trei caracteristici determina
tipul de declansator. Sunt posibile 12 astfel de tipuri: 3*2*2
= 12
Declansatoarele pentru comenzi multiple INSERT, UPDATE,
DELETE asupra unui tabel pot fi combinate intr-un singur declansator
, cu conditia ca toate sa corespunda aceluiasi nivel (row-level sau
statement-level) (ex. INSERT OR UPDATE OR DELETE).
nu se pot combina declansatoare corespunzatoare momentelor
diferite deoarece expresia BEFORE OR AFTER este ilegala.
Exemplu
CREATE OR REPLACE TRIGGER updateMajorStats
AFTER INSERT OR DELETE OR UPDATE
ON
students
-- Oracle va verifica starea tabelului
DECLARE
CURSOR c_statistics IS
SELECT major, COUNT(*) total_students, SUM(current_credits) total_credits
FROM students
GROUP BY major;
BEGIN
FOR v_statsRecord IN c_statistics LOOP
UPDATE major_stats
SET total_credits = v_statsRecord.total_credits,
total_students = v_statsRecord.total_students
WHERE major = v_statsRecord.major;
IF SQL%NOTFOUND THEN
INSERT INTO major_stats(major, total_credits, total_students)
VALUES(v_statsRecord.major,
v_statsRecord.total_credits, v_statsRecord.total_students);
END IF;
END LOOP;
END updateMajorStats;
Utilizarea pseudovariabilelor :old si :new - doar
in declansatoare la nivel de rand
:old se refera la valoarea care exista inaintea
tranzactiei. Actualizarea si stergerea pot face referire la
valorile :old.
valorile :new sunt valorile datelor create
in urma tranzactiei. (cum sunt valorile coloanelor dintr-o inregistrare
inserata).
:old este nedefinita pentru declaratii INSERT , :new
este nedefinita pentru comenzi DELETE.
daca este nevoie sa se stabileasca valoarea unei coloane prin intermediul
unui declansator, trebuie utilizat un declansator
BEFORE INSERT pentru a accesa valorile :new .
Utilizarea unui declansator AFTER INSERT nu va permite
setarea valorilor inserate deoarece randul a fost deja inserat
in tabel (si va fi :old).
Operatiile care sunt in mod normal valide asupra inregistrarilor
nu sunt valide pentru :new si :old.De exemplu.
acestora nu le poate fi asignata o inregistrare completa ---
v_tmprec := :old;--- nu este legala. Pot fi asignate acestora numai
campuri individuale din cadrul inregistrarii -
v_tmprec.col := :old.col;.
:old si :new nu pot fi transmise procedurilor
sau functiilor care iau argumentede tipul triggering_table%ROWTYPE,
deoarece parametrii actuali sunt asignati parametrilor formali in
acelasi mod ca o asignare standard.
Exemplu: declansatorul GenerateStudentID isi propune popularea campului ID din tabelul Students cu o valoare generata de secventa student_secv
CREATE OR REPLACE TRIGGER GenerateStudentID
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW
BEGIN
SELECT student_secv.nextval INTO :new.ID
FROM dual;
END GenerateStudentID;
Declansatorul modifica valoarea pentru :new.ID.Aceasta este una din caracteristicile de baza pentru :new -- cand comanda este executata, vor fi utilizate valorile din :new.
INSERT INTO students(first_name, last_name) VALUES('Lolita', 'Lazarus');
Se poate executa o comanda INSERT fara a genera erori. Nu s-a specificat o valoare pentru coloana cheie primara ID, dar declansatorul o va furniza. => daca se specifica o valoare pentru ID, aceasta va fi ignorata deoarece declansatorul o va modifica.
Clauza WHEN
Clauza WHEN este valida doar pentru declansatoarele la nivel de rand. Daca aceasta este prezenta, corpul declansatorului va fi executat doar pentru acele randuri care corespund conditiei specificate in clauza.
Exemplu, se presupune ca se doreste monitorizarea oricaror
ajustari asupra unor Sume mai mari de 10 procente.
CREATE TRIGGER customer_bef_upd_row
BEFORE UPDATE
ON customer
FOR EACH ROW
WHEN (:new.sume/:old.sume > 1.1)
--se observa ca nu exista semnul :pentru new si
old
-- nu este o eroare.
BEGIN
INSERT INTO customer_audit
-- tabel diferit
VALUES (:old.Action_date, :old.Action, :old.Item, :old.qty, :old.qtyType,
:old.rate, :old.sume)
-- semnul : exista in corpul declansatorului
END;
declansatorul din exemplu va fi executat numai daca noua
valoare din campul Sume va fi cu mai mult de 10 procente mai
mare decat vechea valoare.
clauza WHEN adauga criterii suplimentare conditiilor declansatorului.
Evenimentul declansator nu este numai o actualizare ( UPDATE)
a tabelului customer ,ci el trebuie sa reflecte de asemenea
o cresrtere de peste 10 procente a valorii din coloana Sume
comenzile din corpul declansatorului ( intre BEGIN si
END) vor fi executate pentru fiecare UPDATE asupra randurilor
din tabelul customer care trec de conditia impusa in
WHEN .
Utilizarea predicatelor de declansare: INSERTING, UPDATING si
DELETING
Se pot utiliza trei functii booleene pentru a determina
ce operatie se efectueaza (deoarece un declansator se poate
adresa mai multor evenimente, uneori este necesara determinarea carei
operatii se executa in momentul curent).
CREATE TRIGGER customer_bef_upd_ins_row
BEFORE INSERT OR UPDATE OF Amount ON CUSTOMER
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO customer_audit
VALUES(:new.Action_date, :new.Action, :new.Item, :new.qty, :new.qtyType,
:new.rate, :new.Amount);
ELSE
-- daca nu se insereaza atunci se actualizeaza campul
Amount
INSERT INTO customer_audit
VALUES(:old.Action_date, :old.Action, :old.Item, :old.qty, :old.qtyType,
:old.rate, :old.Amount);
END IF;
END;
Elementele din dictionarul de date corespunzatoare declansatoarelor
USER_TRIGGERS
Declansatoarele si dependentele
Un declansator areun spatiu separat de nume fata de pachete, functii sauproceduri,ceea ce inseamna ca nu se genereaza conflicte de nume cu procedurile. Deoarece un declansator este stocat in format sursa el nu depinde de nici un alt obiect.
Comenzi corespunzatoare declansatoarelor
DROP TRIGGER nume_decl
ALTER TRIGGER nume_decl [{DISABLE | ENABLE }]
ALTER TABLE nume_tabel [{DISABLE | ENABLE } [ALL TRIGGERS]]