03 PL/pgSQL triggery

Obsah článku:

1. 1. Triggery V PL/pgSQL
   1. 1.1. Triggery s WHEN
   2. 1.2. DDL - Event Triggery
   3. 1.3. Zdroje:

Triggery V PL/pgSQL

„Je to mechanizmus, ktorý pri splnení dopredu definovaných podmienok automaticky zabezpečí vykonanie dopredu definovanej akcie“. 1
Hlavným rozdielom medzi funkciou a triggerom je, že funkcie sa môžu volať na serveri a vykonávajú nejaké operácie, voláme ich keď chceme získať nejaké výsledky, zatiaľ čo triggery môžu predstavovať “obmedzenie” a volajú sa pri modifikácii tabuľky. Oboje se používa pre zjednodušenie práce s databázou, pretože sú funkcie uložené na serveri a nemusia sa zakaždým zložito riešiť na strane klienta. V databázach je trigger objekt, prepojený na tabuľku. Ten sa aktivuje, keď nastane určitá udalosť (napr. INSERT, UPDATE či DELETE) v tabuľke databázy. Príkaz CREATE TRIGGER vytvorí nový spúšťač. Podľa syntaxe:

CREATE TRIGGER meno
	{ BEFORE | AFTER | INSTEAD OF }
	{ event [ OR ... ] }
	ON meno_tabulky
	[ FOR [ EACH ] { ROW | STATEMENT } ]
	EXECUTE PROCEDURE meno_funkcie ( argumenty )

Funkciu musíme priradiť triggeru a tabuľke príkazom CREATE TRIGGER. Pri definovaní triggera musíme zadať, akej tabuľky alebo pohľadu sa trigger týka, kedy sa má spustiť (pri akej udalosti) a akú funkciu má spustiť. Je teda potrebné najprv deklarovať funkciu, ktorú chceme spustiť a až potom trigger. Funkcia nemôže mať žiadny argument a musí vracať dátový typ trigger. Argumenty triggera sú totiž funkcii odovzdané cez špeciálne premenné TG_ARGV. V zásade môžeme vytvoriť dva základné typy: tie, ktoré sa vykonajú pred spustením príkazu (BEFORE), vyvolaného triggerom, a tie, ktoré sa spúšťajú po vykonaní príkazu (AFTER). Akciou, ktorá trigger spustí, môže byť INSERT, UPDATE, DELETE alebo TRUNCATE. Pokiaľ napríklad vložíme do tabuľky nejaké dáta, spustia sa triggery definované pre túto tabuľku, ktoré sa majú spustiť pri udalosti napr. INSERT. Trigger môže spúšťať funkciu pre každý riadok tabuľky (v ktorej došlo k udalosti, ktorá trigger spustila), t. j. FOR EACH ROW, alebo iba raz pre spustený SQL príkaz, t. j. FOR EACH STATEMENT. Trigger je možné zrušiť pomocou príkazu:

DROP TRIGGER meno_triggera on meno_tabulky;

Každá trigger funkcia musí spĺňať nasledujúce požiadavky:
Návratový typ: Funkcia musí vracať dátový typ trigger.
Návratová hodnota: Trigger funkcia musí vracať určitú hodnotu.
Žiadne argumenty: Argumenty triggera sú funkcii odovzdané cez špeciálne premenné TG_ARGV.

Triggery je možné z hľadiska použitia rozdeliť na 4 skupiny:

Table-level triggers:
Vykoná aktivitu pred (BEFORE) alebo po (after) DML udalostiach ako vloženie (INSERT), aktualizácia (UPDATE) a zmazanie (DELETE).

View-level triggers:
Používa sa pri pohľadoch (VIEW) často tu využívame nahrádzajúce (INSTEAD OFF) triggery. Prostredníctvom nich môžeme jednoducho vytvoriť aktualizovateľné pohľady. Tento druh triggera sa spustí namiesto danej akcie. Napríklad Instead UPDATE sa spustí namiesto pôvodného UPDATE. Takže musíme aktualizáciu zavolať z triggera.

DDL Database-level triggers:
Dokážu sa aktivovať po zapnutí, či vypnutí databázy. Pre príklad, keď databáza štartuje, môžeme spustiť príkazy na test dostupnosti ostatných databáz alebo web. služieb. Pred vypnutím DB, môžeme upozorniť ďalšie databázy a webové služby, že databáza bude offline.

Session-level triggers:
Využívame ich na ukladanie špecifickych informácií. Ako je prihlasovanie/odhlasovanie užívateľov, poprípade môžeme spustiť kód, ktorý obsahuje preferencie užívateľa a načíta ich do pamäte pre rýchlejší prístup. Po ukončení session, môže trigger ukladať tieto preferencie pre budúce použitie.2

