Advanced SQL Programmability

Procedures, Triggers & Automation

Global retail operations are too complex to be managed by simple queries alone. High-level engineers use built-in "Programs" within the database to automate tasks. How do we ensure every stock change is audited? How do we calculate complex regional taxes without rewriting logic in every app?

This chapter explores Programmable SQL. We will master Stored Procedures for complex workflows, Functions for reusable logic, and Triggers for invisible automation. These are the tools that transform a database from a storage bin into an intelligent engine.

Stored Procedures: Scripting the Business

A Stored Procedure is a group of SQL statements that can be saved and reused. Instead of your Python or Node.js app sending 50 lines of complex query logic, it simply calls the procedure.

Procedures are ideal for "Heavy Lifting" tasks like end-of-day financial reconciliation or bulk inventory re-balancing across regional hubs. They reduce network traffic and keep business logic secure within the database.

SQL
CALL process_monthly_bonus(region => 'USA');

User Defined Functions (UDFs)

Functions are similar to procedures but with one key difference: They always return a value. Think of them as custom keywords you add to SQL. If you frequently need to convert "USD to EUR" or "Celsius to Fahrenheit" in your reports, you can write a function to do it once.

SQL
SELECT product_name, convert_to_usd(price, 'GBP') as usd_price FROM products;

Triggers: The Invisible Watchmen

A Trigger is a special type of function that "Fires" automatically when a specific event happens (like an INSERT, UPDATE, or DELETE).

They are the ultimate tool for Auditing and Derived Data. For example, a trigger can automatically update a "Total Revenue" column in the Stores table every time a new row is added to the Sales table, ensuring your totals are always live and accurate.

SQL
CREATE TRIGGER update_customer_loyaltyAFTER INSERT ON purchasesFOR EACH ROWEXECUTE FUNCTION increment_points();
SQLRuns entirely in your browser — nothing is sent to a server.

Practice Questions

Question 1

What is the main requirement of a Function (UDF) that separates it from a Procedure?

  • It must be written in Python
  • It must return a value
  • It cannot be used with SELECT
  • It must delete data

Question 2

Which event can 'fire' a Database Trigger?

  • A user logging in
  • An INSERT, UPDATE, or DELETE operation
  • A page reloading
  • A timer going off

Question 3

In a trigger function, what variable represents the data BEFORE it was changed?

  • PREV
  • BEFORE
  • OLD
  • PAST

Question 4

Why use a Stored Procedure for complex operations instead of multiple separate queries from your app?

  • It makes the code look longer
  • It reduces network traffic and ensures the whole operation happens as one unit
  • It is only for small databases
  • It doesn't require a password

Question 5

Can a trigger be set to run BEFORE a record is deleted?

  • No, only AFTER
  • Yes, to perform checks or archive data first
  • Only on Sundays
  • Only if the table is empty