Ć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 ZAMIAST1. 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).
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.
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).
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).
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ć).
8. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (4).
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 PO1. 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).
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).
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).
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).
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 PO1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).
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.
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.
Przejdź dalej ![]() |
|||||||||||||||||||||||||||||||
![]() Ćwiczenia > Procedury składowane w MS SQL Server 2000 > Planowanie i tworzenie wyzwalaczy |