Jak korzystać z wyzwalaczy SQL Server

Wyzwalacze to specjalny rodzaj procedur przechowywanych, które wykonują się w odpowiedzi na działania w obiektach SQL Server. Wyzwalacze są przechowywane w pamięci systemu i są wykonywane tylko wtedy, gdy nastąpi określone zdarzenie. W przeciwieństwie do normalnych procedur przechowywanych, wyzwalacze nie akceptują parametrów ani wykonywane ręcznie.

W tym artykule zbadamy, jak tworzyć i pracować z wyzwalaczami na SQL Server.

Istnieją trzy główne typy wyzwalaczy w SQL Server:

1. DML wyzwalacze
2. DDL wyzwalacze
3. Wyzwalacze logowania

Poznajmy te wyzwalacze w tym przewodniku.

SQL Server DML Triggers

DML lub manipulacja danymi Manipulacjami wyzwalacze są rodzajem wyzwalaczy, które strzelają w odpowiedzi na działanie wkładki, aktualizacji i usuwania na tabeli lub widoku. Wyzwalacze DML będą uruchomione po wykonaniu dowolnej prawidłowej operacji, czy dotknięto jakiekolwiek wiersze.

Utwórz DML po wyzwoleniu

Na serwerze SQL możesz utworzyć spust DML za pomocą instrukcji Utwórz Trigger.

Utwórz nazwę_nazową Schema.Trigger_name
Na nazwie Stoli
Po [wstaw, aktualizuj, usuń]
JAK
-- Kod wyzwalający do uruchomienia

Rozbijmy powyższą składnię:

1. Schema_name - nazwa schematu, w którym jest przechowywany.
2. Trigger_name - Nazwa spustu.
3. TABLE_NAME - Nazwa tabeli, w której będzie miał zastosowanie określony wyzwalacz.
4. Po - klauzula po zdefiniowaniu, w których warunki będą miały zastosowanie.

Aby nauczyć się tworzyć i używać wyzwalacza DML, weźmy praktyczny przykład.

Utwórz przykładową bazę danych i wstaw dane podane w zestawie zapytań pokazanych poniżej:

-- Utwórz przykładową bazę danych
Utwórz sprzedaż bazy danych;
IŚĆ
-- przełącz db;
Użyj sprzedaży;
-- Utwórz tabelę
Utwórz sprzedaż tabeli (
Id Int Identity (1,1) Nie zerowy klucz podstawowy,
Product_name varchar (50),
cena,
ilość int
);
-- Włóż przykładowe dane
Wstaw do sprzedaży (nazwa_produktu, cena, ilość)
Wartości („ładowarka iPhone”, 9 USD.99, 10),
(„Google Chromecast”, 59 USD.25, 5),
(„PlayStation Dualsense Wireless kontroler”, 69 USD.00, 100),
(„Xbox Series S”, 322 USD.00, 3),
(„Oculus Quest 2”, 299 USD.50, 7),
(„Netgear Nighthawk”, 236 USD.30, 40),
(„Redragon S101”, 35 USD.98, 100),
(„Figura akcji Star Wars”, 17 USD.50, 10),
(„Mario Kart 8 Deluxe”, 57 USD.00, 5);

Po uzyskaniu przykładowych danych możemy kontynuować i utworzyć spust DML, aby strzelać po operacji aktualizacji na tabeli.

Rozważ przykład pokazany poniżej:

-- Utwórz tabelę, aby przechowywać historię aktualizacji
Utwórz tabelę modyfikowaną datę (id, date_ DateTime)
IŚĆ
-- Utwórz spust
Utwórz Trigger DBO.aktualizacja_trigger
O sprzedaży
po aktualizacji
Nie do replikacji
JAK
ZACZYNAĆ
Włóż do zmodyfikowanej datę
Wybierz id, getDate ()
Od wstawionego
KONIEC

Powyższe zapytanie utworzy spust, który strzela, gdy wykonamy aktualizację na tabeli. Aby przetestować spust, możemy uruchomić aktualizację jako:

-- Tabela aktualizacji
Aktualizacja zestawu sprzedaży Cena = 10 USD.10
Gdzie id = 1;

Po wykonaniu możemy sprawdzić, czy spust działał, wybierając kolumny w tabeli zmodyfikowanej.

-- Sprawdź tabelę zmodyfikowaną
Wybierz * z ModifiedDate;

W SSMS można wyświetlić wyzwalacze na stole, rozszerzając opcję wyzwalaczy:

Utwórz zamiast wyzwalaczy

Innym typem wyzwalaczy DML na SQL Server jest zamiast wyzwalaczy. Są to rodzaj wyzwalaczy, które wykonują zamiast instrukcji DML. Na przykład, jeśli określamy instrukcję Usuń, możemy użyć zamiast wyzwalaczy do uruchomienia przed operacją.

Składnia do tworzenia zamiast spustu jest jak pokazano:

Utwórz nazwę_nazową Schema.Trigger_name
Na nazwie Stoli
Zamiast [wstaw, aktualizuj, usuń]
JAK
-- Oświadczenia wyzwalające

Na przykład zapytanie poniżej tworzy wyzwalacz, który wyświetla komunikat, gdy operacja wstawka jest wykonywana na tabeli.

