Вы легко справились с предыдущими заданиями? Ну что ж, тогда давайте я дам вам что-то посложнее. Давайте перейдем к закреплению ваших знаний по PL-SQL и написанию процедур и триггеров. Готовы?
Создание процедур
Ниже представлены процедуры, которые рекомендуется добавить в базу данных вашего маркетплейса. Каждая процедура сопровождается объяснением, какие задачи и бизнес-процессы она решает и почему ее стоит реализовать. Эти процедуры охватывают автоматизацию ключевых операций, повышение удобства пользователей, оптимизацию работы витрины, логистики, поддержки, маркетинга и аналитики.
1. Оформление заказа с автоматическим резервом товаров
Оформление заказа — ключевая операция для любого маркетплейса. Процедура должна не просто создавать запись заказа и его позиций, а также резервировать товар на складе, списывать количество, фиксировать статус, инициировать платеж и запускать последующие процессы (уведомления, логистику). Автоматизация этого сценария снижает риск ошибок при ручном вводе, помогает избежать over-selling и обеспечивает согласованность запасов в реальном времени.
2. Автоматизация пополнения запасов товара
Для предотвращения out-of-stock и потерь продаж важно своевременно пополнять склады, если уровень остатков ниже порога. Эта процедура проверяет остатки по всем товарам, сравнивает их с минимальным уровнем и автоматически формирует заявки на закупку или внутренний ресаплай. Автоматизация сокращает время реакции и снижает ручную нагрузку на операционный отдел.
3. Массовое обновление статусов заказов и уведомление клиентов
В каталоге часто требуется массово переводить заказы между статусами (например, “paid” → “shipped” или “shipped” → “completed”) в зависимости от этапа обработки. Процедура обновляет статусы всех подходящих заказов, пишет историю изменений (для аудита) и может инициировать отправку уведомлений клиентам. Это автоматизирует работу back-office и минимизирует ручные ошибки.
4. Массовое применение скидок/промокодов к товарам
Продвижение товаров или проведение акций часто требует массового применения промокодов и скидок ко всей категории, бренду или выборке товаров. Процедура автоматически проставляет скидки, следит за ограничениями (дата, лимиты), обновляет счетчики использований и предотвращает дубли.
5. Автоматический возврат средств пользователю
Оформление возвратов и возврат средств — критически важный процесс для клиентского доверия. Процедура должна проверять статус заказа, валидность возврата, инициировать возврат по платежу, обновлять статусы, логировать транзакции и уведомлять пользователя. Отработка всех условий в одной транзакции снижает риск ошибок и злоупотреблений.
6. Перерасчет средней оценки товара / продавца
При каждом новом отзыве, изменении или удалении, средний рейтинг товара или пользователя должен быть актуальным. Процедура оперативно пересчитывает и обновляет поле “avg_rating”/“review_count”, чтобы ускорить фронтовые запросы и обеспечить согласованность аналитики.
7. Автоматическое отключение (деактивация) товаров с нулевым остатком
Для корректной работы витрины маркетплейса и чтобы избежать негативного опыта пользователей, товары с нулевым остатком должны быть автоматически скрыты (деактивированы). Процедура регулярно проверяет склады и статус товаров, меняет статус на “inactive”, логирует изменение и помогает содержать каталог в актуальном состоянии без ручной проверки.
8. Автоматическое присвоение заказа курьеру и обновление статуса доставки
В логистике важно быстро и прозрачно назначать курьера на заказ, изменять статус доставки, фиксировать все действия для отслеживания. Процедура автоматизирует эти шаги и позволяет исключить ручную работу менеджера.
9. Массовая рассылка уведомлений пользователям (триггер акции, напоминания)
Уведомления о распродажах, возвратах, изменениях статусов или маркетинговых активностях должны рассылаться массово, с учётом условий (активные пользователи, не покупали долго, покинули корзину и т.д.). Процедура позволяет инициировать рассылку push/email уведомлений по заданному сегменту.
10. Архивация устаревших данных (например, завершённых заказов или неактивных товаров)
Для поддержания производительности базы данных и снижения объёма “горячих” таблиц устаревшие записи (старые заказы, архивные товары, устаревшие тикеты поддержки) должны периодически переноситься или помечаться как “архив”. Процедура облегчает операции по удалению или перемещению данных и минимизирует ручную работу администраторов.
Создание триггеров
1. Логирование изменения статуса заказа
Ведение полной истории изменения статусов заказов критично для аудита, поддержки, аналитики и автоматической рассылки уведомлений пользователям о ходе обработки их заказов. Триггер автоматически создает запись в "order".order_status_log при каждом изменении статуса заказа, избавляя разработчиков от необходимости вручную поддерживать историю изменений на уровне приложения.
2. Автоматическая фиксация истории изменения цены варианта товара
История изменения цен на SKU необходима для аналитики, отображения "старой цены", отслеживания акций и автоматических уведомлений о скидках. Триггер фиксирует каждое изменение поля price в таблице product.variant в историю product.price_history. Это позволяет хранить полную динамику цен без ошибок и пропусков.
3. Синхронизация остатка по складу при изменении товаров на складе
Каждое изменение остатков на складе (например, пересчет, поступление, списание) должно автоматически обновлять поле last_updated для корректной аналитики и контроля за актуальностью данных. Также, такой триггер может запускать автоматическую проверку на достижение минимального порога и инициировать автозаказ.
4. Автоматическая деактивация вариантов товара с нулевым остатком
Чтобы исключить ситуации, когда клиент покупает отсутствующий товар, при достижении нулевого остатка на всех складах для варианта товара автоматически переводится его поле is_active в FALSE. Это снижает количество негативных отзывов и отмен заказов.
5. Логирование попыток входа администратора (безопасность)
Контроль входа администраторов — ключ к информационной безопасности. Все попытки входа (успешные и неудачные) автоматически фиксируются в admin.login_attempt через BEFORE INSERT триггер. Это позволяет своевременно выявлять атаки, взломы или подозрительную активность.
6. Логирование ключевых изменений в товаре и автоматическая запись истории
Все важные правки товара (изменение статуса, описания, название) должны фиксироваться для аудита действий сотрудников, восстановления ошибок и контроля злоупотреблений. Триггер создает запись в product.status_history при изменении статуса и может быть расширен для других ключевых полей.
7. Автоматическое обновление счетчика использований промокода
Корректный учёт количества использований промокода важен для ограничения акций и предотвращения злоупотреблений. Триггер при каждом вставке в marketing.promo_usage увеличивает счетчик used_count в таблице marketing.promo_code, исключая рассогласование данных.
8. Обновление баланса кошелька пользователя при транзакциях
Для корректности отображения баланса бонусного/кэшбэк-кошелька пользователя важно, чтобы каждая транзакция автоматически обновляла итоговый баланс в таблице payment.wallet. Триггер на вставку новой транзакции уменьшает вероятность потери или искажения данных при ошибках приложения.
9. Автоматическая установка "основного" адреса, email или телефона
Для предотвращения ситуации, когда у пользователя нет основного email/телефона/адреса (а это критично для восстановления доступа и коммуникаций), триггер автоматически выставляет флаг is_primary=TRUE для первой записи, если таковых нет, и гарантирует уникальность среди записей одного пользователя.
10. Подсчёт среднего рейтинга товара при добавлении отзыва
Для быстрого отображения "среднего рейтинга" на карточке товара и в поиске, обновлять это значение при каждом добавлении нового отзыва эффективнее, чем постоянно пересчитывать агрегаты запросами. Триггер поддерживает кэш поля avg_rating и/или review_count в таблице product.product.
11. Автоматическая установка даты публикации контента
Для статей, страниц или иных публикаций при изменении статуса на "published" важно корректно заполнять поле published_at. Это обеспечивает целостность CMS: пользователи и администраторы видят точную дату выхода материала, а фронт не требует дополнительных ручных обновлений.
12. Логирование всех событий суппорта (изменения статуса тикета)
Полная история обращений в поддержку и изменения их статусов позволяет оценивать работу службы поддержки, проводить аналитику и обеспечивать прозрачность для пользователей. Триггер автоматически пишет запись в support.ticket_status_log при смене статуса тикета.
Примечание
Добавление этих триггеров существенно увеличит надежность, прозрачность и автоматизацию ключевых бизнес-процессов маркетплейса, снимет нагрузку с прикладного кода и обеспечит целостность данных на уровне самой базы. Они отражают лучшие практики проектирования реляционных систем для крупного e-commerce.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