Wyzwalacz bazy danych

Wyzwalacz bazy danych to kod proceduralny , który jest automatycznie wykonywany w odpowiedzi na określone zdarzenia w określonej tabeli lub widoku w bazie danych . Wyzwalacz jest najczęściej używany do zachowania integralności informacji w bazie danych. Np. w przypadku dodania nowego rekordu (reprezentującego nowego pracownika) do tabeli pracowników, nowe rekordy powinny zostać utworzone również w tabelach podatków, urlopów i wynagrodzeń. Wyzwalacze mogą być również wykorzystywane do rejestrowania danych historycznych, na przykład do śledzenia poprzednich wynagrodzeń pracowników.

Wyzwalacze w DBMS

Poniżej znajduje się seria opisów wyzwalaczy obsługi niektórych popularnych systemów DBMS .

Wyrocznia

Oprócz wyzwalaczy, które uruchamiają (i wykonują kod PL/SQL ) podczas modyfikowania danych, Oracle 10g obsługuje wyzwalacze, które są uruchamiane, gdy obiekty na poziomie schematu (tj. tabele) są modyfikowane oraz gdy występują zdarzenia logowania lub wylogowania użytkownika.

Wyzwalacze na poziomie schematu

  • Po Stworzeniu
  • Przed Alterem
  • Po Alterze
  • Przed upadkiem
  • Po upuszczeniu
  • Przed wstawieniem

Cztery główne typy wyzwalaczy to:

  1. Wyzwalacz na poziomie wiersza: jest wykonywany przed lub po zmianie dowolnej wartości kolumny w wierszu
  2. Wyzwalacz na poziomie kolumny: jest wykonywany przed lub po zmianie określonej kolumny
  3. Dla każdego typu wiersza: Ten wyzwalacz jest wykonywany raz dla każdego wiersza zestawu wyników, na który ma wpływ wstawienie/aktualizacja/usunięcie
  4. Dla każdego typu instrukcji: ten wyzwalacz jest wykonywany tylko raz dla całego zestawu wyników, ale uruchamia się również za każdym razem, gdy wykonywana jest instrukcja.

Wyzwalacze na poziomie systemu

Od wersji Oracle 8i zdarzenia w bazie danych — logowanie, wylogowanie, uruchamianie — mogą uruchamiać wyzwalacze Oracle.

Serwer Microsoft SQL

Lista wszystkich dostępnych zdarzeń wyzwalania w programie Microsoft SQL Server dla wyzwalaczy DDL jest dostępna w witrynie Microsoft Docs .

Wykonywanie akcji warunkowych w wyzwalaczach (lub testowanie danych po modyfikacji) odbywa się poprzez dostęp do tymczasowych tabel Wstawione i Usunięte .

PostgreSQL

Wprowadzono obsługę wyzwalaczy w 1997 r. Następująca funkcjonalność w SQL:2003 nie była wcześniej zaimplementowana w PostgreSQL:

  • SQL pozwala uruchamiać wyzwalacze w przypadku aktualizacji określonych kolumn; Od wersji 9.0 PostgreSQL ta funkcja jest również zaimplementowana w PostgreSQL.
  • Standard pozwala na wykonanie wielu instrukcji SQL innych niż SELECT , INSERT , UPDATE , takich jak CREATE TABLE jako akcja wyzwalająca. Można to zrobić, tworząc procedurę składowaną lub funkcję wywołującą CREATE TABLE.

Streszczenie:

              
                
          UTWÓRZ  nazwę  WYZWALACZA  {  PRZED  |  PO  }  {  zdarzenie  [  LUB  ...  ]  }  NA  TABELI  [  DLA  [  KAŻDEGO  ]  {  WIERSZ  |  INSTRUKCJA  }  ]  WYKONANIE  PROCEDURY  nazwa funkcji  (  argumenty  )

ognisty Ptak

Firebird obsługuje wiele wyzwalaczy na poziomie wiersza, PRZED lub PO, INSERT, UPDATE, DELETE (lub dowolną ich kombinację) na tabelę, przy czym są one zawsze „oprócz” domyślnych zmian w tabeli, a kolejność wyzwalaczy względem każdego other można określić tam, gdzie w przeciwnym razie byłoby to niejednoznaczne (klauzula POSITION). Wyzwalacze mogą również istnieć w widokach, gdzie zawsze są wyzwalaczami „zamiast”, zastępując domyślną aktualizowalną logikę widoku. (Przed wersją 2.1 wyzwalacze w widokach uznanych za możliwe do aktualizacji byłyby uruchamiane oprócz domyślnej logiki).

