Lekce 12 - MS-SQL - Trigger (DML)
V minulé lekci, MS-SQL krok za krokem: Optimalizace výkonu databáze, jsme se věnovali optimalizaci databáze.
V dnešním MS-SQL tutoriálu se zaměříme na tzv. triggery.
Úvod
Trigger si můžete představit jako spoušť určité činnosti. Jednoduše si nadefinujete, při jaké akci (update, delete, insert) se mají spustit určité SQL příkazy. Obecně trigger definuje událost, která zautomatizuje jednotlivé procesy.
Základní rozdělení
Jsou tři základní typy triggerů:
- DML trigger - jsou prováděny automaticky v odpovědi na DML
události(INSERT, UPDATE, DELETE)
- After triggers - zavolá se po úvodní akci. Občas se volá jako (For triggers).
- Instead of triggers - zavolá se na místo původní akce. V MySQL se používá klíčové slovo before.
- DDL trigger - spouští se při CREATE, DROP, ALTER operacích
- Logon trigger - spouští se při vytvoření uživatelské session
V tomto článku se naučíte, jak používat DML triggery.
V rámci jednoho triggeru existují dvě pseudotabulky. Ty zpřístupňují nová a stará data (INSERTED, DELETED). V rámci triggeru lze pomocí ROLLBACK zrušit operaci, která trigger spustila. Jeden trigger lze použít i pro více akcí najednou (například UPDATE a DELETE).
Syntaxe
CREATE TRIGGER tr_name ON [table||view] [FOR||AFTER||INSTEAD OF] [[INSERT],[UPDATE],[DELETE]] AS ....
Příklad
Po vytvoření nového uživatele se zapíše událost do tabulky (například report). Zapíšeme si id daného uživatele a kdy byl vytvořen:
CREATE TRIGGER tr_user_forInsert ON user FOR INSERT AS BEGIN DECLARE @id int SELECT @id from inserted INSERT INTO myLogTable VALUES('new user with id = ' + cast(@id as nvarchar(5)) + 'is added at '+cast(Getdate() as nvarchar(20))) END
Tento trigger se spustí při zápisu nového uživatele do tabulky user. Zapisuje novou událost do tabulky LogTable.
Ten samý postup bychom zvolili například při vymazání uživatele. Pak bychom trigger pouze trochu pozměnili. Místo původního FROM INSERTED bychom zapsali FROM DELETED.
select * FROM inserted select * FROM deleted
Takto by se trigger zavolat při příkazu DELETE nad tabulkou user.
Příklad 2 - vymezení sloupců
Máme například tabulku produktů, která má následující strukturu:
id | sekce_id | cenova_hladina_id | nazev | url | cena |
3 | 2 | 5 | prod1 | /prod1 | 1000 |
Pokud budeme chtít upravit název, url nebo cenu, tak se příkaz UPDATE provede. Důležité je však zamezit úpravu sloupce id, sekce_id a cenova_hladina_id. Proto musíme při aktualizaci zjistit, které sloupce se mají přepsat a podle toho UPDATE dovolit, nebo naopak zrušit.
CREATE TRIGGER tr_user_forInsert ON user FOR INSERT AS BEGIN If(update(id)) Begin raiserror('Nemůžete upravovat sloupec id!', 16,1) return end If(update(sekce_id)) Begin raiserror('Nemůžete na přímo upravovat sloupec sekce_id', 16,1) return end If(update(cenova_hladina_id)) Begin raiserror('Nemůžete na přímo upravovat sloupec cenova_hladina_id', 16,1) return end -- zde bude update END
After UPDATE TRIGGER
Tento typ triggeru umožňuje použít dvě pseudotabulky (INSERTED, DELETED). Inserted table obsahuje změněná data a Deleted table obsahuje stará data. Pro demonstraci použiji příklad s eventy:
CREATE TRIGGER tr_user_afterUpdate ON user AFTER UPDATE AS BEGIN DECLARE @old_name varchar, @new_name varchar, @odl_age int, @new_age int, @TEXT VARCHAR(1000), @id SELECT * INTO #TempTable; WHILE(EXIST(SELECT id FROM #TempTable)) BEGIN SET @TEXT = ''; SELECT TOP 1 @new_name = name, @new_age = age, @id = id FROM #TempTable SELECT @old_name = name, @odl_age = age FROM deleted WHERE id = 1 SET @TEXT = 'uživatel s id = '+ @id + 'Provedl změny : ' IF(@old_name <> @new_name) SET @TEXT = @TEXT + ' Přejmenoval se z ' @old_name + 'na ' + @new_name + '.' IF(@odl_age <> @new_age) SET @TEXT = @TEXT + ' Změna roku z ' + @old_age + 'na ' + @new_age + '.' INSERT INTO myReportTable VALUES(@TEXT); END END
Instead
Tento druh triggeru se spouští místo dané akce. Například Instead UPDATE se spustí místo původního UPDATE. Takže musíme aktualizaci zavolat z triggeru.
Příklad
Trigger se bude spouštět nad tabulkou tblDepartment. Pokud se do proměnné @DetId zapíše nějaké id (identifikátor daného oddělení) z tabulky tblDepartment, vypíše se error přes funkci Raiserror a příkaz INSERT se neprovede.
Pokud budeme chtít do tabulky uložit oddělení, které již existuje (poznáme tak že hodnota inserted.DeptName se přes join spojí s nějakým záznamem tblDepartment.DeptName), tak se příkaz zruší.
V opačném případě můžeme zapsat nový záznam do tabulky tblDepartment.
CREATE TRIGGER tr_user_afterUpdate ON user AFTER UPDATE AS BEGIN Declare @DetId int Select @DetId = DetId From tblDepartment join inserted on inserted.DeptName = tblDepartment.DeptName If(@DetId is null) Begin Raiserror(‘bla bla bla‘,16,1) return end insert into tblEmployee(id,name,gander,departmentId) select id,name,gender,@DetId from inserted END
Dnes jste se naučili základní dělení triggerů a na příkladech jste mohli vidět, jak tyto triggery fungují.
V příští lekci, MS-SQL - Transakce, se podíváme na transakce.
Komentáře


Zobrazeno 10 zpráv z 17. Zobrazit vše