-- Utwórz zamiast spustu
Utwórz Trigger zamiast_insert
O sprzedaży
zamiast wstawki
JAK
ZACZYNAĆ
Wybierz „Nie można wstawić w tej tabeli” jako błąd
KONIEC
-- Uruchom zamiast_insert wyzwalacz
Wstaw do sprzedaży (nazwa_produktu, cena, ilość)
Wartości („ładowarka iPhone”, 9 USD.99, 10);

Po uruchomieniu powyższego zapytania powinniśmy uzyskać wiadomość wskazującą, że nie możemy wykonać wkładki na tabeli.

Błąd
-------------------------------

Nie możesz wstawić w tym tabeli

SQL DDL wyzwalacze

DDL lub język definicji danych to wyzwalacze, które reagują na zdarzenia na serwer lub bazę danych zamiast tabeli. DDL wyzwalacze odpowiedzą na takie zdarzenia, jak Drop, Grant, Deny, Revok, Aktualizuj statystyki, tworzyć i zmieniać.

Utwórz wyzwalacze DDL

Składnia do utworzenia wyzwalacza DDL jest jak pokazano:

Utwórz Trigger Trigger_name
Na bazie danych | Cały serwer
Z ddl_trigger_parameters
Dla Event_Type | event_group
JAK
-- Oświadczenia wyzwalające

Możemy rozbić składnię jako:

1. Trigger_name - unikalna nazwa spustu.
2. baza danych lub cały serwer - określ, gdzie jest wykonywany wyzwalacz. Baza danych, jeśli ma zastosowanie na bazie danych lub całego serwera, jeśli ma zastosowanie w zakresie serwera.
3. ddl_trigger_parameter - parametry DDL, takie jak wykonanie jako lub szyfruj AS.
4. Event_Type - zdarzenie DDL, które wystrzeliwuje spust.

Poniższe zapytanie Utwórz wyzwalacz DDL, który strzela, gdy wystawiono instrukcję tabeli upuszczenia.

-- Utwórz spust DDL
Utwórz Trigger Drop_ddl_trigger
Na bazie danych
Dla Drop_Table
JAK
ZACZYNAĆ
Wybierz EventData ();
KONIEC

Po uruchomieniu zdarzenia Drop w bazie danych wyzwalacz wyświetli informacje o zdarzeniu za pomocą funkcji eventData ().

Możemy przetestować spust:

-- Wyzwalacz testowy
Sprzedaż tabeli zrzutu;

Zapytanie powinno zwrócić informacje XML o wydarzeniu jako:

W SSMS można wyświetlić wyzwalacze, rozszerzając wyzwalacze bazy danych w ramach programowania w docelowej bazie danych.

Włącz/wyłącz wyzwalacze

SQL Server umożliwia włączenie i wyłączanie wyzwalaczy. Aby włączyć spust na stole, użyj zapytania jako:

Włącz Trigger Update_trigger w sprzedaży;

Gdzie aktualizacja_trigger reprezentuje nazwę i sprzedaż wyzwalacza reprezentują nazwę tabeli.

Możesz także włączyć wszystkie wyzwalacze na stole jako:

Włącz wyzwalacz wszystkie na nazwa_beli;

Aby włączyć wyzwalacz bazy danych, użyj zapytania:

Włącz Trigger Drop_ddl_trigger w sprzedaży;

Tutaj Drop_ddl_trigger reprezentuje nazwę i sprzedaż wyzwalacza reprezentują bazę danych.

Aby włączyć wszystkie wyzwalacze bazy danych, użyj zapytania:

Włącz wyzwalacz w sprzedaży;

Aby wyłączyć spust tabeli lub bazy danych, wymień słowo kluczowe Włącz z wyłączeniem ☺️.

Usuń spust

Aby usunąć spust, możesz użyć instrukcji Drop jak pokazano:

Upuść spust, jeśli istnieje nazwa Trigger;

SQL Server pokazuje wszystkie wyzwalacze

Aby wyświetlić wszystkie wyzwalacze w instancji SQL Server, użyj zapytania, jak pokazano:

Wybierz nazwę, type_desc, is_disabled, is_instead_of_trigger z SYS.Wyzwalacza gdzie typ = „tr”

Zapytanie powinno zwrócić wszystkie wyzwalacze w instancji SQL Server jako:

Wyzwalacze logowania SQL Server

Wyzwalacze logowania są rodzajem wyzwalaczy, które wykonują, gdy na serwerze występuje aktywność logowania. Tego rodzaju wyzwalacze działają po udanym uwierzytelnianiu, ale przed utworzeniem sesji użytkownika. Ponieważ są one używane do obsługi aktywności logowania, tworzymy je na poziomie serwera, jak pokazano w poniższym przykładzie:

OSTROŻNOŚĆ: Poniższy czynnik może zapobiec przyszłym loginom na serwerze. Upewnij się, aby usunąć przed loginem.

OSTROŻNOŚĆ - ☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️.

-- Utwórz wyzwalacz logowania
Utwórz wyzwalacz login_tg
Na wszystkich serwerze
Dla logowania jako
ZACZYNAĆ
Wybierz „Wyzwalacz po zalogowaniu” jako [komunikat]
KONIEC

Spust wyświetli komunikat, gdy użytkownik zaloguje się na serwerze.

Wniosek

W tym przewodniku zrozumiałeś różne rodzaje wyzwalaczy, jak tworzyć, włączać, wyłączać, usuwać i przeglądać wyzwalacze na SQL Server.