Firebird nie zgłasza mutujących wyjątków tabel (jak Oracle), a wyzwalacze będą domyślnie zarówno zagnieżdżać, jak i rekursować zgodnie z wymaganiami (SQL Server domyślnie zezwala na zagnieżdżanie, ale nie na rekursję). ,) i udostępnij flagi AKTUALIZACJA, WSTAWIANIE i USUWANIE, aby wskazać bieżące użycie wyzwalacza.

              
   
   
                  
   

 
 {  UTWÓRZ  |  ODTWORZ  |  CREATE  OR  ALTER  }  TRIGGER  nazwa  DLA  {  nazwa  tabeli  |  nazwa  widoku  }  [  AKTYWNE  |  NIEAKTYWNE  ]  {  PRZED  |  PO  }  {  WSTAW  [  LUB  AKTUALIZUJ  ]  [  LUB  USUŃ  ]  |  AKTUALIZUJ  [  LUB  WSTAW  ]  [  LUB  USUŃ  ]  |  USUŃ  [  LUB  AKTUALIZUJ  ]  [  LUB  WSTAW  ]  }  [  POZYCJA  n  ]  AS  BEGIN  ....  END

Od wersji 2.1 Firebird dodatkowo obsługuje następujące wyzwalacze na poziomie bazy danych:

  • CONNECT (zgłoszone tutaj wyjątki uniemożliwiają nawiązanie połączenia)
  • ROZŁĄCZYĆ SIĘ
  • ROZPOCZĘCIE TRANSAKCJI
  • ZATWIERDZENIE TRANSAKCJI (podane tutaj wyjątki uniemożliwiają zatwierdzenie transakcji lub przygotowanie, jeśli zaangażowane jest zatwierdzenie dwufazowe)
  • WYCOFANIE TRANSAKCJI

Wyzwalacze na poziomie bazy danych mogą pomóc w egzekwowaniu ograniczeń dotyczących wielu tabel lub emulowaniu zmaterializowanych widoków . Jeśli w wyzwalaczu TRANSACTION COMMIT zostanie zgłoszony wyjątek, zmiany dokonane do tej pory przez wyzwalacz są wycofywane i aplikacja kliencka jest powiadamiana, ale transakcja pozostaje aktywna, tak jakby nigdy nie zażądano COMMIT; aplikacja kliencka może kontynuować wprowadzanie zmian i ponownie żądać polecenia COMMIT.

Składnia wyzwalaczy bazy danych:

        
    
            
   

 
 {  UTWÓRZ  |  ODTWORZ  |  CREATE  OR  ALTER  }  TRIGGER  name  [  ACTIVE  |  NIEAKTYWNE  ]  NA  {  POŁĄCZ  |  ODŁĄCZ  |  ROZPOCZĘCIE  TRANSAKCJI  |  ZATWIERDZENIE  TRANSAKCJI  |  WYCOFANIE  TRANSAKCJI  }  [  POZYCJA  n  ]  JAK  POCZĄTEK  .....  KONIEC

MySQL/MariaDB

Ograniczona obsługa wyzwalaczy w MySQL/MariaDB DBMS została dodana w wersji 5.0 MySQL, uruchomionej w 2005 roku.

Od wersji 8.0 pozwalają na wyzwalacze DDL (język definicji danych) i wyzwalacze DML (język manipulacji danymi). Pozwalają również na użycie dowolnego typu wyzwalacza DDL (PO lub PRZED) do definiowania wyzwalaczy. Są one tworzone przy użyciu klauzuli CREATE TRIGGER i usuwane przy użyciu klauzuli DROP TRIGGER . Instrukcja wywoływana po wystąpieniu zdarzenia jest zdefiniowana po klauzuli FOR EACH ROW , po której następuje słowo kluczowe ( SET lub BEGIN ), które wskazuje, czy następuje odpowiednio wyrażenie, czy instrukcja.

IBM DB2 LUW

IBM DB2 dla systemów rozproszonych znany jako DB2 for LUW (LUW oznacza Linux , Unix , Windows ) obsługuje trzy typy wyzwalaczy: Przed wyzwalaczem, Po wyzwalaczu i Zamiast wyzwalacza. Obsługiwane są zarówno wyzwalacze na poziomie instrukcji, jak i na poziomie wiersza. Jeśli w tabeli jest więcej wyzwalaczy dla tej samej operacji, kolejność wyzwalania jest określana na podstawie danych tworzenia wyzwalacza. Od wersji 9.7 IBM DB2 obsługuje transakcje autonomiczne.

