Windows - Trigger-ek alkalmazása az SQL szerverben

forráskód letöltése
Az SQL 2000 szerverben a trigger-ek olyan speciális osztályú tárolt eljárások, amelyek automatikus futtatásra készülnek, amikor egy UPDATE, INSERT, vagy DELETE utasítás lefut egy táblára vagy egy nézetre. A trigger-ek olyan hasznos eszközök, amelyek segítségével az adatmódosítás pillanatában bizonyos szabályok automatikusan érvényesíthetők.
A tábláknak több trigger-ük is lehet. A CREATE TRIGGER utasítás definiálható a FOR UPDATE, FOR INSERT, FOR DELETE feltételek egyikével, hogy egy speciális osztályú adatművelet hatására fussanak le. Amikor a FOR UPDATE a megadott változat, akkor az IF UPDATE (oszlopnév) feltétel használható arra, hogy egy trigger-t egy meghatározott mező módosításához kössünk.
A trigger-ek automatizálhatnak egy cég működésében különféle folyamatokat. Pl. egy leltári rendszer esetében a módosító trigger-ek észlelhetik, amikor valamely készlet mennyisége eléri az a szintet, amikor szükséges lesz új rendelést indítani, amelyet a trigger automatikusan el is végez.
Az alábbi trigger egy e-mailt hoz létre akkor, amikor egy új rekord jelenik meg a titles táblában:
CREATE TRIGGER emlekezteto
ON titles
FOR INSERT
AS
EXEC master..xp_sendmail 'Kovacs',
'Új bejegyzés érkezett a title táblába.'
A trigger-ek T-SQL utasításokat tartalmaznak, majdnem úgy, ahogy egy tárolt eljárás. A trigger-ek a tárolt eljáráshoz hasonlóan visszaadják a lekérdezések által generált eredménykészleteket. SELECT utasítások használata trigger-ekben nem javasolt, kivéve akkor, ha ezek az utasítások paramétereket töltenek fel. Ez azért van, mert a felhasználók nem várják el, hogy eredményt kapjanak vissza UPDATE, INSERT vagy DELETE utasítás folytán.
A FOR feltétel segítségével meghatározhatjuk azt is, hogy mikor fusson le a trigger:
  • AFTER
A trigger akkor fut le, amikor az utasítás, amely a trigger futtatását indítja, befejeződött. Ha hibaüzenettel leáll, mint pl. constraint szabályszegés, vagy szintaktikai hiba, a trigger nem fut le. AFTER trigger-eket csak táblákra adhatunk meg, nézetekre nem. Több AFTER trigger-t is definiálhatunk a különféle műveletekre (INSERT, UPDATE, DELETE). Ha több AFTER trigger-ünk van, akkor használhatjuk az sp_settriggerorder tárolt eljárást, hogy definiáljuk, melyik fusson először és melyik utoljára. Minden egyéb AFTER trigger határozatlan sorrendben helyezkedik el az első és az utolsó között.
Az AFTER az alapértelmezett az SQL 2000 szerverben. A 7.0-ás SQL szerverben nem adhatjuk meg az AFTER vagy az INSTEAD OF tulajdonságot, hanem minden trigger AFTER üzemmódban működik.
  • INSTEAD OF
A trigger az őt kiváltani hivatott művelet helyett fut le. Ilyen trigger egyaránt megadható nézetre és táblára is. Csupán egy INSTEAD OF trigger műveletet definiálhatunk valamennyi parancstípusra (INSERT, UPDATE, DELETE). Az INSTEAD OF trigger-ek arra használhatók, hogy kiterjesszék az integritás-ellenőrzést az adatértékeken, amelyeket az INSERT és UPDATE utasítások szállítanak. Ez az utóbbi típus már kiterjeszthető a nézetekre is, amelyek normális esetben nem módosíthatók.
Az SQL szerver a trigger műveletek kapcsán két speciális táblát tesz elérhetővé. Ezek az inserted és deleted nevű táblák. A deleted tábla az UPDATE és DELETE műveletek által törölt sorokat tartalmazza, míg az INSERTED tábla az új sorokat foglalja magában. Ezek a táblák segítenek a trigger-ekben végrehajtható ellenőrzési műveletekben.
Példák
A trigger-sorrendek beállítását a következő módon hajthatjuk végre:
sp_settriggerorder @triggername= 'SajatTrigger', @order='first', @stmttype = 'UPDATE'
Az @order paraméter értéke az alábbi három konstans lehet:
  • first – elsőként lefutó
  • last – utolsóként lefutó
  • none – meghatározatlan sorrendiség beállítása
Az @stmttype paraméter pedig INSERT, UPDATE vagy DELETE lehet.
Ha már meghatározott első vagy utolsó trigger létezik egy táblán, akkor ha egy másik trigger-t állítunk be első vagy utolsó lefutásra, akkor az előzőleg ebben a szerepben lévő trigger közbülső, meghatározatlan sorrendű lesz.
A mellékelt script létrehoz egy táblát, hozzá egy nézetet, amely a teljes táblát tartalmazza. Létrehoz továbbá egy INSTEAD OF INSERT trigger-t, amely kiváltja a táblába beszúrás műveletét. Ez azért érdekes, mert amint az alábbi példák mutatják, ha IDENTITY mezőbe értéket szeretnénk megadni, vagy olyan mezőbe, amely más mezőkből származik, akkor hibát kapnánk, viszont az INSTEAD OF trigger segítségével, ha a beszúrást a nézetre alkalmazzuk, akkor a beszúrás sikeres lesz.
INSERT INTO PeldaTabla (Szin, Anyag)
VALUES (N'Red', N'Cloth')
Ez az INSERT utasítás sikeres, mivel szabályos.
INSERT INTO PeldaTabla
VALUES (2, N'Green', N'Wood', N'GreenWood')
Itt hibát kell kapnunk, mivel az első és utolsó mező megadása nem lehetséges.
INSERT INTO InsteadView (PrimaryKey, Szin, Anyag, SzamitottMezo)
VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
Ebben az esetben sikeres lesz a beszúrás, mivel az INSTEAD OF trigger csak a két középső értéket veszi figyelembe.
SELECT PrimaryKey, Szin, Anyag, SzamitottMezo
FROM PeldaTabla
Ezzel az utasítással ellenőrizhetjük, hogy a beszúrás során valóban csak az előbb említett értékeket vette figyelembe, így az adatintegritás biztosított.