Adatbázis Rendszerek II. 6. PLSQL Triggerek
B IT M A N
32/1
v: 2016.03.04 B IT MAN
Passzív adatbázisok negatívumai Példa: VIR rendszer
– egyik adatbázis összegyűjti a termelési adatokat, egy másik erre épül adatbázis a jelentésekhez, elemzésekhez összesíti a termelési adatokat – Feladat: adatátemelést kell végezni az első adatbázisból a másikba, ha az módosul Passzív rendszer esetén: – egy operátor figyeli az operatív DB-t, szükség esetén manuálisan elindítja az átemelést végző programot Megoldás: – az operatív adatbázist kezel alkalmazásokba beépítünk olyan modult, amely az operatív adatbázis módosulása után az átemelést végrehajtja 32/2
B IT M A N
Aktív DBMS elemek Triggerek JOB-ok
Előnyök:
– Az adatbázisban, egy példányban tárolható kód – Új lehetőségek: • Állapot figyelés • Események által kiváltott műveletek • Időzítés, naplózás
32/3
B IT M A N
Követelmények Az aktív adatbázisnak biztosítania kell, magában kell
foglalnia a passzív adatbázisok minden szolgáltatását Ki kell dolgozni egy kezelő felületet, nyelvet, mellyel megadhatók, kezelhetők és monitorozhatók az aktív adatbázis elemek Az adatbázis kezelők belső motorját ki kell bővíteni az aktív elemek hatékony végrehajtását biztosító rutinokkal Az alkalmazás fejlesztő eszközöket is ki kell bővíteni az aktív elemek specifikálására szolgáló elemekkel
32/4
B IT M A N
ECA (Event, Condition, Action) modell E
Szabály
Kiváltó esemény
A Válasz tevékenység
C Feltétel
Update dolgozó set fiz = 34
Update dolgozó set kat = 2
fiz > 30 32/5
B IT M A N
ECA modell Events – Események: Műveletsor, melynek
bekövetkezését figyeli a rendszer – Adatkezelő utasítások (DML): • INSERT, DELETE, UPDATE – Adatlekérdező utasítások (DQL): • SELECT – Időfigyelés: • Megadott időpontban aktivizálódik a tevékenység – Összetett események: • and, or, not • Műveletsorok – Esemény előtt, után, vagy helyett 32/6
B IT M A N
ECA modell Condition – Feltétel: logikai kifejezés, melynek teljesülni
kell a választevékenység elindításához: – Adatbázis feltétel: where – Alkalmazás feltétel: az alkalmazásban tetszőlegesen megfogalmazható feltétel
32/7
B IT M A N
ECA modell Action – Akció (választevékenység):
– adatbázis műveletsor • adatbázis-kezelő műveleteket tartalmaz a válasz – alkalmazás modul • egy alkalmazás modul meghívását jelenti a válasz – összetett választevékenységeket is írhatunk
32/8
B IT M A N
Trigger (kioldó, előidéző) E
TRIGGER
DML művelet C
A
PL/SQL blokk végrehajtása
Feltétel Update dolgozó set fiz = 34
Insert into napló Values(…)
Művelet után 32/9
B IT M A N
Trigger
Alkalmazás SQL> UPDATE DOLGOZÓ SET bér= 2850 Where Dkód=7698
Dolgozó tábla DKÓD NÉV 7838 Király 7698 Kiss 7369 Kovi 7788 Nagy
Munkakör Elnök Titkárnő Rendező Elemző
Bér 5000 2850 8000 3000
Naplózó trigger Insert into NAPLO Values(sysdate, user... 32/10
B IT M A N
Trigger Tökéletesen megvalósítja az ECA elvet:
– Ha egy megadott tevékenység és feltétel bekövetkezik, akkor végrehajtódik a választevékenység. – Segítségével az figyelhető, hogy végrehajtásra kerüle valamilyen kijelölt adatkezelő művelet Felhasználás: – Származtatott értékek kiszámítására – Érvénytelen adatmanipulációk kiszűrésére – Működési korlátozások biztosítására – Naplózásra – Statisztika-gyűjtésre 32/11
B IT M A N
Oracle triggerek A triggert kiváltó esemény helye szerint lehetnek:
– Alkalmazástriggerek • Be-kijelentkezés • DDL utasítás (create, alter, drop) • DML utasítás (delete, insert, update) – Adatbázistriggerek • Rendszeresemények (startup, shutdown, servererror…) Oracleben az alap trigger egy egyszerű alkalmazástrigger, mely
DML műveletekre aktivizálódik
32/12
B IT M A N
Trigger létrehozása (egyszerű DML trigger!) create or replace trigger triggernév időzítés kiváltó_ok akció_blokk; Időzítés:
– Befor – After – Instead of (helyett) Kiváltó okok: DML műveletek
– – – –
Insert Update Delete A fenti három tetszőleges kombinációja
Trigger hatásmechanizmusok:
32/13
– Egyszer fut le a trigger egy DML művelet előtt vagy után (alapértelmezés, műveleti trigger) – Minden egyes érintett rekord esetén lefut a trigger, a művelet IT előtt, vagy után (sor szintű trigger)
B
MAN
Trigger hatásmechanizmus
Művelet előtti trigger
Részleg tábla RKód Rnév 10 20 30 40
Tervezés Gyártás Eladás Eladás
Hely Mc. Mc. Mc. Eger
Sor előtti trigger Sor utáni trigger Sor előtti trigger Sor utáni trigger Sor előtti trigger Sor utáni trigger Sor előtti trigger Sor utáni trigger
Művelet utáni trigger
32/14
B IT M A N
Trigger létrehozása create trigger triggernév BEFORE | AFTER INSERT OR UPDATE OR DELETE [ of oszlopok ] on táblanév [ FOR EACH ROW ] [ WHEN feltétel ] BEGIN PL/SQL blokk; END;
32/15
B IT M A N
Trigger – példa 1.
naplo datum
nev
adat
create trigger trg_1 after delete on auto begin insert into naplo values (sysdate, user, 'törlés'); end;
Kiváltó ok (elindulása): delete from auto where tip like 'opel%';
32/16
B IT M A N
Trigger – példa 2.
naplo datum
nev
adat
create trigger trg_2 after delete on auto for each row begin insert into naplo values (sysdate, user, 'törlés'); end;
Kiváltó ok (elindulása): delete from auto where tip like 'opel%';
32/17
B IT M A N
Trigger (deLuxe) create or replace trigger reszveny_figyelo before insert or update or delete on reszvenyek begin Időpont figyelése if (to_char (sysdate,'dy') in ('sat','sun')) or (to_char (sysdate, 'hh24') not between '08' and '18') then if deleting then Művelet figyelése raise_application_error (-20502, 'Csak munkaidőben törölhet a részvények táblából.'); elsif inserting then raise_application_error (-20500, 'Csak munkaidőben vihet fel adatot részvények táblába.'); else raise_application_error (-20504, 'Csak munkaidőben módosíthat a részvények táblában.'); end if; end if; end; 32/18
B IT M A N
Trigger (deLuxe) Magyarázatok: TO_CHAR (sysdate,'DY') IN ('SAT','SUN') – Ha a dátum napja szombat vagy vasárnap, TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18') – Ha az időpontban az óra értéke nem esik 8 és 18 közé, Akkor a megkísérelt műveletnek megfelelő hibaüzenetet küld a rendszer. > Delete from részvények … delete from részvények
* Hiba a(z) 1. sorban: ORA-20502: Csak munkaidőben törölhet a részvények táblából.
32/19
B IT M A N
Trigger (deLuxe) Magyarázatok: RAISE_APPLICATION_ERROR(kód, 'üzenet'): eljárás, csak alprogramokból hívható, meghívásakor azonnal befejeződik az alprogram futása és a vezérlés visszatér a hívó programhoz a megfelelő hibakóddal, amelyet kivételként lekezelhetünk. A felhasználó által kiváltott, illetve definiált hibák kódjának intervalluma: -20000 .. -20999
32/20
B IT M A N
Módosítás előtti és utáni állapotok (1) Az egyes mezők művelet előtti és utáni állapotát jelölik:
– Művelet előtt: OLD.mezőnév – Művelet után: NEW.mezőnév Használati szabályok: Csak sor (rekord) szintű triggereknél használhatók! INSERT esetén csak NEW, UPDATE esetén OLD és NEW, DELETE esetén csak OLD használható. Módosítani csak a :NEW értékét lehet és csak BEFORE trigger esetén. 32/21
B IT M A N
Módosítás előtti és utáni állapotok (2) CREATE [OR REPLACE] TRIGGER trigger_név Kiváltó_esemény(ek) ON tábla_név [REFERENCING OLD AS régi | NEW AS új] FOR EACH ROW [WHEN feltétel] PL/SQL blokk;
Csak sor (rekord) szintű triggereknél használható. OLD és NEW az alapértelmezett nevek. REFERENCING csak akkor kell, ha más nevet akarunk. A WHEN részben OLD és NEW, a PL/SQL blokkban :OLD és :NEW használandó.
32/22
B IT M A N
Trigger példa
dolgozo kod
nev
fizetes
Trigger, amely figyelmeztet, ha egy dolgozó fizetése csökkent: create trigger fizetes_kontroll after update of fizetes on dolgozo for each row when (old.fizetes > new.fizetes) begin dbms_output.put_line (:old.nev || ' fizetése csökkent! ' || :old.fizetes || ' => ' || :new.fizetes); end;
32/23
B IT M A N
Naplózó trigger
naplo datum
nev
adat
Trigger, amely naplózza a változásokat: create trigger valtozas_naplozo after update on dolgozo for each row begin if :old.fizetes != :new.fizetes then insert into naplo values (sysdate, user, 'mf'||:old.fizetes||'-'||:new.fizetes); elseif :old.cim != :new.cim then insert into naplo values (sysdate, user, 'mc'||:old.cim||'-'||:new.cim); elseif :old.beoszt != :new.beoszt then insert into naplo values (sysdate, user, 'mb'||:old.beoszt||'-'||:new.beoszt); end if; end;
32/24
B IT M A N
Triggerek kezelése Engedélyezés, letiltás:
– ALTER TRIGGER triggernév ENABLE; – ALTER TRIGGER triggernév DESABLE; Listázás:
– Select * from user_triggers; Törlés:
– DROP TRIGGER triggernév;
32/25
B IT M A N
Automatikus kód beállítás triggerrel create sequence seq1; create table T4 (kod int, adat varchar2(40)); create trigger t4t1 before insert on t4 for each row begin :new.kod := seq1.nextval; end;
T4 kod
adat
declare a int; begin insert into t4 (adat) values ('Tulipán') returning kod into a; dbms_output.put_line('A rekord kódja: '||a); end;
32/26
B IT M A N
Hibás adatok törlése create trigger t3t1 after insert on t3 for each row begin if :new.kod > 10 then insert into t3temp values (:new.kod); end if; end;
T3 kod
adat
T3temp kod
create or replace trigger t3t2 after insert on t3 declare cursor cur is select * from t3temp; begin for c1 in cur loop delete from t3 where kod=c1.kod; end loop; delete from t3temp; end;
32/27
B IT M A N
Automatikus adatmódosítás create table t5 ( kod int, adat varchar2(6), constraint adat_ell check (adat in ('true', 'false')) );
T5 kod
adat
insert into t5 values (1, 'igaz'); create or replace trigger t5t1 before insert on t5 for each row begin if lower(:new.adat) in ('y','yes','1','t','true','igen') then :new.adat := 'true'; elsif lower(:new.adat) in ('n','no','0','f','false','nem') then :new.adat := 'false'; else dbms_output.put_line('Az adat true vagy false lehet!'); end if; end;
32/28
B IT M A N
Törlés több táblából
auto rsz … tkod
tulaj kod
nev
Egy tulajnak több autója lehet. Ha töröljük a tulajt, törlődjenek az autói. Ha törlünk egy autót, csak akkor töröljük a tulajt, ha nincs több autója. create or replace trigger torl_tulaj before delete on tulaj for each row
begin delete from auto where tkod = :old.kod; end; create or replace trigger torl_auto after delete on auto for each row
declare db int; begin select count(*) from auto where tkod = :old.tkod; if db = 0 then delete from tulaj where kod = :old.tkod; end if; end; 32/29
B IT M A N
Órai feladatok 1.
32/30
Hozza létre a Naplo5 nevű táblát, a következő szerkezettel: [Esemeny varchar(20), Adat varchar(100), Datum timestamp] Készítsen egy triggert (TB), mely naplózza a Vasarlo táblába történt beszúrásokat. Esemény: Beszúrás, Adat: VID+Név+Cím (összefűzve) Szúrjon be 2-3 rekordot a Vasarlo táblába, ellenőrizze a trigger működését, aztán törölje le a triggert. Készítsen egy triggert (TM), mely naplózza a Vasarlo táblában történt módosításokat. Esemény: Módosítás, Adat: A módosított adatok esetén a régi és az új értékek összefűzve, vesszővel és aláhúzás jellel elválasztva. Pl. Kiss Béla_Kiss Géza, Mc. Kő u 11_Mc. Hó u 11 Készítsen egy triggert (TT), mely naplózza a Vasarlo táblában történt törléseket. Esemény: Törlés, Adat: VID+törlést végző user neve
B IT M A N
Órai feladatok 2.
32/31
Készítsen egy tárolt eljárást (ModT), mely módosítja egy paraméterként megadott Tkod-ú termék árát egy paraméterként megadott értékre. Készítsen egy triggert (TArFigy), mely az ár módosításkor nem engedi az árat 20%-nál nagyobb mértékben megváltoztatni, ez esetben írja ki: Nem megengedett mértékű módosítás: régi ár-új ár, és nem változtat az eredeti értéken. (visszaírja az eredetit) Próbálja ki a trigger működését, aztán törölje a triggert.
B IT M A N
VÉGE 32/32
B IT M A N
