Co to jest trigger?

Trigger (wyzwalacz)  - operacja bądź zestaw operacji  uruchamianych w skutek wystąpienia określonego zdarzenia.

Zdarzenia te możemy podzielić na 3 grupy:

• INSERT, UPDATE, DELETE - manipulowanie danymi w bazie danych
• CREATE, ALTER, DROP - manipulowanie obiektami bazodanowymi
• LOGON - logowanie użytkownika, ustanowienie sesji

Warte uwagi jest to, że trigger może zostać skonfigurowany tak, by uruchamiał się jedynie przed lub po zdarzeniu.  W celu łatwiejszego zrozumienia, wyzwalacz można przyrównać do procedury składowanej.

Trigger wywołany w skutek wystąpienia zdarzenia uruchomiony będzie tylko raz, niezależnie od ilości wierszy, które zostały zmodyfikowane lub usunięte. Przykładowo, gdy instrukcja DELETE usuwa za jednym razem 20 wierszy tabeli w tabeli Car, wyzwalacz, który ma być uruchamiany wraz z instrukcją DELETE na tabeli Car, zostanie uruchomiony tylko raz. Trigger zostanie wywołany nawet gdy instrukcja (zdarzenie) nie obejmie żadnego wiersza.

Wyzwalacze - przypadki użycia

• logowanie informacji o historii edycji danych - np. informacje o zmianie ilości dostępnych sztuk towaru na magazynie
• w skutek zmiany statusu zamówienia dodanie nowego wpisu z informacjami o zamówieniach oczekujących na wysyłkę
• przesłanie maila do administratora z informacją o dodaniu nowego wpisu do tabeli z użytkownikami typu 'Moderator'

Microsoft SQL Server - szczegóły

MS SQL Server udostępnia dwie wirtualne tabele, podczas użycia wyzwalaczy typu INSERTED oraz DELETED. SQL Server używa tych tabeli do przechowywania danych dotyczących zmodyfikowanych wierszy, zarówno przed jak i po wystąpieniu zdarzenia.

Dla różnych typów operacji zawartość tych tabeli różni się:

• INSERT - tabela INSERTED zawiera wiersze, które zostaną dodane do tabeli, tabela DELETED jest pusta
• UPDATE - tabela INSERTED zawiera wiersze z nowymi danymi, tabele DELETED zawiera wiersze, które zostaną zaktualizowane
• DELETE - tabele INSERTED  jest pusta, tabela DELETED zawiera usunięte wiersze

Przykłady triggerów, korzystających z wyżej wymienionych tabel wirtualnych

Trigger, który będzie zapisywał informacje o nowych bądź usuniętych wierszach z tabeli Company do tabeli CompanyHistory

CREATE TRIGGER TR_DEL_Company ON Company
AFTER DELETE, INSERT
AS
BEGIN
	INSERT INTO CompanyHistory (CompanyId, CompanyTitle, EventDate, Operation)
	SELECT del.Id, del.Title, GETDATE(), 'DELETE'
	FROM deleted del
	UNION ALL
	SELECT ins.Id, ins.Title, GETDATE(), 'INSERTED'
	FROM inserted ins
END

Trigger, którego zadaniem jest wysłanie maila w przypadku dodania do tabeli SuperUsers nowego wpisu

CREATE TRIGGER TR_SendEmail
ON SuperUsers  
AFTER INSERT
AS  
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'SkillstestAdministrator',  
        @recipients = 'admin@example.com',  
        @body = 'New user added to SuperUsers.',  
        @subject = 'Alert';   
GO

Triggery DDL 

Typ trigger'a uruchamiany w skutek zmian obiektów w bazie danych, poniższy przykład ukazuje trigger uruchamiany gdy wykonana zostanie komenda ALTER TABLE

CREATE TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
 
INSERT INTO TableSchemaChangesHistory
SELECT EVENTDATA(), GETDATE()
 
END

Włączanie / Wyłączanie

Wyzwalacze można lub aktywować lub dezaktywować, jeśli nie chcemy ich usuwać ale jedynie by przestały się wykonywać.

 -- Włączenie (aktywacja) wyzwalacza 
ENABLE TRIGGER TR_SendEmail on SuperUsers
-- Wyłączenie (dezaktywacja) wyzwalacza
DISABLE TRIGGER TR_SendEmail on SuperUsers

Usuwanie wyzwalaczy

DROP TRIGGER TR_SendEmail