Tworzenie prostego triggera do aktualizacji danych: AFTER INSERT

Wyobraź sobie, że mamy tabelę, w której trzymamy dane o studentach. W tej tabeli chcemy automatycznie aktualizować pole last_modified (data ostatniej zmiany rekordu) za każdym razem, gdy dodajemy nowego studenta. To pole jest ważne do śledzenia zmian i zarządzania danymi.

Scenariusz działania wygląda tak:

1. Kiedy do tabeli dodawany jest nowy rekord, pole last_modified automatycznie dostaje aktualną datę i godzinę.
2. Użyjemy triggera AFTER INSERT, który odpali się po udanym dodaniu danych.

Tworzenie funkcji dla triggera

Najpierw musimy stworzyć funkcję w języku PL/pgSQL. Ta funkcja będzie aktualizować pole last_modified w naszej tabeli. Funkcja to obowiązkowy element do działania triggera, bo sam trigger tylko wskazuje, co trzeba zrobić, a całą logikę wykonuje funkcja.

No to zaczynamy od stworzenia tabeli:

-- Tworzymy tabelę students
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    last_modified TIMESTAMP
);

Teraz stworzymy funkcję do aktualizacji pola last_modified:

-- Funkcja do aktualizacji last_modified
CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Ustawiamy aktualny czas w polu last_modified
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Sprawdźmy, co ta funkcja robi:

• CREATE OR REPLACE FUNCTION update_last_modified() — tworzymy funkcję o nazwie update_last_modified. Jeśli taka funkcja już istnieje, zostanie zastąpiona.
• RETURNS TRIGGER — wskazujemy, że funkcja jest do użycia z triggerem.
• NEW.last_modified := NOW(); — aktualizujemy pole last_modified za pomocą funkcji NOW(), która zwraca aktualną datę i godzinę.
• RETURN NEW; — zwracamy zaktualizowany rekord. To obowiązkowy krok dla triggera AFTER.

Tworzenie triggera

Po stworzeniu funkcji możemy zrobić sam trigger, przypinając go do tabeli students. Tak to wygląda:

-- Tworzymy trigger po wstawieniu rekordu
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Co tu się dzieje:

• CREATE TRIGGER set_last_modified — tworzymy trigger o nazwie set_last_modified.
• AFTER INSERT — trigger odpali się po udanym dodaniu wiersza do tabeli.
• ON students — trigger jest przypięty do tabeli students.
• FOR EACH ROW — trigger wykona się dla każdego nowego wiersza dodawanego do tabeli.
• EXECUTE FUNCTION update_last_modified(); — wywołanie funkcji, którą stworzyliśmy wcześniej.

Uwaga: nazwy triggera (set_last_modified) i funkcji (update_last_modified) możesz wybrać dowolnie, ale warto trzymać się standardów nazewnictwa, żeby kod był czytelny.

Testowanie triggera

Sprawdźmy, jak działa nasz trigger. Najpierw dodajmy kilka rekordów do tabeli students:

-- Wstawiamy dane do tabeli
INSERT INTO students (name, age) VALUES ('Ivan Ivanov', 20);
INSERT INTO students (name, age) VALUES ('Anna Petrowa', 22);

Teraz zobaczmy, co mamy w tabeli:

-- Przeglądamy dane w tabeli
SELECT * FROM students;

Oczekiwany wynik może wyglądać mniej więcej tak:

Zwróć uwagę, że pole last_modified automatycznie dostało aktualną datę i godzinę dla każdego rekordu.

Błędy, które mogą się pojawić

1. Błąd: "relation does not exist" przy tworzeniu triggera. Ten błąd pojawia się, jeśli tabela students nie została stworzona. Upewnij się, że stworzyłeś tabelę przed triggerem.
2. Błąd dostępu. Jeśli użytkownik bazy nie ma uprawnień do tworzenia funkcji lub triggerów, trigger nie zostanie utworzony. Sprawdź uprawnienia użytkownika.
3. Brak wywołania funkcji w triggerze. Jeśli zapomnisz dodać EXECUTE FUNCTION update_last_modified(), trigger nie wykona wymaganych akcji.

Ulepszenie triggera: dodanie warunków

W realnych zadaniach czasem warto ograniczyć działanie triggera do określonych warunków. Na przykład, jeśli pole age jest mniejsze niż 18, nie chcemy aktualizować last_modified. Można to zrobić przez warunek WHEN:

-- Tworzymy trigger z warunkiem
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
WHEN (NEW.age >= 18)
EXECUTE FUNCTION update_last_modified();

Teraz pole last_modified będzie aktualizowane tylko dla studentów, których wiek >= 18.

Praktyczne zastosowanie

Triggery takie jak ten są często używane w prawdziwych projektach. Oto kilka przykładów:

• Automatyczna aktualizacja czasu ostatniej zmiany rekordu (tak jak zrobiliśmy).
• Śledzenie zmian w bazie i zapisywanie tych zmian do tabeli logów.
• Zachowanie integralności danych, np. sprawdzanie powiązanych tabel przed wykonaniem operacji.
• Audyt danych dla zgodności z przepisami lub normami firmowymi.

Te umiejętności są szczególnie przydatne, jeśli pracujesz z systemami, gdzie ważna jest dokładność i niezawodność danych, np. w bankowości, systemach zarządzania zapasami czy CRM.

2

Zadanie

SQL SELF, poziom 57, lekcja 3

Niedostępne

Tworzenie funkcji i triggera do automatycznego uzupełniania czasu modyfikacji

Tworzenie funkcji i triggera do automatycznego uzupełniania czasu modyfikacji

Otwórz