Delphi - MS SQL adatbázis kezelés Delphi-ből

A triggereket az INSERT, UPDATE és DELETE utasításokhoz rendelhetjük hozzá. A trigger és az az adatmódosító utasítás, ami a trigger lefutását kiváltja, egy tranzakciót jelent, amit a triggerből is visszagörgethetünk (rollback). Hiba esetén az egész tranzakció automatikusan visszagörög.

Pontokba szedve a triggereket a következőkre használhatjuk:

Egymásba ágyazott triggereket alkalmazva egy füst alatt letudhatunk egy több táblát is érintő adatmódosítást. Ha például a pubs adatbázis title táblájához delete triggert rendelünk, akkor a triggeren belül törölhetjük a title táblához kapcsolódó táblák (titleauthor, sales, stb.) megfelelő rekordjait. Ezek a táblák a title_id mezőn keresztül kapcsolódnak egymáshoz. A triggerben hivatkozott táblákhoz újabb triggerek kapcsolódhatnak. Ez a kaszkádolás.
A triggerek alkalmasak adathibák és hivatkozási függőségek (referenciális integritás) megsértésének kiszűrésére. Ilyen jellegű hiba észlelésekor visszagörgethetik a tranzakciót. Például insert triggert hozhatunk létre a titleauthor táblán, ami minden beszúráskor ellenőrzi, hogy létezik-e az újonnan beszúrt rekord title_id mezeje a titles táblában is. Ha nem, úgy egy nem létező könyvre hivatkoznánk, ilyenkor vissza kell görgetnünk a tranzakciót és meghiúsítanunk a beszúrást. (A korábban már tárgyalt idegen kulcs constraint egyébként ugyanerre való.)
A triggerekkel sokkal összetettebb korlátozásokat valósíthatunk meg, mint a CHECK constraint-tel. Ugyanis egy triggerben tetszőleges számú más táblákra is hivatkozhatunk, amit nem tehetünk meg a CHECK-ben.
A triggerekben összehasonlíthatjuk a tábla módosítás előtti és utáni állapotát, és az eredménytől függően folytathatjuk tovább a feldolgozást.
Egy táblára több ugyanolyan típusú (pl. INSERT) triggert is definiálhatunk, így az esemény bekövetkeztekor több különböző akciót is indíthatunk.

Az SQL Server tehát két különböző eszközt kínál az adatbázis integritás megőrzésére illetve
az üzleti logika megvalósítására; a constrainteket és a triggereket. Bizonyos szituációkban az egyik mechanizmus előnyös, másokban a másik. A triggerek legfontosabb előnye a constraint-ekkel szemben, hogy mivel T-SQL kódoláson keresztül valósul meg, igen bonyolult és összetett logikákat programozhatunk le segítségükkel. Így megvalósíthatjuk például bármelyik constraint-et is, ennek ellenére vannak feladatok, amiket inkább constraint-ekkel oldunk meg.
A legalacsonyabb szintű, ún. egyed integritást indexekkel, és így PRIMARY KEY és UNIQUE constartint-ek segítségével valósítjuk meg. A tartomány szintű integritást legegyszerűbben a CHECK constraint biztosítja, és végül a referenciális integritás felügyelete általában a FOREIGN KEY constratint dolga.

A constraint-ek tehát mindaddig remek eszközök, amíg az alkalmazásunk funcionális igényeit ki tudják elégíteni. Például:

A FOREIGN KEY constraint nem támogatja a kaszkád megoldást, és egy mező értékét csak annak alapján képes ellenőrizni, hogy az szerepel-e pontosan egy másik oszlopban. Ha az alkalmazásunk például azt kívánja, hogy egy törléskor a kapcsolódó mezők is törlődjenek, illetve a referenciális integritás feltétele bonyolultabb, akkor triggert kell használnunk.
A CHECK constraint az oszlop értékeit egy olyan logikai kifejezés alapján tudja csak ellenőrizni, amiben legfeljebb ugyanazon tábla mezői szerepelnek. Ha az alkalmazásunk igénye az, hogy egy adott oszlop értékeit egy másik tábla oszlopának vagy oszlopainak számított értéke alapján kell ellenőrizni, szintén triggert kell használnunk.
Constraint-ek alkalmazásakor hiba esetén az SQL Server a beépített hibaüzeneteit küldi el az alkalmazásnak. Ha a saját hibaüzeneteinket szeretnénk látni, vagy egy magasabb szintű hibakezelést szeretnénk megvalósítani, triggereket kell használnunk.

Trigger létrehozása

Trigger létrehozásakor meg kell adnunk annak nevét; a táblát amin létrehozzuk; azt, hogy az INSERT, UPDATE vagy a DELETE utasítások közül melyik aktiválja; és meg kell írnunk magát a tárolt eljárást, ami majd végrehajtódik. A szintaktika a következő:

CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
    {FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
        AS
            sql_statement
    }

vagy

CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
    {FOR { [INSERT] [,] [UPDATE] }
        AS 
        {IF UPDATE (column) [{AND | OR} UPDATE (column)] }
            sql_statement
        }
    }	
}

Lássuk a példákat!

