Andmebaasid (sessioonõpe) 2023/24 kevad

Trigerite loomine

Funktsioon, mida triger käivitab

Enne kui trigeri loomise juurde saame minna tuleb luua funktsioon, mida triger saaks käivitada.

Triger eeldab teistsuguseid funktsioone võrreldes nendega, milledega me funktsioonide loomise peatükis kokku puutusime.
Trigeri poolt käivitatav funktsioon peab olema loodud PLPGSQL keeles ja tulemus peab olema ilma argumentideta triger tüüpi.

PLPGSQL keel omakorda seab teatud formaadi funktsiooni sisu osas.

Funktsiooni sisu peab tagastama kas NULL või rea struktuuri, millega triger käivitatakse või käivitati.

Struktureeritud tagastamise puhul on valida, kas tagastame uued väärtused (peale trigeri käivitamist) - RETURN NEW; või vanad väärtused (mis olid antud real enne trigeri käivitamist) - RETURN OLD;.

Üldine süntaks trigeri loomiseks:

CREATE OR REPLACE  FUNCTION name () 
RETURNS trigger
LANGUAGE plpgsql
AS $$ 
BEGIN 
function_body;
RETURN (NULL | NEW | OLD);
END;
$$
; 

Loome funktsiooni, mida triger hakkab välja kutsuma. Funktsioon muudab nii ees- kui ka perenime suurtähega algavaks.

CREATE FUNCTION f_suurtaht() 
RETURNS trigger
LANGUAGE plpgsql
AS $$ 
BEGIN 
UPDATE isikud SET eesnimi = initcap(eesnimi), perenimi = initcap(perenimi);
return NULL;
END;
$$;   

Trigeri loomine

Trigeri loomise üldsüntaks:

CREATE [ OR REPLACE ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event }
ON table_name]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION } function_name ()

Vaatame selle lause osasid:

• name - trigerile antav nimi.
• BEFORE - triger käivitatakse enne INSERT, UPDATE, DELETE, TRUNCATE sündmust (event) ehk siis kui tahetakse mingit tegevust blokeerida.
• AFTER - triger käivitatakse pärast INSERT, UPDATE, DELETE, TRUNCATE sündmust (event) ehk siis tahetakse, et tegevus kindlasti aset leiaks, kuid nii, et andmete terviklikkus ei saaks rikutud.
• INSTEAD OF - triger käivitatakse INSERT, UPDATE, DELETE, TRUNCATE sündmuste (event) asemel. Näiteks INSTEAD OF INSERT ON isikud … käivitatakse triger, kui tabelisse isikud lisatakse kirjeid, aga lisamise asemel käivitatakse hoopis antud trigeri poolt käivitatav funktsioon.
• event - tegevus (INSERT, UPDATE, DELETE, TRUNCATE), mis trigeri käivitab. SELECT sündmuse jaoks ei saa trigerit luua.
• table_name - tabel, kus mingi tegevuse (INSERT, UPDATE, DELETE, TRUNCATE) korral triger käivitatakse.
• FOR EACH ROW - triger käivitatakse iga tabeli reale, millega triger seotud on. Näiteks, kui tabelis isikud on 100 rida, siis võib juhtuda (olenevalt tingimustest), et trigerit käivitatatakse 100 korda). Kasutatakse kui on vaja teha mingeid tegevusi teatud ridadel. Näiteks iga kord, kui kustutatakse isikute tabelist kirje, siis luuakse audit kirjed kustutatavate ridade kohta audit tabelisse.
• FOR EACH STATEMENT - triger käivitatakse ainult üks kord. Kui isikute tabelis on 100 kirjet, siis triger käivitatakse ainult korra. Olenevalt trigeri poolt käivitatava funktsiooniga võib tabeli iga rida olla mõjutatud. Kasutatakse üldistema tegevuste jaoks. Näiteks iga öösel mingist tabelist vanemate kui X päeva vanuste logide kustutamiseks.
• WHEN condition - lisatingimus trigeri käivitamiseks või mittekäivitamiseks.

Loome trigeri, mis käivitub iga kord peale (AFTER) isikud tabelisse rea lisamist (INSERT). Triger käivitab funktsiooni, mis muudab kõik nimed selliseks, et ees- ja perenimi algaks suure tähega. Eelnevalt me selle funktsiooni ka lõime. Lepime kokku, et trigeri nime ees on tg_.