Przed wyzwalaniem służy do sprawdzania danych i podejmowania decyzji, czy operacja powinna być dozwolona. Jeśli wyjątek zostanie zgłoszony przed wyzwalaczem, operacja zostanie przerwana i żadne dane nie zostaną zmienione. W DB2 przed wyzwalaczami są tylko do odczytu — nie można modyfikować danych w przed wyzwalaczami. Po zaprojektowaniu wyzwalaczy do przetwarzania końcowego po wykonaniu żądanej zmiany. Po wyzwalaczach można zapisywać dane w tabelach iw przeciwieństwie do niektórych [ które? ] innych baz danych, które możesz zapisać w dowolnej tabeli, w tym w tabeli, na której działa wyzwalacz. Zamiast wyzwalaczy służą do tworzenia widoków z możliwością zapisu.

Wyzwalacze są najczęściej programowane w języku SQL PL .

SQLite 

          
               
   
          

   
 UTWÓRZ  [  TEMP  |  TYMCZASOWY  ]  WYZWALACZ  [  JEŚLI  NIE  ISTNIEJE  ]  [  nazwa_bazy_danych  .]  nazwa_wyzwalacza  [  PRZED  |  PO  |  ZAMIAST  ]  {  USUŃ  |  _  WSTAW  |  UPDATE  [  OF  nazwa_kolumny  [,  nazwa_kolumny  ]...]  }  ON  {  nazwa_tabeli  |  view_name  }  [  DLA  KAŻDEGO  WIERSZA  ]  [  GDY  warunek  jest  obowiązkowy  ]  BEGIN  ...  END

SQLite obsługuje tylko wyzwalacze na poziomie wiersza, a nie wyzwalacze na poziomie instrukcji.

Widoki z możliwością aktualizacji, które nie są obsługiwane w oprogramowaniu SQLite, można emulować za pomocą wyzwalaczy INSTEAD OF.

Bazy danych XML

Przykładem implementacji wyzwalaczy w nierelacyjnej bazie danych może być Sedna , która zapewnia obsługę wyzwalaczy opartych na XQuery . Wyzwalacze w Sednie zostały zaprojektowane tak, aby były analogiczne do SQL: 2003 , ale natywnie bazują na językach zapytań i aktualizacji XML ( XPath , XQuery i język aktualizacji XML).

Wyzwalacz w Sednie jest ustawiany na dowolne węzły dokumentu XML przechowywanego w bazie danych. Kiedy te węzły są aktualizowane, wyzwalacz automatycznie wykonuje zapytania XQuery i aktualizuje określone w jego treści. Na przykład następujący wyzwalacz anuluje usunięcie węzła osoby, jeśli istnieją jakieś otwarte aukcje, do których odwołuje się ta osoba:

  
     
     
      
    
    
         
          
         
     UTWÓRZ  WYZWALANIE  "trigger3"  PRZED  USUNIĘCIEM  NA  doc(  "auction"  )/  site  //  person  DLA  KAŻDEGO  WĘZŁA  DO  {  if  (  istnieje  (  $  WHERE  //  open_auction  /  bidder  /  personref  /  @person  =  $  OLD  /  @id  ))  then  (  )  inny  $  STARY  ;  }

Wyzwalacze na poziomie wierszy i instrukcji

Aby zrozumieć, jak działa zachowanie wyzwalacza, musisz znać dwa główne typy wyzwalaczy; są to wyzwalacze na poziomie wierszy i instrukcji. Różnica między nimi polega na tym, ile razy kod w wyzwalaczu jest wykonywany iw jakim czasie.

Załóżmy, że masz wyzwalacz, który ma być wywoływany w AKTUALIZACJI do określonej tabeli. Wyzwalacze na poziomie wiersza byłyby wykonywane raz dla każdego wiersza, na który ma wpływ AKTUALIZACJA. Należy pamiętać, że jeśli polecenie UPDATE nie wpłynie na żadne wiersze, wyzwalacz nie wykona żadnego kodu w wyzwalaczu. Wyzwalacze na poziomie instrukcji będą wywoływane raz, niezależnie od tego, ile wierszy dotyczy AKTUALIZACJA. W tym miejscu należy zauważyć, że nawet jeśli polecenie UPDATE nie wpłynęło na żadne wiersze, kod w wyzwalaczu zostanie wykonany raz.

