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:

delimiter $$
create trigger triggerneve  mikorfusson milyenmuveletre
for each row begin
      /*További sql utasítások*/
end$$
delimiter ;

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:

drop table if exists fontosadatok;
create table fontosadatok (
        id int not null primary key auto_increment,
        adat text not null
);

drop table if exists verziok;
create table verziok (
        id int not null primary key auto_increment,
        adatid int not null,
        regiadat text not null,
        ujadat text not null,
        time timestamp not null,
        modosito varchar(20) not null
);

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.

drop trigger if exists trig_verzment;
delimiter $$
create trigger trig_verzment before update on fontosadatok for each row
begin
        if OLD.adat != NEW.adat then
                insert into verziok(adatid, regiadat, ujadat, time) values(NEW.id, OLD.adat, NEW.adat, now());
        end if;
end$$
delimiter ;

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:

drop trigger if exists trig_verzment;
delimiter $$
create trigger trig_verzment before update on fontosadatok for each row
begin
        if OLD.adat != NEW.adat then
                insert into verziok(adatid, regiadat, ujadat, modosito, time) values(NEW.id, OLD.adat, NEW.adat, @felhasznalo, now());
        end if;
end$$
delimiter ;

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:

set @felhasznalo = 'Ödön';
update fontosadatok set adat = 'Kiszúrok veled!' where id = 1;

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

mysql_query("set @felhasznalo = '$felhasznalonev'");
//...
mysql_query("update fontosadatok set adat = '$ujadat' where id = $userid");

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:

drop trigger if exists trig_adattorol;
delimiter $$
create trigger trig_adattorol after delete on fontosadatok for each row
begin
        set @toroltadat.id = OLD.id;
        set @toroltadat.adat = OLD.adat;
end$$
delimiter ;

drop trigger if exists trig_adatbeszur;
delimiter $$
create trigger trig_adatbeszur after insert on fontosadatok for each row
begin
        if @toroltadat.id = NEW.id and @toroltadat.adat != NEW.adat then
                insert into verziok(adatid, regiadat, ujadat, modosito, time) values(NEW.id, @toroltadat.adat, NEW.adat, @felhasznalo, now());
        end if;
        set @toroltadat.id = NULL;
        set @toroltadat.adat = NULL;
end$$
delimiter ;

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.

Kategóriák: 
Megosztás/Mentés

Hozzászólások

F1DO képe

Iszonyú jó a cikk, köszönet!

Rimelek képe

Köszönöm! Örülök, hogy segíthettem :)

Zoli képe

hello,
van egy triggerem, ami h módosítok egy rekordban, akkor a régi-új adatokat beírja egy másik táblába, de 2rekordot is létrehoz, egyet az adatokkal, egyet pedig üres értékekkel. itt a trigger:

delimiter $$

CREATE TRIGGER update_bnotes BEFORE UPDATE ON banknotes
FOR EACH ROW

begin

  if

OLD.countryID != NEW.countryID OR
OLD.nevertek != NEW.nevertek OR
OLD.evszam != NEW.evszam OR
OLD.leiras != NEW.leiras OR
OLD.sorozatszam != NEW.sorozatszam OR
OLD.szelesseg != NEW.szelesseg OR
OLD.magassag != NEW.magassag

then

INSERT INTO bnotes_alters_by_users(userID, banknoteID, old_countryID, new_countryID, old_nevertek, new_nevertek, old_evszam, new_evszam, old_leiras, new_leiras, old_sorozatszam, new_sorozatszam, old_szelesseg, new_szelesseg, old_magassag, new_magassag, insert_date, insert_userID)

VALUES(NEW.userID, OLD.banknote_id, OLD.countryID, NEW.countryID, OLD.nevertek, NEW.nevertek, OLD.evszam, NEW.evszam, OLD.leiras, NEW.leiras, OLD.sorozatszam, NEW.sorozatszam, OLD.szelesseg, NEW.szelesseg, OLD.magassag, NEW.magassag, OLD.insert_date, OLD.userID);

   end if;

end$$
delimiter ;

Rimelek képe

Helló

Rejtélyes probléma, de furcsa módon nem is olyan régen említett más is nekem hasonlót. De abban nem voltak triggerek.

Esetében valami a karakterkódolással volt. Hogy mi összefüggés volt a sorok beszúrása és a kódolás közt, nem jöttünk rá. De ha jól rémlik, akkor úgy javult meg, hogy a kódot futtató php fájl kódolását vagy a tábláét változtatta. Nem emlékszem, miről mire.

