Interakcja triggerów z funkcjami PL/pgSQL: OLD, NEW, TG_OP

Triggery w PostgreSQL pozwalają nie tylko odpalać funkcje w odpowiedzi na jakieś akcje, ale też przekazują do tych funkcji fajne zmienne. Dzięki nim możesz sprawdzić, co było w danych w tabeli przed operacją, co się zmieniło po niej i jaka w ogóle operacja zaszła.

• OLD — zawiera stare dane wiersza tabeli przed wykonaniem operacji. Używane w triggerach dla UPDATE i DELETE, bo przy INSERT nic "starego" po prostu nie istnieje.
• NEW — zawiera nowe dane wiersza tabeli po wykonaniu operacji. Używane w triggerach dla INSERT i UPDATE.
• TG_OP — zawiera tekstową informację o aktualnej operacji: INSERT, UPDATE albo DELETE.

Wszystkie te zmienne są automatycznie dostępne wewnątrz funkcji powiązanej z triggerem.

Teoria bez praktyki to jak SQL bez indeksów: wolno i smutno. Więc ogarniemy to na praktycznych przykładach.

Użycie OLD do dostępu do starych danych

Wyobraź sobie, że mamy tabelę students. I ktoś tam poprawia wiek studenta (może się pomylił i myśli, że student ma jeszcze 20 lat, a nie już 25).

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

Żeby śledzić, jakie zmiany zaszły, stworzymy tabelę logów:

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Teraz idziemy dalej: tworzymy funkcję, która będzie zapisywać zmiany. I tu właśnie przyda się OLD:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Logujemy zmiany wieku
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

A teraz robimy trigger:

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- Wykonujemy tylko jeśli wiek się zmienił
EXECUTE FUNCTION log_student_changes();

Dodajmy studenta, a potem zmieńmy dane:

INSERT INTO students (name, age) VALUES ('Alisa', 20);

UPDATE students
SET age = 25
WHERE name = 'Alisa';

-- Sprawdzamy log zmian:
SELECT * FROM student_changes;

Zobaczysz, że do tabeli logów zapisała się zmiana: wiek z 20 na 25. Magia? Nie, OLD.

Użycie NEW dla nowych danych

Teraz wyobraź sobie, że chcemy przy dodaniu nowego studenta automatycznie zapisywać jego ID i imię do tabeli logów (tak, trochę paranoja, ale czasem się przydaje):

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- Logujemy dane nowego studenta
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- Nie ma starej wartości, bo to INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

Znowu, dodajemy nowego studenta i sprawdzamy:

INSERT INTO students (name, age) VALUES ('Bob', 22);

-- Sprawdzamy log:
SELECT * FROM student_changes;

Zobaczysz, że w logach pojawił się nowy student. To już dbanie o dane na wyższym poziomie!

Użycie TG_OP do określenia typu operacji

A co jeśli chcemy mieć uniwersalny trigger do logowania, który ogarnie i INSERT, i UPDATE, i nawet DELETE? Tu z pomocą przychodzi zmienna TG_OP.

Tworzymy uniwersalną funkcję:

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- Dla AFTER triggera na DELETE zwracamy NULL
END;
$$ LANGUAGE plpgsql;

Tworzymy trigger, który będzie działał na wszystkie trzy operacje:

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

Dodajemy, zmieniamy i usuwamy studenta:

INSERT INTO students (name, age) VALUES ('Czarlie', 30);
UPDATE students SET age = 31 WHERE name = 'Czarlie';
DELETE FROM students WHERE name = 'Czarlie';

-- Sprawdzamy log:
SELECT * FROM student_changes;

Będziesz mógł zobaczyć log wszystkich operacji — jeden trigger, żeby rządzić wszystkimi!

Typowe błędy przy użyciu OLD, NEW, TG_OP

Pracując z triggerami, łatwo wpaść na kilka popularnych problemów:

"Czemu OLD nie działa przy insercie?" To domyślne zachowanie: dla INSERT nie ma starych danych. Użyj NEW.

"Co zrobić, jeśli NEW nie działa przy usuwaniu?" Znowu, to oczekiwane: dla DELETE nie ma nowych danych. Użyj OLD.

Logika triggera powoduje nieskończoną rekurencję. Sprawdzaj, żeby trigger przypadkiem nie wywoływał sam siebie. Możesz ustawić konkretne warunki w bloku WHEN albo sprawdzać TG_OP.

1

Ankieta/quiz

Wprowadzenie do triggerów, poziom 57, lekcja 4

Niedostępny

Wprowadzenie do triggerów

Wprowadzenie do triggerów

Otwórz