12. díl - MS-SQL - Trigger (DML)

MS-SQL krok za krokem MS-SQL - Trigger (DML)

Ú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||IN­STEAD OF] [[INSERT],[UP­DATE],[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 dalším díle se dozvíte jak se používají DDL triggery.


 

  Aktivity (4)

Článek pro vás napsal Milan Gallas
Avatar
Autor se věnuje programování, hardwaru a počítačovým sítím.

Jak se ti líbí článek?
Celkem (3 hlasů) :
4444 4


 


Miniatura
Všechny články v sekci
MS-SQL databáze krok za krokem
Miniatura
Následující článek
MS-SQL - Transakce

 

 

Komentáře

Avatar
Martin Tomko
Člen
Avatar
Martin Tomko:

Milan Gallas
Dakujem za doplnenie serialu. Avsak v tomto clanku su pre mna nove veci, ktore v tomto seriali neboli vysvetlene.
Napriklad:
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

Nepostrehol som napriklad naco sluzi '@' alebo potom '#' pri After UPDATE TRIGGER. A celkovo tie zapisy su pre mna malo pochopitelne s vedomostami z tohto serialu. Napriklad tu vidim aj novu podmienku "If" a ?funkciu? "raiserror".

Potom tu v clanku je MOZNO malicka chybicka (ale to uz len detail):
"Tento trigger se spustí při zápisu nového uživatele do tabulky user. Zapisuje novou událost do tabulky LogTable." Nemalo byt "...do tabulky myLogTable"?

Mozno by to chcelo este nejaky doplnujuci clanok pred tento, alebo ak je to mozne, tak doplnit do tohto :-) Dakujem...

Editováno 18. října 14:24
 
Odpovědět 18. října 14:23
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 1 zpráv z 1.