C# - Adatbázis triggerek fejlesztése

Oracle adatbázis-kezelés 5. rész

forráskód letöltése
Miután megismertük, hogy hogyan lehet adatmanipuláló utasításokat egy egységbe zárva kezelni, és az adatbázis-rendszer belsejében hatékonyan eltárolni, mai cikkünkben bemutatjuk azokat a módszereket, melyekkel az adattáblákon végzett módosításokhoz hozzákapcsolható, automatikusan végrehajtódó kódokat készíthetünk. Ezek természetesen a különböző szintű triggerek, melyek elkészítéséhez a PL/SQL hatékony módszereket kínál.
A példaprogram használata előtt létre kell hozni egy SOUSER sémát az Oracle adatbázis-rendszerünkben, és fel kell ruházni DBA jogosultsággal. A cikksorozat harmadik részében a sémát két adattáblával egészítettük ki, melyek a cikksorozat további részeinek tábláit képezik, így az Oracle05.sql script lefuttatása csak az említett táblák hiánya esetén szükséges. Ezek meglétéről meg kell győződni, ugyanis ezek hiányában a létrehozandó további objektumok létrejönnek ugyan, de fordítási hibával. A sémába bejelentkezve le kell viszont futtatni a mellékelt Oracle05ph.sql, Oracle05pb.sql és Oracle05trig.sql script-eket a hivatkozott további objektumok létrehozásához.
Triggerek fejlesztésének lépései
Az adatbázis trigger az adatbázis egy táblájához kötődő névtelen PL/SQL blokk, mely a táblán végrehajtott DML utasítások hatására automatikus végrehajtódik.
Az adatbázis triggerek több szempontból is eltérnek a tárolt alprogramoktól:
  • A trigger egy táblára vonatkozó DML utasítás végrehajtásának mellékhatásaként, automatikusan hajtódik végre.
  • A felhasználó nem tudja befolyásolni, hogy a trigger működésbe lépjen-e, vagy sem, és így a trigger végrehajtásához EXECUTE jogra sincs szükség.
  • Az Oracle7 7.3 előtti verziói a trigger szövegét is az adatszótárban tárolta, de alapvető különbség volt a tárolt alprogramokhoz képest, hogy a trigger P-kódját nem tartotta az Oracle a memóriában az első végrehajtás után, hanem minden egyes végrehajtáskor újraértelmezte a forrásszöveget (azaz a triggerek végrehajtása interpreter elvű volt). Hatékonysági okokból érdemes volt tehát a triggerek törzsét a lehető legrövidebbre kialakítani, tipikusan úgy, hogy tárolt eljárásokat hívtunk, melyeknek minden szükséges információt paraméterként adtunk át. A mai rendszerekben a triggerek is P-kódú alakban tárolódnak.
  • A COMMIT, ROLLBACK és SAVEPOINT utasítások használata triggerekben, és a triggerekből meghívott tárolt eljárásokban tilos.
  • Egy trigger működése más triggereket is elindíthat. Tervezéskor a sokszor végrehajtódó triggerek esetén ezt figyelembe kell venni.
A triggerek rendelkeznek 4 fő jellegzetességgel:
Jellegzetesség Magyarázat
Időzítés BEFORE, AFTER
A trigger által figyelt esemény INSERT, UPDATE, DELETE
A trigger típusa Utasítás szintű, Sorszintű
A trigger törzse Szabályos névtelen PL/SQL blokk
A triggerek működésbe lépésének sorrendje független a triggert kiváltó utasítás által érintett sorok számától. Vegyük a következő példát:
UPDATE DEALERS SET NAME = ’John Pillar’ WHERE ID =1001
Az utasítás - amennyiben egy sor bír az azonosítóval – egy sort érint, mégis több trigger lép működésbe. Lássuk az események sorrendjét:
  • Utasítás szintű BEFORE trigger működésbe lép.
  • Sor szintű BEFORE trigger működésbe lép.
  • NAME oszlop módosul az 1001-as ID esetén.
  • Sor szintű AFTER trigger az 1001-as ID esetén.
  • Utasítás szintű AFTER trigger működésbe lép.
Ez 20 módosítás esetén beláthatatlan időigényt támaszt, így ahol lehet, használjunk utasítás szintű triggereket.
Utasítás szintű triggerek
A létrehozás szintaxisa a következő:
CREATE [OR REPLACE] TRIGGER <triggernév> [BEFORE|AFTER] [DELETE] [OR INSERT] [OR UPDATE [OF <oszlopnév>]]
ON <táblanév>
[DECLARE {<lokális változó>}]
BEGIN
  <törzs>;
END;
A REPLACE opciót akkor használjuk, ha a trigger már létezik. Az OF kulcsszó segítségével megadhatjuk, hogy melyik oszlop módosítására legyen érzékeny a trigger. A PL/SQL blokkot a DECLARE vagy a BEGIN kulcsszóval kezdjük, és az END kulcsszóval zárjuk.
Ha olyan triggert hozunk létre, mely többféle DML utasítás hatására is működésbe lép, előfordulhat, hogy a trigger törzsében el kell döntenünk, hogy éppen melyik utasítás végrehajtása indította a triggert. Így egy trigger eltérő módon viselkedhet INSERT, DELETE vagy UPDATE utasítás esetén. A közvetlenül a trigger törzséből vagy egy tárolt eljáráson keresztül hívható INSERTING, DELETING és UPDATING függvényeket használhatjuk annak kiderítésére, hogy milyen DML utasítás váltotta ki a trigger működését. Például:
CREATE OR REPLACE TRIGGER tri1
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
  IF INSERTING THEN
    ...
  ELSIF UPDATING THEN
    ...
  ELSE
    ...
  END IF;
