C# týden November Black Friday
Black Friday je tu! Využij jedinečnou příležitost a získej až 80 % znalostí navíc zdarma! Více zde
Pouze tento týden sleva až 80 % na e-learning týkající se C#

Lekce 12 - MS-SQL - Trigger (DML)

Unicorn College Tento obsah je dostupný zdarma v rámci projektu IT lidem.
Vydávání, hosting a aktualizace umožňují jeho sponzoři.

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

Tento výukový obsah pomáhají rozvíjet následující firmy, které dost možná hledají právě tebe!

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.


 

 

Článek pro vás napsal Milan Gallas
Avatar
Jak se ti líbí článek?
15 hlasů
Autor se věnuje programování, hardwaru a počítačovým sítím.
Předchozí článek
MS-SQL krok za krokem: Optimalizace výkonu databáze
Všechny články v sekci
MS-SQL databáze krok za krokem
Miniatura
Následující článek
MS-SQL - Transakce
Aktivity (5)

 

 

Komentáře
Zobrazit starší komentáře (4)

Avatar
Tomáš Vrána:27.10.2018 1:20

Tak této lekci vůbec nerozumím. Nevím jestli jsem něco nepřehlédl, ale 11 lekcí jsem si prošel, prozkoušel a neměl jsem problém. U této lekce ani nevím, proč tam jsou ty zavináče. Navíc mi to přijde ohromně nepřehledné. Chybí mi tu nějaký vzor k vlastnímu otestování. Jsem začátečník, tak mě bertě s rezervou. Nicméně z této lekce nejsem moc moudrý.

Odpovědět
27.10.2018 1:20
Každý svého štěstí strůjcem.
Avatar
Patrik Pastor:21. května 14:49

muze prosim nekdo vyvstetlit tento prikaz?

FOR INSERT AS BEGIN

Proc se pise FOR? (jako pro vsechna vlozeni INSERT?), za AS (coz je alias), se dava BEGIN? kdyz alias by mel brat druhy nazev stringovy a nikoliv cely sql prikaz?

 
Odpovědět
21. května 14:49
Avatar
Patrik Pastor:21. května 15:20

co je procka?

 
Odpovědět
21. května 15:20
Avatar
Jastrab
Člen
Avatar
Odpovídá na Patrik Pastor
Jastrab:21. května 15:50

To je proste definicia Trigra - Lisi sa od selectu (insertu, updateu)
**AS **- nie je v tomto pripade ALIAS, ale parameter, ktory oddeluje definiciu trigra od akcie (vykonania) trigra

CREATE TRIGGER tr_user_forInsert
  • vytvor triger s nazvom "tr_user_forInsert"
ON user
  • z tabulky (nad tabulkou) "user"
FOR INSERT
  • pre insert - tj pri akej udalosti sa ma vykonat tento triger - v tomto pripade vzdy, ked sa zavola insert nad tabulkou "user"
  • cize ked niekto vlozi noveho uzivatela, tento triger vykona akciu definovanu nizsie..
AS BEGIN
  • AS - KDE sa ma vykonat nasledujuca akcia - ta je vlozena medzi BEGIN A END
  • BEGIN - zaciatok akcie
DECLARE @id int
SELECT @id=id from inserted
INSERT INTO myLogTable VALUES('new user with id = '
                                  + cast(@id as nvarchar(5)) +
                                 'is added at '+cast(Getdate() as nvarchar(20)))
  • deklarujem si pomocnu "id" typu int
  • vyselektujem si id z tabulky inserted a vlozim do pomocnej "id" - @id (ano tu ma autor chybu v selecte)
  • Ta sa da zapisat 2 sposobmi:
SELECT @id=id from inserted
alebo
set @id = (SELECT id from inserted)
  • a nakoniec vloz do tabulky myLogTable dane id uzivatela a aktualny datum
END
  • END - ukoncenie akcie
 
Odpovědět
21. května 15:50
Avatar
Jastrab
Člen
Avatar
Jastrab:21. května 15:58

procka - procedura -> v nej si definujes rozne operacie s databazou, ktore sa maju vykonat

trigger - je vlastne procedura, ktora sa automaticky vola pri vykonani operacie nad danou tabulkou (preto FOR INSERT, AFTER INSERT a pod)

samotne precedury sa automaticky nevolaju, tj. volaju sa ak ich je potreba

Napr. pre vlozenie noveho uzivatela nemusim volat priamo insert nad tabulkou user s trigrom, ale zavolam si miesto toho proceduru, v ktorej si vytvorim insert nad tabulkou user a potom dalsi insert nad tabulkou myLogTable.

 
Odpovědět
21. května 15:58
Tento výukový obsah pomáhají rozvíjet následující firmy, které dost možná hledají právě tebe!
Avatar
Odpovídá na Jastrab
Patrik Pastor:21. května 19:55

dobre diky, ale stale nevim proc je zapotrebi BEGIN...END, nikde predtim s ms-sql jsem to jeste nevidel. 2) okrem FOR, AFTER (coz nechapu jak se lisi od FOR, kdyz vlastne FOR se taky spusti Potom, co je trigger zavolan), INSTEAD, tak okrem tychto je taky treba FOR EACH (pro vsechny?), a dalsie (ktere jsem videl na stackoverflow), k cemu budu chtit pouzit ktery? . Nejsou tu vysvetleny ani ty deklarace promennych, chapu, ze si je treba tady vytvareji protoze id potrebuji, proc by to proste nemohlo byt v WHERE? tam prece muzu to ID priradit ne?

 
Odpovědět
21. května 19:55
Avatar
Odpovídá na Jastrab
Patrik Pastor:21. května 19:56

PS: clanek je HODNE nedodelany, opet, nesetkavam se stim tady poprve.

 
Odpovědět
21. května 19:56
Avatar
Odpovídá na Jastrab
Patrik Pastor:21. května 20:07

PSS: sory za dalsi ale jeste jsem si neuvedomil k tomu co je ten inserted?, kdyz na tom hooveruju mysi tak se zobrazi dbo.Uzivatele, takze tabulka existuje ale nevytvarel jsem ji ani nevim jak vypada tak jak to, ze existuje? A co v ni je? Krom toho, kdybys mi zodpovedel ty otazky i vyssie (co jsem se pytal prve), jeste jednou diky.

Editováno 21. května 20:08
 
Odpovědět
21. května 20:07
Avatar
Jastrab
Člen
Avatar
Odpovídá na Patrik Pastor
Jastrab:22. května 8:37

Pozeral som tie lekcie, miestami su pekne pisane, ale chybaju tam niekedy zakladne veci, niektore veci nejdu postupne. Aj tieto trigre mali byt vysvetlene az za procedurami!

AFTER - znamena, ze sa vysledok zapise az po uspesnom vykonani triggra

BEGIN END - tu nie je potrebne, pouziva sa skor v podmienkach
pri jednoriadkovom dotaze ho netreba, ale ked mas uz viac toho, vtedy to pouzijes:

IF (ID IS NULL)
     Select * from User

alebo

IF (ID IS NULL)
  BEGIN
     Select * from User
  END

a pri zlozitejsom uz treba, ak chces aby platila podmienka:

IF (ID IS NULL)
  BEGIN
     Select * from User

     Select * from Tabulka

     Select * from Nieco
  END
 
Odpovědět
22. května 8:37
Avatar
Pavel Indrák:6. srpna 21:19

Teda této lekci bych jako laik určitě nerozuměl. Naštěstí mi to dnes vysvětloval kolega v práci, tak se tak nějak chytám, ale i tak.. je to hodně nesrozumitelné.

 
Odpovědět
6. srpna 21:19
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

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