Automatyzacja zadań za pomocą triggerów

Wyobraź sobie sytuację, w której jesteś adminem dużej bazy danych. Nagle ktoś usuwa ważne info z tabeli i wszyscy krzyczą: "Kto to zrobił?!". Żeby uniknąć takich akcji, bazy danych pozwalają zapisywać zmiany i śledzić, co się działo z danymi. Wszystko to ogarniamy przez logowanie i audyt.

• Logowanie zmian pozwala zachować historię tego, co się wydarzyło: który rekord się zmienił, jak i kiedy.
• Audyt danych służy do głębszej kontroli, w tym zapisu nie tylko zmian, ale też info o użytkowniku, który je wywołał.

Teraz, kiedy już wiesz "po co", nauczmy się robić to "jak".

Tworzenie tabeli na logi

Zanim zaczniemy ustawiać triggery, potrzebujemy tabeli do przechowywania logów zmian. Oto przykład:

-- Tworzymy tabelę do logowania zmian
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- Unikalny identyfikator rekordu
    table_name TEXT NOT NULL,        -- Nazwa tabeli, gdzie zaszła zmiana
    operation TEXT NOT NULL,         -- Typ operacji: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- Czas zmiany
    old_data JSONB,                  -- Dane przed zmianą (dla UPDATE/DELETE)
    new_data JSONB                   -- Dane po zmianie (dla INSERT/UPDATE)
);

Co tu się dzieje?

1. log_id — unikalny identyfikator dla każdego wpisu w logu.
2. table_name — zapisujemy, która tabela została zmieniona.
3. operation — typ operacji: INSERT, UPDATE albo DELETE.
4. change_time — zapisuje dokładny czas zmiany.
5. old_data i new_data — dane przed i po zmianie w formacie JSON.

Logowanie zmian za pomocą triggera

Skoro mamy już tabelę na logi, zróbmy trigger dla jednej z tabel, np. students. Będzie on zapisywał wszystkie zmiany: dodanie nowych studentów, ich update albo usunięcie. Oto co robimy:

1. Piszemy funkcję w PL/pgSQL, która będzie wrzucać wpisy do tabeli logów.
2. Tworzymy trigger na tabeli students.

Funkcja dostaje info o operacji (INSERT, UPDATE, DELETE), a także dane, które się zmieniły (OLD i NEW).

-- Funkcja do zapisywania zmian w tabeli logów
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Logowanie operacji INSERT
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- Logowanie operacji DELETE
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- Logowanie operacji UPDATE
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- Zwracamy NULL, bo to AFTER-trigger
END;
$$ LANGUAGE plpgsql;

Tu:

• TG_OP — specjalna zmienna, która trzyma aktualną operację: INSERT, UPDATE, DELETE.
• row_to_json(OLD) i row_to_json(NEW) — zamieniają dane rekordu na JSON, żeby łatwo je przechowywać.
• RETURN NULL — ponieważ to AFTER-trigger, nie musi zwracać zmienionych danych.

Teraz podpinamy naszą funkcję do tabeli students.

-- Tworzymy trigger do logowania zmian
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

Co tu się dzieje?

• AFTER INSERT OR UPDATE OR DELETE — trigger odpala się po wykonaniu tych operacji na tabeli students.
• FOR EACH ROW — trigger działa dla każdego zmienionego rekordu.
• EXECUTE FUNCTION log_student_changes() — wywołuje naszą funkcję do logowania.

Testujemy trigger

Czas sprawdzić, jak działa nasz trigger.

1. Wstawianie nowego rekordu

INSERT INTO students (name, age, grade)
VALUES ('Otto Lin', 20, 'A');

Zobaczmy, co się zapisało w tabeli logów:

SELECT * FROM change_logs;

Przykład wyniku:

2. Aktualizacja rekordu

UPDATE students
SET grade = 'B'
WHERE name = 'Otto Lin';

Znowu sprawdzamy tabelę logów:

SELECT * FROM change_logs ORDER BY change_time DESC;

Wynik:

3. Usuwanie rekordu

DELETE FROM students
WHERE name = 'Otto Lin';

I znowu, sprawdzamy log:

Przykłady z życia

1. Logowanie operacji na krytycznych tabelach: np. tabela z kontami bankowymi wymaga zapisu wszystkich zmian, żeby zapobiec fraudom.
2. Audyt systemu: możesz trzymać wpisy dla zgodności z przepisami albo analizy aktywności użytkowników.
3. Odtwarzanie danych: jeśli ktoś przypadkiem usunie dane, możesz je odzyskać z tabeli logów.

Specyfika i pułapki

Przy wdrażaniu logowania przez triggery trzeba pamiętać o wydajności. Jeśli trigger odpala się bardzo często, może to zwiększyć obciążenie bazy. Dlatego:

• Używaj logowania tylko na naprawdę ważnych tabelach.
• Jeśli logów robi się za dużo, wymyśl strategię archiwizacji.

Z triggerami jest jak z dobrze nastrojoną struną gitary: wymagają precyzyjnego ustawienia, ale dają świetne brzmienie, automatyzując nudne zadania i dając kontrolę nad danymi.