Triggeri pe baza de date
Sunt asociati cu o tabela, view, schema sau database
Sunt stocati īn baza de date
Se executa cānd are loc un eveniment. Tipuri de evenimente:
operatii LMD pe o tabela (insert/ update/ delete)
operatii LMD pe o tabela virtuala (view) cu clauza INSTEAD OF
operatii LDD (Create, Alter, Drop) la nivel de database sau schema
evenimente la nivelul schemei sau bazei de date (shutdown sau logon/off)
Se folosesc pentru gestionarea restrictiilor de integritate complexe, monitorizare, centralizarea operatiilor. Nu se recomanda construirea in exces a triggerilor.
Pentru fiecare trigger se stabileste:
comanda care-l declanseaza (insert| update| delete) - Un trigger poate fi declansat de mai multe comenzi
momentul de timp la care se declanseaza (before| after| instead of). Pentru view se utilizeaza instead of, actiunile DML vor fi inlocuite de corpul triggerului si vor fi afectate tabelele din care este construit view-ul.
nivelul (row| statement) - daca triggerul este la nivel de rānd se executa pentru fiecare rānd afectat de comenzile: insert| update| delete. Daca nu este afectat nici un rand triggerul nu se executa.
Dimensiunea unui trigger nu poate depasi 32 kb! Se poate include apelul unei proceduri in corpul triggerului pentru a micsora dimensiunea acestuia.
Pentru a vedea erorile la compilare:
SHOW ERRORS TRIGGER nume_trigger;
Sintaxa de creare a unui trigger:
CREATE [OR REPLACE] TRIGGER nume_trigger
[BEFORE| AFTER| INSTEAD OF]
[INSERT| [OR] | UPDATE [OF coloana,.]| [OR] | DELETE]
ON tabela
[FOR EACH ROW ]
[WHEN conditie]
corp_trigger
Corp_trigger poate fi un bloc PL/SQL (Begin.End) sau un apel de procedura. Procedura poate fi implementata in PL/SQL, C sau JAVA, iar apelul se realizeaza: CALL nume_proc (fara ; dupa numele sau!!!)
Exemplu:
--Se creeaza un trigger care se declanseaza īnaintea fiecarei operatii de inserare īn tabela PRODUSE.
CREATE OR REPLACE TRIGGER produse_trig
BEFORE INSERT ON produse
BEGIN
dbms_output.put_line('triggerul s-a executat');
END;
Clauzele INSERTING, UPDATING, DELETING
Exemplu:
Triggerul se declanseaza la operatiile de INSERT, DELETE sau UPDATE pe tabela Produse. In tabela TEMP_LOG se introduce tipul operatiei, utilizatorul care a executat-o, data curenta.
CREATE TABLE temp_log
(tip CHAR(1),
utilizator VARCHAR2(50),
data
DATE DEFAULT SYSDATE);
CREATE OR REPLACE TRIGGER produse_trig_log
BEFORE
INSERT or UPDATE or DELETE on produse
DECLARE
v_tip temp_log.tip%TYPE;
BEGIN
case
when INSERTING then v_tip :='I';
when UPDATING then v_tip:='U';
ELSE v_tip :='D';
END case;
INSERT INTO temp_log(tip, utilizator,
data) VALUES (v_tip, user, sysdate);
END;
/
--inserarea in tabela
insert into produse values (300,
'cafea', 'buc', 60);
--stergere
delete from produse where stoc>=300;
Triggeri la nivel de rand - FOR EACH ROW
In triggerul la nivel de rānd se poate accesa rāndul curent procesat folosind doua pseudo-records ( :old, :new). Tipul lor este:
nume_tabela_pe_care_actioneaza_triggerul%ROWTYPE
Valorile pentru :old si :new
|
Operatie |
Valoare pt Old |
Valoare pt New |
|
INSERT |
NULL |
valoarea noua, inserata |
|
UPDATE |
valoare veche, anterioara lui update |
valoare noua, modificata |
|
DELETE |
valoare veche, anterioara lui delete |
NULL |
(:OLD) nu este definit pentru INSERT si (:NEW) nu e definit pentru DELETE
Desi sintactic sunt tratate ca tip de data record, īn realitate ele nu sunt si operatiile de atribuire directa var_record:=:old ce sunt valide pentru record nu sunt valide pentru (:new) si (:old). Din acest motiv trebuie precizate exact campurile din pseudo-record :old.camp sau :new.camp.
Nu se recomanda realizarea de triggeri la nivel de rind care utilizeaza valori ale coloanelor din tabele si care sunt supuse actualizarilor prin comenzile DML ce declanseaza triggerul (mutating table). Va apare aceeasi eroare ca la functii (vezi seminarul 8).
Utilizarea clauzei WHEN pentru a conditiona executia unui trigger:
clauza [when conditie] este valida pentru triggerii la nivel de rānd
corpul triggerului va fi executat numai pentru acele rānduri ce indeplinesc conditia specificata
Exemple:
--Se creeaza un trigger pentru a nu se permite depasirea stocului maxim ce poate fi depozitat
CREATE OR REPLACE TRIGGER restrict_stoc
BEFORE
INSERT or UPDATE on produse
FOR EACH ROW
WHEN
(old.um='buc')
BEGIN
IF :new.stoc>9000 then
RAISE_APPLICATION_ERROR (-20202,
'Pentru un produs nu se poate depasi stocul de 9000');
end if;
END;
/
update produse
set stoc =9500
where codprodus=222;
--Se creeaza un trigger care asigura unicitatea codului produsului folosind valorile generate de o secventa
CREATE SEQUENCE produse_secv
START WITH 1
INCREMENT BY 1
MAXVALUE 100
NOCYCLE;
CREATE OR REPLACE TRIGGER generare_codprodus
BEFORE INSERT ON produse
FOR EACH ROW
BEGIN
SELECT produse_secv.nextval INTO :new.codprodus FROM dual;
END;
show errors;
--Trigger care sa actualizeze cod client si in COMENZI cand el e modificat in FIRME.
CREATE OR REPLACE TRIGGER inlocuieste_cod
BEFORE UPDATE OF codfirma ON firme
FOR EACH ROW
BEGIN
update comenzi
set codfirma = :new.codfirma
where codfirma = :old.codfirma;
END;
CREATE OR REPLACE TRIGGER sterge_cod
BEFORE delete ON comenzi
FOR EACH ROW
BEGIN
delete from rindcom where nrcom = :old.nrcom;
END;
/
Clauza INSTEAD OF
--crearea tabelei virtuale
create or replace view
clienti as
select f.codfirma, f.denfirma, f.loc, c.nrcom, c.data, a.codagent,
a.numeagent
from firme f, comenzi c, agenti a
where f.codfirma=c.codfirma
and c.codagent=a.codagent;
--crearea triggerului
create or replace trigger
exemplu_trigger
instead of insert or update or delete
on clienti
for each row
begin
if inserting then
insert into firme values
(:new.codfirma, :new.denfirma, :new.loc, null, null);
insert into agenti (codagent, numeagent) values (:new.codagent,
:new.numeagent);
insert into comenzi values (:new.nrcom, :new.codfirma,
:new.codagent, :new.data);
elsif deleting then
delete from comenzi where nrcom=:old.nrcom;
elsif updating ('denfirma') then
update firme
set denfirma=:new.denfirma
where codfirma=:old.codfirma;
elsif updating ('data') then
update comenzi
set data=:new.data
where nrcom=:old.nrcom;
end if;
end;
/
show errors;
--testare
trigger
insert into clienti values
(11,'zuzu','Bucuresti',82,'12-APR-04',3,'ionescu');
delete from clienti where denfirma='zuzu';
update clienti
set denfirma='cucu'
where loc='Bucuresti';
Comparatie intre triggeri si proceduri
|
Trigger |
Procedure |
|
codul sursa: USER_TRIGGERS |
codul sursa: USER_SOURCE |
|
Sunt declansati implicit de DML |
Sunt apelate in mod explicit |
|
Nu sunt permise comenzile: COMMIT, ROLLBACK, SAVEPOINT, insa pot contine un apel de procedura in care apar aceste comenzi, dar nu se recomanda |
Sunt permise comenzile: COMMIT, ROLLBACK, SAVEPOINT |
cānd un trigger e creat, codul sursa al triggerului este stocat īn dictionarul de date in user_triggers si se poate afisa:
SELECT trigger_type, trigger_name, triggering_event
FROM user_triggers
WHERE table_name='PRODUSE';
Gestiunea triggerilor:
Un trigger poate fi dezactivat
ALTER TRIGGER nume_trigger DISABLE|ENABLE;
Sau:
ALTER TABLE nume_tabela
DISABLE|ENABLE ALL TRIGGERS;
Exemplu:
ALTER TABLE produse
ENABLE ALL TRIGGERS;
Recompilarea unui trigger se realizeaza prin:
ALTER TRIGGER nume_trigger COMPILE;
Un trigger se sterge cu:
DROP TRIGGER nume_trigger;
Observatie: In momentul stergerii unei tabele se sterg automat toti triggerii asociati acelei tabele.
PL/SQL stocheaza triggerii īn forma compilata la fel ca procedurile, functiile si pachetele. Aceasta permite triggerilor sa fie apelati fara recompilare.
|
