Triggery

Ciele
  1. Vyskúšať si implementáciu editovateľnosti inherentne needitovateľných pohľadov.
  2. Precvičiť si tvorbu triggerov.
Úvod

    Dnes budeme pokračovať v práci s triggermi, ktorú sme na minulom cvičení začali po zápočtovke.

    V tomto týždni máš možnosť napísať opravnú zápočtovku, takže ak to potrebuješ/chceš, tak to využi. Upozorňujem, že pri hodnotení sa berie posledný pokus, tzn. ak sa rozhodneš pre opravný termín, tak sa bude brať hodnotenie z neho.

    Kvôli tomu, že odpadnú cvičenia v stredu budúci týždeň, bude preberanie zadania v stredu v tomto týždni. Pre cvičenia, ktoré nie sú v stredu sa nič nemení.

Postup
  1. Na siedmom cvičení si v jednej z úloh mal 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;

    Úloha: Vytvor si daný pohľad vo svojej databáze.

    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). Pokojne si skús napr. zmeniť umelca 'AC/DC' na 'Le Matos'. V tomto kroku sa z tohto pohľadu pokúsime správiť 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?

    Úloha: Napíš trigger, ktorý umožní UPDATE nad pohľadom allArtists.
    Poznámka: Trigger bude INSTEAD OF, čím nahradí update nad pohľadom nejakou akciou nad tabuľkami, z ktorých pohľad vychádza.
    Poznámka: Zmenu mena umelca z pohľadu budeme interpretovať tak, že sa má podľa toho zmeniť príslušné meno umelca aj v tabuľke artist aj v tabuľke composer (ak napr. 'AC/DC' je interpretom a zároveň skladateľom, jeho update cez pohľad allArtists na hodnotu 'Le Matos' bude znamenať, že sa tak aktualizuje aj jeho meno v tabuľke artist, ale aj všetky jeho výskyty v tabuľke Track v stĺpci composer).
    Úloha: Napíš nov� trigger, ktorý umožní INSERT nad pohľadom allArtists (alebo uprav ten z predchádzajúcej úlohy tak, aby podporoval oba operácie).
    Poznámka: Vloženie nového umelca budeme interpretovať, ako vloženie nového interpreta. To znamená, že ak sa pokúsi používateľ vložiť nové meno medzi umelcov, vložíme do tabuľky artist nový záznam s daným menom. artistid nového interpreta bude najvyššie aktuálne artistid zvýšené o jedna (ak je napr. aktuálne najvyššie artistid v artist 275, artistid nového interpreta nastav na 276).
    Poznámka: Ak upravuješ trigger z predchádzajúcej úlohy, na tejto linke si pozri, ako vieš použiť podmienené predikáty INSERTING, DELETING, UPDATING na rozlíšenie toho, či sa trigger aktuálne spúšťa pre INSERT, UPDATE, alebo DELETE.
    Úloha: Over si správnosť implementácie oboch triggerov pridaním nového záznamu (INSERT), a aktualizáciou existujúceho (UPDATE).
  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 nijako nezmení - UNION totiž odstráni duplikáty. Napriek tomu v tabuľke artist vznikne nový záznam.
    Úloha: Uprav trigger umožňujúci INSERT nad pohľadom allArtist tak, aby sa vykonal iba vtedy, ak v pohľade allArtist neexistuje umelec s menom, aké sa používateľ snaží do pohľadu vložiť.
    Poznámka: Napr. v databáze a teda aj v pohľade už je umelec s menom 'AC/DC'. Ak sa teda v takom stave pokúsi používateľ do pohľadu vložiť nový záznam s menom 'AC/DC', vyhoďte vlastnú používateľskú výnimku a INSERT nevykonajte (výnimku treba samozrejme najprv deklarovať - jej názov môže byť napr. prohibitedOperation).
    Poznámka: Asi najjednoduchší spôsob ako zistiť, či tam už umelec s takým menom je alebo nie je, je deklarovať si číselnú premennú a do nej načítať počet umelcov v pohľade allArtist, ktorých meno je rovnaké ako meno práve vkladaného umelca. Ak je počet väčší ako 0, znamená to, že taký umelec tam už je, a je preto potrebné vyhodiť výnimku.
    Rovnaký problém ako pri INSERT môže nastať aj pri UPDATE - ak sa pokúsim 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.
    Úloha: Uprav trigger pre UPDATE nad pohľadom allArtists tak, aby v prípade, že nové meno umelca je v pohľade už obsadené, sa neudialo nič.
    Poznámka: Pre takúto úpravu môžeš opäť použiť logiku ako pri predchádzajúcej úlohe, tzn. najprv si spočítať, koľkokrát je také meno už použité, a upraviť meno iba vtedy, ak také meno ešte použité nie je.
    Opäť si otestuj vhodnosť a 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ám ako doplňujúcu úlohu. Teraz radšej poďme na ďalší krok, v ktorom si vytvoríme jeden zaujímavý trigger, ktorý sa bude starať o implementáciu logiky definovanej doménou e-shopu, pre ktorý je databáza vytvorená.
  3. V tomto ďalšom kroku už bude tvoja ú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é).

    Úloha: Napíš trigger, ktorý pri vložení nového zákazníka do databázy nastaví jeho supportRepId na id toho pracovníka zákazníckej podpory, ktorý práve zastupuje najmenej zákazníkov. Ak je tých, čo zastupujú najmenej zákazníkov, viac, vyber jedného náhodne.
    Poznámka: Teda to musí to byť jeden zo zamestnancov s titulom "Sales Support Agent", a zo všetkých tých zamestnancov, ktorí majú daný titul, musí reprezentovať najmenej zákazníkov (tzn. že ak by som pridal teraz nového agenta zákazníckej podpory, malo by dávať nových zamestnancov práve jemu). Samotný trigger nie je veľmi ťažký, je však potrebné napísať správny select, ktorý nám vráti id práve toho zamestnanca, ktorý spĺňa uvedené podmienky. Začni tým, že skús napísať select, ktorý ti vráti takého zamestnanca (resp. stačí jeho id).

    Vďaka tomuto triggeru sa nemôže stať, že príde nový zákazník a nebude sa o neho mať kto postarať.

