08 kwietnia 2011

Triggery DML w MS SQL Server

1. Triggery DML


DML triggery (Data Manipulation Language - czyli INSERT/UPDATE/DELETE w kontekście języka SQL ) są to obiekty bazodanowe w MS SQL Server, których implementacja jest wykonywana w związku z wystąpieniem któregoś z poniższych trzech poleceń:
  • INSERT - wstawionie danych
  • DELETE - usunięcie danych
  • UPDATE - aktualizacja danych
i bardziej egzotycznych ich kombinacji
  • INSERT/SELECT
  • INSERT/EXEC
  • MERGE (dostępne od versji SQL Server 2008 i późniejszych)
    Niezeleżnie od ilości modyfikowanych wierszy przez poszczególne polecenia, SQL trigger do obsługi danego zdarzenia będzie wywołany tylko raz. (np. jedno polecenie DELETE usuwa 90 wierszy w tabeli Customers, ale AFTER DELETE trigger na tabeli Customers będzie wywołany tylko raz).  Nawet jeżeli żaden wiersz w tabeli nie będzie zmodifikowany trigger obsługujący dane zdarzenie zostanie wywołany.



    2. Rodzaje triggerów DML


    Triggery DML w MS SQL Server mogą być powiązane zarówno z tabelą jak i widokiem.W kontekście triggerów DML, czyli powiązanych z modifikacją danych możemy wyróżnić dwa typy triggerów:
    INSTEAD OF i AFTER. Triggery mogą być zaimplementowane w T-SQL'u lub za pomocą języka wspieranego przez .NET Framework.
    Typ Triggera Obsługa tabeli Obsługa widoku
    AFTER Tak Nie
    INSTEAD OF Tak Tak
    Triggery INSTEAD OF obsługują najczęściej widoki i służą do  propagowania zmian danych do tabel, które tworzą poszczególne widoki.


    3. Z której strony można podejść do triggera?


    Triggery można tworzyć (CREATE TRIGGER), usuwać (DROP TRIGGER), deaktywowac (ENABLE TRIGGER), aktywować (DISABLE TRIGGER).


    Informacje o statusie DML triggera, jego typie i powiązaniach można odczytać z dwóch tabel systemowych sys.triggers i sys.trigger_events.


    4. Tabele Inserted i Deleted Tabela


    Tabele Inserted i Deleted nie mogą być modyfikowane, mogą być wykorzystywane tylko do odczytu.

    Inserted - zawiera dane wstawione do tabeli/widoku poleceniem INSERT, lub nową wesję danych zmodyfikowanych poleceniem UPDATE.

    Tabela Deleted - zawiera dane usunięte z tabeli/widoku poleceniem DELETE,  lub oryginalne wartości danych przed zmianami wprowadzonymi poleceniem UPDATE.

    Tabel Updated - NIE ISTNIEJE w implementacji DML triggerów w MS SQL Server.



    W triggerach INSTEAD OF dane, które są w tabelach Inserted i Deleted  nie są jeszcze zarejestrowane w logu tranzakcyjnym bazy danych i są utrzymywane w tymczasowej bazie danych. Inna jest implementacja triggerów AFTER, które są wykonywane w momencie gdy wszystkie modyfikacje danych zostały zapisane w logu tranzakcyjnym.Może to mieć wpływ na scenariusz, kiedy istnieje potrzeba zablokowania zmian (np. UPDATE'ów) do danej tabeli:



    Wycofanie tranzakcji nastąpi w momencie kiedy wszystkie modifikacje wykonane przez UPDATE'a będą zaaplikowane do transaction log'a, co może spowodować, że czas wykonania operacji anulowania UPDATE'u niepotrzebnie się wydłuży i obciąży system.

    Prościej i wydajniej byłoby użyć trigger'a INSTEAD OF UPDATE.



    5. Dlaczego triggery należy stosować z pewną nieśmiałością?


    Triggery nie mają zbyt dobrej prasy wsród programistów, inżynierów wsparcia czy administratorów. Wynika to zapewne z pewnej ich cechy. Wykonanie DML triggera jest niejawne, a zatem programista implementujący system wywołując polecenie INSERT nie musi zakładać reakcji łańcuchowej jaką te polecenie wywoła np.

    • AFTER INSERT trigger, który archiwizuje wstawiony rekord w tabeli audyt.
    • AFTER UPDATE trigger, który wykona logikę walidacyjną i w razie niepowodzenia wyrzuci błąd jednocześnie anulując modyfikacje danego UPDATE'u
    Rozwiązania oparte na triggerach poprzez ich naturalną niejawność wprowadzają dodatkową złożoność do architektury systemu i mogą powodować niespodziewane i trudne do wykrycia błędy.Pewną alternatywą dla triggerów mogą być ograniczenia (Constrains).
    Skrypty były testowane na następujących konfiguracjach:
    • SQL Server 2005 SP3, Developer Edition, 32bit.
    • SQL Server 2008 R2,  Developer Edition, 32bit.

    1 komentarz:

    1. źle się czyta bo 'transakcja' a nie 'tranzakcja'

      OdpowiedzUsuń

    Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.