Wyzwalacze (ang. trigers) są specjalnym rodzajem procedur składowanych które są powiązane z językiem manipulowania danymi (DML) i jego zdarzeniami dotyczącymi tabel i widoków. Wyzwalacze nie mogą być wykonywane bezpośrednio. Wyzwalacze są wykonywane gdy zajdzie zdarzenie języka DML z którym są powiązane np INSERT, UPDATE lub DELETE. Wyzwalacze SQL Serwera działają w powiązaniu z dwoma rodzajami zdarzeń:

  • Zdarzenia manipulacji danymi (DML trigers).
  • Zdarzenia definicji danych (DDL trigers) np CREATE TABLE.

Wyzwalacze DML

Wyzwalacze DML są partiami kodu T-SQL powiązanymi z tabelą  dla której chcemy przechwycić wystąpienie zdarzenia DML takiego jak INSERT, UPDATE lub DELETE lub ich kombinację. SQL Serwer pozwala na użycie dwóch rodzajów wyzwalaczy DML:

  • AFTER - ten wyzwalacz jest wykonywany po wystąpieniu zdarzenia i może być zdefiniowany tylko dla stałych tabel.
  • INSTEAD OF - ten wyzwalacz jest wykonywany zamiast zdarzenia i może być zdefiniowany dla stałych tabel i widoków.

Wyzwalacz wykonywany est tylko raz dla każdego zdarzenia DML, bez względu na to ile wierszy jest modyfikowanych. Schemat bazy danych wyzwalacza musi być taki sam jak schemat bazy danych tabeli lub widoku z którym jest powiązany. Wyzwalaczy można używać do audytu, egzekwowania skomplikowanej logiki integralności itp. Wycofanie zmian poprzez komendę ROLLBACK powoduje wycofanie wszystkich zmian jakie zostały dokonane przez wyzwalacz. Jednakże użycie ROLLBACK TRAN w wyzwalaczu może mieć pewne niepożądane wyniki. Zamiast tego zaleca się użycie instrukcji THROW lub RAISERROR i użycie standardowych procedur obsługi błędów. Wyjściem z wyzwalacza jest komenda RETURN. W obu typach wyzwalaczy można użyć tabeli zwanych INSERTED i DELETED. Tabele te zawierają wiersze które zostały zmodyfikowane poprzez zdarzenie które wywołało wyzwalacz. Tabela INSERTED zawiera nowe dane które zostały zmodyfikowane przez instrukcje INSERT lub UPDATE. Tabela DELETED zawiera obraz starych danych które zostały zmodyfikowane przez instrukcje UPDATE lub DELETE.

Wyzwalacz AFTER

Wyzwalacze AFTER mogą być definiowane jedynie dla tabel. W tych wyzwalaczach kod jest wykonywany po tym jak instrukcja języka DML spełni wszystkie ograniczenia nałożone na tabelę jak np ograniczenie klucza obcego lub unikalność. Jeżeli ograniczenie jest naruszone to instrukcja nie zostanie wykonana i wyzwalacz nie zostanie uruchomiony. Przykładowy wyzwalacz AFTER dla tabeli Sales.OrderDetails zwracający ilość modyfikowanych wierszy:

IF OBJECT_ID('Sales.tr_SalesOrderDetailsDML', 'TR') IS NOT NULL
DROP TRIGGER Sales.tr_SalesOrderDetailsDML;
GO
CREATE TRIGGER Sales.tr_SalesOrderDetailsDML ON Sales.OrderDetails
AFTER DELETE
,INSERT
,UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0
RETURN;
SET NOCOUNT ON;
SELECT COUNT(*) AS InsertedCount
FROM Inserted;
SELECT COUNT(*) AS DeletedCount
FROM Deleted;
END;

Wyzwalacz tworzymy używając instrukcji CREATE TRIGGER, po czym podajemy jego nazwę i po wyrażeniu ON wybieramy tabelę z którą ma być powiązany. Po wyborze tabeli deklarujemy typ wyzwalacza jako AFTER lub INSTEAD OF. Typ AFTER zostanie wybrany również jeżeli napiszemy FOR. Następnie deklarujemy dla jakich operacji języka DML ma być uruchamiany wyzwalacz po czym deklarujemy ciało wyzwalacza. Kiedy wykonana została operacja INSERT, UPDATE lub DELETE ale żadne wiersze nie zostały zmodyfikowane to nie ma potrzeby uruchamiać wyzwalacza. Można poprawić wydajność wyzwalacza poprzez sprawdzenie czy rekordy zostały zmodyfikowane. Można dokonać tego poprzez sprawdzenie wartości zmiennej globalnej @@ROWCOUNT na samym początku ciała wyzwalacza. Jeżeli @@ROWCOUNT jest równe zero to opuszczamy wyzwalacz komendą RETURN. Zwracanie wyników z wyzwalaczy nie jest dobrą praktyką. Można wyłączyć tę możliwość poprzez procedurę sp_configure i zmianę właściwości Disallow Results From Triggers. Utwórzmy wyzwalacz który będzie wykonywał jakąś pracę. Tabela Production.Categories nie posiada ograniczenia unikalności na kolumnie categoryname. Poniższy kod wymusi unikalność za pomocą wyzwalacza:

IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL
DROP TRIGGER Production.tr_ProductionCategories_categoryname;
GO
CREATE TRIGGER Production.tr_ProductionCategories_categoryname ON Production.Categories
AFTER INSERT
,UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0
RETURN;
SET NOCOUNT ON;
IF EXISTS (
SELECT COUNT(*)
FROM Inserted AS I
JOIN Production.Categories AS C ON I.categoryname = C.categoryname
GROUP BY I.categoryname
HAVING COUNT(*) > 1
)
BEGIN
THROW 50000
,'Duplicate category names not allowed'
,0;
END;
END;
GO

Przetestujmy wyzwalacz za pomocą wyrażenia:

INSERT INTO Production.Categories (categoryname,description)
VALUES ('TestCategory1', 'Test1 description v1');

Komenda INSERT zadziała za pierwszym razem ponieważ kategoria o podanej nazwie nie istnieje. Przy kolejnej próbie wykonania kodu otrzymamy w rezultacie błąd:

Msg 50000, Level 16, State 0, Procedure tr_ProductionCategories_categoryname, Line 17 Duplicate category names not allowed

Zagnieżdżone wyzwalacze AFTER

Wyzwalacze AFTER mogą być zagnieżdżone. Przykładowo mamy wyzwalacz na tabeli A który aktualizuje tabelę B. Tabela B posiada również wyzwalacz. Maksymalna liczba zagnieżdżeń wyzwalaczy to 32. Kiedy zagnieżdżenia tworzą pętlę to po 32 zagnieżdżeniu wykonywanie wyzwalaczy zostanie zatrzymane. Wykonywanie zagnieżdżonych wyzwalaczy w SQL Serwerze można wyłączyć używając procedury sp_configure:

EXEC sp_configure 'nested triggers', 0;
RECONFIGURE;

Wyzwalacze INSTEAD OF

Wyzwalacze INSTEAD OF wykonują partię kodu T-SQL zamiast instrukcji INSERT, UPDATE lub DELETE. Wyzwalacze INSTEAD OF mogą być tworzone dla tabel i widoków, najczęściej używa się ich w widokach. Powodem jest to, że kiedy używamy komendy UPDATE na widoku to tylko jedna tabela może być aktualizowana jednocześnie. Wyzwalacz INSTEAD OF może być użyty dla komendy UPDATE na widoku i zamiast wykonywać aktualizację zastąpić ją aktualizacją więcej niż jednej tabeli widoku. Przykładowo przeróbmy wyzwalacz z poprzedniej sekcji na wyzwalacz INSTEAD OF dla instrukcji INSERT:

IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL
DROP TRIGGER Production.tr_ProductionCategories_categoryname;
GO
CREATE TRIGGER Production.tr_ProductionCategories_categoryname
ON Production.Categories
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT COUNT(*)
FROM Inserted AS I
JOIN Production.Categories AS C
ON I.categoryname = C.categoryname
GROUP BY I.categoryname
HAVING COUNT(*) > 1 ) BEGIN
THROW 50000, 'Duplicate category names not allowed', 0;
END;
ELSE
INSERT Production.Categories (categoryname, description)
SELECT categoryname, description FROM Inserted;
END;
GO

Funkcje wyzwalaczy DML

Można używać dwóch funkcji w wyzwalaczach do otrzymania informacji na temat zmian jakie zaszły:

  • UPDATE() - można użyć yej funkcji aby dowiedzieć się czy dana kolumna była używana w komendzie INSERT lub UPDATE. Przykładowo można użyć jej tak w wyzwalaczu: 
    IF UPDATE(qty)
PRINT 'Column qty affected';
    Funkcja ta zwróci TRUE jeżeli kolumna była używana w komendzie UPDATE lub INSERT.
  • COLUMN_UPDATED() - można użyć tej funkcji znając sekwencję kolumn w tabeli. Wymaga ona użycia operatora AND (&) do podglądu jakie kolumny były aktualizowane.

Ćwiczenia

I. Kontrola zawartości tabel INSERTED i DELETED

    1. Stwórz wyzwalacz dla tabeli Sales.OrderDetails jak poniżej: 
      USE TSQL2012
GO
IF OBJECT_ID('Sales.tr_SalesOrderDetailsDML', 'TR') IS NOT NULL
DROP TRIGGER Sales.tr_SalesOrderDetailsDML;
GO
CREATE TRIGGER Sales.tr_SalesOrderDetailsDML
ON Sales.OrderDetails
AFTER DELETE, INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
SELECT COUNT(*) AS InsertedCount FROM Inserted;
SELECT COUNT(*) AS DeletedCount FROM Deleted;
END;
      2. Spróbuj usunąć nieistniejące rekordy z tabeli

