MySQL 5.0 přinesla možnost používání triggerů, tedy programových jednotek, které se automaticky provedou v případě předem definované operace s daty. V tomto článku bych chtěl na několika příkladech ukázat, jak trigger vytvořit. Příklady jsou popsány pro tyto aplikace:
mysql konzole - nejjistější způsob jak s triggery manipulovat
phpMyAdmin - rozšířený nástroj pro správu MySQL databází. Avšak nerozumí si s příkazem delimiter a do verze 2.9.2 (nejsem si jist) nenabízí, na stránce pro zadávání SQL příkazů, možnost změnit oddělovač. Starší verze tedy nejsou pro tvorbu triggerů moc použitelné.
phpMinAdmin - aplikace podporující základní funkce pro spávu MySQL databází. Celá se vlezla do jednoho souboru o velikosti cca 140KB a stáhnout si jí můžete z domovských stránek.

Před samotným začátkem se ještě zmíním o dvou příkazech, které by se mohly hodit.
SHOW TRIGGERS; -- vypsání triggerů v aktuální databázi
DROP TRIGGER jmeno_triggeru; -- smazání triggeru s daným jménem

Příprava databáze

Vytvoření databáze:

MySQL:
CREATE DATABASE `test_trigger`;

Vytvoření tabulek:

MySQL:
CREATE TABLE `knihovna` (
  `id_knihy` INT(5) NOT NULL AUTO_INCREMENT,
  `nazev` VARCHAR(30) NOT NULL,
  `autor` VARCHAR(30) NOT NULL,
  `vypujceno` TINYINT(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `id_knihy` (`id_knihy`)
);

CREATE TABLE `vypujcka` (
  `id_vypujcky` INT(5) NOT NULL AUTO_INCREMENT,
  `id_knihy` INT(5) NOT NULL,
  `vypujceno` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `vraceno` TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY `id_vypujcky` (`id_vypujcky`)
);

CREATE TABLE `zaloha` (
  `z_id_knihy` INT(5) NOT NULL,
  `z_nazev` VARCHAR(30) NOT NULL,
  `z_autor` VARCHAR(30) NOT NULL
);

Po naplnění testovacími daty by mohla vypadat tabulka knihovna třeba takto:

SQL:
mysql> SELECT * FROM knihovna;
+----------+--------+--------+-----------+
| id_knihy | nazev  | autor  | vypujceno |
+----------+--------+--------+-----------+
|        1 | kniha1 | autor1 |         0 |
|        2 | kniha2 | autor2 |         0 |
|        3 | kniha3 | autor3 |         0 |
|        4 | kniha4 | autor4 |         0 |
|        5 | kniha5 | autor5 |         0 |
+----------+--------+--------+-----------+

Tvorba triggerů

Trigger after_insert_vypujcka

tedy takový, který při vložení záznamu do tabulky vypujcka uloží do sloupce vypujceno (v tabulce knihovna) pro danou knihu, hodnotu '1'.

Vytvoření triggeru v mysql konzoli

MySQL:
mysql> DELIMITER //
mysql> CREATE TRIGGER after_insert_vypujcka
    -> AFTER INSERT ON vypujcka
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE knihovna SET vypujceno=1 WHERE id_knihy=NEW.id_knihy;
    -> END;//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Vytvoření triggeru v phpMyAdmin

V okně pro zadávání SQL dotazů, je možnost změnit "Oddělovač". Předvyplněný středník tedy nahradíme dvěmi lomítky "//"
Do pole pro SQL kód zadáme dotaz pro vytvoření triggeru

MySQL:
CREATE TRIGGER after_insert_vypujcka
AFTER INSERT ON vypujcka
FOR EACH ROW
BEGIN
UPDATE knihovna SET vypujceno=1 WHERE id_knihy=NEW.id_knihy;
END;

Vytvoření triggeru v phpMinAdmin

Z menu přejdeme na tabulku vypujcka a v sekci Triggery klikneme na odkaz "Přidat trigger". Do pole název vložíme after_insert_vypujcka, jako čas vybereme AFTER a událost INSERT.
Do pole pro kód vložíme

MySQL:
UPDATE knihovna SET vypujceno=1 WHERE id_knihy=NEW.id_knihy;

Trigger after_update_vypujcka

tento trigger uloží do sloupce vypujceno v tabulce knihovna, pro daný záznam, hodnotu '0' po té, co bude změněna hodnota ve sloupci vraceno v tabulce vypujcka.

Vytvoření triggeru v mysql konzoli

MySQL:
mysql> DELIMITER //
mysql> CREATE TRIGGER after_update_vypujcka
    -> AFTER UPDATE ON vypujcka
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.vraceno IS NOT NULL THEN
    -> UPDATE knihovna SET vypujceno=0 WHERE id_knihy=OLD.id_knihy;
    -> END IF;
    -> END;//
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;

Podmínka IF NEW.vraceno IS NOT NULL zajišťuje, že update záznamu se provede pouze tehdy, když byl v tabulce vypujcka změněn sloupec vraceno.

Vytvoření triggeru v phpMyAdmin a phpMinAdmin

Kód triggeru je stejný jako při vytváření v mysql konzoli, jen v aplikaci phpMyAdmin vynecháme změnu delimiteru a nastavíme jej v příslušné kolonce. U phpMinAdmin pak použijeme pouze část kódu mezi BEGIN a END Zbytek nastavíme v polích formuláře.

Trigger before_delete_knihovna

tento trigger vytvoří zálohu každé knížky, která bude z tabulky knihovna smazána.

Vytvoření triggeru v mysql konzoli

MySQL:
mysql> DELIMITER //
mysql> CREATE TRIGGER before_delete_knihovna
    -> BEFORE DELETE ON knihovna
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT INTO zaloha VALUES (OLD.id_knihy, OLD.nazev, OLD.autor);
    -> END;//
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

Vytvoření triggeru v phpMyAdmin a phpMinAdmin

Postup je opět stejný jako u předchozího triggeru;

Otestování triggerů

Pokud nyní vložíme do tabulky vypujcka nový záznam, automaticky se nastaví v tabulce knihovna u vypujčené knihy příznak o jejím vypujčení.

MySQL:
mysql> INSERT INTO vypujcka (id_knihy) VALUES (4);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM vypujcka;
+-------------+----------+---------------------+---------+
| id_vypujcky | id_knihy | vypujceno           | vraceno |
+-------------+----------+---------------------+---------+
|           1 |        4 | 2007-11-25 17:24:52 |    NULL |
+-------------+----------+---------------------+---------+
1 row in SET (0.00 sec)

mysql> SELECT * FROM knihovna;
+----------+--------+--------+-----------+
| id_knihy | nazev  | autor  | vypujceno |
+----------+--------+--------+-----------+
|        1 | kniha1 | autor1 |         0 |
|        2 | kniha2 | autor2 |         0 |
|        3 | kniha3 | autor3 |         0 |
|        4 | kniha4 | autor4 |         1 |
|        5 | kniha5 | autor5 |         0 |
+----------+--------+--------+-----------+
5 rows in SET (0.00 sec)

Při vrácení knihy, tedy update záznamu v tabulce vypujcky, se změní příznak o vypujčení knihy v tabulce knihovna na hodnotu '0'

MySQL:
mysql> UPDATE vypujcka
    -> SET vraceno=CURRENT_TIMESTAMP
    -> WHERE id_vypujcky=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  WARNINGS: 0

mysql> SELECT * FROM vypujcka;
+-------------+----------+---------------------+---------------------+
| id_vypujcky | id_knihy | vypujceno           | vraceno             |
+-------------+----------+---------------------+---------------------+
|           1 |        4 | 2007-11-25 17:24:52 | 2007-11-25 18:03:52 |
+-------------+----------+---------------------+---------------------+
1 row in SET (0.00 sec)

mysql> SELECT * FROM knihovna;
+----------+--------+--------+-----------+
| id_knihy | nazev  | autor  | vypujceno |
+----------+--------+--------+-----------+
|        1 | kniha1 | autor1 |         0 |
|        2 | kniha2 | autor2 |         0 |
|        3 | kniha3 | autor3 |         0 |
|        4 | kniha4 | autor4 |         0 |
|        5 | kniha5 | autor5 |         0 |
+----------+--------+--------+-----------+
5 rows in SET (0.00 sec)

Po smazání knihy z tabulky knihovna se provede záloha údajů do tabulky zaloha.

MySQL:
mysql> SELECT * FROM zaloha;
Empty SET (0.00 sec)

mysql> INSERT INTO knihovna (nazev,autor) VALUES ("kniha6","autor4");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM knihovna;
+----------+--------+--------+-----------+
| id_knihy | nazev  | autor  | vypujceno |
+----------+--------+--------+-----------+
|        1 | kniha1 | autor1 |         0 |
|        2 | kniha2 | autor2 |         0 |
|        3 | kniha3 | autor3 |         0 |
|        4 | kniha4 | autor4 |         0 |
|        5 | kniha5 | autor5 |         0 |
|        6 | kniha6 | autor4 |         0 |
+----------+--------+--------+-----------+
6 rows in SET (0.00 sec)

mysql> DELETE FROM knihovna WHERE autor="autor4";
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM knihovna;
+----------+--------+--------+-----------+
| id_knihy | nazev  | autor  | vypujceno |
+----------+--------+--------+-----------+
|        1 | kniha1 | autor1 |         0 |
|        2 | kniha2 | autor2 |         0 |
|        3 | kniha3 | autor3 |         0 |
|        5 | kniha5 | autor5 |         0 |
+----------+--------+--------+-----------+
4 rows in SET (0.00 sec)

mysql> SELECT * FROM zaloha;
+------------+---------+---------+
| z_id_knihy | z_nazev | z_autor |
+------------+---------+---------+
|          4 | kniha4  | autor4  |
|          6 | kniha6  | autor4  |
+------------+---------+---------+
2 rows in SET (0.00 sec)

"Vychytávka" a dotaz na konec

Bylo by příjemné, kdyby se po smazání záznamu z tabulky knihovna, provedla kontrola, zda daná kniha není vypůjčená. Pokud ano, aby byl upraven záznam o výpůjčce tak, že se atribut vraceno nastavi na aktuální čas. Předešlo by se tak situaci, kdy by se ve výčtu výpůjček objevovaly knihy, které již jsou z knihovny vyřazeny.
Jedno z řešení může vypadat takto:

MySQL:
mysql> DELIMITER //
mysql> CREATE TRIGGER before_delete_knihovna
    -> BEFORE DELETE ON knihovna
    -> FOR EACH ROW
    -> BEGIN
    -> DECLARE cislo_vypujcky INT(5);
    -> DECLARE vypujceno TINYINT(1);
    -> SELECT vypujceno INTO vypujceno FROM knihovna WHERE id_knihy=OLD.id_knihy;
    -> SELECT MAX(id_vypujcky) INTO cislo_vypujcky FROM vypujcka WHERE id_knihy=OLD.id_knihy;
    -> INSERT INTO zaloha VALUES (OLD.id_knihy, OLD.nazev, OLD.autor);
    -> IF vypujceno=1 THEN
    -> UPDATE vypujcka SET vraceno=CURRENT_TIMESTAMP WHERE id_vypujcky=cislo_vypujcky;
    -> END IF;
    -> END;//
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;

V kódu jsou použity implicitní kurzory, které slouží k načtení údajů z tabulky do proměnných. Proměnné je třeba předem deklarovat, a jejich datový typ a velikost se musí shodovat s datovým typem a velikostí sloupce, jehož hodnotu chceme do proměnné uložit.

Jenže toto řešení má jeden háček, a to ten, že při pokusu o smazání záznamu z knihovny končí trigger chybou:

MySQL:
mysql> DELETE FROM knihovna WHERE id_knihy = 1;
ERROR 1442 (HY000): Can't update table 'knihovna' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Zná-li někdo jiné (funkční :-) ) řešení, jak dosáhnout vytouženého výsledku, budu rád, když se o něj podělí.