SQLite триггеры и представления: CREATE TRIGGER и CREATE VIEW | Урок 10

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

Предыдущий урок: Транзакции в SQLite: обеспечение целостности данных (Урок 9)

Следующий урок: SQLite в реальных проектах: Python, мобильная и веб-разработка

Что такое триггеры в SQLite?

Определение триггеров

Триггеры в SQLite — это специальные объекты базы данных, которые автоматически выполняют определенные действия в ответ на события, такие как вставка, обновление или удаление строк в таблице. Триггеры позволяют автоматизировать обработку данных и поддержку целостности базы данных.

Основные сценарии использования триггеров

Триггеры могут быть полезны в различных сценариях, включая:

• Автоматическое обновление полей на основе значений других полей.
• Логирование изменений данных.
• Обеспечение целостности данных путем проверки условий перед выполнением операций.

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

Синтаксис создания триггеров

Создание триггера в SQLite начинается с использования команды CREATE TRIGGER. Вот общий синтаксис:

CREATE TRIGGER trigger_name
AFTER INSERT | AFTER UPDATE | AFTER DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- SQL-запросы, которые будут выполнены
END;

Примеры создания простых триггеров

Пример 1: Автоматическое обновление поля

Рассмотрим пример, где мы автоматически обновляем поле last_updated в таблице employees при каждом обновлении записи:

CREATE TRIGGER update_last_updated
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    UPDATE employees SET last_updated = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

Пример 2: Логирование изменений

Создадим триггер для логирования изменений в таблице employees в таблицу employees_log:

CREATE TRIGGER log_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_log (employee_id, old_salary, new_salary, change_date)
    VALUES (OLD.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

Объяснение примеров

Триггер update_last_updated: Этот триггер срабатывает после обновления записи в таблице employees и обновляет поле last_updated текущей меткой времени.

Триггер log_employee_changes: Этот триггер записывает изменения зарплаты сотрудника в таблицу employees_log, сохраняя старое и новое значение, а также дату изменения.

Практические примеры использования триггеров

Автоматическое обновление полей

Допустим, у нас есть таблица orders, и мы хотим автоматически обновлять поле order_status на «Completed» после того, как все товары в заказе будут доставлены:

CREATE TRIGGER update_order_status
AFTER UPDATE ON order_items
FOR EACH ROW
WHEN NEW.status = 'Delivered'
BEGIN
    UPDATE orders
    SET order_status = 'Completed'
    WHERE id = NEW.order_id AND NOT EXISTS (
        SELECT 1 FROM order_items WHERE order_id = NEW.order_id AND status != 'Delivered'
    );
END;

Логирование изменений в таблице

Создадим триггер для логирования удалений из таблицы products в таблицу products_log:

CREATE TRIGGER log_product_deletions
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO products_log (product_id, product_name, deletion_date)
    VALUES (OLD.id, OLD.name, CURRENT_TIMESTAMP);
END;

Обеспечение целостности данных

Допустим, у нас есть таблица accounts, и мы хотим предотвратить удаление записей, если баланс аккаунта положительный:

CREATE TRIGGER prevent_positive_balance_deletion
BEFORE DELETE ON accounts
FOR EACH ROW
WHEN OLD.balance > 0
BEGIN
    SELECT RAISE(ABORT, 'Cannot delete account with positive balance');
END;

Что такое представления (VIEW) в SQLite?

Определение представлений

Представления (VIEW) в SQLite — это виртуальные таблицы, созданные на основе результата SQL-запроса. Представления позволяют упростить сложные запросы и улучшить читаемость кода.

Преимущества использования представлений

• Упрощают сложные запросы, скрывая детали реализации.
• Обеспечивают безопасность данных, ограничивая доступ к определенным столбцам или строкам.
• Повышают повторное использование кода, позволяя использовать одно и то же представление в различных запросах.

Создание представлений в SQLite

Синтаксис создания представлений

Создание представления в SQLite выполняется с помощью команды CREATE VIEW. Вот общий синтаксис:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Примеры создания представлений

Пример 1: Простое представление

Создадим представление для отображения активных пользователей:

CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'Active';

Пример 2: Сложное представление

Создадим представление для отображения заказов с информацией о клиентах:

CREATE VIEW customer_orders AS
SELECT orders.id AS order_id, orders.date AS order_date, customers.name AS customer_name, customers.email AS customer_email
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Объяснение примеров

Представление active_users: Это представление отображает только активных пользователей, скрывая неактивных.

Представление customer_orders: Это представление объединяет данные из таблиц orders и customers, упрощая доступ к информации о заказах и клиентах.

Практические примеры использования представлений

Упрощение сложных запросов

Представление можно использовать для упрощения сложных запросов. Например, представление для отображения продаж по месяцам:

CREATE VIEW monthly_sales AS
SELECT strftime('%Y-%m', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY strftime('%Y-%m', sale_date);

Теперь мы можем легко получить данные о продажах за определенный месяц:

SELECT * FROM monthly_sales WHERE month = '2023-09';

Объединение данных из нескольких таблиц

Создадим представление для отображения информации о студентах и их оценках:

CREATE VIEW student_grades AS
SELECT students.id AS student_id, students.name AS student_name, courses.name AS course_name, grades.grade
FROM students
JOIN grades ON students.id = grades.student_id
JOIN courses ON grades.course_id = courses.id;

Теперь мы можем легко получить информацию о всех оценках конкретного студента:

SELECT * FROM student_grades WHERE student_name = 'Ivan Ivanov';

Повышение безопасности данных

Представления могут использоваться для ограничения доступа к определенным столбцам. Например, представление для отображения сотрудников без их зарплат:

CREATE VIEW public_employees AS
SELECT id, name, department
FROM employees;

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

Заключение

Краткое подведение итогов урока

В этом уроке мы рассмотрели триггеры и представления в SQLite. Мы узнали, как создавать и использовать триггеры для автоматизации различных процессов и обеспечения целостности данных. Также мы изучили, как создавать представления для упрощения сложных запросов и повышения безопасности данных.

Рекомендации по дальнейшему изучению

Для дальнейшего углубления в тему триггеров и представлений рекомендуется изучить официальную документацию SQLite и экспериментировать с созданием собственных триггеров и представлений в различных сценариях.

Практические задания

1. Создайте триггер, который будет автоматически обновлять поле last_login в таблице users при каждом входе пользователя в систему.
2. Создайте представление, которое отображает информацию о заказах и связанных с ними клиентах, включая общую сумму заказа.
3. Создайте триггер, который будет предотвращать удаление записей из таблицы products, если количество на складе больше нуля.

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