8. Az MSSQL szerver
8.5. A triggerek
5 Eljárások és függvények megismeréséhez javaslok bármely, programozással foglalkozó jegyzetet vagy szakkönyvet.
A triggert és a benne tárolt, lefuttatandó eljárásokat egyszerű tranzakcióként kezeljük, amit szinte bárhonnan visszavonhatunk (roll back).
Triggerek használatakor vegyük figyelembe, hogy a ROLLBACK TRANSACTION parancs hívásakor a teljes művelet visszavonódik. Azonban a triggerekben lehetőleg kerüljük el a ROLLBACK TRANSACTION-okat, mert ezek plusz munkát és időt jelentenek, mert amely műveleteket az adott pontig elvégeztük, azt mind vissza kell vonni. Ennek negatív hatása van a működésben.
8.5.1. A triggerek használata
A triggereket akkor használhatjuk jól, ha az adatintegritás szintje alacsony, azaz kevés és nem túl jelentős adatellenőrzést kell végrehajtanunk. A triggerek elsődleges feladata és előnye, hogy más, pl. a CHECK megszorításnál6 több és komplexebb megoldást nyújtanak adatok ellenőrzésére és így az adatintegritás megőrzésére is.
Alkalmanként az alkalmazásban előfordulhatnak különböző közbeni hibák, melyekről a rendszer értesítést küld a felhasználónak. Triggerek használatával lehetőség van ezen üzenetek lecserélésére, melyek rugalmasabbá teszik adatbázisunkat. Az adatokat módosítás előtt és utáni állapotukban is össze tudja hasonlítani akár különböző táblák között is. (Triggerek használatával azt is meg tudjuk állapítani, hogy az INSERT, UPDATE vagy DELETE parancsok kiadása után egy tábla mely sorait érintette változás.)
Vegyük észre, hogy a triggerek mindig abban az adattáblában érvényesek és hajtódnak végre, amelyben azt definiáltuk, továbbá hogy a triggerek az INSERT, UPDATE és DELETE utasítások után futnak le automatikusan. Pl. egy UPDATE utasítás során a tábla egy sora frissül, majd ezután a trigger ezen a táblán automatikusan lefut.
Egy táblának nem csak egy triggere lehet, hanem akár több is, és egy triggert nem csak egy egyszerű eseményre lehet alkalmazni, hanem akár összetettebb események kezelésére is.
A triggerek megírásához és futtatásához engedélyre van szükségünk, melyeket a rendszer az egyes felhasználóknak automatikusan biztosít. Néhány felhasználók esetében a triggerek jogosultságok hiányában nem futnak le. Ha egy trigger lefuttatásához nincs jogosultságunk, a teljes művelet érvénytelenítődik a trigger meghívása után. Mint ahogy a trigger eljárás, így a trigger lefutásának nincs visszatérési értéke (szemben a függvényekkel).
A triggerek Transact-SQL utasításokat tartalmaznak úgy, mint a tárolt eljárások.
Triggereket a CREATE TRIGGER utasítással tudunk létrehozni. Meg kell határoznunk a tábla nevét, amire a triggert alkalmazni szeretnénk, majd a futtatni kívánt eseményeket, és egyéb esetleges utasításokat írhatunk le.
CREATE TRIGGER [owner.] trigger_nameON [owner.] table_name[WITH ENCRYPTION]{FOR | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}AS[IF UPDATE (column_name)...] [{AND | OR}
UPDATE (column_name)...] sql_statements}
Fontos megjegyeznünk, hogy ha olyan triggert hozunk létre, melynek neve ugyanaz, az eredeti triggert felül fogja írni.
Ha triggert írunk, figyeljünk oda arra, hogy triggerünk a következő SQL utasításokat nem tartalmazhatja:
1. ALTER DATABASE
7. LOAD LOG 8. RECONFIGURE 9. RESTORE DATABASE
10. RESTORE LOG
8.5.2. Néhány jó utasítás a triggerek kezelésére
Ha meg szeretnénk tekinteni a triggereket és a hozzájuk kapcsolódó táblákat, használjuk a sp_depends
<tablename> rendszerbéli tárolt eljárást. A triggerek tartalmának megtekintéséhez a sp_helptext <triggername>
rendszer béli tárolt eljárást használhatjuk.
A példa az Employees táblában hoz létre egy triggert, mely kiköti, hogy a felhasználó egy időben, egyszerre csak egy munkavállalót törölhessen ki a táblából. A trigger mindannyiszor lefut valahányszor rekordok törlésére kerül a sor. Ekkor ellenőrzi, hogy a DELETE utasítás hány rekordot akar kitörölni. Ha ez a szám nagyobb
IF (SELECT COUNT(*) FROM Deleted) > 1 BEGIN
A következő DELETE utasítás működésbe hozza a triggert és megakadályozza, hogy a tranzakció lefusson.
DELETE FROM Employees WHERE EmployeeID > 6
A következő DELETE utasítás működésbe hozza a triggert de nem akadályozza meg, hogy a tranzakció lefusson.
DELETE FROM Employees WHERE EmployeeID = 6
8.5.3. Trigger módosítása
Ha egy triggert módosítani szeretnénk, ezt anélkül tehetjük meg, hogy előtte törölnünk kellene azt. Ha pl. egy trigger egy INSERT utasítás lefutásakor automatizálódik, s a triggerben az INSERT parancsot lecseréljük UPDATE-ra, a módosított trigger az UPDATE utasítás lefutásakor fog aktivizálódni.
A parancs szintaktikája:
ALTER TRIGGER trigger_nameON table [WITH ENCRYPTION]{{FOR {[,] [DELETE] [,] [UPDATE]
[,][INSERT]}[NOT FOR REPLICATION]ASsql_statement [...n] }|{FOR {[,] [INSERT] [,] [UPDATE]}[NOT FOR REPLICATION]ASIF UPDATE (column)[{AND | OR} UPDATE (column) [,...n]]sql_statement [...n] }}
Ez a példa a korábban említett példát módosítja, amikor is egyszerre csak egy rekordot törölhettünk ki egy táblából. A módosítás során az egyidejű törlés maximális számát 6-ra módosítjuk.
Use Northwind GO
CREATE TRIGGER Empl_Delete ON Employees FOR DELETE
AS
IF (SELECT COUNT(*) FROM Deleted) > 6 BEGIN
RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTION
END
A létrehozott triggereket nem kell feltétlenül alkalmaznunk. Megtehetjük azt is, hogy ideiglenesen deaktiváljuk őket. Létezni fognak a triggerek, csak passzívak maradnak. Ugyan így a passzív triggereket aktivizálni is tudjuk.
Erre a
ALTER TABLE table {ENABLE | DISABLE} TRIGGER {ALL | trigger_name[,…n]}
szintaktikájú parancsot használhatjuk.
Ha pedig nincs szükségünk a triggerre, azt törölhetjük is végleg. (Megjegyzés: ha egy táblát törlünk, a hozzá kapcsolódó is trigger automatikusan törlődik).
Szintaktika:
DROP TRIGGER trigger_name
A fontosabb trigger kezelő parancsok után azt nézzük meg, hogyan is működik a trigger valójában.
A triggerek, mint említettem, minden egyes INSERT, UPDATE és DELETE utasítás után automatikusan lefutnak. Egy ilyen utasítás során a módosítások érvényesülnek a táblában, és egy új, ún. trigger tábla jön létre, melyben a módosult sorok vannak. Az eredeti tábla azonban még csak logikailag érhető el, a változások fizikailag nem érintik a táblát. Erre való a trigger tábla, hogy a változásokat ebbe a táblába végezze el először.
Ezen logikai táblában aztán a trigger elvégzi a benne foglalt eljárásokat, utasításokat és hogy ezek milyen változást idéznek elő az adatokban. Ekkor összehasonlítódik az eredeti (változást érintő) és a trigger tábla, majd ha az trigger lefutása után nincs hiba, a változások végérvényesen belekerülnek a logikai, eredeti táblába.