Zdroje
  1. Prednaska 18: Relačná algebra
Doplňujúce úlohy
    Úloha:

    Chinook

    Napíš trigger umožňujúci zmazanie umelca z pohľadu allArtists.
    Poznámka: Zmazanie budeme interpretovať nasledovne: ak v tabuľke Track existuje skladateľ (composer) s daným menom, daný composer v tabuľke Track sa nastaví na NULL. Ak existuje interpret v tabuľke Artist s daným menom, zmaže sa jeho záznam. Pozor, pri na tabuľku Artist sa môže odkazovať jeden alebo viacero albumov, najprv teda musíš zmazať všetky albumy odkazujúce sa na daného umelca (Artist). Aby si nemusel kaskádne mazať aj tracky v albume a potom položky na faktúre, pre všetky skladby (track), ktoré sa zasa odkazujú na album, iba nastav cudzí kľúč albumid na hodnotu NULL (albumId v Track môže mať hodnotu NULL).
    Úloha:

    MiniFB

    V databáze pre náš mini facebook napíš trigger, ktorý bude zaisťovať symetriu priateľského vzťahu. Vždy, keď sa do tabuľky FriendsWith vloži záznam pre nové priateľstvo A s B, vlož do tabuľky aj záznam hovoriaci, že zároveň aj B je priateľom A.
    Poznámka: Daj si pozor na nekonečné volanie triggerov - takýto trigger môže vyvolať sám seba, a takto sa môže zacykliť. Preto v tele triggera pred tým než sa pokúsiš vložiť záznam pre symetriu vzťahu, si over, či takýto záznam už nebol vložený (je viacero spôsobov ako na to, ak sa ti lení googliť, skús pozriet nasledujúcu debatu na StackOverflow).
    Poznámka: Netestuj tieto úlohy na účte minifb/minifb, ktorý používame na prednáškach - ak by to robil každý z vás, navzájom by ste si to kazili. V rámci materiálov boli zverejnené skripty na vytvorenie danej databázy, takže si ju môžeš vytvoriť na svojom účte, resp. na lokálnej inštalácii databázy.
comments powered by Disqus