Триггеры, хранимые процедуры, представления. Часть 1

Финальный бэкап базы - Cinema_final.

 

Для тех слушателей, кто имеет старую версию SQL Server и не может развернуть Cinema_final, предлагается следующая последовательность действий:

1. Сделать бекап своей базы данных Cinema

2. Удалить свою базу данных Cinema

3. Открыть приложенный скрипт - cinema.sql

4. В самом начале скрипта (строки 5 и 7) есть путь до папки SQL Server, который на вашем компьютере может немного отличаться (см. приложенный скрин). Замените путь на ваш правильный (скорее всего, надо будет только цифры 15 в названии папки исправить).

5. Исполните скрипт.

6. Обновите список баз данных, найдите Cinema и назначьте владельца в свойствах базы

 

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

Введение в задачу

Мы продолжаем работу в рамках курса «Введение в моделирование баз данных». Ранее мы смоделировали базу данных кинотеатра, насытили её информацией и привели схему к третьей нормальной форме. Сегодня наша задача — используя заполненную базу, научиться писать триггеры.

В процессе мы будем активно пользоваться языком SQL (Structured Query Language) — основным языком взаимодействия с базой данных. Он позволяет извлекать, изменять, добавлять и удалять информацию. Синтаксис и функционал SQL очень обширны, и в рамках одного курса охватить все тонкости невозможно. Более глубокое знакомство с ним ждет вас в следующих модулях программы.

Анализ структуры данных

Наша база данных содержит несколько связанных таблиц. Ключевым элементом является ассоциативная сущность, которая связывает фильм, формат (например, 2D) и зал. Это позволило нам привести схему к третьей нормальной форме. Далее, в рамках расписания мы сопоставляем этой тройке конкретное время начала сеанса.

Из этой связки мы можем получить и цену билета. Цена определяется сложной логикой, которая зависит от:
• Типа дня (будний или выходной).
• Времени начала сеанса (в нашей модели поле time указывает, с какого момента начинает действовать цена).
• Формата и категории места.

Например, цена билета стандартного класса на 2D-фильм, начинающийся после 9:00 утра в будний день в определенный период, может составлять 100 рублей.

Проблема: бизнес-правила, нереализуемые стандартными средствами

Теперь перейдем к таблице заказов (Order). Обратите внимание на потенциальную логическую ошибку: дата заказа (OrderDate) может оказаться позже, чем дата и время начала сеанса (StartDate). Например, билет на вчерашний сеанс куплен сегодня.

Это противоречит реальной логике бизнес-процесса. Подобное ограничение невозможно реализовать только с помощью связей между таблицами (типа «один-ко-многим») или типов данных. Нам нужен инструмент для программной проверки таких условий непосредственно на уровне базы данных. Именно для этого и существуют триггеры.

Триггеры: определение и виды

Триггер — это частный случай хранимых процедур (программного кода, выполняющегося на стороне сервера базы данных). Главная особенность триггера в том, что он вызывается автоматически при совершении определенного действия над таблицей.

Действие, на которое реагирует триггер, задаете вы. Например, он может срабатывать на операцию вставки новой строки (INSERT). При этом существует несколько ключевых моментов срабатывания, которые определяют его поведение:
• AFTER INSERT (после вставки): Триггер запускается после того, как новая строка была физически добавлена в таблицу. Именно на этом этапе удобно выполнять проверки. Если данные некорректны, операцию можно откатить.
• INSTEAD OF INSERT (вместо вставки): Триггер запускается вместо фактической вставки. Строка остается в оперативной памяти, проходит проверку и только в случае успеха добавляется в таблицу.

Выбор между AFTER и INSTEAD OF зависит от конкретной задачи и предметной области. Универсального правила здесь нет.

Практика: создание триггера для проверки даты заказа

1. Постановка задачи и определение логики
Мы создадим триггер для таблицы Order, который будет следить за тем, чтобы дата заказа не была значительно позже времени начала сеанса. Введем допуск в 10 минут (пока идет реклама), в течение которых продажа билетов еще разрешена.

