Wyzwalacze

Zbigniew Jurkiewicz, Instytut Informatyki UW

Procedury składowane są to procedury lub funkcje, przechowywane na serwerze bazy danych i tam wykonywane. Część z nich jest dostarczona z implementacją, na przykład sin().

Możemy jednak tworzyć własne procedury, na przykład żeby obudować ciąg czynności wykonywanych wspólnie i nie musieć pisać ich za każdym razem. Ale podstawowe przeznaczenie to pisanie wyzwalaczy -- procedur odpalanych automatycznie, ilekroć w bazie danych zajdzie określone zdarzenie. Do ich pisania najczęściej używa się języka PL/SQL, dlatego nim włąśnie będziemy się zajmować.

Wprowadzenie

Zacznijmy jednak od zwykłych funkcji. Prostą funkcję można napisać w SQL. Przypominam składnię z notatek 

CREATE FUNCTION nazwa (parametr typ-parametru, ...)
RETURNS typ-wyniku AS $$
  treść-funkcji
$$ LANGUAGE nazwa-języka;

Ponieważ funkcje mogą być pisane w różnych językach, trzeba było jakoś oznaczyć początek i koniec kodu. Cudzysłowy są tu fatalnym rozwiązaniem, bo zwykle występują w treści. Wybrano $$, ale można te ograniczniki zastąpić innymi.

No to napiszmy funkcję liczącą, ile mamy zwierzaków cięższych niż podana waga. 

CREATE OR REPLACE FUNCTION ile_ciezszych (ponad integer)
RETURNS bigint AS $$
SELECT COUNT(*) FROM Zwierzaki WHERE waga > ponad;
$$ LANGUAGE 'sql';

Żeby sprawdzić naszą funkcję, musimy jej użyć w zapytaniu SQL 

bd=# select ile_ciezszych(10);
 ile_ciezszych 
---------------
            53
(1 wiersz)

