Wyzwalacze to jednostki bazy danych w SQL Server. Technicznie rzecz biorąc, są one szczególną klasą wywołań funkcji, które odpowiadają na określone operacje bazy danych.
Ten niezbędny przewodnik zawiera szczegółowe informacje na temat wyzwalaczy SQL, które mogą być bardzo przydatne w Twoim zawodzie. Zacznijmy!
Spis treści:
Co to są wyzwalacze SQL?
Słowo „wyzwalacz” opisuje stwierdzenie, że serwer automatycznie wykonuje zapytanie za każdym razem, gdy zmienia się zawartość bazy danych.
Wyzwalacz to grupa specjalnie nazwanych zapytań SQL, które są przechowywane w przestrzeni pamięci. Jest to specyficzny typ wywołania funkcji, który jest natychmiast wywoływany za każdym razem, gdy ma miejsce jakiekolwiek zdarzenie w bazie danych. Każdy wyzwalacz ma przypisaną do niego tabelę.
Na przykład wyzwalacz może być aktywowany za każdym razem, gdy do określonej tabeli zostanie dodana nowa kolumna lub gdy zostaną zmienione określone rekordy.
Według Microsoft Developer Network wyzwalacze to szczególna klasa procedur przechowywanych. W instrukcji wyzwalacza wstępnie definiujemy, kiedy wyzwalacz powinien zostać uruchomiony, a następnie podajemy akcję, która powinna zostać podjęta po aktywacji wyzwalacza.
Składnia:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
Wyjaśnienie każdego parametru
- CREATE TRIGGER trigger_name — służy do skonstruowania wyzwalacza lub zmiany nazwy istniejącego wyzwalacza.
- PRZED/PO — to zapytanie służy do określenia czasu wykonania wyzwalacza (przed lub po określonym zdarzeniu).
- INSERT/UPDATE/DELETE – Opisuje akcję, którą chcemy wykonać na stołach.
- ON tableName – Tutaj definiujemy nazwę tabeli, aby skonfigurować wyzwalacz.
- FOR EACH ROW – Ta instrukcja odnosi się do wyzwalacza wiersza, co oznacza, że wyzwalacze będą wykonywane po każdej zmianie wiersza.
- trigger_body — określa akcję, która ma zostać podjęta po aktywowaniu wyzwalacza.
Wyzwalacze to przechowywane funkcje z charakterystycznymi tożsamościami, które umożliwiają nam ponowne wykorzystanie zapytań, które zostały już wykonane i bezpiecznie przechowywane w pamięci. Spróbujmy teraz zrozumieć, dlaczego SQL ich potrzebuje.
Wyzwalacze są najczęściej używane do regulowania wdrażania kodu za każdym razem, gdy ma miejsce zdarzenie. Innymi słowy, użycie wyzwalaczy jest idealnym wyborem, jeśli chcesz, aby określony fragment kodu był stale uruchamiany w odpowiedzi na określone zdarzenie.
Poniżej przedstawiono niektóre zalety korzystania z wyzwalaczy w operacjach na bazie danych SQL.
Integralność referencyjna jest kluczową podstawową właściwością systemów relacyjnych baz danych. Oznacza to, że dane przechowywane w systemie bazodanowym muszą być zawsze dokładne dla każdej transakcji i każdej operacji.
Jeśli dwie tabele znajdują się w oddzielnych bazach danych lub systemach, nie ma sposobu, aby zapewnić w nich walidację danych przy użyciu wartości ograniczających. W takiej sytuacji wyzwalacze są jedyną opcją do wykonania.
Kombinacja argumentów wyzwalających
Dla każdej tabeli możemy określić sześć różnych typów wyzwalaczy. Są to kombinacje argumentów wyzwalaczy zawartych w wyzwalaczach na poziomie wiersza SQL.
PRZED WSTAWIANIEM: Te wyzwalacze wykonują akcję na wierszach przed wykonaniem jakichkolwiek operacji WSTAWIANIA w określonej tabeli lub w bazie danych.
PO WSTAWIENNIU: Wykonuje akcję na wierszach bezpośrednio po każdej czynności INSERT w bazie danych.
PRZED AKTUALIZACJĄ: W przypadku tych wyzwalaczy funkcja w wierszach jest wykonywana przed wykonaniem akcji UPDATE w bazie danych.
PO AKTUALIZACJI: Wykonuje akcję na wierszach bezpośrednio po dowolnej bazie danych lub określonej aktywności UPDATE w tabeli.
PRZED DELETE: Wykonuje określoną operację na wierszach, nawet zanim baza danych lub tabela zostanie poddana działaniu DELETE.
PO USUNIĘCIU: Te wyzwalacze wykonują akcję na wierszach po każdej transakcji DELETE.
Rodzaje wyzwalaczy SQL
Wyzwalacze SQL to przechowywane funkcje, które są uruchamiane natychmiast po wystąpieniu określonych zdarzeń. Przypomina planowanie sterowane zdarzeniami. Kolejne sytuacje mogą rozpocząć wykonywanie wyzwalaczy.
Wyzwalacze DML – DML to skrót od Data Manipulation Language. Wykonanie kodu w reakcji na modyfikację danych umożliwia DML Triggers. Ten wyzwalacz jest aktywowany, gdy wykonywane są polecenia DML, takie jak INSERT, UPDATE i DELETE. Są one również nazywane „wyzwalaczami poziomu tabeli”.
Wyzwalacze DDL – DDL oznacza język definicji danych. Wyzwalacze DDL umożliwiają nam uruchamianie kodu w reakcji na zmiany w schemacie bazy danych, takie jak dodawanie lub usuwanie tabel lub zdarzenia serwera, na przykład podczas ewidencjonowania użytkownika. Są to tak zwane „Wyzwalacze poziomu bazy danych”.
Te wyzwalacze mogą być aktywowane, gdy określone instrukcje DDL, takie jak CREATE, ALTER lub DROP, są wykonywane w aktywnej bazie danych. Można je również wykorzystać do śledzenia i zarządzania wykonywanymi czynnościami.
Wyzwalacze LOGON — za każdym razem, gdy wystąpi jakiekolwiek zdarzenie LOGON (uruchomienie, logowanie, wylogowanie, zamknięcie), wyzwalacze logowania są natychmiast wywoływane. Są one przeprowadzane tylko po procesie uwierzytelniania użytkownika, nawet przed zainicjowaniem transakcji użytkownika. Wyzwalacze LOGON nie zostaną wyzwolone, jeśli autoryzacja nie powiedzie się.
Te wyzwalacze mogą służyć do rejestrowania historii logowania lub ustanawiania ograniczenia zdarzeń dla określonego logowania, a także innych funkcji audytu i zarządzania tożsamością dla połączeń z serwerem.
Wyzwalacze CLR — CLR oznacza środowisko uruchomieniowe języka wspólnego. Wyzwalacze CLR są rzeczywiście unikalnym podzbiorem wyzwalaczy zbudowanych głównie na CLR w ramach technologii .NET. Te wyzwalacze są przydatne, jeśli wyzwalacz musi wykonać wiele obliczeń lub musi odnosić się do jednostki innej niż SQL.
Wyzwalacze DML i DDL można rzeczywiście skonstruować, włączając kodowanie obsługiwanych wyzwalaczy CLR w technologiach .NET, w tym Visual Basic, C# i F-sharp.
Przykładowy wyzwalacz serwera SQL
Rozumiemy te koncepcje wyzwalaczy na przykładzie.
Najpierw utwórzmy bazę danych za pomocą instrukcji SQL.
CREATE DATABASE testdb; use testdb;
Tutaj jako nazwę bazy danych podałem „testdb”. Następnym krokiem jest stworzenie stołu.
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
Stworzyłem tabelę do przechowywania danych ucznia. A oto polecenie opisujące strukturę tabeli. Tutaj „student” to nazwa stołu, którą podałem.
DESC student;
Poniżej znajduje się struktura utworzonej przeze mnie tabeli.
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
Po utworzeniu tabeli kolejnym krokiem jest skonfigurowanie wyzwalacza. Spróbujmy użyć argumentu BEFORE INSERT.
Nazwa utworzonego przeze mnie wyzwalacza to „znaki”. Gdy tylko tabela zostanie zmodyfikowana z ocenami ucznia, poniższy wyzwalacz próbuje automatycznie określić ogólną ocenę ucznia.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Ponieważ musimy zastąpić dane wierszy, a nie pracować ze starymi, zdefiniowaliśmy „total” za pomocą nowej nazwy klasy, a wszystkie kolejne wyrażenia są poprzedzone nowymi słowami kluczowymi po podsumowaniu za pomocą operatora kropki. Teraz dodamy wartości do każdego wiersza i zobaczymy wyniki. Początkowo łączna ocena dla każdego ucznia wynosi 0.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
W takim przypadku instrukcja wyzwalacza zostanie uruchomiona automatycznie po wstawieniu danych do tabeli ucznia. Suma punktów każdego ucznia zostanie obliczona przez wyzwalacz. Teraz zobaczmy, czy wyzwalacz jest wywoływany przy użyciu instrukcji SELECT.
SELECT * FROM table_name;
A oto ostateczny wynik.
mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)
W powyższym wyniku widać, że wszystkie oceny z przedmiotów są automatycznie dodawane dla każdego ucznia. Możemy więc stwierdzić, że wyzwalacz został pomyślnie wywołany.
Dodatkowe operacje wyzwalania
Za pomocą wyzwalaczy możemy wykonać wiele operacji. Niektóre mogą być proste, a inne nieco skomplikowane, ale po przejrzeniu zapytań łatwo je zrozumieć. Korzystając z instrukcji Transact-SQL, możesz włączać, wyłączać lub usuwać wyzwalacze za pomocą następujących poleceń.
Zapytanie, aby sprawdzić, czy istnieje określony wyzwalacz, czy nie
To polecenie sprawdza określony wyzwalacz w całej bazie danych.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
Zapytanie do wyświetlenia wyzwalaczy
Wszystkie wyzwalacze, które są dostępne w aktywnej bazie danych, zostaną pokazane w poniższej instrukcji.
SHOW TRIGGERS;
Zapytanie o wyłączenie wyzwalacza
Poniższe polecenie dezaktywuje wyzwalacz w działającej bazie danych.
DISABLE TRIGGER trigger_name ON DATABASE;
Możesz również określić określoną nazwę tabeli, aby wyłączyć wyzwalacz.
DISABLE TRIGGER trigger_name ON table_name;
Zapytanie o włączenie wyzwalacza
Poniższa komenda najpierw dezaktywuje określony wyzwalacz, który został zdefiniowany w określonej tabeli w aktywnej bazie danych, zanim zostanie ponownie włączony.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
Wyzwalacz musi być wyłączony, zanim spróbujesz go włączyć,
Zapytanie o włączenie lub wyłączenie wszystkich wyzwalaczy w tabeli
Korzystając z powyższej instrukcji SQL, możemy dezaktywować lub aktywować wszystkie wyzwalacze tabeli naraz, zastępując „ALL” w miejscu określonej nazwy wyzwalacza.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Zapytanie o usunięcie lub usunięcie wyzwalacza
Wyzwalacz można wyeliminować, usuwając go lub całą tabelę. Każdy powiązany wyzwalacz jest również usuwany po usunięciu tabeli.
DROP TRIGGER [trigger_name];
Za każdym razem, gdy wyzwalacz zostanie usunięty, powiązane dane są usuwane z tabeli danych sys.objects.
Zalety wyzwalaczy
- Tworzenie wyzwalaczy jest proste, a sam wyzwalacz może wywoływać przechowywane funkcje i metody.
- Użytkownicy mogą zaimplementować prostą inspekcję za pomocą wyzwalaczy.
- Niestety, nie można tworzyć ograniczeń między jednostkami w systemach baz danych za pomocą SQL Server, chociaż można emulować działanie ograniczeń za pomocą wyzwalaczy.
- Ograniczenia integralności można zaimplementować w różnych bazach danych za pomocą wyzwalaczy.
- Gdy wymagana jest weryfikacja grupy, a nie weryfikacja wiersz po wierszu nowo wprowadzonych lub zmienionych danych, pomocne mogą być wyzwalacze.
Wady wyzwalaczy
Wyzwalacze SQL mogą nie być najlepszym wyborem w niektórych sytuacjach ze względu na ich ograniczenia.
- Wyzwalacze muszą być dokładnie udokumentowane.
- Ze względu na jednoczesne wykonywanie bazy danych, które mogą być niedostępne dla składników aplikacji, wyzwalacze mogą być trudne do debugowania.
- Instrukcje DML stają się bardziej złożone, gdy używane są wyzwalacze.
- Nawet drobny problem z wyzwalaczem może prowadzić do błędów logicznych w oświadczeniu.
Wniosek
Wyzwalacze są bardzo pomocnymi składnikami Transact-SQL i SQL i można ich używać również w Oracle. Użycie wyzwalaczy ma kluczowe znaczenie podczas wywoływania metod przechowywanych. Te wyzwalacze SQL pozwalają nam analizować osie czasu aktywności i określać, jak na nie reagować, jeśli to konieczne. Możemy również sprawdzić konkretną tabelę, która jest podłączona do wyzwalacza, aby uzyskać dane.
Rekurencję można włączyć za pomocą wyzwalaczy. Za każdym razem, gdy wyzwalacz w tabeli wykonuje polecenie w tabeli nadrzędnej, uruchamiana jest druga iteracja wyzwalacza i jest to znane jako wyzwalacz rekurencyjny. Pomaga to podczas próby rozwiązania korelacji tożsamości.
Ponadto wyzwalacze regulują wzorzec aktualizacji, który baza danych może zaakceptować. Bardzo korzystne jest zachowanie ograniczeń integralności danych w systemie bazy danych, jeśli klucze ograniczeń SQL nie istnieją, głównie klucz podstawowy i klucz obcy.
Mam nadzieję, że ten artykuł okazał się pomocny w poznawaniu wyzwalaczy SQL.
Jeśli chcesz dogłębnie poznać bazy danych, oto kilka doskonałych zasobów do nauki SQL i NoSQL.