Trigger funkcia má po svojom spustení automaticky vytvorené niekoľko špeciálnych premenných:
NEW
Obsahuje riadok s novou verziou hodnôt (iba pre INSERT a UPDATE pri akcií DELETE je NULL).
OLD
Obsahuje starý riadok (pred modifikáciou / zmazaním). Pri akcií INSERT je NULL.
TG_NAME
Meno triggera.
TG_TABLE_NAME
Meno tabuľky, nad ktorou sa trigger spúšťa.
TG_RELNAME
Nesie názov tabuľky prevedený na malé písmena, na ktorej sa trigger spustil.
TG_OP
Meno akcie, ktorá trigger spustila (INSERT/UPDATE/DELETE/TRUNCATE).
TG_WHEN
Čas kedy sa trigger spustil (AFTER/BEFORE/INSTEAD OF)
TG_LEVEL
ROW alebo STATEMENT
TG_NARG
Počet argumentov odovzdaných funkcií.
TG_ARG[]
Argument triggera typu TEXT array. Indexovanie začína od nuly a zlý index vráti NULL.
TG_TAG
Používa sa u DDL event triggerov. Táto premenná obsahuje meno príkazu, ktorý spustil trigger.
TG_EVENT
Používa sa u DDL event triggerov. Premenná obsahuje meno udalosti, ktorým môže byť ddl_command_start , ddl_comman_end a sql_drop .

Prvá vytvorená trigger funkcia bude zameraná na spätnú väzbu pre klienta databázy. Zakaždým keď sa trigger aktivuje poskytne spustená funkcia o sebe niekoľko informácií.

Trigger funkcia pre ukážku špeciálnych premenných:

CREATE OR REPLACE FUNCTION notify_trigger()
RETURNS TRIGGER AS $$
BEGIN
	RAISE NOTICE
		'Ahoj, som funkcia - %,
		zavolaná ako % % % na tabuľke %',
			TG_NAME,
			TG_LEVEL,
			TG_WHEN,
			TG_OP,
			TG_TABLE_NAME;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Je potrebné aby funkcia vracala nejakú hodnotu a to aj v prípade, že to funkčne nie je potrebné. Bez tejto návratovej hodnoty by funkcia skončila chybou a operácia INSERT by sa nevykonala. Okrem RETURN NEW môžeme tiež použiť RETURN NULL, tieto návratové hodnoty sú pri AFTER triggeroch ignorované.
Vytvoríme trigger, ktorý spustí funkciu notify_trigger() raz pre každý riadok (EARCH ROW), po každom pridaní (INSERT) nového riadka do tabuľky.

CREATE TRIGGER notify_insert_trigger
	AFTER INSERT ON tst_tabulka
	FOR EACH ROW
EXECUTE PROCEDURE notify_trigger();

Použitie:

teoria_tst=# INSERT INTO tst_tabulka
	VALUES
	(3, 'Ivan', 1990, 'Vavrecka 240',
	'Namestovo', 'SK', '02901');
NOTICE:
Ahoj, som funkcia - notify_insert_trigger,
zavolaná ako ROW AFTER INSERT na tabuľke tst_tabulka
 1 row affected in 21ms
INSERT 0 1

Triggery s WHEN

Klauzulu WHEN používame pre spustenie triggera s podmienkou, pri ktorej sa má trigger spustiť. WHEN je podobný WHERE v SQL dotazoch. Podmienka sa musí vyhodnotiť ako TRUE, pokiaľ sa vyhodnotí FLASE funkcia sa nespustí. Môžeme ju použiť, pokiaľ chceme spustiť trigger funkciu iba za určitej podmienky. Napríklad pokiaľ pozmeníme niektoré riadky v tabuľke alebo pokiaľ chceme funkciu používať iba v určitom časovom rozpätí.1

CREATE OR REPLACE FUNCTION sprava_o_zruseni()
RETURNS TRIGGER AS $$
BEGIN
	RAISE EXCEPTION '%', TG_ARGV[0];
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Predošlý kód vyvodí výnimku spolu s reťazcom, ktorý funkcia dostane ako argument triggera. Treba si všimnúť, že TG_ARGV[0] sa nemôže použiť priamo ako chybová správa, nakoľko syntax príkazu RAISE požaduje reťazec.

CREATE TRIGGER ziadne_upravy_v_piatok_po_obede
	BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE
	ON tst_tabulka
	FOR EACH STATEMENT
	WHEN (CURRENT_TIME > '12:00'
	AND extract(DOW from CURRENT_TIMESTAMP)= 5)
