SQL - triggery

Triggery (spúšťače) sú špeciálne prípady uložených procedúr. MySQL podporuje triggery od verzie 5. Trigger sa spúšťa automaticky, keď nastane špecifická udalosť. Takáto udalosť môže byť vloženie a mazanie dát. Tu ešte treba špecifikovať či sa trigger spustí pred akciou alebo po požadovanej akcii.

Trigger - definícia

• Trigger je 'uložená procedúra'. To znamená, že vnútri trigger-a možno vykonávať väčšinu akcií, ktoré sa dajú robiť pomocou uložených procedúr. Trigger môže obsahovať cykly, podmienky, lokálne premenné, matematický výpočet a podobne.
• Trigger sa spúšťa 'v súvislosti' s akčným dotazom. To v praxi znamená, že sa trigger môže spustiť
  • pred vložením údajov do tabuľky,
  • po vložení údajov do tabuľky,
  • pred zmazaním údajov z tabuľky,
  • po zmazaní údajov z tabuľky,
  • pred aktualizáciou údajov v tabuľke,
  • po aktualizáciou údajov v tabuľke.
• Trigger nemožno v žiadnom prípade spustiť príkazom SELECT.
• Každý trigger patrí práve jednej tabuľke. Hoci môže existovať tabuľka bez trigger-a, nie je možné, aby existoval trigger nezávisle na tabuľke.

Čím sa líši trigger od uložených procedúr

Triggery majú veľmi podobnú syntax ako uložené procedúry. Triggerom nie je možné odovzdávať žiadne vstupné parametre. Triggery navyše narozdiel od uložených procedúr nemôžu vracať sadu záznamov. A naviac, databázové systémy mávajú niektoré obmedzenia a príkazy, ktoré sa v triggeri nesmú objaviť. Pre MySQL platí, že sa v triggeri nesmú objaviť tieto príkazy:

• príkazy ALTER (ALTER TABLE, ALTER DATABASE a tak ďalej),
• príkazy pre riadenie transakcií (START TRANSACTION, ROLLBACK, COMMIT),
• volanie procedúr (CALL),
• príkazy pre nastavovanie práv (GRANT, REVOKE).

Na druhej strane triggery majú oproti uloženým procedúram niečo naviac: majú totiž prístup k dátam, ktorá sa práve menia. To napríklad znamená, že trigger, ktorý sa spúšťa pred aktualizáciou nejakej tabuľky má prístup k hodnotám tých riadkov, ktoré sa snažíme zmeniť. Tiež to znamená, že v triggeri môžete urobiť určité rozhodnutie v závislosti na dátach, ktoré má tento trigger zmeniť.

Použitie triggerov

Triggery sa v tabuľkách používajú z niekoľkých dôvodov. Tie môžu súvisieť s konzistenciou dát, s ich správou a údržbou alebo s tým, ako a kedy bude databáza komunikovať so svojím okolím. Príklady použitia triggerov:

Konzistencia dát.

Trigger môže urobiť výpočet a na základe toho povoliť alebo nepovoliť zmenu údajov v databáze. Napríklad v tabuľke skladových zásob môže byť trigger, ktorý zakáže vyskladniť tovar v prípade, že by sme sa tým dostali do záporného stavu tovaru. Trigger môže zakázať zmazanie zákazníka z databázy v prípade, keď má u nás nejaký dlh a podobne.

Protokolovanie zmien.

Trigger môže evidovať kto, kedy a ako menil dáta. Možno tak dohliadať na pracovníka, ktorý zadal zlé údaje alebo zistiť, o koľkej sa robila včerajšia uzávierka.

Verziovanie dát.

Vďaka triggerom možno ľahko naprogramovať aplikáciu tak, aby jedna tabuľka udržiavala históriu zmien inej tabuľky. To možno s úspechom použiť napríklad ako bezpečnostný mechanizmus.

Zasielanie správ svetu.

Trigger môže spustiť nejaký externý program alebo proces.

