12. diel - MS-SQL - Trigger (DML)

V minulej lekcii, MS-SQL krok za krokom: Optimalizácia výkonu databázy , sme sa venovali optimalizáciu databázy.

V dnešnom MS-SQL tutoriálu sa zameriame na tzv. Triggery.

Úvod

Trigger si môžete predstaviť ako spúšť určitej činnosti. Jednoducho si nadefinujete, pri akej akcii (update, delete, insert) sa majú spustiť určité SQL príkazy. Všeobecne trigger definuje udalosť, ktorá zautomatizuje jednotlivé procesy.

Základné rozdelenie

Sú tri základné typy triggerov:

• DML trigger - sú vykonávané automaticky v odpovedi na LÚD udalosti (INSERT, UPDATE, DELETE) After triggers - zavolá sa po úvodnej akcii. Občas sa volá ako (For triggers).

  Instead of triggers - zavolá sa na miesto pôvodnej akcie. V MySQL sa používa kľúčové slovo before.

• After triggers - zavolá sa po úvodnej akcii. Občas sa volá ako (For triggers).
• Instead of triggers - zavolá sa na miesto pôvodnej akcie. V MySQL sa používa kľúčové slovo before.
• DDL trigger - spúšťa sa pri CREATE, DROP, ALTER operáciách
• Logon trigger - spúšťa sa pri vytvorení užívateľské session

V tomto článku sa naučíte, ako používať LÚD triggery.

V rámci jedného triggeru existujú dve pseudotabulky. Tie sprístupňujú nové a staré dáta (inserted, DELETED). V rámci triggeru možno pomocou ROLLBACK zrušiť operáciu, ktorá trigger spustila. Jeden trigger možné použiť aj pre viac akcií naraz (napríklad UPDATE a DELETE).

Syntaxe

CREATE TRIGGER tr_name ON [table||view]
[FOR||AFTER||INSTEAD OF]
[[INSERT],[UPDATE],[DELETE]] AS ....

Príklad

Po vytvorení nového používateľa sa zapíše udalosť do tabuľky (napríklad report). Zapíšeme si id daného užívateľa a kedy bol vytvorený:

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 sa spustí pri zápise nového používateľa do tabuľky user. Zapisuje novú udalosť do tabuľky LogTable.

Ten istý postup by sme zvolili napríklad pri vymazanie používateľa. Potom by sme trigger iba trochu pozmenili. Namiesto pôvodného FROM inserted by sme zapísali FROM DELETED.

select * FROM inserted
select * FROM deleted

Takto by sa trigger zavolať pri príkaze DELETE nad tabuľkou user.

Príklad 2 - vymedzenie stĺpcov

Máme napríklad tabuľku produktov, ktorá má nasledujúcu štruktúru:

id	sekce_id	cenova_hladina_id	nazev	url	cena
3	2	5	prod1	/ prod1	1000

Ak budeme chcieť upraviť názov, url alebo cenu, tak sa príkaz UPDATE vykoná. Dôležité je však zamedziť úpravu stĺpce id, sekce_id a cenova_hladina_id. Preto musíme pri aktualizácii zistiť, ktoré stĺpce sa majú prepísať a podľa toho UPDATE dovoliť, alebo naopak zrušiť.

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žiť dve pseudotabulky (inserted, DELETED). Inserted table obsahuje zmenené dáta a Deleted table obsahuje staré dáta. Pre demonštráciu použijem prí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 sa spúšťa miesto danej akcie. Napríklad Instead UPDATE sa spustí namiesto pôvodného UPDATE. Takže musíme aktualizáciu zavolať z triggeru.

Príklad

Trigger sa bude spúšťať nad tabuľkou tblDepartment. Ak sa do premennej @DetId zapíše nejaké id (identifikátor daného oddelenia) z tabuľky tblDepartment, vypíše sa error cez funkciu Raiserror a príkaz INSERT sa nevykoná.

Pokiaľ budeme chcieť do tabuľky uložiť oddelenie, ktoré už existuje (spoznáme tak že hodnota inserted.DeptName sa cez join spojí s nejakým záznamom tblDepartment­.DeptName), tak sa príkaz zruší.

V opačnom prípade môžeme zapísať nový záznam do tabuľky 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 ste sa naučili základné delenie triggerov a na príkladoch ste mohli vidieť, ako tieto triggery fungujú.

V nasledujúcom kvíze, Kvíz - Výber dát, radenie a dátové typy v MS-SQL, si vyskúšame nadobudnuté skúsenosti z predchádzajúcich lekcií.

 

Mal si s čímkoľvek problém? Stiahni si vzorovú aplikáciu nižšie a porovnaj ju so svojím projektom, chybu tak ľahko nájdeš.