Wprowadzenie

Trigger (wyzwalacz) jest obiektem bazy danych, który jest dołączony do tabeli. W wielu aspektach jest podobny do procedury składowanej. W rzeczywistości są często określane jako „specjalny rodzaj procedury składowanej”. Główna różnica polega na tym, że ta pierwsza jest dołączona do tabeli i wykonywana tylko wtedy gdy wystąpią operacje takie jak: INSERT, UPDATE lub DELETE. Określasz działania modyfikujące, które powodują uruchomienie wyzwalacza.

Poniżej przedstawiono przykład wyzwalacza, który będzie wyświetlał bieżący czas systemowy w momencie dodania wiersza do tabeli do której tej wyzwalacz jest przypisany:

CREATE TABLE SampleDataTable(SampleID int IDENTITY, SampleName varchar(20))
GO
CREATE TRIGGER SampleDataTable_INSERT
ON SampleDataTable
FOR INSERT
AS
PRINT GETDATE()
GO
Wywołanie oraz rezultat:
INSERT SampleDataTable(SampleName) VALUES ('SampleData')
-- Aug 29 2017 10:31AM
Jest to jedynie przykład poglądowy. W dalszej cześci artykułu pokaże przykład ilustrujące użycie wyzwalacza w rzeczywistym świecie.

Kiedy używać wyzwalaczy?

Jest tylko niewielka garstka programistów, którzy nie wiedzą kiedy powinny być stosowane wyzwalacze. Osobiście używam ich tylko gdy muszę wykonać określone działanie będące następstwem operacji takich jak dodanie, zmodyfikowanie czy usunięcie danych. Większość operacji manipulacji na danych przygotowuję w procedurach składowanych. Dzięki takiemu zabiegowi funkcjonalność wyzwlaczy może być zdefinowana w tych procedurach. Dla przykładu, załóżmy, że chcesz wysłać wiadomość e-mail do managera sprzedaży, gdy do systemu zostało dodane zlecenie o dużym priorytecie. Kiedy takie zlecenie zostaje dodane do bazy danych trigger jest używany do określania priorytetu oraz wysłania wiadomości, kiedy kryteria są spełnione. Poniżej przedstawiam częściową implementację takiego zachowania:

CREATE TABLE Orders (OrdID INT, OrdPiority varchar(10))
GO
CREATE TRIGGER tr_Orders_Insert
ON Orders
FOR INSERT
AS
iF( SELECT COUNT(*) FROM inserted WHERE OrdPiority = 'High') = 1
	BEGIN
		PRINT 'Email Code Goes Here'
	END
GO
INSERT Orders (OrdPiority) VALUES ('High')
Oraz wynik dodania nowego pierwsza do tabeli Orders:
-- Email Code Goes Here
Kiedy jednak używana jest procedura składowana możemy przenieść do niej kod wyzwalacza:
CREATE PROCEDURE proc_Orders_INSERT @OrdPiority varchar(10)
AS
BEGIN TRANSACTION
	INSERT Orders (OrdPiority) VALUES (@OrdPiority)
	IF @@ERROR <> 0
		GOTO ErrorCode
	IF @OrdPiority = 'High'
		PRINT 'Email Code Goes Here'
	COMMIT TRANSACTION
ErrorCode:
	IF @@TRANCOUNT <> 0
		PRINT 'Error Code'
GO
Przyjrzymy się bardziej szczegółowo przykładowi wyzwalacza. Prawdopodobnie pierwszą rzeczą na którą zwróciłeś uwagę jest odwołanie SELECT do tabeli nazywanej inserted. Wyzwalacze korzystają z dwóch specjalnych tabel zwanych inserted oraz deleted. Pierwsza z nich odwołuje się do operacji INSERT zanim ta zostanie rzeczywiście wykonana. Usunięta tabela zawiera referencje do tabeli bazowej na której wykonujemy operację DELETE. Podobnie jak w poprzednim przypadku dotyczy to stanu sprzed operacji. W momencie przeprowadzenia operacji UPDATE obie z tych tabel są brane pod uwagę. Będąc bardziej precyzyjnym: nowe dane odnoszące się do operacji UPDATE odnoszą się do tabeli inserted a dane, które zostały zaktualizowane do danych zawartych w tabeli deleted.

W naszym przykładzie możemy zobaczyć, że dane dostaną dodane do tabeli. Instrukcja IF szuka wyniku równego 1. Oznacza to, że wyzwalacz zakłada, że do tabeli dodany jest tylko jeden wiersz. Jeżeli do tabeli zostanie dodany więcej niż jeden wiersz możesz pominać zamównie z priorytetem High ponieważ wyzwalacz uruchamia się tylko raz dla każdej skojarzonej instrukcji. Zdaje sobie sprawę, że może to być mylące dlatego spojrzmy na dwa przykłady. Poniżej możemy zobaczyć, że wyzwalacz uruchamia się dla każdej instrukcji INSERT:

INSERT Orders (OrdPiority) VALUES ('High')
INSERT Orders (OrdPiority) VALUES ('High')
-- Result:
--
-- Email Code Goes Here
-- Email Code Goes Here
Teraz w tabeli Orders mamy trzy wiersze z priorytetem High. Dodajmy teraz nowe wiersze w oparciu o bieżącą treść, aby pokazać jak zachowuje się wyzwalacz, gdy chcemy wykonać instrukcję składającą się z wielu poleceń:
INSERT INTO Orders (OrdPiority) 
SELECT OrdPiority FROM Orders
Wiadomość Email Code Goes Here się nie wyświetliła miło, że to tabeli zostały dodane trzy nowe wierwsz z odpowiednim priorytetem. Wszystko dlatego, że nie zostały spełnione warunki instrukcji IF. Wyzwalacz uruchamia się tylko raz dla całego ciągu instrukcji dlatego wartość po zsumowaniu wynosiła 3 a nie oczekiwane 1. Poniżej przykład jak poradzić sobie z obsługą wielu instrukcji INSERT w jednym poleceniu:
ALTER TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
 IF EXISTS(SELECT * FROM inserted WHERE OrdPiority = 'High')
	BEGIN
		DECLARE @count tinyint
		SET @count = (SELECT COUNT(*) FROM inserted where OrdPiority = 'High')
		PRINT CAST(@count as varchar(3)) + ' row(s) with a priority of High were entered'
	END
