Взаємодія тригерів з функціями PL/pgSQL: OLD, NEW, TG_OP

Тригери в PostgreSQL дозволяють не просто запускати функції у відповідь на якісь дії, вони ще й передають у ці функції круті змінні. Саме завдяки цим змінним можна дізнатися, що було з даними в таблиці до операції, що стало після неї, і яка взагалі операція відбулася.

• OLD — містить старі дані рядка таблиці до виконання операції. Використовується в тригерах для UPDATE і DELETE, бо для INSERT нічого "старого" просто не існує.
• NEW — містить нові дані рядка таблиці після виконання операції. Використовується в тригерах для INSERT і UPDATE.
• TG_OP — містить текстову інфу про поточну операцію: INSERT, UPDATE або DELETE.

Всі ці змінні автоматично доступні всередині функції, яка прив’язана до тригера.

Теорія без практики — як SQL без індексів: повільно і сумно. Тому давай розберемо на практиці.

Використання OLD для доступу до старих даних

Уяви, що у нас є таблиця students. І хтось там виправляє вік студента (раптом в голові закралася помилка, що студенту ще 20, а не всі 25).

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

Щоб відслідкувати, які зміни відбулися, створимо таблицю логів:

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
);

Йдемо далі: створюємо функцію, яка буде записувати зміни. Ось тут і стане в нагоді OLD:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Логуємо зміни віку
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

А тепер робимо тригер:

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- Виконуємо тільки якщо вік змінився
EXECUTE FUNCTION log_student_changes();

Давай додамо студента, а потім змінимо дані:

INSERT INTO students (name, age) VALUES ('Аліса', 20);

UPDATE students
SET age = 25
WHERE name = 'Аліса';

-- Перевіряємо лог змін:
SELECT * FROM student_changes;

Ти побачиш, що в таблицю логів записалася зміна: вік з 20 на 25. Магія? Ні, OLD.

Використання NEW для нових даних

Тепер уяви, що ми хочемо при додаванні нового студента автоматично записувати його ID і ім’я в таблицю логів (так, це паранойя, але іноді корисна):

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- Логуємо дані нового студента
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- Старого значення нема, бо це INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Знову ж, додаємо нового студента і дивимось:

INSERT INTO students (name, age) VALUES ('Боб', 22);

-- Перевіряємо лог:
SELECT * FROM student_changes;

Ти побачиш, що в логах з’явився новий студент. Оце вже турбота про дані на новому рівні!

Використання TG_OP для визначення типу операції

А що, якщо ми хочемо мати універсальний тригер для логування, який зможе обробляти і INSERT, і UPDATE, і навіть DELETE? Тут на допомогу приходить змінна TG_OP.

Створимо універсальну функцію:

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; -- Для AFTER тригера на DELETE повертаємо NULL
END;
$$ LANGUAGE plpgsql;

Створюємо тригер, який буде спрацьовувати на всі три операції:

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

Додаємо, змінюємо і видаляємо студента:

INSERT INTO students (name, age) VALUES ('Чарлі', 30);
UPDATE students SET age = 31 WHERE name = 'Чарлі';
DELETE FROM students WHERE name = 'Чарлі';

-- Перевіряємо лог:
SELECT * FROM student_changes;

Ти зможеш побачити лог усіх операцій — один тригер, щоб керувати ними всіма!

Типові помилки при використанні OLD, NEW, TG_OP

Працюючи з тригерами, можна нарватися на кілька поширених граблів:

"Чому OLD не працює при вставці?" Це поведінка за замовчуванням: для INSERT немає старих даних. Використовуй NEW.

"Що робити, якщо NEW не працює при видаленні?" Знову ж, це очікувана поведінка: для DELETE немає нових даних. Використовуй OLD.

Логіка тригера викликає нескінченну рекурсію. Перевіряй, щоб тригер випадково не викликав сам себе. Для цього можна прописувати чіткі умови в блоці WHEN або перевіряти TG_OP.

1

Опитування

Вступ до тригерів, рівень 57, лекція 4

Недоступний

Вступ до тригерів

Вступ до тригерів

Відкрити