Ćwiczenia > Procedury składowane w MS SQL Server 2000 > Planowanie i tworzenie wyzwalaczy

8.6 Planowanie i tworzenie wyzwalaczy



Wyzwalacze (ang. triggers) są szczególnymi procedurami składowanymi. Nie można ich wywołać bezpośrednio z poziomu kodu T-SQL. Wyzwalacze działają w odpowiedzi na wykonywanie operacji INSERT, UPDATE lub DELETE. W tej lekcji poznasz bliżej działanie wyzwalaczy, ich typy oraz przeznaczenie.

Krok 1 - Kiedy stosować wyzwalacze?

Wyzwalacze stosujemy w celu zapewnienia spójności logicznej danych w bazie. Pamiętaj jednak, że jeśli spójność tą można osiągnąć innymi środkami (nakładając ograniczenia lub używając odpowiednich typów danych dla kolumn), to wyzwalacze są ostatnią opcją i raczej się unika ich stosowania bez potrzeby (ze względu na wydajność).

Przykładowe sytuacje, kiedy możesz użyć wyzwalacza:
- jeśli nie wystarcza Ci funkcjonalność deklarowanych metod wymuszania spójności danych (np. ograniczeń w tabelach),
- jeśli zmiany w jednej tabeli muszą dotknąć kolumn w powiązanych tabelach (np. dodanie zakupu powoduje zmniejszenie w innej tabeli ilości towaru na stanie), ale nie licząc pól będących kluczami obcymi (tu można wymusić kaskadowe zmiany bez użycia wyzwalaczy),
- jeśli baza jest zdenormalizowana (czasem się tak zdarza, np. ze względu na współpracę baz danych z hurtowniami danych) i należy przetworzyć dane w celu usunięcia nadmiarowości,
- jeśli wartość w jednej tabeli nie może być równa wartości w innej tabeli,
- jeśli chcesz, by serwer zwracał Twoje komunikaty,
- w innych sytuacjach, w których przy wykonywaniu poleceń DML (bez SELECT) chcesz wykonać jakąś nietypową operację na danych dotkniętych operacją zmiany.

Masz do dyspozycji dwa tpy wyzwalaczy: wyzwalacze typu ZAMIAST (ang. instead of) oraz wyzwalacze typu PO (ang. after). Zobaczmy w działaniu oba typy.

Krok 2 - Zobacz w działaniu wyzwalacz typu ZAMIAST

1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_8_6_1.sql.
5. Zaznacz i uruchom (klawisz F5) fragment kodu oznaczony w komentarzu jako (1).


-- (1) przygotujmy tabele 
-- do testowania wyzwalaczy
USE Northwind
GO

SELECT EmployeeID, LastName, FirstName, BirthDate, ReportsTo
INTO Employees_kopia 
FROM Employees
GO


Wynikiem wykonania powyższego kodu jest utworzenie w bazie danych Northwind tabeli Employees_kopia, w której znajdą się wybrane kolumny skopiowane z tabeli Employees. Tabela ta będzie nam potrzebna do testowania wyzwalaczy.

Możliwe, że we wcześniejszych lekcjach już tabela o takiej nazwie w bazie istnieje. Usuń ją wówczas z bazy i wykonaj powyższy kod w celu utworzenia tabeli na nowo.


6. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (2), by zobaczyć, jakie dane zawiera tabela. Zwróć uwagę na kolumnę ReportsTo - policz, ile razy występuje w niej identyfikator równy 2 (powinieneś naliczyć 5 takich wierszy).


-- (2) zobaczmy, co znajduje sie w tabeli
SELECT * FROM Employees_kopia
GO


EmployeeID  LastName             FirstName  BirthDate               ReportsTo   
----------- -------------------- ---------- ----------------------- ----------- 
1           Davolio              Nancy      1948-12-08 00:00:00.000 2
2           Fuller               Andrew     1952-02-19 00:00:00.000 NULL
3           Leverling            Janet      1963-08-30 00:00:00.000 2
4           Peacock              Margaret   1937-09-19 00:00:00.000 2
5           Buchanan             Steven     1955-03-04 00:00:00.000 2
6           Suyama               Michael    1963-07-02 00:00:00.000 5
7           King                 Robert     1960-05-29 00:00:00.000 5
8           Callahan             Laura      1958-01-09 00:00:00.000 2
9           Dodsworth            Anne       1966-01-27 00:00:00.000 5