EXECUTE PROCEDURE
sprava_o_zruseni('Prepáčte ale v piatok po obede nie je možné zmeniť tabuľku tst_tabulka!');

Spustenie triggera (v piatok po obede):

teoria_tst=# INSERT INTO tabulka_tst
VALUES
(4, 'Igor', 1990, 'Vavrecka 240',
 'Namestovo', 'SK', '02901');
ERROR:  Prepáčte ale v piatok po obede
nie je možné zmeniť tabuľku tst_tabulka!

DDL - Event Triggery

PostgreSQL od verzie 9.3 dokáže spúšťať triggery aj pre DDL udalosti. Event triggery sú triggery aktivované DDL operáciou (CREATE, DROP, ALTER). Primárne majú slúžiť k nastaveniu prístupových práv (tam, kde prednastavené práva pre schému nestačí), k vytvorení (k aktualizácií) audit tabuliek a triggerov. Často nájdu svoje použitie pri výkonnostnej analýze, kde je možné zmerať čas vykonávania príkazov medzi ddl_command_start a ddl_command_end. Môžu pomôcť vyriešiť určité situácie s pridávaním, či rušením Large objektov, ktoré sa správajú ako samostatné objekty. Môžu pomôcť so správou užívateľov. Môžu aktivovať podrobnejšiu kontrolu vlastných funkcií (syntaxe, sémantiky, formátovania).1

Je možne napísať EVENT TRIGGER pre 3 druhy event udalosti: ddl_command_start, ddl_command_end, sql_drop. Pokiaľ napr. napíšeme CREATE EVENT TRIGGER .. ON ddl_command_start …, spustí sa trigger pre všetky DDL, ktoré majú v tabuľke Event Trigger Firing Matrix v stĺpci ddl_command_start krížik. Výber DDL udalostí pre trigger je možné obmedziť klauzulou … WHEN TAG IN (‘CREATE TABLE’, ‘CREATE TABLE AS’). Event Triggery môžu využívať špeciálne premenné TG_TAG a TG_EVENT. Funkcia, ktorú DDL trigger spustí musí vracať typ EVENT_TRIGGER.2

Event triggerry podporujú nasledujúce eventy:
ddl_command_start :
Táto udalosť nastane tesne pred spustením DDL príkazov CREATE, ALTER alebo DROP.
ddl_command_end :
Udalosť nastane po vykonaní príkazov CREATE , ALTER alebo DROP
sql_drop :
Táto udalosť nastane tesne pred spustením ddl_command_end eventu u príkazov na zrušenie databázových objektov.

Zoznam všetkých podporovaných príkazov pre tieto tri udalosti je dostupný v oficiálnej dokumentácií postgreSQL. Na adrese: http://bit.ly/prikazyEventTrigger

Ako ukážku použitia Event triggrov vytvoríme audit, ktorý zapisuje vybrané DDL akcie v databáze do tabuľky.
Vytvorenie tabuľky, do ktorej budú DDL akcie zapisované:

teoria_tst=# CREATE TABLE ddl_audit
(
  event    TEXT,
  prikaz  TEXT,
  ddl_time TIMESTAMPTZ,
  uzivatel      TEXT
);

Vytvorenie funkcie, ktorá zapíše údaje do tabuľky:

CREATE OR REPLACE FUNCTION ddl_audit_funkcia()
RETURNS EVENT_TRIGGER
AS $$
BEGIN
  	INSERT INTO ddl_audit
	VALUES (tg_tag, tg_event, now(), session_user);
  	RAISE NOTICE
		'Akcia DDL bola zapisana do audit tabulky';
END
- -  SECURITY DEFINER
-- nechceme aby ostatný užívatelia mali prístup k
-- tabuľke ddl_audit
$$ LANGUAGE plpgsql SECURITY DEFINER;

Vytvorenie event triggera, pri ktorom sa má funkcia spustiť:

CREATE EVENT TRIGGER ddl_event
ON ddl_command_start
WHEN TAG IN
('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE')
EXECUTE PROCEDURE ddl_audit_funkcia();

Skúška príkladu:

CREATE TABLE kontorola_ddl_eventu(
  i INT
);

teoria_tst=# SELECT * FROM ddl_audit;
-[ RECORD 1 ]------------------------
event		| CREATE TABLE
prikaz 		| ddl_command_start
ddl_time 		| 2014-04-13 16:58:40.331385
uzivatel		| postgres

Zdroje:

KRAJČI, S. 2005. Databázové systémy. Košice :
UPJŠ Košice, 2005. 270s.

http://www.sallyx.org/sally/psql/triggery.php?showAdds=0

http://postgres.cz/wiki/PostgreSQL_9.3_(2013)