Za pomocą opcji PRZED i PO określ, kiedy wyzwalacz zostanie wywołany. Załóżmy, że masz wyzwalacz, który jest wywoływany na INSERT do określonej tabeli. Jeśli twój wyzwalacz używa opcji PRZED, kod w wyzwalaczu zostanie wykonany przed wystąpieniem INSERT w tabeli. Typowym zastosowaniem wyzwalacza PRZED jest weryfikacja wartości wejściowych INSERT lub odpowiednia modyfikacja wartości. Teraz powiedzmy, że mamy wyzwalacz, który zamiast tego używa AFTER. Kod w wyzwalaczu jest wykonywany po wystąpieniu INSERT w tabeli. Przykładowym zastosowaniem tego wyzwalacza jest tworzenie historii audytu tego, kto dokonał wpisów do bazy danych, śledzenie wprowadzonych zmian. Korzystając z tych opcji, należy pamiętać o kilku rzeczach. Opcja PRZED nie pozwala na modyfikację tabel, dlatego walidacja danych wejściowych jest praktycznym zastosowaniem. Korzystanie z wyzwalaczy AFTER umożliwia modyfikowanie tabel, na przykład wstawianie ich do tabeli historii audytu.

Podczas tworzenia wyzwalacza w celu określenia, czy jest to instrukcja, czy wiersz, po prostu dołącz klauzulę FOR EACH ROW dla poziomu wiersza lub pomiń klauzulę dla poziomu instrukcji. Zachowaj ostrożność podczas używania dodatkowych INSERT / UPDATE / DELETE w wyzwalaczu, ponieważ rekurencja wyzwalacza jest możliwa i powoduje niepożądane zachowanie. W poniższych przykładach każdy wyzwalacz modyfikuje inną tabelę, patrząc na to, co jest modyfikowane, można zobaczyć kilka typowych zastosowań, gdy używane są różne typy wyzwalaczy.

Poniżej znajduje się przykład składni Oracle wyzwalacza na poziomie wiersza, który jest wywoływany PO aktualizacji DLA KAŻDEGO WIERSZA, którego to dotyczy. Ten wyzwalacz jest wywoływany przy aktualizacji bazy danych książki telefonicznej. Po wywołaniu wyzwalacza dodaje on wpis do oddzielnej tabeli o nazwie phone_book_audit. Zwróć również uwagę na wyzwalacze, które mogą korzystać z obiektów schematu, takich jak sekwencje. W tym przykładzie audit_id_sequence.nexVal służy do generowania unikalnych kluczy podstawowych w tabeli phone_book_audit. 

    
        

     
          
    
         
 UTWÓRZ  LUB  ZAMIEŃ  WYZWALACZ  phone_book_audit  PO  UPDATE  ON  phone_book  DLA  KAŻDEGO  WIERSZA  ROZPOCZNIJ  WSTAWIANIE  DO  phone_book_audit  (  audit_id  ,  audit_change  ,  audit_l_name  ,  audit_f_name  ,  audit_old_phone_number  ,  audit_new_phone_number  ,  audit_date  )  WARTOŚCI  (  Audit_id_sequence  .  nextVal  ,  '  Update  '  ,  :  OLD  .nazwisko  ,  :  OLD  .imię  ,  :  STARY  numer_telefonu  ,:  NOWY  numer_telefonu  ,  SYSDATE  )  ;  _  _  KONIEC  ;

Teraz dzwonię na AKTUALIZACJĘ w tabeli phone_book dla osób o nazwisku „Jones”. 

          UPDATE  phone_book  SET  phone_number  =  '111-111-1111'  WHERE  last_name  =  'Jones'  ;
Audit_ID Audit_Change F_Nazwa L_Nazwa Nowy_numer_telefonu Stary_numer_telefonu Data_audytu
1 Aktualizacja Jordania Jonesa 111-111-1111 098-765-4321 02-MAJ-14
2 Aktualizacja Megan Jonesa 111-111-1111 111-222-3456 02-MAJ-14


Zauważ, że tabela phone_number_audit jest teraz wypełniona dwoma wpisami. Wynika to z faktu, że w bazie danych znajdują się dwa wpisy o nazwisku „Jones”. Ponieważ aktualizacja zmodyfikowała dwie oddzielne wartości wierszy, utworzony wyzwalacz został wywołany dwukrotnie; raz po każdej modyfikacji.

Po — wyzwalacz na poziomie instrukcji

