Spouštěče představují klíčovou součást databázového systému SQL Server. Jde o specifický typ funkcí, které se automaticky aktivují v reakci na definované databázové operace.
Tento průvodce poskytne detailní pohled na spouštěče SQL, jejichž znalost může být velmi přínosná. Pojďme se na to podívat!
Co jsou to spouštěče v SQL?
Termín „spouštěč“ označuje situaci, kdy databázový server automaticky provede zadaný SQL dotaz, když dojde ke změně dat v databázi.
Spouštěč je definován jako skupina specificky pojmenovaných SQL příkazů, které jsou uloženy v paměti. Jde o typ volání funkce, která se automaticky spustí při jakékoli databázové události. Každý spouštěč je spojen s určitou tabulkou.
Například, spouštěč může být aktivován při přidání nového sloupce do tabulky, nebo při úpravě specifických záznamů.
Podle Microsoft Developer Network jsou spouštěče specifickou kategorií uložených procedur. Při vytváření spouštěče se nejprve specifikuje událost, která ho má aktivovat, a následně akce, která se má provést.
Syntaxe:
CREATE TRIGGER nazev_spouštěče BEFORE/AFTER INSERT/UPDATE/DELETE ON nazev_tabulky FOR EACH ROW SET operace [tělo_spouštěče];
Vysvětlení jednotlivých parametrů:
CREATE TRIGGER název_spouštěče– Používá se pro vytvoření nového spouštěče nebo změnu názvu existujícího.BEFORE/AFTER– Definuje, zda se spouštěč provede před nebo po určité události.INSERT/UPDATE/DELETE– Určuje databázovou operaci, která spouštěč aktivuje.ON nazev_tabulky– Název tabulky, ke které je spouštěč přiřazen.FOR EACH ROW– Určuje, že spouštěč se provede pro každý upravený řádek.tělo_spouštěče– Akce, která se má vykonat při aktivaci spouštěče.
Spouštěče jsou uložené funkce s jednoznačnými identifikátory, což umožňuje opakované použití uložených dotazů. Pojďme se podívat, proč jsou spouštěče v SQL důležité.
Spouštěče se často používají k řízení provedení kódu při výskytu události. Jsou ideální, pokud je potřeba, aby se určitý kus kódu spouštěl automaticky při výskytu konkrétní události.
Následují některé výhody použití spouštěčů v databázových operacích SQL:
Referenční integrita je klíčovou vlastností relačních databází. Zajišťuje, že data v databázi jsou vždy konzistentní pro všechny operace.
Pokud jsou dvě tabulky umístěny v různých databázích, validace dat pomocí omezení nemusí být možná. V takových případech jsou spouštěče jedinou možností pro validaci.
Kombinace argumentů spouštěčů
Pro každou tabulku lze definovat šest různých typů spouštěčů. Jedná se o kombinaci argumentů Trigger obsažených v spouštěčích na úrovni řádků SQL.
BEFORE INSERT: Tyto spouštěče provedou akci na řádcích před provedením jakékoli operace INSERT v zadané tabulce.
AFTER INSERT: Provedou akci na řádcích ihned po operaci INSERT.
BEFORE UPDATE: Funkce na řádcích se provede před operací UPDATE.
AFTER UPDATE: Provedou akci na řádcích ihned po operaci UPDATE.
BEFORE DELETE: Provedou operaci na řádcích před provedením operace DELETE.
AFTER DELETE: Provedou akci na řádcích po operaci DELETE.
Typy spouštěčů SQL
Spouštěče SQL jsou uložené funkce, které se spustí automaticky při výskytu specifických událostí. Připomínají plánování řízené událostmi. Následující situace mohou vyvolat spuštění spouštěčů.
DML Triggers – DML znamená Data Manipulation Language. Spouštění kódu v reakci na úpravy dat je umožněno pomocí DML Triggers. Tento typ spouštěče se aktivuje při provádění příkazů INSERT, UPDATE a DELETE. Označují se také jako „spouštěče na úrovni tabulky“.
DDL Triggers – DDL znamená Data Definition Language. Spouštěče DDL umožňují spouštět kód v reakci na změny schématu databáze, jako je přidávání nebo mazání tabulek, nebo události serveru, například když se uživatel přihlásí. Tyto události se nazývají „spouštěče na úrovni databáze“.
Tyto spouštěče mohou být aktivovány při provedení příkazů DDL, jako je CREATE, ALTER nebo DROP. Lze je také použít pro sledování a řízení prováděných činností.
LOGON Triggers – Kdykoli dojde k události LOGON (spuštění, přihlášení, odhlášení, vypnutí), jsou aktivovány spouštěče přihlášení. Spouštějí se až po autentizaci uživatele, před zahájením uživatelské transakce. Pokud autorizace selže, spouštěče LOGON se nespustí.
Tyto spouštěče lze použít k zaznamenávání historie přihlášení nebo k vytváření omezení pro konkrétní přihlášení, mimo jiné pro auditování a správu identit pro připojení k serveru.
CLR Triggers – CLR znamená Common Language Runtime. Spouštěče CLR jsou specifickou podskupinou spouštěčů postavených na CLR v rámci technologie .NET. Jsou vhodné, pokud spouštěč vyžaduje složitější výpočty nebo interakci s entitami mimo SQL.
Spouštěče DML a DDL lze vytvořit povolením kódování pomocí podporovaných jazyků .NET, jako Visual Basic, C# a F#.
Ukázkový spouštěč SQL Server
Pojďme si tyto koncepty demonstrovat na příkladu.
Nejprve vytvoříme databázi pomocí SQL příkazů.
CREATE DATABASE testdb; use testdb;
Zde je název databáze „testdb“. Nyní vytvoříme tabulku.
CREATE TABLE student(
name varchar(25),
id int(2),
maths int(2),
physics int(2),
biology int(2),
social int(2),
total int(2)
);
Vytvořili jsme tabulku pro ukládání informací o studentech. Následuje příkaz pro zobrazení struktury tabulky. „student“ je zde název tabulky.
DESC student;
Následuje struktura vytvořené tabulky:
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
Po vytvoření tabulky následuje nastavení spouštěče. Použijeme argument BEFORE INSERT.
Spouštěč má název „marks“. Při modifikaci tabulky se spouštěč pokusí automaticky vypočítat celkové skóre studenta.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Definujeme „total“ pomocí klíčového slova new. Nyní vložíme data do tabulky a ověříme výsledek. Zpočátku je celkové skóre 0 pro každého studenta.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
Při vkládání dat do tabulky student se automaticky spustí spouštěč. Vypočítá celkové skóre každého studenta. Nyní se pomocí příkazu SELECT podíváme, zda byl spouštěč vyvolán.
SELECT * FROM table_name;
A zde je konečný výstup.
mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)
Z výsledku je patrné, že byly automaticky sečteny známky z jednotlivých předmětů. To znamená, že se spouštěč úspěšně spustil.
Další operace se spouštěči
Pomocí spouštěčů lze provádět různé operace. Některé jsou jednoduché, jiné složitější, ale po pochopení syntaxe se dají snadno zvládnout. Spouštěče se aktivují, deaktivují a odstraňují pomocí příkazů Transact-SQL, jako jsou níže.
Dotaz pro ověření existence konkrétního spouštěče
Tento příkaz zkontroluje existenci spouštěče v celé databázi.
SELECT * FROM [sys].[triggers] WHERE [name] = 'nazev_spouštěče'
Dotaz pro zobrazení spouštěčů
Následující příkaz zobrazí všechny spouštěče dostupné v databázi.
SHOW TRIGGERS;
Dotaz pro deaktivaci spouštěče
Následující příkaz deaktivuje spouštěč v databázi.
DISABLE TRIGGER nazev_spouštěče ON DATABASE;
Můžete také specifikovat tabulku a deaktivovat spouštěč pouze pro danou tabulku.
DISABLE TRIGGER nazev_spouštěče ON nazev_tabulky;
Dotaz pro aktivaci spouštěče
Následující příkaz nejprve deaktivuje spouštěč definovaný pro danou tabulku a následně ho aktivuje.
ALTER TABLE nazev_tabulky DISABLE TRIGGER nazev_spouštěče ALTER TABLE nazev_tabulky ENABLE TRIGGER nazev_spouštěče
Před aktivací spouštěče je nutné ho deaktivovat.
Dotaz pro aktivaci nebo deaktivaci všech spouštěčů v tabulce
Pomocí příkazu SQL je možné deaktivovat nebo aktivovat všechny spouštěče tabulky najednou, nahrazením názvu spouštěče slovem ALL.
ALTER TABLE nazev_tabulky DISABLE TRIGGER ALL ALTER TABLE nazev_tabulky ENABLE TRIGGER ALL
Dotaz pro smazání spouštěče
Spouštěč lze odstranit jeho smazáním nebo smazáním celé tabulky. Při odstranění tabulky se odstraní i všechny spouštěče, které s ní souvisí.
DROP TRIGGER [nazev_spouštěče];
Při odstranění spouštěče se odstraní i související data z tabulky sys.objects.
Výhody spouštěčů
- Vytvoření spouštěčů je jednoduché a mohou vyvolávat uložené funkce a metody.
- Uživatelé mohou snadno implementovat auditování pomocí spouštěčů.
- SQL Server neumožňuje vytvářet omezení mezi entitami v databázových systémech, ale tento nedostatek lze vyřešit pomocí spouštěčů.
- Spouštěče umožňují implementovat omezení integrity napříč různými databázemi.
- Spouštěče jsou užitečné v případě hromadné validace nově vložených nebo upravených dat, namísto validace po jednotlivých řádcích.
Nevýhody spouštěčů
V některých situacích spouštěče SQL nemusí být optimálním řešením kvůli svým omezením.
- Spouštěče vyžadují přesnou dokumentaci.
- Ladění spouštěčů může být komplikované, zejména kvůli jejich simultánnímu spouštění v databázi, které nemusí být dostupné pro aplikační komponenty.
- Příkazy DML jsou při používání spouštěčů složitější.
- I malá chyba ve spouštěči může vést k logickým chybám v aplikaci.
Závěr
Spouštěče jsou velmi užitečnou součástí jazyka Transact-SQL a SQL a lze je používat i v Oracle. Použití spouštěčů je zásadní pro volání uložených metod. Spouštěče SQL umožňují analyzovat časové osy aktivit a definovat, jak na ně v případě potřeby reagovat. Umožňují také zkontrolovat konkrétní tabulku, která je propojena se spouštěčem a extrahovat data.
Pomocí spouštěčů lze povolit rekurzi. Pokud spouštěč v tabulce provede příkaz v nadřazené tabulce, spustí se druhá iterace spouštěče. Tomu se říká rekurzivní spouštěč. Pomáhá to při řešení korelace identity.
Spouštěče navíc řídí aktualizační vzory, které databáze může přijímat. Je velmi užitečné zachovat integritu dat v databázovém systému, zejména když chybí klíče omezení SQL, konkrétně primární a cizí klíč.
Doufám, že vám tento článek pomohl při porozumění spouštěčům SQL.
Pokud se chcete naučit databáze do hloubky, zde je několik vynikajících zdrojů, jak se naučit SQL a NoSQL.