A jelenség ott is duplikált sor volt egyetlen insert hatására. De lehet, hogy ott nem üres sorok voltak. Hát sokat ezzel nem segítettem, tudom. A trigger jónak tűnik. Még ha le is futna kétszer valamiért, az üres sort akkor sem értem.

Ha rájössz a megoldásra, kérlek, írd meg nekem is! Még nem volt időm kísérletezni ezzel. És nagyon érdekel az oka.

Esetleg megpróbálhatod, hogy egy másik táblába is megpróbálsz írni. hogy abban is duplikál-e. A fent említett esetben csak egyetlen táblánál fordult elő a probléma. Talán egy táblajavítást is meg lehet próbálni. Vagy csak módosítani valamit rajta, majd visszaállítani. Nem normális, hogy ilyen történik szerintem, de valahogy ki kell deríteni legalább, mihez van köze.

Anonymous képe

Minden új rekord mentésekor a KOD mezőben a következő növekvő kódot szeretném beszúrni, de nem az AUTOINCREMENT opciót akarom használni.
Azért nem, mert ez utóbbi kihagyhat kódot, ha pl. az utolsót törlöm előtte,
Lehet-e olyan triggert írni, amely ezt a folyamatosan növekvő kód mezőt írja új rekord mentése esetén?

Rimelek képe

Természetesen lehet olyat írni. A (before insert) triggerben előbb le kell kérdezni a maximum értéket és a

NEW.kod

értékét max+1 -re beállítani. Viszont az ID-t nem is sorszámozásra szokás használni. Az auto_increment azonosítóval a legbiztosabb, hogy nem éri az embert meglepetés és nem próbál kétszer ugyanolyan értéket beírni.

Ha ez tényleg sorszámozásra kell, én inkább a megjelenítéskor javasolnám ezt lekezelni.

De itt egy példa a trigerre:

drop trigger teszt_trigger;
delimiter $$
create trigger teszt_trigger before insert on teszt for each row
begin
        declare max int unsigned;
        select coalesce(max(id), 0) into max from teszt;
        set NEW.id = max+1;
end$$
delimiter ;
Anonymous képe

Kedves Rimelek !

Értékadásnál az autoincrement kód megadása nem akar sikerülni, milyen szintaktikával kell ezt beírnom?

INSERT INTO wishlisthist
VALUES ('', '', '', '', CURRENT_TIMESTAMP,'G')

Az első érték lenne az, gondolom az auto_increment függvény lenne a megoldás, de nem fogadja el.

Köszönöm

Rimelek képe

Nincs a olyan, hogy auto_increment függvény. A tábla deklarálásánál auto_inrcement -ként definiált mezőnek pedig vagy NULL értéket kell adni, vagy a táblanév után zárójelben a mezőneveket is fel kell sorolni az auto_increment mező kivételével és a values után annak a mezőnek az értékét szintén el kell hagyni.

Viszont ennek nincs nagyon köze a triggerekhez. Ha nem hagyományos auto_increment mező kellene, akkor az előző kommentemben írtam példát, ami működik is, mert kipróbáltam.

Anonymous képe

Kedves Rímelek !

Egy kis segítséget szeretnék kérni. Triggerből hívott tárolt eljárás hibaüzenete:
Can't update table 'wishlist' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Milyen megoldás jöhet szóba "szub-delete" esetén

Köszönettel

Rimelek képe

Üdv. Mi a "szub-delete" ebben az esetben? Ugyanabból a táblából kellene törölni, mint aminek a módosítása kiváltotta a triggert de egy másik rekordot? Mi lenne a feladat?

Anonymous képe

Egy trigger fut delete after módban és törli a rekordot és egy másik táblában a hozzá kapcsolódó ID azonosítóval lévő rekordokat, Viszont abban a táblában is fut egy delete after trigger, amelyik töröl rekordokat egy harmadik táblából. Ehhez jön hogy az első trigger is töröl rekordokat a harmadik táblából. Szerintem ez akad össze sajnos. külön működik a második trigger törlés funkciója rendesen. Remélem érthetően írtam.

Rimelek képe

Ennyi egymástól függő triggert és eljárást nem nagyon szoktam írni, de a hibaüzentből nem gondolom, hogy a harmadik táblából törlő két külön trigger okozná a gondot.

Esetleg egy sql kódot is kaphatnék az érintett táblák leírásával? Nem kell minden mezővel együtt, épp csak az azonosítók és a táblák kapcsolata lenne, ami segítene megfejteni a problémát. Illetve hogy pontosan melyik trigger mit indít el. A bizalmas részekre nincs szükségem.