Breezed through the previous assignments? Alright, let me give you something a bit more challenging. Let’s move on to locking in your PL-SQL skills by writing some procedures and triggers. Ready?
Creating Procedures
Below are some procedures you should consider adding to your marketplace database. Each procedure comes with an explanation of what business tasks it solves and why it’s worth implementing. These procedures cover automation of key operations, improving user experience, optimizing storefront, logistics, support, marketing, and analytics.
1. Placing an Order with Automatic Product Reservation
Order placement is a core operation for any marketplace. The procedure should not only create the order record and its items, but also reserve the product in the warehouse, deduct the quantity, set the status, kick off payment, and trigger follow-up processes (notifications, logistics). Automating this scenario reduces the risk of manual errors, helps avoid over-selling, and keeps your inventory in sync in real time.
2. Automating Product Restocking
To prevent out-of-stock situations and lost sales, it’s crucial to restock warehouses on time if inventory drops below a threshold. This procedure checks stock levels for all products, compares them to the minimum, and automatically creates purchase or internal resupply requests. Automation speeds up reaction time and cuts down on manual work for the ops team.
3. Bulk Order Status Updates and Customer Notifications
In the catalog, you often need to bulk move orders between statuses (like “paid” → “shipped” or “shipped” → “completed”) depending on the processing stage. The procedure updates statuses for all matching orders, logs the changes (for audit), and can trigger notifications to customers. This automates back-office work and minimizes manual mistakes.
4. Bulk Applying Discounts/Promo Codes to Products
Promoting products or running campaigns often means applying promo codes and discounts to a whole category, brand, or selection of products. The procedure automatically sets discounts, enforces restrictions (date, limits), updates usage counters, and prevents duplicates.
5. Automatic Refunds to Users
Processing returns and refunds is super important for customer trust. The procedure should check the order status, validate the return, initiate the payment refund, update statuses, log transactions, and notify the user. Handling all conditions in one transaction reduces the risk of errors and abuse.
6. Recalculating Average Product/Seller Rating
Whenever there’s a new review, an edit, or a deletion, the average rating for a product or user should stay up to date. The procedure quickly recalculates and updates the “avg_rating”/“review_count” field to speed up frontend queries and keep analytics consistent.
7. Automatically Disabling Products with Zero Stock
To keep your marketplace storefront running smoothly and avoid bad user experiences, products with zero stock should be automatically hidden (deactivated). The procedure regularly checks warehouses and product statuses, sets status to “inactive”, logs the change, and helps keep the catalog fresh without manual checks.
8. Automatically Assigning Orders to Couriers and Updating Delivery Status
In logistics, it’s important to quickly and transparently assign a courier to an order, update delivery status, and log all actions for tracking. The procedure automates these steps and removes manual work for managers.
9. Bulk User Notifications (Promo Triggers, Reminders)
Notifications about sales, returns, status changes, or marketing activities should be sent out in bulk, based on conditions (active users, haven’t bought in a while, abandoned cart, etc.). The procedure lets you trigger push/email notifications for a given segment.
10. Archiving Old Data (like completed orders or inactive products)
To keep your database performing well and reduce the size of “hot” tables, old records (old orders, archived products, outdated support tickets) should be periodically moved or marked as “archive”. The procedure makes it easier to delete or move data and cuts down on manual admin work.
Creating Triggers
1. Logging Order Status Changes
Keeping a full history of order status changes is critical for audit, support, analytics, and automatically notifying users about their order progress. The trigger automatically creates a record in "order".order_status_log every time an order status changes, so devs don’t have to manually maintain the history in the app code.
2. Automatically Logging Price Changes for Product Variants
Tracking SKU price changes is a must for analytics, showing “old price”, tracking promos, and auto-notifying about discounts. The trigger logs every change to the price field in product.variant into product.price_history. This way, you get a full price history with no gaps or mistakes.
3. Syncing Warehouse Stock on Product Changes
Every change in warehouse stock (like recounts, arrivals, write-offs) should automatically update the last_updated field for accurate analytics and data freshness. This trigger can also kick off an auto-reorder if the minimum threshold is hit.
4. Automatically Deactivating Product Variants with Zero Stock
To avoid situations where a customer buys an out-of-stock item, when all warehouses hit zero for a product variant, its is_active field is set to FALSE automatically. This cuts down on negative reviews and order cancellations.
5. Logging Admin Login Attempts (Security)
Controlling admin logins is key for info security. All login attempts (successful and failed) are automatically logged in admin.login_attempt via a BEFORE INSERT trigger. This helps spot attacks, hacks, or suspicious activity in time.
6. Logging Key Product Changes and Auto-Writing History
All important product edits (status, description, name changes) should be logged for auditing staff actions, fixing mistakes, and abuse control. The trigger creates a record in product.status_history when the status changes, and can be expanded for other key fields.
7. Automatically Updating Promo Code Usage Counter
Accurately tracking promo code usage is important for limiting promos and preventing abuse. The trigger bumps the used_count in marketing.promo_code every time there’s an insert into marketing.promo_usage, keeping data in sync.
8. Updating User Wallet Balance on Transactions
To keep the bonus/cashback wallet balance accurate, every transaction should automatically update the final balance in payment.wallet. The trigger on new transaction inserts lowers the chance of data loss or corruption from app bugs.
9. Automatically Setting “Primary” Address, Email, or Phone
To avoid cases where a user has no primary email/phone/address (which is critical for account recovery and communication), the trigger automatically sets is_primary=TRUE for the first record if none exist, and guarantees uniqueness for each user’s records.
10. Calculating Average Product Rating When Adding a Review
For fast display of “average rating” on product cards and in search, it’s more efficient to update this value with each new review than to constantly recalculate aggregates in queries. The trigger keeps the avg_rating and/or review_count cache fields up to date in product.product.
11. Automatically Setting Content Publish Date
For articles, pages, or other publications, when the status changes to “published”, it’s important to correctly fill the published_at field. This keeps the CMS consistent: users and admins see the real publish date, and the frontend doesn’t need extra manual updates.
12. Logging All Support Events (Ticket Status Changes)
Having a full history of support requests and their status changes lets you analyze support performance, run analytics, and keep things transparent for users. The trigger automatically writes a record to support.ticket_status_log when a ticket status changes.
Note
Adding these triggers will seriously boost the reliability, transparency, and automation of your marketplace’s key business processes, take the load off your app code, and keep your data consistent right at the database level. They reflect best practices for designing relational systems for big e-commerce.
GO TO FULL VERSION