Ako to vlastne funguje?

1. Databáza zachytí požiadavku na zmenu dát - dajme tomu na aktualizáciu dát v tabuľke.
2. Databázový systém zistí, či je pre túto tabuľku definovaný trigger, ktorý sa má vykonať pred aktualizáciou záznamov. Ak áno, vykoná ho.
3. Ak trigger aktualizáciu nezrušil, zapíšu sa dáta do tabuľky.
4. Databázový systém zistí, či je pre túto tabuľku definovaný trigger, ktorý sa má vykonať po aktualizácií záznamov. Ak áno, vykoná ho.
5. Dokončené. Systémové prostriedky použité pre zápis dát sú vrátené systému a čaká sa na ďalšiu požiadavku.

Vytvorenie triggera

SQL príkaz pre vytvorenie triggera je CREATE TRIGGER. Syntax je nasledovná:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

Podrobný opis tejto konštrukcie je v samostatnej kapitole CREATE TRIGGER

Len pripomeňme, že pre vytvorenie triggera musíme mať patričné oprávania.

Prípadové štúdie

Bezpečné mazanie

Úloha: Budeme pracovať s databázou world, konkrétne s tabuľkou city. Do našej databázy world chceme pridať funkcionalitu, ktorá zabezpečí, že pri zmazaní nejakého mesta s tabuľky city sa toto nezmazalo nenávratne, ale aby sa presunulo 'do koša'.

Poznámka: Tabuľku city si môžete stiahnuť z http://subory.fmtnuni.sk/ki_files/databazy/city.zip

Analýza úlohy:

Ak zmažeme záznam z databázy, tak sa tento zmaže nenávratne. Riešenie je vytvoriť si pre tabuľku city jednu novú tabuľku city_trash, do ktorej budeme ukladať zmazané mestá.

Tabuľka city bola vytvorená SQL príkazom:

CREATE TABLE `city` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` char(35) NOT NULL DEFAULT '',
 `CountryCode` char(3) NOT NULL DEFAULT '',
 `District` char(20) NOT NULL DEFAULT '',
 `Population` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`)
)

Tabuľka city_trash bude mať rovnakú štruktúru, naviac ale pridáme atribúty

• delete_time - informácia o tom, kedy bol záznam odstránený,
• user - informácia o tom, kto záznam odstránil.

Tabuľku city_trash vytvoríme nasledovne:

   CREATE TABLE city_trash LIKE city;
   ALTER TABLE city_trash ADD COLUMN delete_time DATETIME;
   ALTER TABLE city_trash ADD COLUMN delete_user VARCHAR(32)

Teraz vytvoríme nad tabuľkou city trigger ktorý sa spustí pred zmazaním záznamu v tabuľke city'. Práve zmazávaný záznam uloží do tabuľky city_trash.

Riešenie

• Trigger budeme vytvárať v prostredí MySQL Workbench. Tabuľky city a city_trash si pomocou reverzného inžinierstva (Database->Reverse Engineer) importujeme do nového projektu.
• Trigger budeme vytvárať pre tabuľku city. Vlastnosti triggera:
  • názov triggera: cityToTrash,
  • akcia triggera bude zmazanie záznamu (DELETE),
  • čas akcie bude pred zmazaním (BEFORE DELETE),
  • trigger definujeme nad tabuľkou city.

Postup pri vytváraní triggera:

1. v prostredí MySQL Workbench zobrazíme vlastnosti tabuľky city (dvojklikom na tabuľku),

2. v spodných záložkách vyberieme 'Triggers'. Samotný kód triggera je nasledovný:

  DELIMITER $$
  CREATE TRIGGER cityToTrash
  BEFORE DELETE 
  ON city
  FOR EACH ROW
  BEGIN
    INSERT INTO city_trash(ID, Name, CountryCode, District, Population, delete_time, delete_user)
    VALUES (old.ID, old.Name, old.CountryCode, old.District, old.Population, now(), user()); 
  END;
  $$

