Zobrazenie zdroja stránky SQL - triggery

{{Skripta_dbs}}
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<ref>MySQL (46) - Triggery http://www.linuxsoft.cz/article.php?id_article=1019</ref>.

===Čí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?===
#Databáza zachytí požiadavku na zmenu dát - dajme tomu na aktualizáciu dát v tabuľke.
#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.
#Ak trigger aktualizáciu nezrušil, zapíšu sa dáta do tabuľky.
#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.
#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á:
<source lang="sql">
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body
</source>
Podrobný opis tejto konštrukcie je v samostatnej kapitole [[SQL_-_DDL#CREATE_TRIGGER|CREATE TRIGGER]]

Len pripomeňme, že pre vytvorenie triggera musíme mať [[SQL_-_DCL#GRANT|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:
<source lang="sql">
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`)
)
</source>
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:
<source lang="sql">
   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)
</source>

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ý:

<source lang="sql">
  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;
  $$
</source>

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

'''3. ''' [[MySQL workbench - spolupráca s databázou#Vytvorenie databázy z ER diagramu|Synchronizujeme]] náš dátový model s databázou. Pri výbere možností synchronizácie vyberieme náš vytvorený trigger:

[[Súbor:SQL trigger1.jpg|frame|center|frame|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

[[Súbor:SQL trigger2.jpg|frame|center|frame|Testovanie funkčnosti triggera cityToTrash - vymazanie mesta z tabuľky city]]

'''4.2 ''' Zobrazíme obsah tabuľky ''city_trash'':

<source lang="sql">
   SELECT * FROM city_trash
</source>

Výsledok:
{| class="wikitable"
|-
!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:''
<source lang="sql">
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;
$$
</source>


''Overenie funkčnosti:''

Pokúsme sa vložiť záznam o novom meste so záporným počtom obyvateľov:
<source lang="sql">
  INSERT INTO city
    (Name,CountryCode,District,Population)
    VALUES ('Kocurkovo', 'SVK', 'Kocúrkovský', -23) 
</source>

MySQL vráti chybové hlásenie:
 #1048 - Column 'Name' cannot be null
A vloženie nového záznamu nenastane.
==Zdroje a odkazy==
<references/>