CREATE TRIGGER tg_isikud_suurtaht
AFTER INSERT ON isikud
FOR EACH ROW
EXECUTE function f_suurtaht();

Lisame tabelisse isikud uue kirje. Paneme tähele, et ees- ja perenime esitähed on väiketähed.

INSERT INTO isikud (eesnimi, perenimi, isikukood, klubis) VALUES ('sammal', 'habe', '37303140311', 1);

Kontrollime isikute tabelist ja märkame, et loodud kirjel on suured algustähed. Ehk siis kuigi me ei kutsunud välja funktsiooni f_suurtaht(), siis triger käivitas selle automaatselt.

Loodud trigerite funktsioonid asuvad nagu ikka DBeaver rakenduses “Function” menüü all. Nagu indeksid, nii on aga ka trigerid konkreetse tabeli juures, millele need on loodud:

Loengus rääkisime, et loodud triger pole optimaalne, vaid aeglustab andmebaasi tööd. Otstarbekam oleks siin luua trigeri funktsioon selliselt:

CREATE FUNCTION f_suurtaht1() 
RETURNS trigger
LANGUAGE plpgsql
AS $$ 
BEGIN 
UPDATE isikud SET NEW.eesnimi = initcap(NEW.eesnimi), NEW.perenimi = initcap(NEW.perenimi);
return NEW;
END;
$$; 

ja selle alusel BEFORE triger, mis enne lisamist muudab konkreetse lisatava kirje korrektseks (kuula loengust lisa, miks nii on otstarbekam).

CREATE TRIGGER tg_isikud_suurtaht1
BEFORE INSERT ON isikud
FOR EACH ROW
EXECUTE function f_suurtaht1();

Trigeri loomise näide

CREATE FUNCTION f_kustuta_isikud()  
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF (EXISTS (SELECT * FROM Isikud, vana WHERE Isikud.klubis = vana.id)) THEN 
     DELETE FROM Isikud WHERE Isikud.klubis = vana.id;  
    END IF;
 END;
 $$;

CREATE TRIGGER tg_kustuta_klubi_isikutega 
AFTER DELETE ON Klubid
REFERENCING OLD TABLE AS vana
FOR EACH ROW
EXECUTE FUNCTION f_kustuta_isikud();

DELETE FROM Klubid WHERE id=1;

BEFORE triger nagu öeldud eelnevalt püüab parandada, kui midagi on valesti ja siis alles tegutseb. AFTER triger lubab teha, aga siis kui selgub, et midagi on valesti saanud, siis parandab. Loome BEFORE trigeri, mis kontrolliks, kas turniiri algusaeg poleks suurem lõppajast ja kui on, siis lõppajaks paneks algusaja. NB! Reaalses elus peaks andma veateate ja laskma sisestajal vea parandada.

Kuna meil on selle tabeli peal kitsendus, siis selleks, et see kitsendus ei hakkaks meil teise trigeri tööd segama, esmalt kustutame kitsenduse:

ALTER TABLE Turniirid DROP CONSTRAINT ajakontroll;

Nüüd loome funktsiooni, mida triger välja kutsub:

CREATE FUNCTION f_turniiriaeg1()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.alguskuupaev>NEW.loppkuupaev THEN
    New.loppkuupaev:=NEW.alguskuupaev;
    END IF;
   RETURN NEW;
END;
$$;

Näeme, et siin kasutame NEW.alguskuupaev ja NEW.loppkuupaev. NEW on kirje tüüpi muutuja, mis esindab lisatavat või uuendatavat rida enne lisamist/uuendamist. Siin me kontrollisime, kas alguskuupäev on suurem kui lõppkuupäev ning kui nii on, siis triger lõppkuupäevale omistaks alguskuupäeva väärtuse. BEFORE trigerina enne lisamist toimub võrdsustamine, seega kui lisatakse võttes kirje muutujast NEW, siis seal on juba need kaks kuupäeva võrdsustatud ja tabelisse turniirid lisatakse juba võrdsustatud kuupäevad.

Sarnaselt kasutatakse kirjetüüpi muutujat OLD kustutamise (DELETE) korral ning UPDATE puhul võib kasutada nii NEW kui ka OLD muutujat.