(9 row(s) affected)
7. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) utworzmy wyzwalacz typu ZAMIAST
CREATE TRIGGER instead_insert_employee ON Employees_kopia
INSTEAD OF INSERT
AS
IF
(SELECT Count(*) FROM Employees, inserted
WHERE Employees.ReportsTo = inserted.ReportsTo 
AND Employees.ReportsTo IS NOT NULL) < 5
BEGIN
	INSERT INTO Employees_kopia
		SELECT LastName, FirstName, BirthDate, ReportsTo  
		FROM inserted
END
ELSE
BEGIN
	PRINT 'Pracownik nie moze miec wiecej niz 5 podwladnych.'
END
GO


Wynikiem wykonania powyższego kodu jest utworzenie w tabeli Employees_kopia wyzwalacza typu ZAMIAST o nazwie instead_insert_employee dla polecenia INSERT.

Powyższy wyzwalacz służy do zaimplementowania warunku, który mówi, że każdy pracownik może mieć maksymalnie pięciu bezpośrednio podlegających podwładnych (o tym, kto jest szefem, mówi kolumna ReportsTo zawierająca identyfikator szefa). Tego warunku inaczej niż przy pomocy wyzwalacza osiągnąć nie można. Zwróć uwagę na wykorzystanie tabeli inserted, która zawiera dane, które użytkownik usiłuje w danym momencie wstawić do tabeli (przy automatycznym wywołaniu wyzwalacza typu ZAMIAST dane nie zostają wstawione do tabeli Employees_kopia, tylko właśnie do tabeli inserted, z której następnie można skorzystać).

Jak już zapewne wiesz z wykładów, SQL Server oferuje na potrzeby wyzwalaczy dwie tabele tymczasowe - inserted oraz deleted. Pierwsza z nich przechowuje dane, które miały zostać wstawione do tabeli, ale uruchomienie wyzwalacza uprzedziło operację wstawienia. Druga tabela służy do przechowywania danych usuwanych z tabeli oraz danych modyfikowanych (tabela przechowuje dane sprzed modyfikacji).


Polecenie CREATE TRIGGER musi zawsze być pierwszym poleceniem we wsadzie. Pozostałe polecenia w tym samym wsadzie traktowane są jako ciało definicji wyzwalacza.


8. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (4).


-- (4) wyprobujmy dzialanie wyzwalacza
INSERT INTO Employees_kopia(LastName, FirstName, BirthDate, ReportsTo)
VALUES ('Kowalski','Jan','1970/04/10',2)
GO


Powyższy kod jest próbą wstawienia do tabeli Employees_kopia nowego pracownika, którego szefem jest pracownik o identyfikatorze równym 2 (patrz kolumna ReportsTo). Próba kończy się wyświetleniem informacji: Pracownik nie moze miec wiecej niz 5 podwladnych w zakładce Messages.

9. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (2). Zauważ, że nie został dodany żaden nowy wiersz do tabeli.
10. We wykonanym w punkcie 8. (polecenie INSERT) zmień wartość wstawianą w kolumnę ReportsTo z 2 na 5. Zaznacz i uruchom (F5) ten fragment kodu. Tym razem operacja przebiegła pomyślnie.
11. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (2). Zauważ, że został dodany nowy wiersz do tabeli.

Krok 3 - Zobacz w działaniu wyzwalacz typu PO

1. W menu głównym programu Query Analyzer wybierz File - Open.
2. W oknie Open Query File wybierz plik demo_8_6_2.sql.
3. Zaznacz i uruchom (klawisz F5) fragment kodu oznaczony w komentarzu jako (1).


-- (1) przygotujmy dodatkowa tabele
-- do testowania wyzwalaczy
USE Northwind
GO

CREATE TABLE Employees_deleted
(
	EmployeeID int,
	LastName nvarchar(40),
	FirstName nvarchar(20),
	BirthDate datetime,
	ReportsTo int,
	WhenDeleted datetime
)
GO


Wynikiem wykonania powyższego kodu jest utworzenie w bazie danych Northwind nowej tabeli Employees_deleted, w której będziemy chcieli przechowywać dane usuwane z tabeli Employees_kopia (dodatkowo dodaliśmy kolumnę WhenDeleted, która będzie przechowywała datę i czas usunięcia wiersza z tabeli Employees_kopia).

4. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (2).