Teraz coś naprawdę pożytecznego. Załóżmy, że tabela Zwierzaki ma klucz sztuczny id typu SERIAL (zmieniona definicja w pliku zoo-bis.sql. Napiszemy funkcję, która wstawi nowy wiersz do tabeli i zwróci automatycznie nadany identyfikator. 

CREATE OR REPLACE FUNCTION nowy_zwierzak (imie VARCHAR, gatunek VARCHAR,
                                          wiek INT, waga INT)
RETURNS integer AS $$
  INSERT INTO Zwierzaki(imie, gatunek, wiek, waga)
    VALUES($1, $2, $3, $4) RETURNING id;
$$ LANGUAGE 'sql';

Pora coś wstawić 

bd=# select nowy_zwierzak('Kicia','tygrys',2,100);
 nowy_zwierzak 
---------------
            55
(1 wiersz)

To często jedyny sposób, żeby dowiedzieć się o nadany identyfikator. Przy okazji poznaliśmy modyfikator RETURNING, który zwraca podane wartości (tak jak dla frazy SELECT), można w ten sposób otrzymać listę usuniętych wierszy: 

DELETE FROM Zwierzaki 
WHERE gatunek = 'krowa' RETURNING *;

To jeszcze funkcja w PG/SQL, po podaniu liczby wierszy utworzy wiersze o kolejnych wartościach 

CREATE OR REPLACE FUNCTION wstaw (ile integer)
RETURNS integer AS $$
DECLARE
  i NUMERIC(3) := 1;
BEGIN
  LOOP
    INSERT INTO T1 VALUES(i, i * i);
    i := i + 1;
    EXIT WHEN i > ile;
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE 'plpgsql';

Wywołujemy podobnie 

bd=# select wstaw(11);
 wstaw 
-------
    12
(1 wiersz)

Wyzwalacze

Wyzwalacze są specjalną konstrukcją PL/SQL, będącą rozszerzeniem funkcja. Podczas gdy zwykła funkcja musi być wywołana jawnie (z innej funkcji lub instrukcji SELECT), wyzwalacze wywoływane są automatycznie zawsze wtedy, gdy wystąpią związane z nimi zdarzenia. Zdarzeniem jest modyfikacja tabeli.

Wyzwalacze w PL/pgSQL definiuje się używając uprzednio zdefiniowanych funkcji bezargumentowych zwracajacych specjalny typ TRIGGER. Funkcja ta otrzymuje dane od wyzwalacza (za pośrednictwem struktury TriggerData, o której wolicie nie wiedzieć), a nie przez zwykłe parametry funkcyjne. Nie należy takich funkcji wywoływać bezpośrednio. Sa one wywoływane niejawnie przez stowarzyszony wyzwalacz, ilekroć wystąpi zdarzenie z nim związane.

Funkcja wyzwalacza musi zostać zdefiniowana przed utworzeniem wyzwalacza.

Zdarzeniem może być próba wykonania jednej z operacji SQL INSERT, DELETE lub UPDATE. Dla wyzwalaczy określa się też, czy ich funkcje mają być wołane przed czy po właściwej operacji (BEFORE lub AFTER).

Ponadto dla wyzwalacza określony jest jeden z dwóch poziomów: wiersza lub zdania. Wyzwalacz poziomu zdania jest odpalany tylko raz dla całego polecenia SQL, natomiast wyzwalacz poziomu wiersza jest odpalany niezależnie dla każdego modyfikowanego wiersza.

Składnia deklaracji wyzwalacza w PL/pgSQL jest następująca: 

CREATE TRIGGER nazwa
  {BEFORE | AFTER} {INSERT | DELETE | UPDATE} [OR ...] ON tabela
  [FOR EACH {ROW | STATEMENT}]
  EXECUTE PROCEDURE nazwa-procedury(argumenty);

Dodatkowe wymagania:

Usuwając wyzwalacz trzeba oprócz jego nazwy podać nazwę tabeli 

DROP TRIGGER nazwa ON tabela;

W treści funkcji związanej z wyzwalaczem są dostępne dwie zmienne rekordowe NEW i OLD, odnoszące się do nowej i starej zawartości wiersza. Procedura wyzwalacza dla wstawiania i modyfikacji powinna zwracać odpowiedni rekord, zwykle jest to po prostu NEW. Można też zwracać NULL, co w wyzwalaczach typu BEFORE oznacza, że wykonanie wyzwalacza nie powiodło się i operacja powinna zostać zignorowana.

Wyzwalacze tego samego typu uruchamia się w kolejności alfabetycznej według ich nazw. Jeśli w funkcji wyzwalacza używa się poleceń SQL, mogą one spowodować uruchomienie innych wyzwalaczy, nazywa się to kaskadowaniem wyzwalaczy. Nie istnieje żadne ograniczenie na liczbę poziomów wywołań kaskadowych, w szczególności możliwe są wywołania rekurencyjne. Programista jest odpowiedzialny za unikanie nieskończonej rekursji.

Pierwszy wyzwalacz

Pierwszy wyzwalacz napiszemy wspólnie. Będzie on tylko wypisywał ,,Tu jestem''. Po co pisać taki wyzwalacz? Otrzymamy poprawny składniowo schemat, który będziemy mogli modyfikować w kolejnych wyzwalaczach.

Wypisanie ,,Tu jestem'' pozwala stwierdzić, czy wyzwalacz w ogóle się odpalił.

Najpierw funkcja 

CREATE FUNCTION pierwszy () RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'tu jestem';
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

Dwa wyjaśnienia:

Pora na wyzwalacz 

CREATE TRIGGER pierwszywyzw
  BEFORE INSERT OR UPDATE ON Gatunki
  FOR EACH ROW
  EXECUTE PROCEDURE pierwszy();

Pamiętajmy, że wykonanie polecenia CREATE TRIGGER jedynie tworzy wyzwalacz nie wykonując go. Aby uruchomić wyzwalacz należy spowodować zajście odpowiedniego zdarzenia, w tym przypadku wstawienia lub modyfikacji wiersza dla tabeli Gatunki. 

bd=# select * from gatunki;
bd=# insert into Gatunki values ('krowa','nie','Europa','siano');
UWAGA:  tu jestem
INSERT 0 1

Zadania

  1. Bardzo często w danych wejściowych występują typowe błędy. Można je korygować wyzwalaczami BEFORE. Należy napisać wyzwalacz dbający, aby nazwa kontynentu rozpoczynała się dużą literą (jest w notatkach, ale spróbujcie sami). 
    bd=# insert into Gatunki values ('krowa','nie','eUrOpA','siano');
bd=# select * from gatunki;
...
krowa   nie   Europa   siano
...
  2. Mamy mały zwierzyniec i nie możemy mieć więcej niż 4 zwierzaków jednego gatunku. Napisz wyzwalacz sprzeciwiający się dodaniu piątego zwierzaka do gatunku.

    Najlepiej użyć RAISE EXCEPTION, powodującego oprócz wypisania komunikatu wycofanie transakcji. Lepiej to robić wyzwalaczem AFTER, błędna akcja zostanie porzucona. Wyzwalacze są wykonywane w jednej transakcji ze swoim zdarzeniem, czyli operacją modyfikacji.