Triggerek használata MySQL-ben

Triggerek a MySQL 5.0 óta léteznek. Egy trigger bizonyos táblaműveletek hatására kiváltódó események kezelésére szolgál, amiben tárolt eljáráshoz hasonló szintaktikával további műveletek végezhetők. Bár még nem olyan kifinomult a MySQL trigger, mint például Oracle-ben, de be fogom mutatni, hogy így is hasznos szolgálatot tehet, amennyiben van jogosultságunk a használatára.

Triggerek létrehozása:

Egy utasítás esetén:
create trigger triggerneve  mikorfusson milyenmuveletre for each row sqlutasitas;
Több utasítás esetén:

mikorfusson: Lehet before vagy after.
Azaz egy művelet előtt is lefuthat, vagy csak az után.

milyenmuveletre: Lehet insert, update vagy delete

A „create” előtti sorra azért van szükség, mert a trigger törzsében levő sql utasítások utáni pontosvessző lezárná a triggert is. Meg kell tehát változtatni a delimitert, azaz az utasításokat határoló karaktert. A trigger lezárása után pedig vissza lehet azt állítani. Most pedig jöjjön egy életszerű példa a triggerek használatára!

Tegyük fel, hogy szupertitkos, fontos adatokat tárolok adatbázisban, de nem csak nekem van jogom webes felületen módosításokat végezni. Nem szeretném, hogy valamelyik adminisztrátor, amelyik megharagszik rám, kitöröljön, vagy átírjon bármit is visszaállíthatatlanul. Legyen tehát erre a következő két tábla létrehozva:

A „fontosadatok” táblában vannak a szupertitkos adataim és bármilyen módosítás történik ezen a táblán, én azt szeretném naplózni. A megoldást kezdem azzal, hogy létrehozok egy triggert, ami a „fontosadatok” frissítésekor lefut.

A trigger neve „trig_verzment”. A „before update” jelzi, hogy a tábla frissítése előtt kell lefutnia. Az „on fontosadatok” pedig azt a táblát jelöli meg, amelyik frissítésekor lefuthat a trigger. A „for each row” pedig azt jelzi, hogy a triggernek minden sor megváltoztatásakor le kell futnia. Oracle-ben ez elhagyható, és akkor csak egyszer fut le, miután az összes sor módosítása megtörtént. MySQL-ben viszont kötelező szintaktikai elem.

A trigger törzsében bármilyen összetett műveletet végezhetünk, amihez két hivatkozás is felhasználható. Ezek a NEW és az OLD, melyek azt a táblát jelentik, amihez az triggert rendeltük. A NEW az új adatra hivatkozik, míg az OLD a módosítás előttire.

Amikor tehát frissül a „fontosadatok” tábla valamely rekordja, a triggerben levő feltétel megvizsgálja, hogy a módosítás utáni adat különbözik-e az eredetitől. Ha igen, akkor naplózza a módosítást. Azaz a „verziok” táblába elmenti a módosított rekord azonosítóját, a régi adatot, a módosított adatot, és hogy mikor történt a módosítás. Talán feleslegesnek tűnik mindig az előzőt és az újat is elmenteni, hisz a következő rekord a „verziok” táblában úgyis tartalmazni fogja ismét a most újnak számító adatot úgy, mint eredeti. Viszont ha megengedjük a törlést a naplóból, akkor már nem garantálja semmi, hogy akár az előző, akár a következő rekord létezik.

Módosult, de mégis ki által?
A fenti megoldással azt már meg tudom oldani, hogy visszaállíthassam egy haragos adminisztrátor által módosított rekordomat, de nem tudom, hogy ki haragszik rám. Ezért a triggert a következőképpen módosítom:

A különbség annyi, hogy egy úgynevezett „user-defined változót” ( felhasználó által definiált ) is bevezettem „@felhasznalo” néven. Ezt majd minden update előtt be kell állítani. SQL-ben ez így nézne ki:

PHP-ben pedig valahol az módosító utasítások előtt célszerű egy queryben megadni a felhasználó nevét.

Ebben az esetben nem kell az utasítások végére pontosvessző! Felhasználónév helyett pedig élesben célszerűbb felhasználó id-t menteni. Itt csak a példa egyszerűsége miatt nem úgy tettem. Most már azért talán sikerült tisztáznom, mire is jó egy trigger. Van azonban pár fontos tudnivaló, amiről még nem beszéltem, de ezt most bepótolom.

