PL/pgSQL - Triggery

Krok 1

Pre vytvorenie triggera je potrebné najprv vytvoriť "trigger funkciu". Takáto funkcia sa vytvára ako regulárna funkciu, avšak nesmie brať žiadne parametre a musí vrátiť hodnotu typu TRIGGER. Zjednodušená syntax trigger funkcie:

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

Trigger funkciu je možne vytvoriť v akomkoľvek jazyku podporovanom v PostgreSQL. My pokračujeme v PL/pgSQL.

Samotný trigger je možné vytvoriť pomocou príkazu CREATE TRIGGER. Zjednodušená syntax príkazu CREATE TRIGGER:

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER | INSTEAD OF} { event }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

V tejto syntaxi:

1. Zadajte názov triggera po kľúčovom slove TRIGGER.
2. Určte časovanie, ktoré spôsobí spustenie triggera. Môže to byť pred BEFORE alebo po AFTER udalosti, ktorá vyvolala trigger. Kľučové slovo INSTEAD OF sa využíva pre trigger nad pohľadom.
3. Zadajte udalosť, ktorá vyvolá trigger. Udalosť môže byť INSERT , DELETE, UPDATE alebo TRUNCATE.
4. Po kľúčovom slove ON zadajte názov tabuľky asociovanej s triggerom.
5. Špecifikujte typ triggera, ktorý môže byť:
   1. FOR EACH ROW z názvu vypovedá, tento typ triggera sa spustí pri vyvolaní zmeny na každom riadku (v prípade triggera nad tabuľkou, jedina dovolená možnosť),
   2. FOR EACH STATEMENT, tento typ triggera sa spustí raz, bez ohľadu na to koľko riadkou akcia ktorá spustila trigger ovplyvňuje.
6. Na záver zadajte meno trigger funkcie po kľúčovom slove EXECUTE PROCEDURE.

Na siedmom cvičení ste v jednej z úloh mali získať zoznam umelcov spolu so skladateľmi a to bez duplikátov, usporiadaný podľa abecedy. Nasledujúci blok kódu vytvára pohľad, ktorý ukazuje práve takýto zoznam:

CREATE OR REPLACE VIEW allArtists AS
SELECT name
FROM   artist
UNION
SELECT composer
FROM   track
WHERE  composer IS NOT NULL
ORDER BY name;

Pretože tento pohľad používa zjednotenie (UNION) a usporiadanie (ORDER BY), takýto pohľad nie je editovateľný (neumožňuje operácie INSERT/UPDATE/DELETE). Napríklad pokúste sa zmeniť umelca 'AC/DC' na 'Le Matos'. V ďalšom kroku sa z tohto pohľadu pokúsime spraviť editovateľný pohľad.

Najdôležitejšia a zároveň najťažšia časť vytvorenia triggera na editovateľnosť pohľadu je nájsť správny a vhodný "preklad" DML operácie nad pohľadom do tabuliek, ktoré pohľad využíva. V kontexte aktuálnej úlohy, ako sa zmena mena umelca 'AC/DC' na meno 'Le Matos' prejaví na tabuľkách Artist a Track, z ktorých čerpá pohľad údaje?

Krok 2

V tomto kroku upravíme implementácie predchádzajúcich triggerov tak, aby zachovávali konzistenciu pohľadu allArtist. Ak sa totiž vloží nový záznam s menom umelca, aký už existuje, vzhľadom na pohľad allArtist sa stav pohľadu nezmení - UNION totiž odstráni duplikáty. Napriek tomu v tabuľke artist vznikne nový záznam.

Rovnaký problém ako pri INSERT môže nastať aj pri UPDATE - v prípade pokusu zmeniť meno jedného umelca na také meno, aké tam už je. Umožnenie takej zmeny by sa navonok javilo ako zmazanie daného umelca z databázy, pretože by sa po zmene považoval za duplikát a vo výsledku by sa teda objavil len raz.

Opäť si otestujte správnosť implementácie vhodnými INSERT a UPDATE príkazmi. Aj keď umožnenie DELETE príkazov by nebolo oveľa náročnejšie, to už necháme ako doplňujúcu úlohu.

Krok 3

V tomto ďalšom kroku už bude úloha náročnejšia. Povedzme, že pri vytvorení nového zákazníka mu chceme v rámci zákazníckej podpory automaticky priradiť nejakého zamestnanca obchodu. Vnútorná politika firmy je taká, že rozdelenie zákazníkov pre jednotlivých zamestnancov v roli "Sales Support Agent" má byť rovnomerné (spravodlivo rozdelené).