-- (2) utworzmy dwa wyzwalacze typu PO
CREATE TRIGGER after_delete_employee1 ON Employees_kopia
AFTER DELETE
AS
INSERT INTO Employees_deleted
	SELECT *, GetDate() 
	FROM deleted
GO

CREATE TRIGGER after_delete_employee2 ON Employees_kopia
AFTER DELETE
AS
	PRINT 'Wykonano usuwanie.'
GO


Wynikiem wykonania powyższego kodu jest utworzenie w tabeli Emoloyees_kopia dwóch wyzwalaczy typu PO: after_delete_employee1 oraz after_delete_employee2. Pierwszy wyzwalacz ma za zadanie skopiować usuwane z tabeli Employees_kopia dane do tabeli Employees_deleted. Wykorzystuje przy tym tymczasową tabelę deleted. Drugi wyzwalacz jest prostszy i jedynym jego zadaniem jest poinformowanie o wykonaniu polecenia DELETE (nieważne, czy polecenie usunęło jakieś wiersze z tabeli).

5. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) przetestujmy dzialanie wyzwalaczy
DELETE FROM Employees_kopia WHERE LastName = 'Davolio'
GO


Wynikiem wykonania powyższego kodu jest usunięcie z tabeli Employees_kopia pracownika o nazwisku Davolio. W zakładce Messages pojawiają się dwa komunikaty:

(1 row(s) affected)

Usunieto pracownika.
Pierwszy komunikat dotyczy wyzwalacza after_delete_employee1 (jeden usunięty wiersz), zaś drugi wyzwalacza after_delete_employee2 (informacja o wykonaniu polecenia usuwania).

6. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (4) zobaczmy zawartosc tabeli 
-- zawierajacej usuniete rekordy
SELECT * FROM Employees_deleted
GO


W wyniku wykonania powyższego kodu wyświetlana jest zawartość tabeli Employees_deleted, która w tej chwili powinna zawierać wiersz właśnie usunięty z tabeli Employees_kopia, na przykład:

EmployeeID  LastName   FirstName   BirthDate               ReportsTo   WhenDeleted
----------- ---------- ----------- ----------------------- ----------- ----------------------- 
1           Davolio    Nancy       1948-12-08 00:00:00.000 2           2004-05-10 13:28:38.933

(1 row(s) affected)

Krok 4 - Zmień kolejność wykonywania wyzwalaczy typu PO

1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (5) zmienmy kolejnosc wykonywania wyzwalaczy
EXEC sp_settriggerorder 
@triggername = 'after_delete_employee2',
@order = 'first',
@stmttype = 'DELETE'
GO


Wynikiem wykonania powyższego kodu jest ustawienie wyzwalacza after_delete_employee2 na początku (ang. first - pierwszy) kolejki wykonywania wyzwalaczy typu PO dla operacji DELETE w tabeli Employees_kopia.

Jeśli nie ustalisz kolejności wykonywania wyzwalaczy typu PO w tabeli, to będą one wykonywane w przypadkowej kolejności. Do ustalenia kolejności możesz użyć procedury systemowej sp_settiggerorder. Procedura ta ma trzy parametry: nazwę wyzwalacza, parametr określający położenie wyzwalacza w kolejce (możliwe opcje: first - pierwszy w kolejce, last - ostatni w kolejce, none - położenie nieokreślone) oraz nazwę polecenia dla którego porządkujemy kolejność wykonywania wyzwalaczy.


2. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (4) (polecenie DELETE). Zajrzyj do zakładki Messages i sprawdź, czy najpierw wyświetlony został komunikat wyzwalacza after_delete_employee2.

Oczywiście wyzwalaczami możesz zarządzać także przy pomocy programu Enterprise Manager. Klikasz prawym przyciskiem myszy na wybranej tabeli i z menu kontekstowego wybierasz Wszystkie zadania - Manage Triggers.... Ukazuje się okno zarządzania wyzwalaczami - Trigger Properties.



Rys. 8.6.1 Enterprise Manager - okno zarządzania wyzwalaczami




Do projektu Prace dyplomowe zaplanuj i utwórz kilka wyzwalaczy (przynajmniej jeden każdego typu). Ich definicje zapisz w skrypcie o nazwie wyzwalacze.sql. Dołącz ten skrypt do projektu. Do każdego wyzwalacza dołącz w dokumentacji projektu opis i uzasadnienie stworzenia.


Przejdź dalej



Ćwiczenia > Procedury składowane w MS SQL Server 2000 > Planowanie i tworzenie wyzwalaczy