Assignment 8-A

Build Stored Objects

Create a complete library of stored procedures, user-defined functions, and triggers for a business application. You'll encapsulate business logic, create reusable functions, and automate data operations.

6-8 hours
Advanced
175 Points
What You'll Practice
  • Stored procedures with parameters
  • Scalar and table-valued functions
  • BEFORE and AFTER triggers
  • Error handling in procedures
  • Audit trail automation
Contents
01

Assignment Overview

In this assignment, you will build a complete Inventory Management System backend using stored procedures for business operations, functions for calculations, and triggers for automatic auditing.

Skills Applied: This assignment tests your understanding of Stored Procedures (Topic 8.1), User-Defined Functions (Topic 8.2), and Triggers (Topic 8.3) from Module 8.
Stored Procedures

Input/output parameters, control flow

Functions

Scalar, table-valued, deterministic

Triggers

BEFORE, AFTER, INSERT/UPDATE/DELETE

02

The Scenario

SmartStock Inventory System

SmartStock needs a programmable database backend. All business logic should be encapsulated in stored procedures, with reusable functions for calculations.

"We need procedures for orders, inventory updates, and reports. Every stock change must be automatically logged in our audit table."

03

Implementation Tasks

1
Stored Procedures (6 procedures)
  • sp_CreateOrder - Create new order with items
  • sp_UpdateStock - Adjust inventory levels
  • sp_ProcessReturn - Handle product returns
  • sp_GetInventoryReport - Generate stock report
  • sp_SearchProducts - Search with multiple filters
  • sp_TransferStock - Move stock between warehouses
2
User-Defined Functions (5 functions)
  • fn_CalculateDiscount - Apply tiered discount pricing
  • fn_GetStockStatus - Return stock level category
  • fn_CalculateTax - Compute tax based on region
  • fn_GetLowStockItems - Table-valued: items below reorder
  • fn_GetProductHistory - Table-valued: product transactions
3
Triggers (4 triggers)
  • trg_AuditStockChange - Log all inventory changes
  • trg_UpdateLastModified - Auto-update timestamp
  • trg_PreventNegativeStock - BEFORE trigger validation
  • trg_NotifyLowStock - Alert when stock below threshold
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-8-stored-objects
  • Include procedures.sql with all stored procedures
  • Include functions.sql with all UDFs
  • Include triggers.sql with all triggers
  • Include test-cases.sql demonstrating each object
05

Grading Rubric

Criteria Points
Stored procedures (6 × 15 pts) 90
User-defined functions (5 × 10 pts) 50
Triggers (4 × 8.75 pts) 35
Total 175