1. Ha a pubs adatbázis titles táblájába új rekord kerül, vagy egy rekord megváltozik, szeretnénk egy figyelmeztetést az eseményről. Ehhez tudnunk kell, hogy üzeneteket az sp_addmessage utasítással készíthetünk.

USE master
EXEC sp_addmessage 50009, 16, 'A titles tábla 
    tartalma megváltozott!'

Az első paraméter az üzenet sorszáma, ezzel fogunk rá hivatkozni. Az 50001 alatti sorszámok az SQL Server saját üzeneteinek van fenntartva. A második paraméterrel az üzenetet kiváltó esemény, rendszerint hiba súlyosságát jellemezhetjük egy 1 és 25 közötti számmal. Végül megadtuk az üzenet szövegét.

USE pubs
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE 
AS 
  RAISERROR (50009, 16, 10)
GO

A trigger neve tehát reminder, a titles táblán hoztuk létre, és az insert vagy update utasítások hatására hajtódik végre. A trigger törzse egyetlen utasítás, amiben az alkalmazásnak az 50009-es üzenetet jelezzük vissza.

2. A titles tábla bármilyen módosítása esetén mailt szeretnénk küldeni az 'Admin' nevű felhasználónak. Ehhez ismét csinálunk egy reminder nevű triggert, de hogy ne legyen gond az egyező név, először megnézzük a sysobjects táblában, hogy van-e már ilyen nevű trigger, és ha van, töröljük a DROP TRIGGER utasítással.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'reminder' AND type = 'TR')
    DROP TRIGGER reminder
GO

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE 
AS
  EXEC master..xp_sendmail 'Admin',
      'A titles tábla tartalma megváltozott!'
GO

3. Két speciális táblát használhatunk a triggerekben, a deleted és az inserted táblákat. A deleted tábla tartalmazza azokat a sorokat, amelyek érintettek egy DELETE vagy egy UPDATE utasítás esetében, azok a sorok, amiket törlünk a táblából, a deleted táblában jelennek meg. A deleted táblában és a trigger táblájában soha nem szerepelnek egyszerre ugyanazok a sorok.
Az inserted táblába azok a sorok kerülnek, amik INSERT vagy UPDATE utasítások esetében érintettek. Az új sorok egyszerre kerülnek az inserted és a trigger táblába.

Az UPDATE utasítás úgy működik, mint egy DELETE és egy INSERT egymás után végrehajtva, ezért szerepel az előbbi részben mind az INSERT, mind a DELETE mellett. A régi sorok először a deleted táblába kerülnek, majd az új sorok bekerülnek a trigger táblájába és másolat készül róluk az inserted táblába. Így a triggerben megvizsgálhatjuk, hogy mely sorokat érintette a módosítás, és ettől függően akár vissza is görgethetjük az egész tranzakciót. Minden esetben létrejön mindkét segéd tábla, de INSERT esetén a deleted tábla, DELETE esetén az inserted tábla üres lesz.

A pubs adatbázisban az employee tábla tárolja, hogy a munkatársak milyen munkakörben dolgoznak (job_id), és minden munkatárshoz meg lehet adni egy szintet (job_lvl), ami a munkakörének megfelelő fizetését egy kicsit módosítja. Így differenciáltabban tudunk fizetést képezni annál, mintha egyszerűen csak a munkakörhöz rendelnénk egy összeget. Hogy az egyes munkakörökhöz milyen határok között lehet job_lvl-t rendelni, az a jobs táblában van eltárolva (min_lvl, max_lvl). Írjunk olyan triggert az employee táblára, ami a munkatárs beszúrásakor vagy módosításakor ellenőrzi, hogy a megadott job_lvl a két határérték között van-e. Ha nincs, a tranzakció nem mehet végbe.

CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Négy változót készítünk */
DECLARE @min_lvl tinyint,
    @max_lvl tinyint,
    @emp_lvl tinyint,
    @job_id smallint
/* Az employee tábla és az inserted tábla metszete adja a
     módosított sorokat, ehhez hozzávesszük a job_id-nek
     megfelelő szinteket is a job táblából. */
SELECT @min_lvl = min_lvl, 
    @max_lvl = max_lvl, 
    @emp_lvl = i.job_lvl,
    @job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id 
    JOIN jobs j ON j.job_id = i.job_id
