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

Введение

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

Создание таблиц: employees и employee_audit

На этом шаге вы создадите две таблицы: employees для хранения данных о сотрудниках и employee_audit для регистрации обновлений зарплаты.

Сначала откройте оболочку SQLite в вашем терминале.

sqlite3 /home/labex/project/test.db

Эта команда открывает оболочку SQLite и подключается к файлу базы данных с именем test.db. Если файл не существует, SQLite создаст его.

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

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

Эта SQL-команда создает таблицу с именем employees с тремя столбцами:

• id: Целое число, которое служит первичным ключом для каждого сотрудника.
• name: Текстовое поле для хранения имени сотрудника.
• salary: Вещественное число для хранения зарплаты сотрудника.

Далее создайте таблицу employee_audit:

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Эта таблица будет хранить журнал изменений зарплаты. Столбцы:

• id: Уникальный идентификатор для каждой записи аудита, автоматически увеличивающийся (AUTOINCREMENT).
• employee_id: ID сотрудника, чья зарплата была изменена.
• old_salary: Зарплата до обновления.
• new_salary: Зарплата после обновления.
• updated_at: Временная метка (timestamp), указывающая, когда произошло обновление.

Вставка образцов данных в employees

На этом шаге вы вставите несколько образцов данных в таблицу employees. Эти данные будут использоваться для тестирования триггера, который вы создадите на следующем шаге.

Выполните следующие SQL-команды, чтобы вставить три записи о сотрудниках:

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

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

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

SELECT * FROM employees;

Ожидаемый результат:

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

Этот вывод показывает id, name и salary для каждой записи.

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

Теперь вы создадите триггер, который автоматически регистрирует обновления зарплаты в таблице employee_audit.

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

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

Эта команда создает триггер с именем salary_update_trigger. Этот триггер будет автоматически выполняться после любого обновления столбца salary в таблице employees.

• AFTER UPDATE OF salary ON employees: Указывает, что триггер будет активирован после обновления столбца salary в таблице employees.
• BEGIN ... END: Заключает SQL-инструкции, которые будут выполнены при активации триггера.
• INSERT INTO employee_audit ...: Вставляет новую запись в таблицу employee_audit с ID сотрудника, старой зарплатой и новой зарплатой.
  • OLD.id: Ссылка на id строки до обновления.
  • OLD.salary: Ссылка на salary до обновления.
  • NEW.salary: Ссылка на salary после обновления.

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

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

Давайте обновим зарплату Боба:

UPDATE employees SET salary = 65000.00 WHERE id = 2;

Эта команда обновляет столбец salary для сотрудника с id 2 (Боб) до 65000.00.

Теперь запросите таблицу employee_audit, чтобы увидеть, сработал ли триггер:

SELECT * FROM employee_audit;

Ожидаемый результат:

1|2|60000.0|65000.0|...

Вы должны увидеть строку в таблице employee_audit с ID сотрудника Боба, старой зарплатой и новой зарплатой. Столбец updated_at покажет временную метку (timestamp) обновления. Точная временная метка будет варьироваться в зависимости от того, когда вы выполнили обновление.

Удаление триггера

На этом шаге вы узнаете, как удалить триггер из вашей базы данных SQLite.

Чтобы удалить salary_update_trigger, выполните следующую команду:

DROP TRIGGER salary_update_trigger;

Эта команда удаляет salary_update_trigger из базы данных.

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

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

Этот запрос должен вернуть пустой результирующий набор (empty result set), указывающий на то, что триггер больше не существует.

Теперь давайте попробуем обновить зарплату Алисы, чтобы подтвердить, что триггер действительно удален:

UPDATE employees SET salary = 55000.00 WHERE id = 1;

Затем проверьте таблицу employee_audit:

SELECT * FROM employee_audit;

Вы должны увидеть, что в таблицу employee_audit не было добавлено ни одной новой записи, что подтверждает, что триггер был успешно удален.

Наконец, выйдите из оболочки SQLite (SQLite shell):

.exit

Резюме

В этой лабораторной работе вы узнали, как создавать триггеры SQLite, которые автоматически выполняют SQL-запросы в ответ на события обновления в определенной таблице. Вы создали таблицу employees и таблицу employee_audit. Затем вы создали триггер с именем salary_update_trigger, который регистрирует обновления зарплаты в таблице employee_audit. Наконец, вы узнали, как удалить триггер.