Trigger (adatbázisok)

A triggerek olyan speciális eljárások egy adatbázisban, amelyeket az INSERT, UPDATE, DELETE, TRUNCATE parancsok végrehajtások előtt, után vagy helyett hív meg a rendszer. A trigger vagy engedélyezheti vagy elvetheti az adott táblán történt módosításokat, naplózási feladatokat végezhet, járulékos módosításokat hajthat végre az adatbázisban.

A triggerek fajtái

• BEFORE-trigger
• INSTEAD OF-trigger
• AFTER-trigger

Fontos tudni, hogy egy táblázathoz parancsonként csak egy trigger rendelhető hozzá. Ez azt jelenti, hogy egy táblához legfeljebb három trigger készíthető el (INSERT, UPDATE, DELETE). Persze lehetőség van arra is, hogy egy trigger ne csak egy parancsnál legyen aktiválva. Előfordulhat, hogy például az INSERT és UPDATE parancsoknál történő ellenőrzések (szinte) megegyeznek: ilyenkor nem szükséges két triggert írni, hiszen az ellenőrzést az egyikkel is meg lehet oldani.

BEFORE-trigger

BEFORE-trigger: A parancs végrehajtása előtt fut le.

INSTEAD OF-trigger

INSTEAD OF trigger fő jellemzője, hogy a triggert egy INSERT, UPDATE vagy DELETE parancs helyett hajtják végre. Tehát a parancs a trigger lefutása után sem fog végrehajtódni. Ez azt jelenti, hogy ha mégis úgy döntünk, hogy a parancs nem vezet inkonzisztenciához, akkor azt a triggeren belül nekünk kell "kézzel" újra végrehajtanunk.

AFTER-trigger

A leggyakoribb triggerfajta (AFTER) fő jellemzője, hogy a triggert egy INSERT, UPDATE vagy DELETE parancs futtatása után (tehát amikor az adatok már módosultak) hajtják végre. Ha a triggerben a tranzakciót visszavonják (ROLLBACK), akkor az adatokat az adatbázis a ROLLBACK-folyamat keretében törli. Ha a trigger mindent rendben talál, akkor az adatokon már nem változtat, hisz az SQL-parancs már a trigger meghívása előtt végrehajtódott.

Szintaxis

T-SQL-szintaxis (Microsoft SQL Server 2000 és 2005)

  CREATE TRIGGER <trigger_név>
     ON <táblázat_neve>
     { BEFORE | AFTER } <sql_parancs_típus>
     AS <T-SQL_parancs>
   
  <trigger_név>       ::= a trigger neve
  <táblázat_neve>     ::= a táblázat neve, amelyre az adatbanknak ezt a triggert alkalmaznia kell
  <sql_parancs_típus> ::= INSERT | UPDATE | DELETE (több megadása így lehetséges: INSERT, UPDATE)
  <T-SQL_parancs>     ::= egy parancs vagy blokk, amit a trigger a meghívásakor végrehajt

Példa:

  CREATE TRIGGER ins_upd_Beszallito
     ON Beszallito
     AFTER INSERT, UPDATE
     AS
     BEGIN
        IF UPDATE(BeszallitoID)
        BEGIN
           ROLLBACK TRANSACTION
        END
     END

Magyarázat

Létrehozunk egy triggert, aminek a neve "ins_upd_Beszallito" lesz. (Egy le nem írt konvenció szerint úgy illik elnevezni a triggereket, hogy a nevükben látható legyen valamilyen formában a táblázat és a parancsok, amikhez a triggert létrehozták) Jelen esetben a trigger neve utalást ad arra, hogy a trigger a "Beszallito" nevű táblának INSERT illetve UPDATE paranccsal történő módosításakor lesz meghívva. Ezekre a dolgokra a következő sorok adnak utasítást.

Az "ON" kulcsszó mögött áll annak a táblának a neve, amelyiket ez a trigger felügyel.

Az "AFTER" kulcsszó es az az utáni "INSERT, UPDATE" kulcsszavak jelzik, hogy a trigger minden olyan INSERT és UPDATE parancs után lesz meghívva, amelyek a fent nevezett táblázatot módosítják.

Az "AS" utáni blokkban lekérdezhető, hogy a BeszallitoID nevű oszlopot (feltételezzük, hogy ez jelen esetben elsődleges kulcs) módosították-e. Ha igen, akkor a tranzakciót a ROLLBACK TRANSACTION paranccsal visszavonjuk, mivel az elsődleges kulcs oszlopai nem módosulhatnak.

Új, módosított vagy törölt adatok lekérdezése

A triggerekben (triggertől függően) létezik két speciális táblázat. A felépítésük megegyezik az INSERT, UPDATE vagy DELETE parancs cél-táblázatának felépítésével.
MS SQL Servernél ez a két táblázat a következő:

• "inserted": Ebben a táblázatban csak az új (beillesztett vagy beillesztendő) adatok szerepelnek.
• "deleted": Ebben a táblázatban csak a régi (törölt vagy törlendő) adatok szerepelnek.

Egy INSERT parancs esetében csak az "inserted" táblázat, míg az UPDATE parancsnál mindkettő létezik.
A DELETE parancsnál már csak a "deleted" táblázat létezik.
Fontos: SQL engedélyezi, hogy egy parancs alkalmával több sor legyen módosítva. (Pl. INSERT INTO (SELECT * FROM …);, vagy DELETE FROM xyz;) A triggerben erre is gondolni kell és ha egyszerű SQL-kéréssel nem lehet ellenőrizni a művelet helyességét, akkor azt ún "cursor"-ral kell megtenni.

PL/SQL-szintaxis

PL/SQL (pl. Oracle Database) szintaxis:

  CREATE [OR REPLACE] TRIGGER <trigger_name> 
     {BEFORE|AFTER} {INSERT|DELETE|UPDATE} 
     ON <table_name> 
     [REFERENCING [NEW AS <new_row_name>] [[OLD AS <old_row_name>]]    
        [FOR EACH ROW [WHEN (<trigger_condition>)]]
        <trigger_body>
   
  <trigger_name> ::= a trigger neve
  <table_name>   ::= a táblázat neve, amelyre az adatbanknak ezt a triggert alkalmaznia kell
  <new_row_name> ::= a táblázat neve, amelyben az új adatok szerepelnek (default: ":NEW")
  <old_row_name> ::= a táblázat neve, amelyben a régi adatok szerepelnek (default: ":OLD")
  <trigger_condition> ::= egy feltétel, amely fennállása a trigger meghívásához vezet. Más esetben a trigger nem lesz meghívva.
  <trigger_body> ::= egy PL/SQL parancs vagy blokk, amelyben a trigger programkódja van.

REFERENCING: nem kötelező megadni. Ilyen esetben a fent nevezett default értékek lesznek véve.
FOR EACH ROW: a PL/SQL előnye T-SQL-el szemben, hogy ennek a paraméternek a megadásával a szerver kényszerítve lesz, hogy a trigger például egy INSERT INTO (SELECT * FROM …); parancs minden sorára egyenként hívja meg. Ha a FOR EACH ROW-t nem írjuk oda, a trigger parancsonként lesz meghívva. A PL/SQL ilyenkor ugyanúgy fog viselkedni, mint a T-SQL.

További információk

• An event/trigger mechanism to enforce complex consistency constraints in design databases
• Trigger Happy – A look at the many implementations of database triggers.

Források

• Dr. Hillebrand, Kurt. Angewandte Datentechnik, 9. kiadás (német nyelven) 
• PostgreSQL dokumentáció