Mikor fut le egy trigger?
Említettem, hogy megjelölhető insert, update vagy delete művelet. Viszont ez nem egyenlő azzal, hogy az ezeknek megfelelő sql utasításokat futtatom le. A load data utasításra is sorok szúródnak be, így arra is lefut az insert-hez rendelt trigger. A mysql-ben levő replace utasítás pedig gyakorlatilag egy delete és egy insert utasítás egymás után. Ekkor tehát delete és insert eseményekhez rendelt triggerek is lefutnának. Ennek problémájára még visszatérek. A másik eset, amikor egyszerre kétféle trigger is érintett, amikor a „duplicate key” eseményt alkalmazzuk egy insert utasításban. Akkor ugyanis, ha olyan sort próbálunk beszúrni, aminek az elsődleges azonosítója már létezik a táblában, insert helyett update fog történni.

Hogy hivatkozom a táblára triggerben?
A NEW kulcsszóval lehet hivatkozni a táblára, ha a módosítás utáni adatra van szükségem, és az OLD kulcsszóval, ha az az előttire. Azt észre kell viszont venni, hogy delete művelet esetén nem tudunk az új adatra hivatkozni, hiszen éppen törlés történik. Így ott csak az OLD használható. Az insert -ben pedig csak a NEW, mivel beszúrás előtt nem volt még semmilyen adat.

replace -szel való módosítás problémája:
Említettem, hogy ha replace utasítást használunk, akkor először egy delete művelet fog történni feltételben az elsődleges kulccsal, amit replace-ben kötelező megadni, majd egy insert művelet az új adatokkal. Ekkor tehát az update-hez kötött trigger nem fog lefutni, pedig ebben az esetben is módosul a rekord és mégsem lesz naplózva az eddigi megoldásommal. Lehet előre tervezni, és a

replace fontosadatok set id = 1, adat = 'Új adat';

helyett mindig a következőt használni:

insert into fontosadatok(id, adat) values(1, 'Új adat') on duplicate key update values(adat);

Vagy pedig létre kell hozni egy triggert a delete-nek és az insert-nek is. A delete-ben egy változót kell beállítani annak jelzésére, hogy melyik elsődleges kulcsú rekord lett törölve. Ha ez után lefut az insert-hez rendelt trigger, abban pedig lehet vizsgálni, hogy a beszúrandó rekord azonosítója megegyezik-e a legutóbb töröltével. Fontos, hogy ebben az esetben a triggereket az „after” jelzővel lássuk el, különben előbb az insert triggere fog lefutni, és csak aztán a delete-é. Úgy pedig nem lesznek beállítva időben a változók és nem történik naplózás. Az új triggerek tehát:

Praktikusabb lenne a naplózásra egy tárolt eljárást írni és a triggerekben csak azt futtatni, de a tárolt eljárások használatáról és létrehozásáról majd egy másik bejegyzésben írok. Aki használ InnoDB táblatípust MySQL-ben, ami támogatja a referenciákat idegen kulcsokkal, azok számára fontos lehet, hogy egy „cascade” típusú referencia nem aktiválja a triggereket. Így abban az esetben a naplózás sem lehetséges. Viszont a „cascade” referencia működése triggerrel is megvalósítható. Amellett pedig a többi, a fenti példákban bemutatott műveletek is elvégezhetők. Csak oda kell figyelni mikor, mit használunk. Valamint arra, hogy ne módosítsuk ugyanazt a táblát a triggerben, mint amelyiknek az update műveletéhez rendeltük a triggert. Ugyanis az rekurzióhoz vezethetne és ezt a MySQL nem engedélyezi.

A bemutatott módszer még nem kezeli, ha csak törlök egy rekordot az adatok közül. Pedig ezzel is lehetne kárt okozni. A delete-hez való triggert már létrehoztam. Abban, a változók létrehozása mellett elmenthetném a verziok táblába a törölt rekord adatait az ujadat-ban NULL értéket beállítva, vagy külön mezőt is létrehozhatnék a törlés jelzésére. Majd pedig ha replace utasítás miatti törlés történt, akkor az insert triggerében törölhetném az utoljára létrehozott naplóbejegyzést az aktuális adat azonosítóra vonatkozóan. Ezt azonban már nem mutatom be. Bízom benne, hogy így is egy hasznos bejegyzés született.

Az esetleges észrevételeket szívesen fogadom hozzászólásban.