Wyzwalacze (triggery) w SQL - AFTER/INSTEAD OF (T-SQL)

CREATE TRIGGER trgAudyt ON Klienci
AFTER INSERT AS
BEGIN
  INSERT INTO Audyt(Info)
  SELECT 'Nowy klient' FROM inserted;
END;

Wyzwalacze reagują w odpowiedzi na zdarzenia generowane przez obiekty bazodanowe, bazę danych i serwer. Możemy je podzielić na trzy grupy:

-na klasyczne wyzwalacze DML, które reagują na polecenia takie jak INSERT, UPDATE, DELETE lub MERGE na tabelach

-na wyzwalacze DDL, które uruchamiane są przy wyrażeniach takich jak CREATE, ALTER i DROP

-na wyzwalacze logowania, które reagują na zdarzenia typu LOGON.

Sprawdźmy czy jakikolwiek UPDATE na tej tabeli bo uruchomi wyzwalacz:

Wyzwalacz "InfoNaKonsole" uruchomił się pomimo tego że w tabeli "Employees" nic tak naprawdę się nie zmieniło. Raz, że nowej pensji została przypisana stara pensja, dwa, że uwaględniony został pracownik o numerze pierwszym, a nikogo takiego w tej tabeli nie ma.

Za pomocą np. Konstrukcji IF...ELSE i zmiennej systemowej @@ROWCOUNT możemy się zabezpieczyć aby w przypadku jeśli dana instrukcja UPDATE nie zmodyfikuje ani jednego wiersza, to aby wyzwalacz nie wykonywał danego kodu.

Sprawdzamy czy dołożenie instrukcji warunkowej spełniło swoją rolę:

Jak widać, z wyzwalacza nie otrzymujemy żadnego komunikatu.

Zakładając wyzwalacz na tabeli, jesteśmy w stanie wyciągnąć informację która (lub które) kolumna były modyfikowane, wystarczy użyć funkcji UPDATE:

Sprawdźmy, najpierw modyfikacja w kolumnie Salary:

A teraz modyfikacja innej kolumny - LastName:

W wyzwalaczach istnieje możliwość korzystania z wirtualnych tabel INSERTED i DELETED, dzięki którym możemy się dowiedzieć co zostało wstawione, zmodyfikowane lub usunięte.

Zmodyfikujemy teraz tabele "Employees", damy podwyżkę o 100 dwóm pracownikom:

Po wykonaniu instrukcji UPDATE, wyzwalacz zwraca nam wirtualną tabelę "Inserted", która zawiera zmodyfikowane rekordy.

W przypadku kiedy gdybyśmy chcieli móc porównać co się zmieniło, to idąc po najmniejszej lini oporu należało by wyswielić również dane z wirtualnej tabeli "Deleted":

Wykonujemy UPDATE i sprawdzamy wyniki:

Wirtualną tabelę "Inserted" otrzymujemy w wyniku instrukcji INSERT oraz UPDATE, natomiast tabelę "Deleted" w wyniku DELETE oraz UPDATE.

Wyzwalacze, poza tym, że możemy je oczywiście usunąć, to możemy je również deaktywować.

Aktywujemy je w analogiczny sposób zastępując słowo DISABLE słowem ENABLE.

Możemy również na raz aktywować lub deaktywować wszystkie wyzwalacze, które są załóżone na daną tabelę.

Stan wyzwalaczy możemy sprawdzić w słowniku SYS.TRIGGERS.

Wyzwalacze DML - INSTEAD OF

Wyzwalacze typu INSTEAD OF mają zadziałać zamiast instrukcji na jaką zostały założone. Np jeśli założymy taki wyzwalacz na instrukcję UPDATE na tabeli Employees, to UPDATE na tej tabeli nie dojdzie do skutku, natomiast wydarzy się to co zamiast tego zrobi wyzwalacz.

Spróbujmy teraz pracownikom z działu o numerze 90 przypisać nowe imie – 'Janusz'.

Jak widać dostajemy stosowny komunikat z wyzwalacz – "AKTUALIZACJA 3 WIERSZA/Y NIE POWIODŁA SIĘ" a pod spodem jeszcze informacja ilu wierszy by ten UPDATE rzeczywiście dotyczył.

Wyzwalacze DDL

Chcąc śledzić na bazie danych instrukcje z grupy DDL wystarczy załozyć odpowiedni wyzwalacz i skorzystać w nim z funkcji EVENTDATA().

Teraz stworzymy przykładową tabelę:

Funkcja EVENTDATA() zwraca w formie XML informacje co się wydarzyło na bazie danych.

Wystarczy odpowiednio zmodyfikować zwracany wynik, i możemy w formie wierszy w tabeli śledzić poczynania użytkowników na bazie danych.

Najpierw przygotujemy odpowiednią tabelę na nasze śledzenie, gdzie będziemy gromadzili informacje m.in. o tym kto, kiedy i jakim poleceniem tworzył tabele:

Teraz zmodyfikujemy wyzwalacz AudytDDL, w którym skorzystamy z przedstawionej już funkcji EVENTDATA() i z wyniku zwróconego przez nią pobierzemy odpowiednie informacje, ktore to potem umieścimy w odpowiednich kolumnach w tabeli "LogiDDL"

Standardowe rozwiązanie, jeśli tabela "Tabelka" istnieje to ma zostać usunięta, po czym leci instrukcja tworząca od podstaw taką tabelę.

W tabeli "LogiDDL" mamy pełen wpis o tym co się właśnie wydarzyło na bazie danych:

Aby usunąć taki wyzwalacz należy skorzystać ze specjalnego polecenia:

"DROP TRIGGER AuditDDL ON ALL SERVER".

Wyzwalacze LOGON

Wyzwalaczami typu LOGON powinni się zajmować wyłącznie doświadczeni administratorzy, ponieważ najmniejszy błąd i może się okazać że są problemy z podłączeniem się do serwera.

Przykładowy kod tworzący taki wyzwalacz wraz z tabelą w której będą gromadzone wpisy o logowaniach.

I efekty po zalogowaniu sie do serwera:

Aby usunąć taki wyzwalacz należy skorzystać ze specjalnego polecenia:

"DROP TRIGGER AuditLogon ON ALL SERVER".

Szkolenie Kompleksowe SQL w Microsoft SQL Server →

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5