Автоматизация триггеров PostgreSQL

Введение

В этой лабораторной работе вы узнаете, как автоматизировать задачи в PostgreSQL с помощью триггеров. Лабораторная работа посвящена созданию триггерной функции с использованием PL/pgSQL для автоматического протоколирования изменений заработной платы в таблице employees в отдельную таблицу salary_changes.

Вы будете руководствоваться процессом написания триггерной функции, привязкой ее к событию таблицы (в частности, обновлению таблицы employees), тестированием поведения триггера и, наконец, отключением и удалением триггера.

Создание таблиц и функции триггера

На этом шаге вы создадите таблицы employees и salary_changes, а затем определите функцию log_salary_change с использованием PL/pgSQL.

Сначала подключитесь к базе данных PostgreSQL от имени пользователя postgres:

sudo -u postgres psql

Теперь создайте таблицу employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

Эта команда создает таблицу с колонками id (первичный ключ), name и salary.

Далее создайте таблицу salary_changes для хранения истории изменений заработной платы:

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP
);

Теперь давайте напишем триггерную функцию с использованием PL/pgSQL. Эта функция будет выполняться перед операцией обновления (update operation) в таблице employees. Она будет записывать старые и новые значения заработной платы в таблицу salary_changes.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Этот код определяет функцию с именем log_salary_change. Она проверяет, изменилась ли заработная плата. Если да, то она вставляет новую строку в таблицу salary_changes с идентификатором сотрудника (employee ID), старой заработной платой, новой заработной платой и текущей временной меткой (timestamp).

Создание триггера

На этом шаге вы привяжете функцию log_salary_change к таблице employees, чтобы она автоматически выполнялась при обновлении заработной платы.

Создайте триггер с помощью следующей SQL-команды:

CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Эта команда создает триггер с именем salary_change_trigger. Она указывает, что триггер должен активироваться перед операцией UPDATE в таблице employees. Предложение FOR EACH ROW указывает, что триггерная функция должна выполняться для каждой обновляемой строки.

Тестирование триггера

На этом шаге вы протестируете поведение триггера. Вы вставите данные в таблицу employees, а затем обновите зарплату, чтобы увидеть, правильно ли триггер регистрирует изменения в таблице salary_changes.

Сначала вставьте некоторые начальные данные в таблицу employees:

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);

Теперь обновите зарплату Алисы:

UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';

Чтобы убедиться, что триггер сработал правильно, запросите таблицу salary_changes:

SELECT * FROM salary_changes;

Вы должны увидеть строку в таблице salary_changes с информацией об изменении зарплаты Алисы.

Далее, обновите зарплату Боба до того же значения, которое у него уже есть:

UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';

Поскольку зарплата фактически не меняется, триггер не должен вставлять новую строку в таблицу salary_changes. Давайте проверим это:

SELECT * FROM salary_changes;

Вы по-прежнему должны видеть только одну строку, связанную с изменением зарплаты Алисы.

Наконец, вставьте нового сотрудника:

INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

Вставка нового сотрудника не должна запускать триггер log_salary_change, поскольку он настроен на срабатывание только при событиях UPDATE. Давайте подтвердим это, снова проверив таблицу salary_changes:

SELECT * FROM salary_changes;

Вы по-прежнему должны видеть только одну строку, связанную с изменением зарплаты Алисы.

Отключение и удаление триггера

На этом шаге вы узнаете, как отключить и удалить триггер в PostgreSQL.

Сначала отключите salary_change_trigger:

ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;

Теперь снова обновите зарплату Алисы:

UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';

Проверьте таблицу salary_changes:

SELECT * FROM salary_changes;

Вы по-прежнему должны видеть только одну строку из предыдущего обновления. Это подтверждает, что триггер отключен.

Чтобы снова включить триггер, вы можете использовать следующую команду:

ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;

Наконец, чтобы окончательно удалить триггер, удалите его с помощью следующей команды:

DROP TRIGGER salary_change_trigger ON employees;

Резюме

В этой лабораторной работе вы узнали, как автоматизировать задачи в PostgreSQL с помощью триггеров. Вы создали функцию триггера, используя PL/pgSQL, для регистрации изменений зарплаты в таблице employees в таблице salary_changes. Вы написали функцию, привязали ее к событию таблицы, протестировали ее и отключили/удалили ее.