Wyzwalacz instrukcji składni Oracle, który jest wywoływany po UPDATE w tabeli phone_book. Gdy wyzwalacz zostanie wywołany, wstawia się do tabeli phone_book_edit_history

    
     

     
        
    
      
 UTWÓRZ  LUB  ZAMIEŃ  WYZWALACZ  phone_book_history  PO  UPDATE  ON  phone_book  ROZPOCZNIJ  WSTAWIANIE  DO  phone_book_edit_history  (  identyfikator_historii_audytu  ,  nazwa_użytkownika  ,  modyfikacja  ,  data_edycji  )  WARTOŚCI  (  sekwencja_id_historii audytu  .  następna wartość  ,  USER  ,  'Update'  ,  SYSDATE  );  KONIEC  ;

Teraz wykonuje dokładnie tę samą aktualizację, co w powyższym przykładzie, jednak tym razem z wyzwalaczem na poziomie instrukcji.

          UPDATE  phone_book  SET  phone_number  =  '111-111-1111'  WHERE  last_name  =  'Jones'  ;
Audit_History_ID Nazwa użytkownika Modyfikacja Data edycji
1 HAUSCHBC Aktualizacja 02-MAJ-14

Wynik pokazuje, że wyzwalacz został wywołany tylko raz, mimo że aktualizacja zmieniła dwa wiersze.

Przed każdym — wyzwalacz na poziomie wiersza

Ten przykład ilustruje wyzwalacz PRZED KAŻDYM ROWEM, który modyfikuje INSERT przy użyciu warunku WHEN. Jeśli nazwisko jest dłuższe niż 10 liter, za pomocą funkcji SUBSTR zmieniamy wartość kolumny last_name na skrót.

    
        
     

      0
 UTWÓRZ  LUB  ZAMIEŃ  WYZWALACZ  phone_book_insert  PRZED  WSTAWIENIEM  NA  phone_book  DLA  KAŻDEGO  WIERSZA  GDY  (  DŁUGOŚĆ  (  nowe.nazwisko  )  >  10  )  POCZĄTEK  :  nowy  .  _  _  nazwisko_nazwisko  :  =  SUBSTR  (:  nowy  .  nazwisko_nazwisko  ,  ,  1  );  KONIEC  ;

Teraz wykonuję INSERT osoby o dużym nazwisku.

   
          WSTAW  DO  WARTOŚCI  książki_telefonicznej  (  6  ,  'VeryVeryLongLastName'  ,  'Erin'  ,  'Minneapolis'  ,  'MN'  ,  '989 University Drive'  ,  '123-222-4456'  ,  55408  ,  TO_DATE  (  '11/21/1991'  ,  ' MM/DD/RRRR'  ));
Identyfikator_osoby Nazwisko Imię Miasto Skrót_stanu Adres Numer telefonu Kod pocztowy Data urodzenia
6 V Erin Minneapolis MN 989 Napęd Uniwersytecki 123-222-4456 55408 21-listopad-91

Wyzwalacz działał zgodnie z powyższym wynikiem, modyfikując wartość INSERT przed jego wykonaniem.

Przed — wyzwalacz na poziomie instrukcji

Użycie wyzwalacza instrukcji BEFORE jest szczególnie przydatne podczas wymuszania ograniczeń bazy danych. Ten przykład pokazuje, jak wymusić ograniczenie na osobie o imieniu „SOMEUSER” w tabeli phone_book.

     
     

     
           
           
 UTWÓRZ  LUB  WYMIEŃ  TRIGGER  hauschbc  PRZED  WSTAWIENIEM  NA  NIEKTÓREGO UŻYTKOWNIKA  .  phone_book  BEGIN  RAISE_APPLICATION_ERROR  (  num  =>  -  20050  ,  msg  =>  'Tutaj trafia komunikat o błędzie.'  );  KONIEC  ;

Teraz, gdy „SOMEUSER” jest zalogowany po próbie WSTAWIENIA, ten komunikat o błędzie wyświetli:

Błąd SQL: ORA-20050: Tutaj pojawia się komunikat o błędzie.

Niestandardowe błędy, takie jak ten, mają ograniczenia co do tego, jak można zdefiniować zmienną num. Ze względu na liczne inne predefiniowane błędy, zmienna ta musi mieścić się w zakresie od -20000 do -20999.

Linki zewnętrzne

Pobrano z „ https://en.wikipedia.org/w/index.php?title=Database_trigger&oldid=1137404396 ”