/*Az 1-es job_id-jű munkakör fix fizetésű, az
     emp_lvl csak 10 lehet.*/
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
    RAISERROR ('A 1 azonosítójú munka csak 10-es 
        szintű lehet.', 16, 1)
    ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
    RAISERROR ('Az %d azonosítójú munkakör szintje
        %d és %d.közé kell essen.', 16, 1, @job_id, 
        @min_lvl, @max_lvl)
    ROLLBACK TRANSACTION 
END

További műveletek triggerekkel

Ha módosítani szeretnénk egy triggert, töröljük ki és hozzuk létre újból. Törölni a DROP TRIGGER utasítással lehet. A tábla törlésekor a táblán értelmezett triggerek is megszűnnek. Egy lépésben is módosíthatjuk a triggert az ALTER TRIGGER utasítással. Paraméterezése megegyezik a CREATE TRIGGER utasításéval. Lehetőségünk van átnevezni a triggert az sp_rename tárolt eljárás hívásával. Az utasítás szintaxisa:

sp_rename név, új_név

Az utasítás nemcsak triggerek, hanem bármilyen általunk létrehozott adatbázis objektum nevét is meg tudja változtatni.

Ha kíváncsiak vagyunk, hogy egy táblán milyen triggerek vannak, az sp_helptrigger táblanév tárolt eljárást használhatjuk. Ha a trigger szövegét szeretnénk látni, az sp_helptext triggernév utasítást kell meghívnunk.

Egy sor - több sor

Fontos megjegyezni, hogy egy táblán végrehajtott UPDATE vagy DELETE, ritkábban INSERT utasítás általában egyszerre több sort is érint. A trigger utasításait ennek megfelelően kell megírni. Nézzünk erre egy példát. Ha a pubs adatbázisban szereplő könyvesboltok közül valamelyikben megrendelnek néhány könyvet, akkor ez a sales táblában kerül rögzítésre. Hogy a könyvekből mennyi fogy, a titles tábla ytd_sales mezeje tartalmazza. Írjunk hát egy INSERT triggert a sales táblára, hogy minden eladáskor a titles táblába is legyen vezetve, hogy éppen mennyi könyv kelt el.

CREATE TRIGGER intrig
ON sales
FOR INSERT AS

    UPDATE titles
    SET ytd_sales = ytd_sales + qty
    FROM inserted
    WHERE titles.title_id = inserted.title_id

Ez a trigger kiválóan működik, ha mindig csak egy elemet szúrunk be a sales táblába. Jól látszik, hogy az eladott mennyiséggel (qty) megnöveljük a ytd_sales mezőt a megfelelő (title_id) rekordban. Ha egyszerre több rekordot szúrnánk be, a trigger nem működne jól, mert a ytd_sales + qty kifejezés csak egyetlen értéket ad eredményül, nekünk pedig minden title_id-hez más-más értékre van szükségünk. Fogalmazzuk hát át a feladatot először szóban: Módosítsuk a titles tábla minden olyan rekordját, amelyik érintett a beszúrásban, vagyis szerepel az inserted táblában. Ez lesz a WHERE feltételünk. A módosítás pedig úgy történjen, hogy egy adott titles-beli rekordhoz (title_id) leválogatjuk az összes azonos title_id-jű beszúrt rekordot, és azoknak összesítjük a qty mezejét. Az új trigger így néz ki:

CREATE TRIGGER intrig
ON sales
FOR INSERT AS

    UPDATE titles
    SET ytd_sales = ytd_sales +
        (SELECT SUM(qty)
        FROM inserted
        WHERE titles.title_id = inserted.title_id)
    WHERE titles.title_id IN
        (SELECT title_id FROM inserted)

Ez a változat jól működik mind egy, mind több sor beszúrása esetén, azonban a halmazművelet (IN) és a beágyazott query egysoros insertnél teljesen felesleges. Az optimális megoldáshoz a triggerben először meg kell vizsgálni, hogy hány rekord érintett a műveletben, aztán vagy az egyik, vagy a másik módosítást végrehajtani. Ezt a @@ROWCOUNT függvény meghívásával tehetjük meg egyszerűen. A trigger végső változata:

CREATE TRIGGER intrig
ON sales
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
    UPDATE titles
    SET ytd_sales = ytd_sales + qty
    FROM inserted
    WHERE titles.title_id = inserted.title_id
END
ELSE
BEGIN
    UPDATE titles
    SET ytd_sales = ytd_sales + 
    (SELECT SUM(qty)
        FROM inserted
        WHERE titles.title_id = inserted.title_id) 
    WHERE titles.title_id IN 
        (SELECT title_id FROM inserted)
END

Példaprogram

A példaprogramban kipróbáljuk a harmadik példában mutatott employee_insupd nevű triggert. Még egyszer röviden: A trigger a munkatárs beszúrásakor vagy módosításakor ellenőrzi, hogy a megadott job_lvl a jobs táblában specifikált két határérték között van-e. Ha nincs, a tranzakció nem mehet végbe.

A program első futtatásakor nyomjuk meg a Trigger létrehozása gombot, ekkor kitöröljük az ilyen nevű triggert, ha létezik és létrehozzuk a saját változatunkat. Az ablakban láthatjuk az employee táblát, fölötte beszúrhatunk egy új alkalmazottat a mezők kitöltésével. Az egyes értékeket TEdit komponensekből gyűjtjük össze, és egy egyszerű ellenőrzés után egy query komponensben megvalósított INSERT utasítással próbáljuk meg beírni a táblába. Ha a job_lvl értéket rosszul adtuk meg, akkor a trigger visszagörgeti a tranzakciót, és a raiserror utasítással hibát generál. Ezt a Delphiben egy try..except szerkezetben fogjuk el, és egyszerűen egy raise utasítással írjuk ki.

Egyre vigyázzunk, az alkalmazott azonosítójának a formátuma: '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' vagy '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'.

MS SQL adatbázis kezelés Delphi-ből

14. rész