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

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

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

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

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

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

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

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

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

 

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

Автоматизация расчёта дохода кассира с помощью триггеров SQL

1. Корректировка триггера проверки времени покупки

Ранее был создан триггер, запрещающий покупку билета после начала сеанса. Договорились, что покупка возможна в течение 10 минут после начала. Логика изменилась: если к времени сеанса прибавить 10 минут и это новое время всё ещё меньше времени заказа, операцию нужно блокировать. Для прибавления интервала используется функция DateAdd. Она позволяет добавить к дате заданное количество единиц (минут). Изменённый триггер успешно пропускает вставку заказа, сделанного через 5 минут после начала сеанса.

2. Постановка задачи расчёта дохода

Существует таблица Cashier (кассир) с полем Income (доход). При продаже билета — вставке строки в таблицу OrderDetail — необходимо автоматически прибавлять стоимость проданного билета к доходу кассира, оформившего заказ. Задача осложняется тем, что цена зависит от нескольких факторов: формата показа, категории места, даты заказа и времени начала сеанса.

3. Ручной анализ данных для понимания логики

Имеющиеся заказы (таблица Order): три записи. Все фильмы демонстрируются в формате 2D (ID формата 1).
Анализ таблицы Price показывает:
• для буднего дня, формата 1 и категории 1 цена составляет 100 рублей до 15:00 и 120 рублей после 15:00;
• для категории 2 (VIP-место) цена не была задана, поэтому добавили стоимость 200 рублей.

Ручной расчёт дохода:
• Первый заказ: два билета — бюджет (категория 1) и VIP (категория 2) в зале 2. Сеанс до 15:00. Сумма: 100 + 200 = 300 руб.
• Второй заказ: один бюджетный билет в зале 5, сеанс в 15:00. Цена — 120 руб. Сумма: 120 руб.
• Третий заказ: один бюджетный билет в зале 2, сеанс в 13:05. Цена — 100 руб. Сумма: 100 руб.
Общий доход кассира: 520 рублей.

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

4. Проектирование триггера и вспомогательных объектов

Триггер создаётся на таблицу OrderDetail, событие AFTER INSERT (после вставки). Для вычисления цены потребуется:
• Из вставленной строки получить ID заказа и ID места.
• По ID места из таблицы SeatPlace извлечь категорию места.
• По ID заказа из таблицы Order извлечь: время начала сеанса (StartDate), формат показа, дату заказа и ID кассира.
• На основе этих данных определить актуальную цену, учитывая временной интервал внутри суток.

Прямое определение цены сложное, так как для одного формата и категории в течение дня может быть несколько ценовых интервалов (например, 9:00 — 100 руб., 15:00 — 120 руб.). Чтобы упростить задачу, создаются представление и хранимая функция.

5. Создание представления PriceView

Представление (view) — виртуальная таблица, сохраняющая запрос. Создано PriceView на основе таблицы Price. В него включены поля: Time (время начала действия цены), ID_Format, ID_Category, Price_Start и Price_End (период актуальности), Price. Фактически это таблица цен без служебных полей. Обращаться к представлению можно как к обычной таблице, что упрощает повторяющиеся запросы.

6. Создание хранимой функции PriceTime

Функция возвращает скалярное значение — цену билета. Параметры: @OrderDate (дата заказа), @Category (категория места), @Format (формат), @Start (начало сеанса).
Логика функции:
• Из PriceView выбираются записи, где:
o @OrderDate BETWEEN Price_Start AND Price_End (актуальность цены);
o ID_Format = @Format;
o ID_Category = @Category.
• Добавляется условие по времени: время начала ценового интервала (Time) должно быть меньше или равно времени начала сеанса. Для выделения времени из полной даты используется CONVERT(TIME, @Start). Условие: Time <= CONVERT(TIME, @Start).
• Результаты сортируются по убыванию Time (ORDER BY Time DESC), и с помощью TOP 1 выбирается первая запись. Это последний временной интервал, начавшийся до или ровно в момент сеанса.
• Функция возвращает поле Price выбранной строки.

Пример: для сеанса в 17:00 при наличии записей с Time 9:00 и 15:00 функция вернёт цену для 15:00 (120 руб.).

7. Написание триггера обновления дохода

