W kontekście baz danych SQL Server, wyzwalacze stanowią specyficzny rodzaj funkcji, które reagują na określone akcje wykonywane na bazie danych.
Ten wszechstronny przewodnik dostarczy Ci szczegółowej wiedzy o wyzwalaczach SQL, która może okazać się niezwykle cenna w Twojej karierze. Przejdźmy zatem do konkretów!
Czym są wyzwalacze SQL?
Określenie „wyzwalacz” odnosi się do automatycznej reakcji serwera poprzez wykonanie zapytania w momencie, gdy nastąpi modyfikacja zawartości bazy danych.
Wyzwalacz to zbiór zdefiniowanych zapytań SQL, przechowywanych w pamięci. Jest to szczególny typ funkcji, która jest natychmiast aktywowana w odpowiedzi na jakiekolwiek zdarzenie w obrębie bazy danych. Każdy wyzwalacz jest powiązany z konkretną tabelą.
Przykładowo, wyzwalacz może zostać uruchomiony, gdy do danej tabeli zostanie dodana nowa kolumna lub gdy zostaną zmodyfikowane konkretne rekordy.
Według Microsoft Developer Network, wyzwalacze są specjalnym rodzajem procedur składowanych. W definicji wyzwalacza określamy, kiedy ma on zostać aktywowany, a następnie definiujemy akcję, która ma być wykonana po jego uruchomieniu.
Składnia:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
Objaśnienie poszczególnych parametrów:
- CREATE TRIGGER trigger_name — służy do utworzenia nowego wyzwalacza lub zmiany nazwy już istniejącego.
- BEFORE/AFTER — ten parametr określa moment wykonania wyzwalacza: przed, czy po wystąpieniu określonego zdarzenia.
- INSERT/UPDATE/DELETE — definiuje akcję, która ma zostać wykonana na tabelach.
- ON tableName — tutaj podajemy nazwę tabeli, dla której konfigurujemy wyzwalacz.
- FOR EACH ROW — ta klauzula odnosi się do wyzwalacza wierszowego, co oznacza, że wyzwalacz będzie wykonywany po każdej zmianie w wierszu.
- trigger_body — określa akcję, która ma zostać podjęta po aktywacji wyzwalacza.
Wyzwalacze to składowane funkcje z unikatowymi identyfikatorami, pozwalające nam ponownie używać zapytań, które zostały już wykonane i bezpiecznie zachowane w pamięci. Zastanówmy się teraz, dlaczego SQL potrzebuje takich mechanizmów.
Wyzwalacze są najczęściej wykorzystywane do automatyzacji wykonywania kodu w odpowiedzi na określone zdarzenia. Innymi słowy, są idealnym rozwiązaniem, gdy chcemy, aby określony fragment kodu był regularnie uruchamiany w reakcji na dane zdarzenie.
Poniżej przedstawiono kilka zalet stosowania wyzwalaczy w operacjach baz danych SQL:
Integralność referencyjna stanowi kluczową cechę systemów relacyjnych baz danych. Oznacza to, że dane przechowywane w bazie muszą być zawsze aktualne i spójne w kontekście każdej transakcji.
Gdy dwie tabele znajdują się w odrębnych bazach danych, nie ma możliwości zapewnienia walidacji danych za pomocą standardowych mechanizmów. W takich przypadkach wyzwalacze stają się niezbędne.
Kombinacje argumentów wyzwalających
Dla każdej tabeli możemy zdefiniować sześć różnych rodzajów wyzwalaczy, wynikających z kombinacji argumentów, które odnoszą się do wyzwalaczy na poziomie wiersza w SQL.
PRZED WSTAWIANIEM: Te wyzwalacze wykonują działanie na wierszach jeszcze przed wykonaniem jakiejkolwiek operacji WSTAWIANIA do tabeli lub bazy danych.
PO WSTAWIANIU: Uruchamia działanie na wierszach bezpośrednio po każdej operacji INSERT w bazie danych.
PRZED AKTUALIZACJĄ: W przypadku tych wyzwalaczy funkcja w wierszach jest uruchamiana przed wykonaniem operacji UPDATE w bazie danych.
PO AKTUALIZACJI: Wykonuje akcję na wierszach bezpośrednio po wykonaniu operacji UPDATE w bazie danych.
PRZED USUNIĘCIEM: Wykonuje określoną operację na wierszach zanim nastąpi operacja DELETE w bazie danych.
PO USUNIĘCIU: Te wyzwalacze wykonują działanie na wierszach po zakończeniu transakcji DELETE.
Rodzaje wyzwalaczy SQL
Wyzwalacze SQL to składowane funkcje, które są uruchamiane automatycznie po wystąpieniu zdefiniowanych zdarzeń. Działa to na zasadzie harmonogramu sterowanego zdarzeniami. Istnieje wiele sytuacji, które mogą zapoczątkować wykonanie wyzwalacza.
Wyzwalacze DML – DML (Data Manipulation Language) to język manipulacji danymi. Wyzwalacze DML pozwalają na wykonywanie kodu w reakcji na modyfikacje danych. Ten rodzaj wyzwalacza jest aktywowany przy wykonywaniu poleceń DML, takich jak INSERT, UPDATE i DELETE. Są one również określane jako „wyzwalacze poziomu tabeli”.
Wyzwalacze DDL – DDL (Data Definition Language) to język definicji danych. Wyzwalacze DDL umożliwiają uruchamianie kodu w odpowiedzi na zmiany w schemacie bazy danych, takie jak dodawanie, usuwanie tabel, czy zdarzenia serwera (np. logowanie użytkownika). Są one znane jako „wyzwalacze poziomu bazy danych”.
Wyzwalacze te mogą być aktywowane przy wykonywaniu określonych instrukcji DDL, takich jak CREATE, ALTER lub DROP w aktywnej bazie danych. Mogą być też wykorzystywane do monitorowania i zarządzania wykonywanymi operacjami.
Wyzwalacze LOGON – uruchamiają się za każdym razem, gdy wystąpi zdarzenie LOGON (uruchomienie, logowanie, wylogowanie, zamknięcie). Wykonywane są one po procesie uwierzytelniania użytkownika, a nawet przed rozpoczęciem transakcji. Wyzwalacze LOGON nie zostaną aktywowane w przypadku nieudanej autoryzacji.
Można je wykorzystywać do rejestrowania historii logowania, ustanawiania ograniczeń dla danego logowania, a także do innych funkcji audytu i zarządzania tożsamością dla połączeń z serwerem.
Wyzwalacze CLR – CLR (Common Language Runtime) to środowisko uruchomieniowe języka wspólnego. Wyzwalacze CLR są unikatowym podzbiorem wyzwalaczy opartych na CLR w ramach technologii .NET. Te wyzwalacze są przydatne, gdy wymagane jest wykonanie skomplikowanych obliczeń lub gdy wyzwalacz musi odnosić się do podmiotów spoza SQL.
Wyzwalacze DML i DDL można tworzyć z wykorzystaniem kodu obsługującego wyzwalacze CLR w technologiach .NET, takich jak Visual Basic, C# i F#.
Przykładowy wyzwalacz serwera SQL
Zilustrujemy te koncepcje wyzwalaczy na przykładzie.
Na początek utwórzmy bazę danych przy użyciu instrukcji SQL:
CREATE DATABASE testdb; use testdb;
W tym przypadku nazwa bazy danych to „testdb”. Kolejnym krokiem jest utworzenie tabeli:
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
Stworzyliśmy tabelę do przechowywania danych uczniów. Poniżej znajduje się polecenie opisujące strukturę tabeli, gdzie „student” to jej nazwa.
DESC student;
Oto struktura utworzonej 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 konfiguracja wyzwalacza. Użyjemy argumentu BEFORE INSERT.
Nazwa utworzonego wyzwalacza to „marks”. Gdy tylko tabela zostanie zmodyfikowana o oceny uczniów, poniższy wyzwalacz automatycznie obliczy sumę ocen danego ucznia.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Ponieważ chcemy operować na nowych danych, a nie na starych, zdefiniowaliśmy „total” za pomocą nowego identyfikatora, a wszystkie kolejne wyrażenia poprzedzamy słowem kluczowym „new”. Teraz dodamy wartości do każdego wiersza i zobaczymy efekty. Na początku suma ocen 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 tym przypadku instrukcja wyzwalacza zostanie uruchomiona automatycznie po wstawieniu danych do tabeli „student”. Suma ocen każdego ucznia zostanie obliczona przez wyzwalacz. Sprawdźmy, czy wyzwalacz jest wywoływany przy użyciu instrukcji SELECT.
SELECT * FROM table_name;
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)
Jak widać, wszystkie oceny z przedmiotów są automatycznie sumowane dla każdego ucznia. Możemy więc stwierdzić, że wyzwalacz został poprawnie wywołany.
Dodatkowe operacje wyzwalania
Za pomocą wyzwalaczy można wykonywać różnorodne operacje. Niektóre z nich są proste, inne bardziej skomplikowane, ale po analizie zapytań stają się zrozumiałe. Korzystając z instrukcji Transact-SQL, można włączać, wyłączać lub usuwać wyzwalacze za pomocą następujących poleceń:
Zapytanie sprawdzające istnienie konkretnego wyzwalacza
To polecenie sprawdza, czy określony wyzwalacz istnieje w całej bazie danych.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
Zapytanie wyświetlające wyzwalacze
Poniższa instrukcja pokaże wszystkie wyzwalacze, które są dostępne w aktywnej bazie danych.
SHOW TRIGGERS;
Zapytanie o wyłączenie wyzwalacza
Poniższe polecenie wyłącza wyzwalacz w działającej bazie danych.
DISABLE TRIGGER trigger_name ON DATABASE;
Można również wskazać konkretną nazwę tabeli, aby wyłączyć wyzwalacz.
DISABLE TRIGGER trigger_name ON table_name;
Zapytanie o włączenie wyzwalacza
Poniższa komenda najpierw wyłącza określony wyzwalacz, zdefiniowany w określonej tabeli w aktywnej bazie danych, a następnie go ponownie włącza.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
Wyzwalacz musi być wyłączony, zanim zostanie ponownie włączony.
Zapytanie o włączenie lub wyłączenie wszystkich wyzwalaczy w tabeli
Korzystając z powyższej instrukcji SQL, możemy jednocześnie wyłączyć lub włączyć wszystkie wyzwalacze tabeli, wpisując „ALL” zamiast nazwy konkretnego wyzwalacza.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Zapytanie o usunięcie wyzwalacza
Wyzwalacz można usunąć osobno lub wraz z całą tabelą. Każdy powiązany wyzwalacz jest usuwany w momencie usunięcia tabeli.
DROP TRIGGER [trigger_name];
Za każdym razem, gdy wyzwalacz zostanie usunięty, związane z nim dane są usuwane z tabeli sys.objects.
Zalety wyzwalaczy
Wady wyzwalaczy
W niektórych sytuacjach, wyzwalacze SQL nie są optymalnym rozwiązaniem ze względu na pewne ograniczenia:
Podsumowanie
Wyzwalacze są bardzo przydatnymi elementami Transact-SQL i SQL, i mogą być stosowane także w Oracle. Użycie wyzwalaczy jest kluczowe podczas wywoływania przechowywanych metod. Te mechanizmy pozwalają na analizowanie osi czasu aktywności i reagowanie na nie w razie potrzeby. Dodatkowo, możemy sprawdzić konkretną tabelę podłączoną do wyzwalacza, aby uzyskać dane.
Wyzwalacze umożliwiają także włączanie rekurencji. Gdy wyzwalacz w tabeli wykonuje polecenie w tabeli nadrzędnej, uruchamiana jest kolejna iteracja wyzwalacza, co nazywamy wyzwalaczem rekurencyjnym. To rozwiązanie jest pomocne podczas rozwiązywania problemów z relacjami tożsamości.
Wyzwalacze regulują także schemat aktualizacji, który baza danych może zaakceptować. Utrzymanie integralności danych w systemie bazodanowym jest niezwykle korzystne, szczególnie gdy klucze ograniczeń SQL (głównie podstawowy i obcy) nie istnieją.
Mam nadzieję, że ten artykuł okazał się pomocny w zrozumieniu działania wyzwalaczy SQL.
Jeśli chcesz zgłębić wiedzę na temat baz danych, oto kilka wartościowych materiałów do nauki SQL i NoSQL.