Triggery na poziomie wierszy i tabeli: FOR EACH ROW vs FOR EACH STATEMENT

Jeśli kiedykolwiek miałeś sytuację, gdy trzeba wykonać jakąś akcję dla każdego wiersza przy masowej aktualizacji danych albo tylko raz dla całej tabeli, to pewnie zastanawiałeś się: jak najlepiej to ogarnąć? PostgreSQL daje dwa warianty: triggery na poziomie wierszy i triggery na poziomie operacji. Zrozumienie, kiedy użyć którego podejścia, jest ważne dla dobrego zaprojektowania bazy, optymalizacji jej wydajności i unikania błędów. No to rozkminiamy!

Triggery, które działają na poziomie wierszy (FOR EACH ROW), odpalają się za każdym razem dla każdego wiersza, którego dotyczy operacja INSERT, UPDATE albo DELETE. Czyli jeśli SQL-owe zapytanie dotyka 100 wierszy, trigger poleci 100 razy.

Kiedy ich używać?

Triggery na poziomie wierszy są spoko, jeśli musisz ogarnąć każdy zmieniony wiersz osobno. Na przykład:

• Logowanie zmian dla każdego wiersza.
• Automatyczna aktualizacja powiązanych danych dla każdego wiersza.

Przykład: logowanie zmian dla każdego wiersza

Załóżmy, że mamy tabelę studentów:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Chcemy logować każdy wiersz, który jest aktualizowany w tej tabeli, do osobnej tabeli students_log:

CREATE TABLE students_log (
    log_id SERIAL PRIMARY KEY,
    student_id INT,
    old_name VARCHAR(100),
    new_name VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Funkcja do logowania zmian:

CREATE OR REPLACE FUNCTION log_student_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO students_log(student_id, old_name, new_name, changed_at)
    VALUES (OLD.id, OLD.name, NEW.name, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Tworzenie triggera FOR EACH ROW:

CREATE TRIGGER student_update_logger
AFTER UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_update();

Testowanie:

UPDATE students
SET name = 'Jan Kowalski'
WHERE id = 1;

Po wykonaniu tego zapytania, w tabeli students_log pojawi się wpis ze szczegółami zmian.

Triggery na poziomie operacji (FOR EACH STATEMENT)

Triggery na poziomie operacji (FOR EACH STATEMENT) odpalają się tylko raz dla całego SQL-owego zapytania, niezależnie od tego, ile wierszy zostało zmienionych. Jeśli zapytanie aktualizuje 100 wierszy, trigger poleci tylko raz.

Triggery na poziomie operacji są przydatne, jeśli chcesz:

• Zrobić coś tylko raz dla całej operacji.
• Pracować na zaggregowanych danych albo wykonać obliczenia dla całej tabeli.

Przykład: aktualizacja licznika zmian

Załóżmy, że mamy tabelę licznika zmian dla tabeli students:

CREATE TABLE students_changes_log (
    total_changes INT DEFAULT 0
);
INSERT INTO students_changes_log(total_changes) VALUES (0);

Chcemy zwiększać wartość tego licznika za każdym razem, gdy wykonuje się operacja UPDATE na tabeli students.

Funkcja do aktualizacji licznika:

CREATE OR REPLACE FUNCTION increment_changes_counter()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE students_changes_log
    SET total_changes = total_changes + 1;
    RETURN NULL; -- Trigger na poziomie operacji nie zwraca wierszy
END;
$$ LANGUAGE plpgsql;

Tworzenie triggera FOR EACH STATEMENT:

CREATE TRIGGER update_changes_counter
AFTER UPDATE ON students
FOR EACH STATEMENT
EXECUTE FUNCTION increment_changes_counter();

Testowanie:

UPDATE students
SET age = age + 1
WHERE age < 20;

Po wykonaniu zapytania trigger odpali się tylko raz, a licznik zmian zwiększy się o jeden.

Porównanie FOR EACH ROW i FOR EACH STATEMENT

Kiedy używać FOR EACH ROW i FOR EACH STATEMENT?

Używaj FOR EACH ROW, jeśli:

1. Chcesz, żeby trigger odpalał się dla każdego wiersza.
2. Logika wykonania musi być powiązana ze zmianami konkretnych wierszy.
3. Potrzebujesz dostępu do danych OLD i NEW dla każdego wiersza.

Przykład: Logowanie zmian w tabeli albo automatyczne tworzenie powiązanych rekordów.

Używaj FOR EACH STATEMENT, jeśli:

1. Chcesz wykonać akcję tylko raz dla całej operacji.
2. Logika triggera nie zależy od zmian konkretnych wierszy.
3. Wydajność jest krytyczna i nie chcesz masowo odpalać triggera.

Przykład: Aktualizacja liczników, obliczanie metadanych dla tabeli.

Błędy i ważne momenty

Użycie właściwego typu triggera może nie być oczywiste, więc zwróć uwagę na to:

1. Jednym z najczęstszych błędów jest próba użycia danych OLD i NEW w triggerze FOR EACH STATEMENT. To wywali błąd, bo te zmienne są dostępne tylko w triggerach na poziomie wierszy.
2. Triggery na poziomie wierszy (FOR EACH ROW) mogą mocno spowolnić operacje, jeśli zapytanie dotyka dużo wierszy. Zawsze miej na uwadze wydajność.
3. Uważaj na potencjalną rekurencję triggerów. Na przykład, jeśli trigger wywołuje zmianę danych w tej samej tabeli, może to prowadzić do nieskończonej pętli.