GO
Możemy teraz przetestować nasz kod używając tego samego polecenia co w poprzednim przykładzie:
--6 row(s) with a priority of High were entered

Przykład z życia wzięty

Osoby zaznajomione z tematem zarządzania witrynami sieciowymi wiedzą jak ważne jest sprawdzenie ruchu na stronie celem sprawdzenia, które obszary witryny są odwiedzane przez użytkowników. IIS posiada funkcje rejestrowania wielu atrybutów związanych z każdym użytkownikiem. Dla przykładu, za każdym razem kiedy użytkownik odwiedza daną witrynę i chce się zalogować. Domyślnie, dane te zapisywane są do pliku loga ale można zmienić to zachowanie aby było zgodne ze standardem ODBC. Jest to interfejs pozwalający programom łączyć się z systemami zarządzającymi bazą danych.

Używam tego podejścia dla jednego z klientów od pewnego czasu ponieważ chciał w prosty sposób śledzić każdy obszar swojej witryny. Główny obszar został zdefiniowany jako sekcje wymienione na głównym pasku nawigacyjnym, np. Strona główna, o nas, kontakt, etc. Celem było przygotowanie raportu, który wskazywałby miesięczne zestawienie odwiedzin każdego z głównych obszarów serwisu. Możesz się zastanawiać dlaczego wyzwalacz jest potrzebny do wdrożenia takiego rozwiązania. Przecież SELECT z klauzulą WHERE do filtrowania zakresu dat oraz GROUP BY do policzenia odwiedzin każdej z podkategorii powinien być wystarczający.

Powód dla którego zdecydowałem się na użycie wyzwalacza był związany z niedopuszczalnym czasem przygotowania raportu. Nawet na stronach ze stosunkowo małych ruchem liczba wierszy związanych z odwiedzinami rośnie w zastraszającym tempie. Dla każdej strony odwiedzanej przez użytkownika należy liczyć jeden wiersz dodany do tabeli. Jeżeli strona zawiera dodatkowe odwołnia, np. pliki graficzne, należy dodać kolejny wiersz.

Najważniejsze z naszego punktu widzenia jest to, że wygenerowania raportu z takiej ilości danych trawało by niezwykle długo. Aby skrócić czas potrzebny do wykonania raportu zdecydowałem się na użycie tabeli podusmowywującej do liczenia odsłon stron odwiedzanych przez różnych użytkowników. Dzięki temu rozwiazaniu mamy tabelę zawierającą jedynie osiem (liczba zależna od głównych kategorii na stronie) wierszy a generowanie raportu trwa mniej niż jedną sekundę.

Poniżej poglądowy przykład użycia wspomnianego wcześniej wyzwalacza okrojonego do operowania jedynie na dwóch głównych obszarach serwisu:

CREATE TABLE CustomLog(ClientHost varchar(255), LogTime datetime, Target varchar(255))
GO
CREATE TABLE LogSummary(LogSumCategory varchar(30), LogSumCount int)
GO
INSERT LogSummary VALUES('O nas',0)
INSERT LogSummary VALUES('Kontakt', 0)
CustomLog to główna tabela logowania a LogSummary to tabela z podsumowaniem. Dwia główne obszary serwisu to ‘O nas’ oraz ‘Kontakt’. Celem przygotowanego wyzwalacza jest aktualizacja wartości kolumny LogSumCount za każdym razem gdy użytkownik odwiedzi strony About.aspx oraz Contact.aspx:
CREATE TRIGGER tr_CustomLog_INSERT
ON CustomLog
FOR INSERT
AS
	IF EXISTS(SELECT * FROM inserted WHERE Target = 'About.aspx')
		BEGIN
			UPDATE LogSummary
			SET LogSumCount = (SELECT COUNT(*) FROM CustomLog WHERE Target = 'About.aspx')
			WHERE LogSumCategory = 'About'
		END
	IF EXISTS(SELECT * FROM inserted WHERE Target = 'Contact.aspx')
		BEGIN
			UPDATE LogSummary
			SET LogSumCount = (SELECT COUNT(*) FROM CustomLog WHERE Target = 'Contact.aspx')
			WHERE LogSumCategory = 'Contact'
		END
GO
Wyzwalacz będzie oczekiwał nowych wartości w tabeli ale również będzie sprawdzał przygotowany przez nas warunek, aby logować informację w odpowiednich polach. Poniżej przykład użycia tak przygotowanego wyzwalacza:
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'Default.aspx')
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'About.aspx')
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'About.aspx')
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'Contact.aspx')
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'Contact.aspx')
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'About.aspx')
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'Contact.aspx')
INSERT INTO CustomLog VALUES('111.111.111.111', '4/1/29 12:00:50', 'About.aspx')
Oraz wynik z tabeli LogSummary:
 
LogSummary                     LogSumCount
------------------------------ ------------
O nas                          4
Kontakt                        3
Takie rozwiązanie w dzisiejszych czasach nie jest najlepsze do monitorowania ruchu użytkowników i nie chciałbym, żeby było trakowane jako dobra praktyka. Jest to prosty przykład szkoleniowy pozwalający pokazać do czego mogą zostać użyte wyzwalacze.