SQL – jak na triggery

V řadě informačních systémů, které běží nad nějakou databází, potřebujeme v  případě vzniku nějaké události, např. modifikujeme řádek v nějaké tabulce, automaticky spustit příkaz, který provede nějaké operace. K tomuto účelu slouží triggery (z angl. trigger = ‚spoušť‘). V článku se podíváme na jejich tvorbu a praktické využití. Sami uvidíte, že se jedná o mocný nástroj, který nám může někdy usnadnit práci s daty.

Události

Během práce s naším databázovým systémem může dojít k různým událostem. Např. z naší firmy odejde zaměstnanec. My budeme při rušení jeho záznamu v tabulce LIDÉ chtít automaticky zrušit relevantní záznamy v jiných tabulkách. Např. v tabulce PLATY budeme chtít odstranit záznam, který příslušel danému pracovníkovi. Nebo v evidenci knihovny vyřazujeme nějakou knihu, tj. rušíme její záznam v tabulce KNIHA, ale zároveň chceme, aby se nám vytvořil nový záznam o této knize v tabulce VYŘAZENÉ_KNIHY. Nebo budeme našim zaměstnancům ve firmě měnit průbežně platy, ale při každé změně platu budeme chtít uchovat informaci, jaký plat dosud daný zaměstnanec chtěl. Tj. zcela automaticky budeme generovat tabulku HISTORIE_PLATU, kde budou uloženy jednotlivé výše platů. Všechny tyto události bychom mohli ošetřit na aplikační úrovni, ale databáze nám právě nabízí řadu takovýchto věcí plně zautomatizovat, že se o ně dál nebudeme muset starat a bude v tabulkách zajištěna aktuálnost informací. Tolik asi k motivaci, nyní se podívejme, jak takové automatické akce v SQL můžeme vytvořit.

Vytvoření triggeru

Zde se opět mohou databázové systémy rozcházet. Je to způsobeno tím, že trigger je definován ve standardu SQL3 z roku 1999. (Jedná se o nový standard, který by měl postupně nahradit standard SQL92.) Je možné, že některé databázové systémy v současné době ani neumožňují vytvářet a používat triggery.

Jaké typy triggerů máme? Můžeme mít trigger, který se vykoná, pokud nějakou hodnotu ve sloupci budeme přidávat, nebo modifikovat a nebo mazat. U všech těchto tří akcí můžeme v některých systémech specifikovat, zdali se trigger vyvolá před vlastním příkazem, nebo až po vlastním příkazu. Odtud pojmy before triggery a after triggery. Kompletní syntaxe vytvoření jednoduchého triggeru vypadá následovně (tak, jak ji definuje zhruba standard SQL3):

JMÉNO_TRIGGERU je řetězec charakterizující název objektu v rámci celé databáze, v JMÉNO_TABULKY je uveden název tabulky, na kterou se trigger aplikuje, AKCE je uvedení akce na tabulce, při které se má trigger aktivovat, tj. DELETE, UPDATE, nebo INSERT. V případě akce UPDATE se uvádí ještě závorka, do které se zadá jméno sledovaného sloupce, např. FOR UPDATE(plat). Za klíčovým slovem AS následujcí sql příkaz k provedení, většinou je zde uveden příkaz DELETE nebo UDPATE. Důležité je, že se trigger provádí pouze pro aktuální řádek, se kterým provádíme příslušný DELETE, nebo UPDATE. Některé databázové systémy umožňují trigger provést i na všechny řádky v tabulce.

Příklady

Pokud bychom v tabulce PLATY chtěli automaticky odstranit záznam pracovníka, jehož záznam v tabulce LIDÉ mažeme, pak takový trigger bude vypadat následovně:

Když pak v databázovém systému dojde ke smazání zaměstnance číslo 12 v tabulce LIDÉ, bude automaticky smazán záznam v tabulce PLATY pro osobu číslo 12.

Vězměme si opačný příklad. Do naší firmy nastoupí nový zaměstnanec, my pro něj založíme nový záznam v tabulce LIDÉ a budeme chtít, aby se automaticky vytvořil záznam v tabulce PLATY s hodnotou nějaké minimální mzdy, např. 4000. Trigger zapíšeme takto:

Rozšíření triggerů

Uvedl jsem, že některé databázové systémy mají mnohá vylepšení, jedním z nich je systém Oracle. V něm se trigger vytváří pomocí následující syntaxe:

Tento příklad jsem uvedl jenom pro přehled, pro vaši představu, jak takový trigger může v praxi vypadat. Užitečné je, že máte možnost specifikovat, zdali se trigger provede před nebo po operaci, na kterou má reagovat (standardně lze až po operaci). Dále můžeme kombinovat akce, při kterých se má trigger spustit. Např. můžeme napsat trigger, reagující na UPDATE nebo DELETE. Výhodou je, že nemusíme psát dva triggery pro různé akce se stejným tělem.

Část REFERENCES je nepovinná, zpřístupňuje nám starou a novou hodnotu v rámci těla triggeru. To využijeme např. v situaci, chceme-li mít trigger, který se vyvolá jen v případě, že nějakému zaměstnanci zvýšíme plat a to minimálně o 300 Kč. Tzn. v těle triggeru se potřebujeme odkazovat jak na starý plat, tak i na nově spočítaný. Za klíčové slovo WHEN lze uvést ještě další speciální podmínky, za kterých se má trigger vyvolat.

Ostatní databázové systémy mají řešeny vylepšení obdobným způsobem, každý systém má trochu jinou syntaxi. Uveďme si jenom pro ilustraci složitější trigger, který bude plnit následující úkol: dojde-li ke změně platů pracovníků, pak těm, kteří mají nově spočítaný plat vyšší jak 30000 Kč, tak jim částku strhneme na 28000 Kč, a ti co by po změně platu měli méně jak 12000 Kč, tak jim dáme plat 15000 Kč.

Zde jsem už pomalu naznačil, o čem budu mluvit v jednom z příštích dílů, a to o procedurálním rozšíření jazyka SQL, známého pod označením PL/SQL. Význam uvedeného příkladu je snad jasný. Pokud bude splněna jedna z podmínek, nová hodnota, kterou se bude aktualizovat hodnota ve sloupci, bude změněna na tu, co je uvedena v části THEN.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.