Trigger do automatycznego aktualizowania pola last_modified przy zmianie rekordu

Wyobraź sobie, że robisz apkę do zarządzania studentami i kursami, i masz tabelę students. W tej tabeli jest pole last_modified, które powinno się automatycznie aktualizować za każdym razem, gdy zmieniasz dane w rekordzie (np. zmiana imienia studenta albo jego wieku).

Zamiast ręcznie dopisywać aktualizację last_modified w każdym zapytaniu SQL, zrobimy trigger, który ogarnie to za nas.

Struktura tabeli students

Na początek stwórzmy tabelę students, której będziemy używać w przykładzie. Ta tabela zawiera podstawowe info o studentach:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Unikalny identyfikator studenta
    name VARCHAR(100) NOT NULL,   -- Imię studenta
    age INT,                      -- Wiek studenta
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- Czas ostatniej zmiany
);

• Pole last_modified na starcie wypełnia się aktualnym czasem (NOW()) przy tworzeniu rekordu.
• To pole będzie się automatycznie aktualizować przy zmianie danych studenta.

Wrzucamy do tabeli trochę testowych danych:

INSERT INTO students (name, age)
VALUES 
    ('Otto Lin', 20),
    ('Maria Chi', 22),
    ('Aleks Song', 19);

Teraz dane w tabeli wyglądają tak:

Tworzenie funkcji do aktualizacji last_modified

Funkcja w PL/pgSQL będzie używana przez trigger do aktualizowania wartości pola last_modified. Odpali się automatycznie przed zmianą rekordu.

Tworzymy funkcję update_last_modified:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Aktualizujemy pole last_modified na aktualny czas
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW — to specjalna zmienna, która trzyma nowe dane rekordu (po zmianie).
• Ustawiamy NEW.last_modified na NOW() (aktualna data i godzina).
• Funkcja zwraca zaktualizowaną zmienną NEW, co jest potrzebne, żeby trigger działał poprawnie.

Tworzenie triggera

Teraz robimy trigger, który automatycznie odpali funkcję update_last_modified przy każdej aktualizacji rekordu w tabeli students.

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Co tu się dzieje:

• BEFORE UPDATE oznacza, że trigger odpala się przed wykonaniem operacji UPDATE.
• FOR EACH ROW znaczy, że trigger działa dla każdego zmienianego rekordu.
• EXECUTE FUNCTION update_last_modified() mówi, żeby wywołać funkcję update_last_modified.

Testowanie triggera

Sprawdźmy teraz, jak działa nasz trigger. Wybierzmy aktualne dane z tabeli students:

SELECT * FROM students;

Wynik:

Teraz zaktualizujemy wiek studenta z student_id = 1:

UPDATE students
SET age = 21
WHERE student_id = 1;

Znowu wybieramy dane z tabeli:

SELECT * FROM students;

Oczekiwany wynik:

Zwróć uwagę: pole last_modified dla rekordu z student_id = 1 zaktualizowało się na aktualny czas, a reszta rekordów została bez zmian.

Rozszerzenie logiki triggera

Załóżmy, że teraz chcemy, żeby pole last_modified aktualizowało się tylko jeśli zmienią się konkretne kolumny. Na przykład, jeśli zmienia się tylko imię studenta albo jego wiek, trigger ma się odpalić, a w innych przypadkach — nie.

Do tego można dodać warunek z operatorem WHEN w definicji triggera.

Tworzymy nowy trigger z warunkiem:

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

Tu:

• Warunek WHEN sprawdza, czy stare wartości (OLD) różnią się od nowych (NEW) dla kolumn name i age.
• Jeśli żadna z tych kolumn się nie zmieniła, trigger się nie odpala.

Spróbujmy znowu zaktualizować dane w tabeli i przetestować nową logikę.

Wskazówki dotyczące używania triggerów

1. Nie przesadzaj z triggerami. Są wygodne, ale mogą skomplikować logikę bazy i utrudnić debugowanie.
2. Zawsze dokumentuj, co robi trigger i w jakich przypadkach jest używany.
3. Używaj warunków WHEN, żeby zminimalizować przypadkowe odpalenia triggera.
4. Pamiętaj, że triggery mogą wpływać na wydajność bazy, szczególnie jeśli tabela ma dużo rekordów.

Typowe błędy przy pracy z triggerami

Zła zmiana danych. Na przykład, zapomniałeś ustawić wartość dla NEW i zwróciłeś oryginalne dane bez zmian.

Złe warunki. Na przykład, zapomniałeś dodać warunku WHEN i trigger odpala się nawet wtedy, gdy nie trzeba nic zmieniać.

Rekursja. Jeśli trigger wywołuje funkcję, która znowu wywołuje trigger, możesz przypadkiem zrobić nieskończoną pętlę. PostgreSQL ma ochronę przed rekursją, ale lepiej unikać takich sytuacji.

Ten przykład pokazuje, jak użycie triggerów może uprościć automatyczne aktualizowanie danych. W prawdziwych projektach taka technika jest często używana do logowania zmian, pilnowania spójności danych i automatyzacji nudnych rzeczy.