¿Te resultaron fáciles los ejercicios anteriores? Pues venga, ahora te traigo algo más interesante. Vamos a afianzar tus conocimientos de PL-SQL escribiendo procedimientos y triggers. ¿Listo?
Creación de procedimientos
Aquí tienes algunos procedimientos que te recomiendo añadir a la base de datos de tu marketplace. Cada uno viene con una explicación de qué problema resuelve y por qué merece la pena implementarlo. Estos procedimientos cubren la automatización de operaciones clave, mejoran la experiencia de usuario, optimizan el escaparate, la logística, el soporte, el marketing y la analítica.
1. Procesar pedido con reserva automática de productos
Procesar un pedido es una operación clave en cualquier marketplace. El procedimiento no solo debe crear el registro del pedido y sus posiciones, sino también reservar el producto en el almacén, descontar la cantidad, fijar el estado, iniciar el pago y lanzar los procesos siguientes (notificaciones, logística). Automatizar este escenario reduce errores humanos, evita el over-selling y mantiene el stock sincronizado en tiempo real.
2. Automatización de la reposición de stock
Para evitar out-of-stock y perder ventas, es importante reponer el almacén a tiempo si el stock baja del mínimo. Este procedimiento revisa el stock de todos los productos, lo compara con el mínimo y automáticamente genera solicitudes de compra o resupply interno. Automatizar esto reduce el tiempo de reacción y la carga manual del equipo de operaciones.
3. Actualización masiva de estados de pedidos y notificación a clientes
A menudo hay que cambiar el estado de muchos pedidos a la vez (por ejemplo, “paid” → “shipped” o “shipped” → “completed”) según la fase de procesamiento. El procedimiento actualiza los estados de todos los pedidos que correspondan, guarda el historial de cambios (para auditoría) y puede lanzar notificaciones a los clientes. Así se automatiza el trabajo del back-office y se minimizan los errores manuales.
4. Aplicación masiva de descuentos/promocodes a productos
Para promocionar productos o lanzar campañas, muchas veces hay que aplicar descuentos o promocodes a toda una categoría, marca o selección de productos. El procedimiento pone los descuentos automáticamente, controla las restricciones (fecha, límites), actualiza los contadores de uso y evita duplicados.
5. Reembolso automático al usuario
Gestionar devoluciones y reembolsos es clave para la confianza del cliente. El procedimiento debe comprobar el estado del pedido, la validez de la devolución, iniciar el reembolso del pago, actualizar los estados, registrar la transacción y notificar al usuario. Hacer todo esto en una sola transacción reduce el riesgo de errores y fraudes.
6. Recalcular la valoración media de producto/vendedor
Con cada nueva reseña, cambio o borrado, la valoración media del producto o usuario debe estar actualizada. El procedimiento recalcula y actualiza el campo “avg_rating”/“review_count” para acelerar las consultas del frontend y mantener la analítica coherente.
7. Desactivación automática de productos con stock cero
Para que el escaparate funcione bien y evitar malas experiencias, los productos con stock cero deben ocultarse automáticamente (desactivarse). El procedimiento revisa periódicamente los almacenes y el estado de los productos, cambia el estado a “inactive”, registra el cambio y mantiene el catálogo actualizado sin tener que revisarlo a mano.
8. Asignación automática de pedido a repartidor y actualización del estado de entrega
En logística es importante asignar rápido y de forma clara un repartidor al pedido, cambiar el estado de la entrega y registrar todas las acciones para el tracking. El procedimiento automatiza estos pasos y elimina el trabajo manual del manager.
9. Envío masivo de notificaciones a usuarios (trigger de promo, recordatorios)
Las notificaciones sobre rebajas, devoluciones, cambios de estado o campañas de marketing deben enviarse en masa, según condiciones (usuarios activos, inactivos, abandonaron carrito, etc). El procedimiento permite lanzar notificaciones push/email a un segmento definido.
10. Archivado de datos antiguos (por ejemplo, pedidos completados o productos inactivos)
Para mantener el rendimiento de la base de datos y reducir el tamaño de las tablas “calientes”, los registros antiguos (pedidos viejos, productos archivados, tickets de soporte antiguos) deben moverse o marcarse como “archivo” periódicamente. El procedimiento facilita el borrado o traslado de datos y minimiza el trabajo manual de los admins.
Creación de triggers
1. Logging de cambios de estado de pedido
Llevar el historial completo de los cambios de estado de los pedidos es clave para auditoría, soporte, analítica y notificaciones automáticas a los usuarios sobre el progreso de sus pedidos. El trigger crea automáticamente un registro en "order".order_status_log cada vez que cambia el estado del pedido, así los desarrolladores no tienen que mantener el historial manualmente en la app.
2. Registro automático del historial de cambios de precio de variantes de producto
El historial de precios de los SKU es necesario para analítica, mostrar el “precio anterior”, seguir campañas y notificar automáticamente sobre descuentos. El trigger registra cada cambio en el campo price de la tabla product.variant en el historial product.price_history. Así se guarda toda la dinámica de precios sin errores ni omisiones.
3. Sincronización de stock de almacén al cambiar productos en almacén
Cada cambio de stock en almacén (recuento, entrada, salida) debe actualizar automáticamente el campo last_updated para una analítica correcta y control de la actualidad de los datos. Además, este trigger puede lanzar una comprobación automática del mínimo y generar un auto-pedido.
4. Desactivación automática de variantes de producto con stock cero
Para evitar que un cliente compre un producto agotado, cuando el stock llega a cero en todos los almacenes, el campo is_active de la variante se pone automáticamente en FALSE. Así se reducen las valoraciones negativas y cancelaciones de pedidos.
5. Logging de intentos de acceso de administrador (seguridad)
Controlar los accesos de administradores es clave para la seguridad de la información. Todos los intentos de acceso (exitosos y fallidos) se registran automáticamente en admin.login_attempt mediante un trigger BEFORE INSERT. Así se pueden detectar ataques, hackeos o actividad sospechosa a tiempo.
6. Logging de cambios clave en producto y registro automático del historial
Todos los cambios importantes en un producto (estado, descripción, nombre) deben registrarse para auditar acciones del staff, recuperar errores y controlar abusos. El trigger crea un registro en product.status_history al cambiar el estado y se puede ampliar para otros campos clave.
7. Actualización automática del contador de usos de promocode
Llevar bien la cuenta de los usos de un promocode es importante para limitar campañas y evitar abusos. El trigger, cada vez que se inserta en marketing.promo_usage, incrementa el contador used_count en la tabla marketing.promo_code, evitando inconsistencias.
8. Actualización del saldo del monedero del usuario en transacciones
Para mostrar correctamente el saldo del monedero de puntos/cashback del usuario, cada transacción debe actualizar automáticamente el saldo final en la tabla payment.wallet. El trigger al insertar una nueva transacción reduce el riesgo de pérdida o corrupción de datos por errores de la app.
9. Asignación automática de “principal” a dirección, email o teléfono
Para evitar que un usuario no tenga email/teléfono/dirección principal (clave para recuperar acceso y comunicaciones), el trigger pone automáticamente el flag is_primary=TRUE en el primer registro si no hay ninguno, y garantiza la unicidad entre los registros de un usuario.
10. Cálculo del rating medio del producto al añadir una reseña
Para mostrar rápido el “rating medio” en la ficha del producto y en búsquedas, es más eficiente actualizar este valor al añadir una reseña que recalcularlo siempre con queries. El trigger mantiene el caché del campo avg_rating y/o review_count en la tabla product.product.
11. Asignación automática de la fecha de publicación de contenido
Para artículos, páginas u otras publicaciones, al cambiar el estado a “published” es importante rellenar bien el campo published_at. Así el CMS es coherente: usuarios y admins ven la fecha real de publicación y el frontend no necesita actualizaciones manuales extra.
12. Logging de todos los eventos de soporte (cambios de estado de ticket)
Llevar el historial completo de tickets de soporte y sus cambios de estado permite analizar el trabajo del soporte, hacer analítica y dar transparencia a los usuarios. El trigger escribe automáticamente un registro en support.ticket_status_log al cambiar el estado del ticket.
Nota
Añadir estos triggers aumenta mucho la fiabilidad, transparencia y automatización de los procesos clave del marketplace, descarga el código de la app y asegura la integridad de los datos en la propia base. Reflejan las mejores prácticas de diseño de sistemas relacionales para e-commerce a gran escala.
GO TO FULL VERSION