Adatbázis Rendszerek II. 5. Gy: PLSQL Triggerek
B I T MAN
1/37
B Iv: T2015.03.09 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 2/37
B I T MAN
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
3/37
B I T MAN
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
4/37
B I T MAN
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
5/37
B I T MAN
ECA modell Events – Események: Műveletsor, melynek
6/37
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 B I T MAN
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
7/37
B I T MAN
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
8/37
B I T MAN
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
9/37
B I T MAN
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... 10/37
B I T MAN
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 11/37
B I T MAN
Trigger Általános formátuma:
CREATE TRIGGER triggernév kiváltó_ok akció_blokk; Kiváltó okok: DML műveletek
– – – –
INSERT UPDATE DELETE A fenti három tetszőleges kombinációja
Trigger hatásmechanizmusok:
– Egyszer fut le a trigger egy DML művelet előtt vagy után (alapértelmezés) – Minden egyes érintett rekord esetén lefut a trigger, a művelet előtt, vagy után 12/37
B I T 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
13/37
B I T MAN
Trigger Oracle-ben:
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;
14/37
B I T MAN
Trigger – példa 1. CREATE TRIGGER trg1 AFTER DELETE ON auto BEGIN INSERT INTO naplo VALUES ('törlés', sysdate, user); END;
Kiváltó ok (elindulása): DELETE FROM auto WHERE tip LIKE 'opel%';
15/37
B I T MAN
Trigger – példa 2. CREATE TRIGGER trg2 AFTER DELETE ON auto FOR EACH ROW BEGIN INSERT INTO naplo VALUES ('törlés', sysdate, user); END;
Kiváltó ok (elindulása): DELETE FROM auto WHERE tip LIKE ‘opel%';
16/37
B I T MAN
Trigger (deLuxe) CREATE OR REPLACE TRIGGER módosít BEFORE INSERT OR UPDATE OR DELETE ON részvények BEGIN IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18') THEN IF DELETING THEN 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; 17/37
B I T MAN
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. 18/37
B I T MAN
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
19/37
B I T MAN
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: 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.
20/37
B I T MAN
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 szintű triggerné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ó.
21/37
B I T MAN
Trigger példa 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; 22/37
B I T MAN
Trigger példa 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, ′MC′||:OLD.beoszt||′-′||:NEW.beoszt); END IF; END; 23/37
B I T MAN
Triggerek kezelése Engedélyezés, letiltás:
– ALTER TRIGGER triggernév ENABLE; – ALTER TRIGGER triggernév DISABLE; Listázás:
– Select * from user_triggers; Törlés:
– DROP TRIGGER triggernév;
24/37
B I T MAN
Autós példa 27. Műveleti szintű trigger készítése, mely az autó táblából
történt törléseket figyeli és naplózza! create table naplo (esemeny char(30), datum char(20), felhasznalo char(10)); EXECUTE
create or replace trigger t1 after delete on auto begin insert into naplo values('torles', to_char(sysdate, ’yyyymm-dd hh:mi:ss’), user); end; 25/37
B I T MAN
Autós példa 27. (kipróbálás) select * from auto;
delete from auto where tipus like ’skoda%’; select * from auto; select * from naplo;
drop trigger t1;
26/37
B I T MAN
Autós példa 28. Rekord szintű trigger készítése, mely az autó táblából
történt törléseket figyeli és naplózza! create or replace trigger t2 after delete on auto for each row begin insert into naplo values('torles', to_char(sysdate, ’yyyymm-dd hh:mi:ss’), user); end;
27/37
B I T MAN
Autós példa 29. Rekord szintű trigger készítése, mely az autó táblából
történt törléseket figyeli és naplózza. Adjuk meg feltételként, hogy a trigger csak a 15 évnél idősebb autók törlése esetén aktiválódjon! create or replace trigger t3 before delete on auto for each row when (old.kor>15) begin insert into naplo values('torles', to_char(sysdate, 'yyyy-mmdd hh:mi:ss'), user); end;
28/37
B I T MAN
Autós példa 30. Trigger készítése, mely az autó táblába történő rekord-
felvitelt és az ár mező módosítását figyeli. Felvitelkor ne engedjen 500 eFt-nál olcsóbb autót felvinni, módosításkor pedig a megengedett árnövekedés max. 10%. create or replace trigger ins before insert or update of ar on auto for each row begin if inserting and (:new.ar < 500000) then :new.ar := 500000; end if; if updating and (:new.ar > (:old.ar*1.1) ) then :new.ar := (:old.ar*1.1); end if; end; B I T MAN
29/37
Autós példa 30. (kipróbálás) select * from auto;
insert into auto values (’hkl876’, ’mazda’, ’fehér’, 15,
300000); select * from auto; update auto set ar=800000 where rsz like ’hkl876%’; select * from auto; drop trigger ins;
30/37
B I T MAN
Autós példa 31. Trigger készítése, mely figyeli az autó táblán történt
módosításokat és egy archiv nevű táblába elmenti módosításkor a régi és az új értékeket is. Az archiv tábla kód mezőjének automatikus sorszámosztással (sequence) adjunk értéket. create table archiv (kod number(3), datum date, rsz char(6), tipus char(10), szin char(10), kor number(2), ar number(8)); EXECUTE create sequence kod; EXECUTE
31/37
B I T MAN
Autós példa 31. (folytatás) create or replace trigger seq before update on auto for each row Kód növelése begin insert into archiv values (kod.nextval, sysdate, :old.rsz, :old.tipus, :old.szin, :old.kor, :old.ar); insert into archiv values (kod.currval, sysdate, :new.rsz, :new.tipus, :new.szin, :new.kor, :new.ar); end; Kód aktuális értéke
32/37
B I T MAN
Autós példa 32. Olyan trigger készítése, melynek hatására egy másik trigger működésbe
lép. Figyeljük az ember táblán történő törléseket, ha törlünk egy embert törlődjenek a hozzá tartozó autó rekordok. Az autó rekordok törlését naplózzuk. create or replace trigger torl before delete on ember for each row begin delete from auto_uj where tulaj=:old.id; end; create or replace trigger trn after delete on auto_uj for each row begin insert into naplo values('torles', to_char(sysdate, 'yyyy-mm-dd hh:mi:ss'), user); end; 33/37
B I T MAN
Órai feladatok 1.
34/37
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 neveB M
I T
AN
Órai feladatok 2.
35/37
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 I T MAN
Felhasznált irodalom Kovács László: PL/SQL, elektronikus jegyzet
Barabás Péter: Adatbázis rendszerek 2.,
elektronikus jegyzet Jeffery D. Ullman, Jennifer Widom: Adatbázisrendszerek Kende Mária, Nagy István: ORACLE példatár Don Burleson: Oracle Tips (www.dba-oracle.com)
36/37
B I T MAN
VÉGE V ÉGE 37/37
B I T MAN