Триггер на OrderDetail выполняет следующие шаги:
• Объявляются переменные: @OrderID, @PlaceID, @Category, @Format, @StartDate, @OrderDate, @PersonID, @Price.
• Из виртуальной таблицы INSERTED (содержит вставленную строку) считываются ID заказа и ID места.
• Категория места извлекается запросом: SELECT @Category = ID_Category FROM SeatPlace WHERE ID = @PlaceID.
• Данные заказа извлекаются из таблицы Order: SELECT @StartDate = StartDate, @Format = ID_Format, @PersonID = ID_Person, @OrderDate = OrderDate FROM [Order] WHERE ID = @OrderID.
• Цена определяется вызовом функции: SET @Price = dbo.PriceTime(@OrderDate, @Category, @Format, @StartDate).
• Доход кассира обновляется: UPDATE Cashier SET Income = Income + @Price WHERE ID_Person = @PersonID.

Триггер успешно компилируется.

8. Проверка работоспособности

Для проверки создаётся новый сеанс в расписании на 17:00 (зал 2, формат 1). В таблицу Order вставляется заказ на этот сеанс с датой заказа 13:00. В OrderDetail добавляется билет на бюджетное место №10. Триггер срабатывает, доход кассира увеличивается с 520 до 640 рублей (актуальная цена после 15:00 — 120 руб.). Затем для демонстрации добавляется VIP-цена для времени 16:00 (150 руб.). При вставке билета на VIP-место №12 доход становится 790 рублей. Автоматизация работает корректно.

9. Важные замечания и ограничения примера

• Триггер обрабатывает вставку одной строки. В реальности данные часто поступают пакетно (bulk insert). Для обработки множества строк используется курсор — объект, позволяющий итеративно пройти по всем записям вставленного набора. В примере он не применялся.
• Отсутствует обработка ошибок: если какой-либо из SELECT не вернёт значение, возникнет исключение. Необходимы проверки и обработчики.
• Не реализована проверка типа дня (будний/выходной), так как для этого потребовался бы отдельный справочник-календарь.
• Логика функции PriceTime корректна, если ценовые интервалы не пересекаются и покрывают весь день без разрывов.
• Код не оптимизирован: реальные запросы могут быть написаны эффективнее.

10. Заключение по курсу

Пройденные этапы: инфологическое и даталогическое проектирование, создание структуры таблиц и связей, перенос на физический уровень СУБД. Затем насыщение данных и реализация бизнес-логики через триггеры и хранимые процедуры. Показано, как представления, функции и триггеры комбинируются для автоматизации. Следующий курс будет посвящён хранилищам данных, затем углублённое изучение SQL, а после — анализ данных с использованием Python и технологии больших данных.

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

Ручной пересчёт дохода при каждой продаже быстро становится неприемлемым, поэтому закономерно встаёт вопрос автоматизации. На первый план выходит способность базы данных не только хранить информацию, но и активно следить за соблюдением бизнес-правил. Последовательное усложнение задачи — от простого сравнения дат к многофакторному определению цены — демонстрирует, как отдельные объекты (представления, функции, триггеры) собираются в единый работающий механизм. Представление инкапсулирует повторяющийся запрос, снижая дублирование кода и вероятность ошибок. Скалярная функция изолирует сложную логику выбора цены, делая её переиспользуемой и тестируемой независимо. Триггер же связывает событие вставки данных с автоматическим обновлением зависимых показателей, гарантируя консистентность состояния системы независимо от внешнего приложения.

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

Вместе с тем осознанно оставленные за рамками аспекты — обработка пакетных вставок через курсоры, полноценная обработка исключений, учёт производственного календаря и оптимизация запросов — очерчивают границу между учебным прототипом и промышленной системой. Это не умаляет значимости изложенных принципов, а, наоборот, задаёт вектор дальнейшего профессионального роста. Освоив базовую связку «триггер – функция – представление», разработчик получает в руки универсальный шаблон для решения широкого класса задач автоматизации бизнес-логики. Закрепление понимания того, как спроектированная структура данных (нормализация, внешние ключи) напрямую облегчает написание кода автоматизации, формирует системное мышление, необходимое для перехода к более сложным архитектурам — от хранилищ данных до аналитических платформ.