2. Создание объекта в среде разработки
В среде управления базой данных (например, SQL Server Management Studio) нужно развернуть список объектов нужной таблицы, найти папку Triggers и создать новый объект.

3. Анализ шаблона кода
Среда сгенерирует шаблон. Важные моменты:
• Комментарии: Однострочные начинаются с --. Многострочные ограничиваются /* и */.
• Опции SET: В начале идут настройки сервера (например, обработка кавычек), которые можно оставить без изменений.
• Основной блок начинается с команды CREATE TRIGGER.

4. Написание кода триггера
Заменим шаблонную часть на наш код:

sql
-- ... (начальные опции SET оставлены без изменений) ...

CREATE TRIGGER [dbo].[TriggerDateCheck] -- Имя триггера
ON [dbo].[Order] -- Таблица, на которой создается триггер
AFTER INSERT -- Срабатывание после вставки
AS
BEGIN
-- Объявление переменных для хранения дат
DECLARE @StartDate DATETIME;
DECLARE @OrderDate DATETIME;

-- Получение значений из только что вставленной строки
-- inserted — это виртуальная таблица, содержащая новые данные
SELECT @StartDate = StartDate,
@OrderDate = OrderDate
FROM inserted;

-- Проверка условия: если время заказа позже времени начала сеанса (с учетом допуска)
-- DATEADD(MINUTE, -10, @StartDate) вычитает 10 минут из времени начала
IF (@OrderDate > DATEADD(MINUTE, -10, @StartDate))
BEGIN
-- Откат операции вставки
ROLLBACK TRANSACTION;
-- Вывод сообщения об ошибке
PRINT 'Дата заказа не может быть позже времени начала сеанса (с учетом допуска в 10 минут).';
END;
END;

Ключевые моменты в коде:
• Переменные объявляются с помощью DECLARE, их имена начинаются с символа @.
• Таблица inserted: Специальная виртуальная таблица, доступная в триггерах INSERT и UPDATE. Она содержит ровно ту строку, которая вставляется. С помощью конструкции SELECT ... FROM inserted мы извлекаем нужные значения в переменные.
• Условный оператор IF: Здесь мы реализуем саму проверку бизнес-правила. Мы сравниваем дату заказа со временем начала сеанса, уменьшенным на 10 минут (функция DATEADD).
• ROLLBACK TRANSACTION: Если условие истинно (ошибка), эта команда отменяет все изменения в рамках текущей операции, возвращая таблицу в исходное состояние.
• PRINT: Выводит пользовательское сообщение.

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

sql
INSERT INTO [Order] (ID, StartDate, HallID, FilmID, FormatID, OrderDate, PersonID)
VALUES (3, '2017-01-01 13:00:00', 2, 1, 1, '2017-01-02 10:00:00', 1);

Здесь OrderDate (2017-01-02) явно позже StartDate (2017-01-01). При попытке выполнения этого запроса триггер сработает:
• Вставит строку (так как он AFTER INSERT).
• Запустит проверку.
• Обнаружит нарушение условия.
• Выполнит ROLLBACK, удалив строку.
• Выведет в сообщениях наш текст.

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

Краткие итоги

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

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

Практический выбор между режимами AFTER и INSTEAD OF сводится к дилемме: проверить данные постфактум, выполнив откат при ошибке, или перехватить управление до физической записи. Каждый подход имеет свою сферу применения, и решение здесь диктуется исключительно контекстом задачи, а не догматическим предпочтением. Вне зависимости от выбранной модели, разработчик оперирует стандартным инструментарием SQL: объявляет переменные для захвата значений из виртуальной таблицы inserted, конструирует предикат проверки в операторе IF и определяет реакцию на нарушение через ROLLBACK и информирование. Таким образом, логика приложения не просто документируется, а принудительно исполняется на уровне хранилища, что кардинально повышает надежность системы и защищает ее от некорректных данных.