Procedury składowane w PostgreSQL/Wyzwalacze

Z Wikibooks, biblioteki wolnych podręczników.

Wyzwalacze mogą być używane do wielu różnych celów, np.

  • bardziej zaawansowana kontrola danych przed wstawieniem/aktualizacją,
  • kontrola dostępu, np. wyłącznie możliwości kasowanie wybranych danych,
  • automatyczne logowanie akcji,
  • automatyczne tworzenie wpisów historycznych,
  • realizacja widoków zmaterializowanych.

Składnia tworzenia wyzwalacza:

CREATE TRIGGER nazwa_wyzwalacza { BEFORE | AFTER } { zdarzenie [ OR ... ] }
    ON tabela [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( warunek ) ]
    EXECUTE PROCEDURE PROCEDURE nazwa_procedury ( argumenty )

Zdarzenie to INSERT, UPDATE, DELETE, TRUNCATE.

Procedura może zostać wywołana na dwóch poziomach: albo dla każdego przetwarzanego wiersza (ROW) albo dla pojedynczej instrukcji SQL (STATEMENT). Wywołanie może nastąpić przed (BEFORE) lub po (AFTER) operacji.

Opcjonalny warunek WHEN, wprowadzony w wersji 9.0, pozwala odpalać procedurę warunkowo; można uzyskać w ten sposób wyzwalacze działające na poziomie kolumn.


Informacja
Pojedyncza procedura składowana może być używana w wielu wyzwalaczach.

W PostgreSQL wyzwalacze wywołują procedury składowane, które (1) nie mogą przyjmować argumentów i (2) zwracać psuedotyp TRIGGER. Argumenty dla procedury są określane podczas definiowania wyzwalacza i dostępne przez zmienne TG_NARGS i TG_ARGV. Procedura musi zwrócić albo rekord o tej samej strukturze co zmienna NEW (patrz niżej), albo NULL, albo zgłosić wyjątek. Procedur wyzwalaczy nie można wywoływać wprost.

Procedura ma dostęp do wielu danych dotyczących wyzwalacza i tabeli dla której została wywołana, więc można jedną funkcją obsłużyć np. logowanie zmian w wielu tabelach.

Predefiniowane zmienne[edytuj]

Procedura wyzwalacza ma w chwili wywołania dostęp do następujących zmiennych:

NEW
Zmienna typu RECORD zawiera wiersz, który ma zostać wstawiony (INSERT) lub zaktualizowany (UPDATE). Ma wartość NULL dla operacji DELETE/TRUNCATE i dla wszystkich operacji wykonywanych na poziomie instrukcji SQL.
OLD
Zmienna typu RECORD zawiera wiersz który ma zostać zastąpiony (UPDATE) lub skasowany (DELETE). Ma wartość NULL dla operacji INSERT/TRUNCATE i dla wszystkich operacji wykonywanych na poziomie instrukcji SQL.
TG_NAME
nazwa_wyzwalacza (napis)
TG_WHEN
W zależności od chwili odpalenia wyzwalacza, napis "BEFORE" - przed lub "AFTER" - po zdarzeniu
TG_LEVEL
W zależności od poziomu działania wyzwalacza, napis "ROW" - przetwarzanie wiersza lub "STATEMENT" - wykonywanie instrukcji
TG_OP
napis określający dla jakiej operacji został wywołany: 'INSERT', 'UPDATE', 'DELETE' lub 'TRUNCATE'.
TG_RELID
oid tabeli TG_TABLE_NAME
TG_TABLE_NAME
nazwa tabeli dla której wyzwalacz został wywołany
TG_TABLE_SCHEMA
nazwa schematu, w którym umieszczona jest tabela TG_TABLE_NAME
TG_NARGS
liczba argumentów, które zostały zdefiniowane dla wyzwalacza
TG_ARGV[]
lista argumentów w formie napisów, które zostały zdefiniowane dla wyzwalacza; sięgnięcie poza zakres tablicy nie powoduje błędu, jedynie zwrócenie NULL.

Kolejność wykonywania wyzwalaczy[edytuj]

PostgreSQL nie trzyma się tutaj standardu SQL, który przewiduje, że wyzwalacze są uruchamiane zgodnie z kolejnością tworzenia. W PostgreSQL decyduje porządek alfabetyczny nazw wyzwalaczy, co ułatwia kontrolę nad kolejnością.

Kolejność zdarzeń[edytuj]

Zdarzenia podczas wykonywania instrukcji:

  • BEFORE STATEMENT
    • BEFORE ROW - wiersz 1
    • BEFORE ROW - wiersz 2
    • ...
    • BEFORE ROW - wiersz n
    • AFTER ROW - wiersz 1
    • AFTER ROW - wiersz 2
    • ...
    • AFTER ROW - wiersz n
  • AFTER STATEMENT

Zmiana NEW[edytuj]

Procedura wyzwalacza wykonywana przed zmianą wiersza (BEFORE, ROW) dopuszczalna jest modyfikacja pól rekordu NEW. W ten sposób można np. automatycznie uzupełniać dane.

CREATE OR REPLACE FUNCTION autor_czas() RETURNS TRIGGER AS $$
    BEGIN
        NEW.uzytkownik := current_user;

        IF TG_OP = 'INSERT' THEN
            NEW.czas_utworzenia := now();
        ELSIF TG_OP = 'UPDATE' THEN
            NEW.czas_aktualizacji := now();
        ELSE
            -- na wypadek przypadkowego użycia procedury
            -- w nieprzewidziany sposób
            RAISE 'Nie wiadomo co zrobić podczas operacji %', TG_OP;
        END IF;

        -- ok, zwracamy nowy rekord
        RETURN NEW;
    END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER uzupelnienie_danych
BEFORE UPDATE OR INSERT
    ON dokumenty
   FOR EACH ROW
EXECUTE PROCEDURE autor_czas();

Zwracanie NULL[edytuj]

Zwrócenie NULL powoduje nie wykonanie danej operacji, bez zgłaszania błędu.

Np.

CREATE OR REPLACE FUNCTION czarna_lista() RETURNS TRIGGER AS $$
    BEGIN
        IF EXISTS(SELECT 1 FROM spamerzy WHERE id = NEW.uzytkownik_id) THEN
            -- rekord nie zostanie dodany ani zaktualizowany
            RETURN NULL;
        ELSE
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER bez_spamu
BEFORE UPDATE OR INSERT
    ON dokumenty
   FOR EACH ROW
EXECUTE PROCEDURE czarna_lista();

Zgłaszanie wyjątków[edytuj]

Zgłoszenie wyjątku w dowolnym momencie i na dowolnym poziomie skutkuje wycofaniem zmian spowodowanych przez instrukcję.

CREATE OR REPLACE FUNCTION zabezpieczenie() RETURNS TRIGGER AS $$
    BEGIN
        IF OLD.zewnetrzny_id <> NEW.zewnetrzny_id THEN
            RAISE 'Zmiana ID zewnętrznego rekordu spowoduje problemy!';
        END IF;
    END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trig1
 AFTER UPDATE
    ON dokumenty
   FOR EACH ROW
EXECUTE PROCEDURE zabezpieczenie();