V prípade, že prostredie nezobrazuje žiadnu chybu, pokračujeme ďalej.

3. Synchronizujeme náš dátový model s databázou. Pri výbere možností synchronizácie vyberieme náš vytvorený trigger:

Prostredie MySQLWorkbench - možnosti synchronizácie databázového modelu

4.1 Vyskúšame funkcionalitu nášho triggera: V databáze city zmažeme ľubovoľné mesto. V tomto prípade mažem 'Trenčín' s ID=4085

Testovanie funkčnosti triggera cityToTrash - vymazanie mesta z tabuľky city

4.2 Zobrazíme obsah tabuľky city_trash:

   SELECT * FROM city_trash

Výsledok:

ID	Name	CountryCode	District	Population	delete_time	delete_user
4085	Trenčín	SVK	Trencinasky kraj	90000	2010-11-23 19:56:06	juraj@localhost

Kontrola dát pred vložením

Úloha: Pre tabuľku city vytvorte trigger, ktorý zabezpečí, aby sa nedal vložiť záznam (teda mesto), s nulovým alebo záporným počtom obyvateľov (atribút Population).

Analýza úlohy:

Samotné zadanie úlohy navádza na vytvorenie triggera, ktorý sa spustí pred vložením nových údajov (BEFORE INSERT). Trigger nazvime newCityCheck. Atribút, ktorého hodnotu budeme kontrolovať bude Population. V prípade, ak bude mať atribút Populatoin zápornú hodnotu, nový záznam nesmieme vložiť. Tu nastáva problém, pretože trigger sa spúšťa pred samotným vložením. Ak by sme teda v triggeri newCityCheck napísali SQL príkaz INSERT, ktorý sa vykoná len v tom prípade, ak bude populácia kladná, tak by sa tento príkaz INSERT naozaj vložil iba pri splnenej podmienke, ale následne nasleduje samotné vloženie údajov, ktorá sa udeje vždy po skončení triggeru. Poznamenajme, že ako by bola populácia kladné číslo, záznam by sa vložil 2×.

Úlohu treba riešiť iným spôsobom. V prípade, ak bude populácia záporná, treba zrušiť vkladanie údajov. Ak bude populácia kladná, netreba nič robiť, pretože vloženie údajov nasleduje okamžite po vykonaní triggera.

Pre zrušenie akcie INSERT môžeme využiť vlastnosť triggera, kde pri vkladaní a úprave údajov sú tieto údaje pripravené vo virtuálnej tabuľke NEW (Pozn.: pri mazaní údajov sú dáta, ktoré mažeme vo virtuálnej tabuľke OLD). Po vykonaní triggera BEFORE INSERT sú do tabuľky city vložené nové údaje. Tieto údaje sa vždy nachádzajú v tabuľke NEW. Štruktúra tabuľky NEW je rovnaká ako štruktúra tabuľky city. Stačí teda určitm spôsobom 'zmazať' z tabuľky NEW všetky hodnoty a následné vloženie skončí neúspechom, pretože do tabuľky city sa nedajú vložiť prázdne hodnoty (podľa definície musí mať hodnotu každý atribút).

Riešnie:

DELIMITER $$   
   CREATE TRIGGER newCityCheck
   BEFORE INSERT
   ON city
   FOR EACH ROW
   BEGIN
    IF new.Population <  0 THEN 
      SET NEW.ID = NULL;
      SET NEW.Name= NULL;
      SET NEW.CountryCode = NULL;
      SET NEW.District = NULL;
      SET NEW.Population = NULL;
    END IF;
   END;
$$

Overenie funkčnosti:

Pokúsme sa vložiť záznam o novom meste so záporným počtom obyvateľov:

  INSERT INTO city
    (Name,CountryCode,District,Population)
    VALUES ('Kocurkovo', 'SVK', 'Kocúrkovský', -23)

MySQL vráti chybové hlásenie:

#1048 - Column 'Name' cannot be null

A vloženie nového záznamu nenastane.