Nüüd loome trigeri, mis käivituks nii lisamise kui ka muutmise korral:

CREATE TRIGGER tg_turniiriaeg1
BEFORE INSERT OR UPDATE ON turniirid
FOR EACH ROW
EXECUTE FUNCTION f_turniiriaeg1();

Kontrollime trigeri toimimist. Lisame turniiri Prooviturniir, mille alguskuupäevaks paneme 02.02.2023 ja lõppkuupäevaks 02.02.2022.

INSERT INTO Turniirid (nimi, alguskuupaev, loppkuupaev) 
VALUES ('Prooviturniir', '02.02.2023', '02.02.2022');

Vaatame turniiride nimekirja, seal on

ehk siis triger on võrdsustanud need kuupäevad.

Ühe ja sama trigeri saame me luua nii BEFORE kui ka AFTER trigerina. Sarnaselt käituv triger AFTER trigerina oleks järgmine. Esmalt loome nagu ikka funktsiooni ja seekord ka sellise, mis väljastab teate, et on selline viga avastatud ning mida on selle veaga tehtud:

CREATE FUNCTION f_turniiriaeg2() 
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.loppkuupaev < NEW.alguskuupaev THEN
   RAISE NOTICE 'Turniiri algusaeg ei saa olla väiksem kui lõppaeg. Need ajad võrdsustatakse. Kui siiski on turniir mitmepäevane, palun muutke lõppkuupäeva!';
   UPDATE turniirid SET loppkuupaev = alguskuupaev
       WHERE id = NEW.id;
   END IF;
   RETURN NEW;
END;
$$;

Siin toimub sama kontroll, kuid kuna tahame luua AFTER trigerit, siis see tähendab, et turniiride tabelisse on juba vastav kirje lisatud. Triger pani selle aga ka muutujasse NEW. Nüüd võrdsustatakse turniiride tabelis sellise turniiri kuupäevad, mille id on võrdne selle id’ga, mis on muutujas NEW.

CREATE TRIGGER tg_turniiriaeg2
AFTER INSERT OR UPDATE ON turniirid
FOR EACH ROW
EXECUTE FUNCTION f_turniiriaeg2(); 

Taas võite proovida lisada mõnda turniiri, mille lõppkuupäev on väiksem kui alguskuupäev. Selleks peate aga kustutama BEFORE trigeri, sest vastasel juhul BEFORE triger on enne lisamist juba muutnud kirje õigeks ja AFTER trigeril polegi midagi enam vaja muuta.

Nagu ka protseduuride korral, nii ka trigerites väljastatakse sageli teateid RAISE NOTICE, RAISE INFO, RAISE WARNING jms abil. RAISE EXCEPTION ehk vaikimisi RAISE tõstatab veateate, mis tavaliselt katkestab transaktsiooni. Teised teated genereerivad ainult erineva prioriteeditasemega sõnumeid. Lisalugemist teadete kohta PostgreSQL dokumentatsioonis.

Vaata videost, kuidas luua trigerit:

Trigeri kustutamine ja trigeri käivituse keelamine

Trigeri kustutamisel peab lisaks trigeri nimele viitama ka tabelile, millega triger on seotud. Näiteks kui tahame kustutada loodud turniiriaja trigerit, siis

DROP TRIGGER tg_turniiriaeg1 ON turniirid;

Trigerit ei pea aga tingimata kustutama, kui leitakse, et seda enam vaja pole. Saame ka seadistada, et tegevustega trigerit välja ei kutsuta. Näiteks kui me ei soovi, et käivituks triger tg_turniiriaeg2, siis kirjutame:

ALTER TABLE turniirid
DISABLE TRIGGER tg_turniiriaeg2;

Kui soovime mingi tabeli korral kõiki sellele tabelile loodud trigerite väljakutsumist keelustada, siis

ALTER TABLE tabeli_nimi
DISABLE TRIGGER ALL;

Ja hiljem, kui siiski neid trigereid soovitakse käivitada, siis seda saab taas võimaldada:

ALTER TABLE tabeli_nimi
ENABLE TRIGGER trigeri_nimi |  ALL;

Kontrollimaks, kas said trigerite loomisest aru ava soovi korral test.
AVA TEST

Link PostgreSQL dokumentatsioonile