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:

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.