END trig1;
Sorszintű triggerek
Szintaxisa a következő:
CREATE [OR REPLACE] TRIGGER <triggernév> [BEFORE|AFTER] [DELETE] [OR INSERT] [OR UPDATE [OF <oszlopnév>]]
ON <táblanév>
[FOR EACH ROW
  [WHEN <feltétel>]]
[DECLARE {<lokális változó>}]
BEGIN
  <törzs>;
END;
A trigger deklarációjának FOR EACH ROW kiegészítésében adhatjuk meg az oszlopokat, melyeket érintő DML utasítások indítják a triggert. A WHEN kulcsszó után megadott értékekkel tovább szűkíthetjük az érintett adathalmazt. Szabályok:
  • A WHEN utasításrészben a NEW és az OLD értékekre kell hivatkozni.
  • A WHEN utasításrész nem használható utasításszintű triggerekben.
  • A korrelációs neveket hivatkozásokban a mezők neve elé, ponttal elválasztva kell írni.
  • A NEW korrelációs név értékadó utasítások bal oldalán csak BEFORE triggerekben használható.
  • Az OLD korrelációs név sohasem állhat értékadó utasítás bal oldalán.
A sor szintű triggerekben hivatkozhatunk a módosított sorok mezőinek eredeti és új értékeire egyaránt. Erre a célra az adott sor régi és új változatát jelölő OLD és NEW ún. korrelációs neveket használhatjuk, ahol az Old az eredeti értéket, míg a NEW az új értéket szolgáltatja. Értékeik átadhatók tárolt alprogramoknak. Csak sorszintű triggerekben használhatók.
Művelet OLD NEW
INSERT NULL A beszúrt érték.
UPDATE A régi érték. A megváltoztatott érték.
DELETE A törlés előtti érték. NULL
Módosuló táblának nevezünk egy olyan táblát, melyen éppen INSERT, DELETE vagy UPDATE művelet végrehajtása van folyamatban. Az ilyen táblákhoz csak sorszintű triggerek férhetnek, így előfordulhatnak olyan esetek, hogy egy sorszintű trigger törzsében inkonzisztens adatokat olvashatnánk egy adott táblából.
Ezek elkerülése végett be kell tartanunk két alapvető szabályt sorszintű triggerek fejlesztése közben.
  • Ha egy T táblán elsődleges és/vagy idegen kulcs megszorításokat definiáltunk, akkor az oszlopok adatait a triggerben nem lehet megváltoztatni.
  • Módosuló tábla triggerből nem kérdezhető le és nem módosítható. A tábla aktuális sorának értékeit a NEW és az OLD korrelációs nevekkel érhetjük el, és BEFORE sorszintű triggerben az új értéket a NEW korrelációs névvel módosíthatjuk.
Triggerek kezelése
A triggerek be- és kikapcsolt állapotban lehetnek. A bekapcsolt trigger a megadott esemény hatására beindul, a kikapcsolt trigger nem. A triggerek bekapcsolt állapotban vannak létrehozáskor. A ki- és bekapcsolás utasításainak szintaxisa a következő:
  • Trigger tiltása
ALTER TRIGGER <triggernév> DISABLE;
  • Trigger engedélyezése
ALTER TRIGGER <triggernév> ENABLE;
  • Egy tábla összes triggerének tiltása
ALTER TABLE <táblanév> DISABLE ALL TRIGGERS;
  • Egy tábla összes triggerének engedélyezése
ALTER TABLE <táblanév> ENABLE ALL TRIGGERS;
Az utasítás szintű triggereket a következő műveletekre hozzuk létre:
  • Nem megengedett műveletek megakadályozására
  • Az adatmódosító műveletek naplózására
  • A csomagolt globális változók állítására
A sor szintű triggert használjuk:
  • Érvénytelen adatok bevitelének megakadályozására
  • A DML utasítások által érintett sorok értékének naplózására
  • Az integritás biztosítása érdekében
  • A táblák másolására
  • A származtatott értékek számítására
  • Implicit adatváltoztatásra, a függvények implicit végrehajtására.
Gyakorlati felhasználás
A mellékelt példában egy adatmódosításhoz kapcsolt sorszintű triggert hozunk létre, mely a DEALERS tábla ID oszlopának értékeit módosítva lép működésbe. A trigger feladata, hogy a módosítást érvényre juttassa a TRIPS táblában is, ahol az ID oszlop idegen kulcs.
A módosítások azonnal láthatók a Form-ra helyezett DataGrid kontrolokban. A művelethez választani kell ID értéket a ComboBox kontrolból, majd megadni az új értéket a TextBox kontrolban. A ’Módosítás’ feliratú gombra kattintva lefut egy UpdateId nevű tárolt eljárás, mely módosítja az adott ID-t a DEALERS táblában. A tárolt eljárás az Oracle05ph.sql és Oracle05pb.sql script-ek futtatása után kerül az adatbázisba, a ’TriggerPack’ csomagba ágyazva. A módosítást a Trigger1 nevű trigger érvényesíti a TRIPS táblában. Ennek kódja az Oracle05trig.sql parancsállomány futtatásakor kerül a táblába. Létrehozásának kódja igen rövid, de a végrehajtási sebesség miatt ez is volt a célunk:
CREATE OR REPLACE TRIGGER Trigger1 AFTER UPDATE OF ID
ON DEALERS
FOR EACH ROW
BEGIN
  UPDATE TRIPS SET ID = :new.ID WHERE ID = :old.ID;     
END Trigger1;

Oracle adatbázis-kezelés cikksorozat