Triggerek − A megszorítások által kért ellen rzést a rendszer akkor hajtja végre, ha az adat, melyre a megszorítás vonatkozik megváltozik. (SQL2) − Az SQL3 további lehet ségeket ad az adatbázisba tárolásra kerül adatok helyességének az ellen rzésére. − Ezek közül a triggerek már sok kereskedelmi rendszerben meg is vannak valósítva, pl. Oracle, MS SQL Server. − A trigger szó jelentése: elsüt, kivált.
− A triggerek tárolt eljárások, melyek elsüt események hatására indulnak. − Ilyen elsüt esemény lehet egy táblára vonatkozó INSERT parancs DELETE parancs UPDATE parancs. − Az ABKR felismeri az adott helyzeteket és meghívja a triggert. − Egy trigger mindig egy táblára vonatkozik. − Azokat az adatbázisokat, melyekben triggerek is implementálva vannak aktív adatbázisoknak nevezzük.
•
Trigger 3 részb l áll
• esemény (event) –egy táblára vonatkozó beszúrás, törlés vagy módosítás. • feltétel (condition) – a triggert végrehajtja a rendszer, ha a feltétel igaz. Ha a megadott feltétel nem igaz, nem történik semmi a triggerrel összefüggésben. • m velet (action) – egy eljárás, melyet végrehajt a rendszer amikor a trigger aktiválva van és a feltétel igaz.
− a m velet akár megakadályozhatja a kiváltó esemény megtörténtét vagy meg nem történtté teheti azt (pl. kitörölheti az épp felvitt sorokat); − a trigger m velet része hivatkozhat a triggert kiváltó parancs által módosított sorok régi és új értékeire, − végrehajthat új lekérdezéseket, változtathatja az adatbázist. − végrehajthat adatdefiníciós parancsokat (új táblákat hozhat létre, megváltoztathatja a hozzáférési jogokat).
A triggerek segítségével megoldható feladatok: • automatikusan generálhatók származtatott oszlop értékek; • hivatkozási épség megszorítások osztott adatbázis esetén; • komplex logikai összefüggések programozhatók; • átlátszóan megoldható az események naplózása; • másolt táblák osztott adatbázisok esetén megoldhatók; • statisztikákat készíthetünk a táblához való hozzáférésr l; • triggerek segíthetnek a biztonság ellen rzésében, bejegyezni egy táblába ki milyen táblákon végzett valamilyen m veletet.
A trigger számos lehet séget kínál a programozónak: • a m veletet végrehajthatjuk a kiváltó esemény el tt (BEFORE), után (AFTER) vagy helyette (INSTEAD OF); • INSERT trigger m velete hivatkozhat a m veletet kiváltó esemény által beszúrt sorok új értékeire; • DELETE trigger m velete hivatkozhat a törölt sorok régi értékeire; • UPDATE trigger m velete hivatkozhat a törölt sorok módosított sorok régi és új értékeire;
• ha az esemény módosítás, akkor megadhatunk egy bizonyos oszlopot vagy oszlopokat, amelyre az esemény vonatkozik; • a WHEN záradékban megadhatuk egy feltételt, és a m veletet csak akkor hajtja végre a rendszer, ha a trigger aktívvá válik és a kiváltó esemény bekövetkezésekor a feltétel igaz; • a programozó megadhatja, hogy a m velet végrehajtása o minden módosított sorra egyszer (FOR EACH ROW); o egy adatbázis-m velet által módosított összes sorra vonatkozóan egyszer történjen (alapértelmezés szerint);
Példa: egy SQL3 szintaxissal megírt triggerre:
CREATE TRIGGER Részl9Trigger AFTER UPDATE OF Fizetés ON Alkalmazottak REFERENCING OLD AS Régi, NEW AS Uj WHEN (Régi.Fizetés > Uj.Fizetés and Uj.RészlegID = 9) UPDATE Alkalmazottak SET Fizetés = Régi.Fizetés WHERE SzemSzám = Uj.SzemSzám FOR EACH ROW
• különbség az utasítás szint és sor szint triggerek között akkor látszik, ha egy kiváltó esemény több sorra is vonatkozik példa: UPDATE m velet egy tábla több sorát is módosítja, nem csak egyet. az utasítás szint trigger csak egyszer kerül végrehajtásra hivatkozni tudunk a módosítás el tti értékekre a régi sorok halmazának (OLD_TABLE) a segítségével; a módosítás utáni értékekre, az új sorok halmazával (NEW_TABLE).
Ha trigger sor szint , akkor a trigger m velet része minden sorra végrehajtódik. ha a kiváltó esemény beszúrás, akkor a beszúrt sornak a NEW AS záradék segítségével adhatunk nevet. törlés esetén az OLD AS záradék segítségével nevezhetjük el a törölt sort. ha a kiváltó esemény a módosítás, a rendszer a módosítás el tti sort tekinti régi sornak, a módosítás utánit pedig újnak.
Az utasítás szint trigger hasznos, ha a trigger m velet része nem függ a kiváltó esemény által érintett sorok értékeit l, Példa: biztonsági ellen rzés az aktuális felhasználóra vonatkozóan. generál egy sort a kiváltó eseményre vonatkozóan. példa Utasítás szint trigger SQL3-ban:
CREATE TRIGGER set_count AFTER INSERT ON Diakok /*event*/ REFERENCING NEW TABLE AS InsertedTuples FOR EACH STATEMENT INSERT INTO StatisticTable(ModifiedTable, ModificationType, Count) SELECT ’Diakok’, ’Insert’, count (*) FROM InsertedTuples I WHERE I.age <18
Egy trigger lehet: • Sor szint és kiváltó esemény el tt kerül végrehajtásra, amikor is miel tt módosítaná a kiváltó esemény által érintett sorokat és miel tt ellen rizné az összes helyességi megszorítást, végrehajtja a trigger m velet részét, ha a trigger feltétel része igaz. • Utasítás szint és kiváltó esemény el tt kerül végrehajtásra. Ebben az esetben miel tt végrehajtaná a triggert kiváltó eseményt, végrehajtja a trigger m veletét.
• Sor szint és kiváltó esemény után kerül végrehajtásra. Miután módosítja a kiváltó esemény által érintett sorokat és ellen rzi a helyességi megszorításokat, végrehajtja a trigger m velet részét az aktuális sorra, ha a feltétel igaz. Ellentétben a BEFORE triggerekkel, az AFTER trigger esetén a rendszer lezárja a sorokat. (Lásd lezárás a fejezetben) • Utasítás szint és kiváltó esemény után kerül végrehajtásra. Ebben az esetben a kiváltó esemény után ellen rzi a helyességi megszorításokat, majd végrehajtja a trigger m veleti részét.
• Triggerek tervezése • A trigger egy er s mehanizmus, hogy az adatbázisban végzett módosításokat könnyebben tudjuk feldolgozni, de el vigyázatosan kell használni ket. • Triggerek halmazának a hatása lehet nagyon komplex, egy aktív adatbázis karbantartása nehézzé válhat. • Ha egy parancs egynél több trigger-t aktivál, az ABKR mindegyiket végrehajtja, tetsz leges sorrendben. • Sokszor nem tudhatjuk, a trigger mely program mellékhatásaként lett végrehajtva.
• Fontos, hogy egy trigger m velet része aktiválhat egy másik triggert. Sajátos esetben el fordulhat, hogy ismét aktiválja az el z triggert – ezeket rekurzív triggereknek nevezik. Az ilyen lánc aktiválásakor mivel nem tudjuk, hogy az ABKR milyen sorrendben hajtja ket végre, nehezen lehet eldönteni, hogy mi lesz az összhatásuk.
A triggerek tervezése esetén vegyük figyelembe a következ irányelveket: • Ne írjunk triggereket olyan tevékenységre, melyre a megszorítások adnak lehet séget. (például hivatkozási épség megszorítás). • Korlátozzuk a triggert legfeljebb 60 sorra. Ha több m veletre is szükségünk van, helyezzük azokat egy tárolt eljárásba és hívjuk meg a triggerb l a tárolt eljárást. • Ne tervezzünk rekurzív triggereket.
• Használjuk mértékkel a triggereket, gondoljunk arra, hogy minden egyes adatkezelési m velet esetén meghívásra kerülnek, és nagyon sok munkát igényelhetnek. • Csak olyan m veletek elvégzésére használjuk, azaz a globális m veletekre, melyek minden felhasználó esetében szükségesek.
Megszorítások triggerekkel szemben: • Triggereket is azért találták ki, hogy az adatbázis konzisztens maradjon. A helyességi megszorításokat úgyszintén, viszont ezek nem m veletként vannak értelmezve, ellentétben a triggerekkel, így könnyebb megérteni a helyességi megszorításokat. • A helyességi megszorítások megvédik az adatot mindenféle parancs esetén, hogy ne kerüljenek inkonzisztens állapotba, de triggereket csak speciális parancsok aktiválnak (INSERT, DELETE, UPDATE).
• A triggerek rugalmasabb úton tartják meg az adatbázis integritását. • Példák triggerekre MS SQL Server-ben új sorra az inserted kulcsszóval hivatkozhatunk törölt sorokra a deleted kulcsszóval lehet hivatkozni UPDATE úgy történik, hogy kitörli a módosított sorokat (vagy csak egy sort) és beszúrja az új sort, ezért lehet hivatkozni az inserted és deleted sorokra is.
Példa Egy software cég projektjeinek a tárolása esetén érdekeltek vagyunk a megrendel cég (tárolva a Customers táblában), illetve a rendelt projekt (tárolva a Projects táblában) adataiban. Egy projekt különböz állapotban lehet: tervezett, aktív, megszakítva, befejezett, stb. (tárolva a ProjStates relációban). Egy projektet felosztunk több kiszabott feladatra (Tasks), melyet egy alkalmazott (Employee) kell elvégezzen, több napra is kiterjedhet egy feladat.
Egy feladatot apró tevékenységekre osztunk, melyet ugyanaz az alkalmazott végez, mint a feladatot, és nem terjedhet több napra, csak egyre. A projektnek, feladatnak és tevékenységnek is vannak: o tervezett kezdési dátum (PlannedStartDate), o effektív kezdési dátum (RealStartDate), o tervezett befejezési dátum (PlannedEndDate), o effektív befejezési dátum (RealEndDate). A projekt, feladat és tevékenység lehet befejezett vagy nem. A Tasks és Activities relációk esetén a Finished (1 ha befejezett, 0 ha nem befejezett) mez adja ezt meg, a
Projects esetén pedig a ProjStateID (4-es ID a befejezett terv). Customers [CustomerID, Name, Address, …] Projects [ProjectID, Title, CustomerID, ProjManID, ProjStateID, PlannedStartDate, PlannedEndDate, RealStartDate,RealEndDate,…] Employees [EmployeeID, FirstName, LastName, …] Tasks [TaskID, Name, ProjectID, Finished, EmployeeID,PlannedStartDate, PlannedEndDate, RealStartDate, RealEndDate, …]
Activities [ActivID, Text, TaskID, Finished, PlannedStartDate, PlannedEndDate, RealStartDate, RealEndDate, …] ProjStates [ProjStateID, Name]
A feladatok amit a triggerek segítségével szeretnénk megoldani a következ k: • helyességi feltételek ellen rzése: nem lehet egy projekt befejezett állapotban, ha létezik olyan a projekthez tartozó feladat, mely nincs befejezve; (lásd Proj_upd trigger)
CREATE TRIGGER Proj_upd ON Projects FOR UPDATE AS declare @vProjID int, @vProjStID as int /* Tests if the ProjStateID is modified to be finished, if there are some tasks belonging to the project, which has Finished on 0 */ set nocount on select @vProjStID = i.ProjStateID, @vProjID =i.ProjectID from inserted i
if @vProjStID = 4 begin if exists (select TaskID from Tasks where ProjectID = @vProjID and Finished = 0) begin raiserror ('There are tasks belonging to the project, which are not finished.',16,1) ROLLBACK TRANSACTION end end set nocount off
nem lehet egy feladat befejezett állapotban, ha létezik olyan a feladathoz tartozó tevékenység, mely nincs befejezve. ha egy feladat Finished mez jét 0-ra módosítjuk, azt jelenti azel tt befejezett volt és most be nem fejezetté módosítjuk, el fordulhat, hogy már a projekt is befejezettnek lett nyilvánítva és ha megengedjük, hogy a feladatot be nem fejezetté módosítsa a felhasználó, akkor olyan állapotba jut az adatbázis, hogy a projekt be van fejezve, de van egy feladat, mely hozzá tartozik és nincs befejezve; (lásd Task_upd)
CREATE TRIGGER Task_upd ON Tasks FOR INSERT, UPDATE AS declare @task_finished tinyint, @vTaskID int, @vfinished tinyint, @vProjID int, @proj_state int /* Tests if the Finished field is modified to 1, if there are some activities belonging to the task, which has Finished on 0 */ set nocount on select @task_finished = i.Finished, @vTaskID=i.TaskID, @vProjID = i.ProjectID from inserted i
if @task_finished = 1 begin if exists (select ActivID from Activities where TaskID = @vTaskID and Finished = 0) begin raiserror ('There are activities belonging to the task, which are not finished.',16,1) ROLLBACK TRANSACTION end end
/* Tests if the Finished field is modified to 0, the project which it belongs has been marked Finished (ProjStateID= 4) */ if @task_finished = 0 begin select @proj_state = ProjStateID from Projects where ProjectID = @vProjID if @proj_state = 4 begin raiserror ('The project from which the task belongs was marked finished.',16,1) ROLLBACK TRANSACTION
end end set nocount off
A Projects esetén csak UPDATE triggert írtunk, míg a Tasks esetén UPDATE és INSERT triggert. A Tasks esetén hivatkozási épség megszorítás van a ProjectID-re, ez azt jelenti, hogy nem tudunk feladatot begy jteni projekt el tt, el ször léteznie kell egy projektnek és ahhoz gy jthetünk be feladatokat. Tehát INSERT esetén a projekthez még nem tartozhat egyetlen
feladat sem, nem áll fenn a helytelen állapot veszélye, mely módosítás esetén fennállhat. A feladat esetén, el fordulhat, hogy egy befejezett projekthez akarunk beszúrni egy új, be nem fejezett tevékenységet.