Sales.OrderDetails

      :
DELETE FROM Sales.OrderDetails
WHERE orderid = 10249 and productid in (15, 16);
GO
      Dodaj dane do tabeli

Sales.OrderDetails

      :
INSERT INTO Sales.OrderDetails (orderid,productid,unitprice,qty,discount)
VALUES (10249, 16, 9.00, 1, 0.60) ,
(10249, 15, 9.00, 1, 0.40);
GO
  1. Zaktualizuj jeden z poprzednio dodanyc wierszy: 
    UPDATE Sales.OrderDetails
SET unitprice = 99
WHERE orderid = 10249 AND productid = 16;
GO
  2. Usuń dodane wiersze: 
    DELETE FROM Sales.OrderDetails
WHERE orderid = 10249 and productid in (15, 16);
  3. Usuń wyzwalacz: 
    IF OBJECT_ID('Sales.tr_SalesOrderDetailsDML', 'TR') IS NOT NULL
DROP TRIGGER Sales.tr_SalesOrderDetailsDML;
GO

II. Użycie wyzwalaczy AFTER do obsługi logiki biznesowej

  1. napisz wyzwalacz który będzie wykonywał następujące czynności: jeżeli jakikolwiek wiersz z tabeli Sales.OrderDetails posiada cenę jednostkową mniejszą niż 10 to nie może posiadać rabatu większego niż 0.5: 
    USE TSQL2012;
GO
-- Step 1: Basic trigger
IF OBJECT_ID('Sales.OrderDetails_AfterTrigger', 'TR') IS NOT NULL
DROP Trigger Sales.OrderDetails_AfterTrigger;
GO
CREATE TRIGGER Sales.OrderDetails_AfterTrigger ON Sales.OrderDetails
AFTER INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- Perform the check
DECLARE @unitprice AS money, @discount AS NUMERIC(4,3);
SELECT @unitprice = unitprice FROM inserted;
SELECT @discount = discount FROM inserted;
IF @unitprice < 10 AND @discount > .5
BEGIN
THROW 50000, 'Discount must be < = .5 when unitprice < 10', 0;
END;
END;
GO
  2. Przetestuj wyzwalacz na dwóch wierszach z których pierwszy nie spełnia warunku wyzwalacza: 
    INSERT INTO Sales.OrderDetails (orderid,productid,unitprice,qty,discount)
VALUES (10249, 16, 9.00, 1, 0.60) ,
(10249, 15, 9.00, 1, 0.40);
  3. Przetestuj wyzwalacz na dwóch wierszach z których drugi nie spełnia warunku wyzwalacza: 
    INSERT INTO Sales.OrderDetails (orderid,productid,unitprice,qty,discount)
VALUES (10249, 15, 9.00, 1, 0.40),
(10249, 16, 9.00, 1, 0.60) ;
  4. Usuń złe wiersze: 
    DELETE FROM Sales.OrderDetails WHERE orderid = 10249 AND productid IN (15, 16);
GO
  5. Popraw wyzwalacz, tak aby sprawdzał wszystkie wiersze: 
    IF OBJECT_ID('Sales.OrderDetails_AfterTrigger', 'TR') IS NOT NULL
DROP Trigger Sales.OrderDetails_AfterTrigger;
GO
CREATE TRIGGER Sales.OrderDetails_AfterTrigger ON Sales.OrderDetails
AFTER INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
-- Check all rows
IF EXISTS(SELECT * FROM inserted AS I WHERE unitprice < 10 AND discount > .5)
BEGIN
THROW 50000, 'Discount must be < = .5 when unitprice < 10', 0;
END
END
GO
  6. Przetestuj wyzwalacz na dwóch wierszach z których drugi nie spełnia warunku wyzwalacza: 
    INSERT INTO Sales.OrderDetails (orderid,productid,unitprice,qty,discount)
VALUES (10249, 15, 9.00, 1, 0.40),
(10249, 16, 9.00, 1, 0.60) ;
  7. Usuń wyzwalacz: 
    IF OBJECT_ID('Sales.OrderDetails_AfterTrigger', 'TR') IS NOT NULL
DROP Trigger Sales.OrderDetails_AfterTrigger;
GO

Podsumowanie

  1. Wyzwalacze DML są partiami kodu T-SQL podobnymi do procedur składowanych powiązanymi z tabelami i widokami. Można użyć wyzwalaczy do audytu, egzekwowania skomplikowanych zasad integralności, dodatkowej logiki biznesowej itp.
  2. Wyzwalacze są uruchamiane po wystąpieniu zdarzeń DML takich jak INSERT, UPDATE lub DELETE.
  3. T-SQL pozwala na użycie dwóch rodzajów wyzwalaczy DML: AFTER i INSTEAD OF.
  4. W obu typach można używać tabel INSERTED i DELETED.