SQL - triggery

Triggery (spúšťače) sú špeciálne prípady uložených procedúr. MySQL podporuje uložené procedúry od verzie 5. Trigger sa spúšťa automaticke, keď nastane špecifická udalosť. Takáto udalosť môže byť vloženie a mazanie dát. Tu ešte traba š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ť slučky, 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. Triggerům nie je možné odovzdávať žiadne vstupné parametre. To znamená, že trigger nemá žiadne informácie o tom, ako by mal byť vykonávaný. Triggery navyše narozdiel od uložených procedúr nemôžu vracať sadu záznamov. A navyše, databázové systémy mávajú niektoré obmedzenia a príkazy, ktoré sa v triggeri nesmú objaviť. Pre MySQL platí, že sa v trigger nesmie objaviť aspoň 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)
• A niektoré ďalšie príkazy

Na druhej strane majú triggery 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 trigger môžete urobiť nejaké rozhodnutie v závislosti na dátach, ktoré má tento trigger zmeniť.

Pož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 sa robila včerajšia uzávierka.

Verzovanie 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.sql

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 user VARCHAR(32)

Teraz vytvoríme nad tabuľkou city_trash trigger ktorý pred zmazaním záznamu v tabuľke city , práve zmazávaný záznam uloží do tabuľku 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_trash. 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:

Súbor:SQL trigger1.png

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

Súbor:SQL trigger2.png

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)