Triggerek

Múlt héten kaptam egy feladatot a munkahelyemen, amit úgy gondoltam, hogy egy trigger elkészítésével tudom a legegyszerűbben és leghatékonyabban megoldani. Ez pont jó alkalom volt arra, hogy kicsit közelebbről is megismerkedjek vele. Az eseménygyűjtő rendszerünk egy Microsoft SQL Server 2005 adatbázisra épül, így a T-SQL nyelvet kellett használnom.
Álljon itt egy áttekintés a triggerekről, melyeket megpróbálok rendszertől függetlenül bemutatni. Természetesen kitérek a Microsoft által használt T-SQL és az Oracle által használt PL/SQL közötti szintektikai és szemantikai különbégekre, valamint szó lesz a TimesTen és a triggerek kapcsolatáról is.

Trigger
A trigger nem más, mint egy tárolt eljárás, melynek lefutását egy esemény vált ki. Az események típusa szerint háromféle trigger létezik. DML(Data Manipulation Language), DDL(Data Definition Language), logon trigger.
DML trigger akkor aktiválódik, ha valamilyen DML esemény történik. Ilyen események az INSERT, UPDATE vagy DELETE , melyeket táblákon vagy nézeteken hajthatunk végre.
DDL trigger akkor aktiválódik, ha DDL esemény történik. DDL események a CREATE, ALTER, DROP műveletek végrehajtása.
A logon trigger akkor tüzel, ha valamilyen LOGON esemény történik.
Egy trigger egy esemény előtt, helyett vagy után futhat le. Ennek a segítségével jóval összetettebb megszoírtásokat készíthetünk az adatbázisunkhoz, így növelve a biztonságot és a megbízhatóságot.
Ha például az adatbázisunkban csak olyan eseményeket szetnénk tárolni, melyek nem öregebbek 1 évnél, akkor ezt egy helyettesítő triggerrel szűrhetjük. Csak azt az adatot engedjük beilleszteni, melynek időpontja 1 évnél fiatalabb.
A triggerek logikailag az adattáblákhoz tartoznak. Egy táblához és egy eseményhez tőbb trigger is tartozhat, azonban ezeknek a tüzelési sorrendje nem meghatározható.
A trigger által generált visszatérési értékek, pl. egy SELECT utasítás eredménye a sikeres lefutás után átadódik a kiváltó eseménynek, mely úgy kezeli, mintha az ővé lenne. Ha azt szeretnénk, hogy ez ne jelenjen meg, akkor a trigger elején be kell állítani a NOCOUNT-ot ON-ra. (SET NOCOUNT ON) Ezzel természetesen szelektálhatunk, hogy mi az az érték, amire szükségünk van, és mi az, a mire nincs.
Rendelkezésünkre áll két átmeneti tábla a triggerben. Az egyik az INSERTED, a másik a DELETED. Az INSERTED táblába kerülnek az új értékek, ha INSERT, vagy UPDATE művelet hatására fut le a trigger. A DELETED táblában pedig a régi adatok lesznek, melyek DELETE vagy UPDATE hatására töltődnek be.
Trigger létrehozása. Csak a legalapvetőbb beállításokat mutatom be, bővebben itt lehet róluk olvasni:
T-SQL CREATE TRIGGER
PL/SQL CREATE TRIGGER
A két esetben a szintaxis majdnem ugyan az. Én a T-SQL -t használva mutatom meg egy trigger
létrehozását.

DML trigger készítése:

CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH  [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME  }

[ schema_name . ] - a schema, melyben az adattáblánk is
     szzerepel trigger_name - általunk
     megadott trigger név
ON { table | view } - megadjuk, hogy melyik táblához,
       vagy nézethez akarjuk létrehozni
       a triggert
 ::= [ ENCRYPTION ] - ennek segítségével meggátolhatjuk,
       hogy ha az adatbázis replikálják,
       akkor ez a trigger ne kerüljön át
[ EXECUTE AS Clause ] - lehetőségünk van a triggert egy
  meghatározott felhasználó nevében
  futtatni
FOR - a trigger a kiváltó esemény előtt fut le
AFTER -  a trigger a kiváltó esemény után fut le
INSTEAD OF - a trigger a kiváltó esemény helyett fut le
INSERT|UPDATE|DELETE - kiválaszthatjuk, hogy milyen
        eseményre triggereljünk, tetszőleges
        kombináció előállítható
AS sql statement - az AS utáni rész maga a trigger feltétel
   teljesülése esetén végrehajtandó rész ha
   több műveletből áll, akkor BEGIN .. END
   közé kell rakni

Pl.:
use teszt_db
CREATE TRIGGER ins_trigger
ON tabla1
INSTEAD OF INSERT
AS
BEGIN
IF ((SELECT id FROM inserted)>10) INSERT INTO tabla1 (id, nev)
VALUES (SELECT id, nev FROM inserted)
END

Rövid magyarázat: Ez a trigger csak abban az esetben engedi beillszteni
az új sort, ha az "id" > 10.

A DDL- és logon-trigger létrehozása nagyon hasonló felépítésű a DML-
hez, csak ott nem a táblához, hanem egy adatbázishoz vagy szerverhez
rendeljük hozzá a triggert és helyettesítés nincsen.
Érdekes kérdés, hogy egy többsoros INSERT esetében mi történik. A
trigger csak egyszer hívódik meg, vagy minden sorra külön külön. Na
itt különbség van a PL/SQL és T-SQL között. A PL/SQL-ben lehetőség
van megadni, hogy többsoros INSERT esetén a trigger soronként tüzeljen,
míg T-SQL esetében erre nincs mód.
Ennek a korántsem teljes beszámolónak a végén meg kell még említenem,
hogy a TimesTen-ben nincs lehetőség triggerek létrehozására. Ennek
az lehet az oka, hogy triggerek kezelése nagyon megbonyolítaná a
tudatosan leegyszerűsített működést.

Pár hasznos link:
MSDN - CREATE TRIGGER (T-SQL)
hu.wikipedia.org - trigger
wikipedia.org - trigger
psoug.org - jó példák tipikus alkalmazásokra (PL/SQL)