Procedury składowane w PostgreSQL/Wyzwalacze

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.

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

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

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ń

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

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

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

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();