Advanced Track

Advanced Stored Procedures, Functions & Triggers

By the end of this lesson you'll be able to move logic into the database: write stored procedures that take parameters and run control flow, build scalar and table-valued functions you can drop straight into a query, and create triggers that fire automatically to keep an audit trail or fill in derived columns. This is how real systems enforce rules and stay consistent no matter which app touches the data.

What You'll Learn

✓Write stored procedures with IN / OUT / INOUT parameters
✓Use control flow — IF, WHILE, loops — and local variables
✓Handle errors inside a routine instead of letting them leak
✓Tell scalar from table-valued user-defined functions
✓Build audit triggers (BEFORE / AFTER, INSERT / UPDATE / DELETE)
✓Use a BEFORE trigger to fill in a derived column automatically

Before you start: finish Window Functions and be comfortable with JOIN, UPDATE, and basic transactions. The in-browser editor lets you write and edit SQL; to run procedural code, copy it into a free playground such as db-fiddle.com (pick MySQL or PostgreSQL) or sqliteonline.com.

Heads up — procedural SQL is not portable. Plain queries (SELECT, JOIN) look almost identical everywhere, but the language for procedures, functions and triggers varies a lot between engines: MySQL uses DELIMITER + BEGIN…END, PostgreSQL wraps a body in $$ … $$ as plpgsql, SQL Server uses T-SQL with @variables, and SQLite is far more limited. Every example below is labelled with the dialect it uses. Learn the concepts here; check your engine's manual for its exact keywords.

Our Sample Table: `employees`

The procedures, functions, and triggers below act on this little employees table (plus a matching departments table joined on department_id). Picture the data as you read.

Result:

id	name	department_id	salary
1	Ada Lovelace	1	7000
2	Alan Turing	1	8200
3	Grace Hopper	2	6400
4	Linus Torvalds	1	7500

1. Stored Procedures — Parameters, Control Flow & Errors

A stored procedure is a block of SQL you save in the database under a name and run later with CALL. Because it lives in the database, every application — your website, a script, a colleague's dashboard — runs the same logic, so the rules can't drift apart.

Procedures take three kinds of parameter. IN passes a value in (read-only inside). OUT hands a value back out to the caller. INOUT does both. Inside, you get real programming: DECLARE local variables, branch with IF, and loop with WHILE or LOOP.

🏦 Real-world analogy

A stored procedure is a bank teller's checklist. You hand over a request (the IN params), the teller follows fixed steps — check it's valid, do the work, hand back a receipt (the OUT param) — and refuses anything that breaks the rules (an error). Nobody gets to skip a step.

The worked example below gives every employee a raise by a percentage. Read the comments — each non-obvious line says what it does.

give_raise — IN/OUT params + IF guard

A procedure that validates input, reads a local variable, updates a row, and returns a value.

Try it Yourself »

SQL

-- A stored procedure is a named block of SQL you save in the database
-- and CALL by name — like a reusable function for your data.
-- Syntax style: MySQL.  (PostgreSQL labelled separately below.)

DELIMITER //                       -- tell the client "// ends the routine", not ;

CREATE PROCEDURE give_raise(
    IN  p_emp_id   INT,            -- IN  = caller passes a value in (read-only inside)
    IN  p_percent  DECIMAL(5,2),   -- e.g. 10.00 means a 10% raise
    OUT p_new_pay  DECIMAL(10,2) 
...

Calling CALL give_raise(1, 10.00, @new) raises Ada's salary from 7000.00 to 7700.00, writes that back through the OUT parameter, and the follow-up SELECT @new shows it:

Result — value returned via the OUT parameter:

new_salary
7700

Why DELIMITER //? A procedure body contains its own ; statements. If the client still treated ; as "send this now", it would cut the procedure off at the first line. DELIMITER // temporarily makes // the end-of-statement marker so the whole BEGIN…END arrives in one piece, then you switch back with DELIMITER ;. PostgreSQL avoids this by wrapping the body in $$ … $$ instead.

Your Turn: complete a procedure

Fill in the two blanks so this procedure takes a user id, switches that user off, and reports how many rows it changed. The expected behaviour is in the comments so you can check yourself.

🎯 Your Turn: deactivate_user

Name the IN parameter and supply the value that means off.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — complete the stored procedure (MySQL style).
-- Goal: deactivate one user by id, then report how many rows changed.

DELIMITER //
CREATE PROCEDURE deactivate_user(
    IN ___ INT                 -- 👉 name the input parameter: p_user_id
)
BEGIN
    UPDATE users
    SET is_active = ___        -- 👉 the value that means "switched off": FALSE
    WHERE id = p_user_id;

    SELECT ROW_COUNT() AS rows_updated;   -- how many rows the UPDATE touched
END //
DELIMITER ;

CALL deactivate
...

2. User-Defined Functions — Scalar vs Table-Valued

A function is like a procedure but it's built to return a value and can be used right inside a query. There are two flavours, and the difference is simply what shape they hand back.

Scalar function — returns exactly one value (a number, a string, a date). Use it anywhere a value is valid: in the SELECT list, in WHERE, in ORDER BY.
Table-valued function — returns a whole result set. Use it in the FROM clause as if it were a table.

First, a scalar function that turns a monthly salary into a yearly one:

annual_salary — a scalar function

Returns one value; called per row inside a SELECT.

Try it Yourself »

SQL

-- A SCALAR function returns ONE value. Use it anywhere a value is allowed:
-- in SELECT lists, WHERE clauses, ORDER BY...   (Syntax style: MySQL)

DELIMITER //
CREATE FUNCTION annual_salary(p_monthly DECIMAL(10,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC                       -- same input always gives the same output
BEGIN
    RETURN p_monthly * 12;          -- exactly one value comes back
END //
DELIMITER ;

-- Drop it straight into a query, like a built-in function:
SELECT
    name,
    salary  
...

Result — the function runs once per row:

name	monthly_pay	yearly_pay
Ada Lovelace	7000	84000
Alan Turing	8200	98400
…	…	…

💡 Function vs Procedure — which one?

Reach for a function when you want a value back to use inside a query (it slots into SELECT/FROM/WHERE). Reach for a procedure when you want to perform actions — update rows, run several statements, manage a transaction. As a rule of thumb: functions compute, procedures do.

Now a table-valued function. Here it's shown in PostgreSQL, whose RETURNS TABLE syntax makes the idea crystal clear — it returns rows you can SELECT * FROM:

employees_in — a table-valued function

Returns a result set you query in the FROM clause.

Try it Yourself »

SQL

-- A TABLE-VALUED function returns a whole result set you can SELECT FROM.
-- PostgreSQL expresses this cleanly with RETURNS TABLE.   (Syntax style: PostgreSQL)

CREATE OR REPLACE FUNCTION employees_in(p_dept VARCHAR)
RETURNS TABLE (              -- 👈 the shape of the rows handed back
    employee_name VARCHAR,
    salary        DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY             -- stream these rows out as the function's result
    SELECT e.name, e.salary
    FROM employees e
 
...

Result — employees_in('Engineering'):

employee_name	salary
Alan Turing	8200
Linus Torvalds	7500
Ada Lovelace	7000

3. Triggers — Code That Runs Automatically

A trigger is procedural code the database runs by itself whenever a table changes. You never CALL a trigger — an INSERT, UPDATE, or DELETE fires it for you. That makes triggers perfect for rules that must hold no matter which app writes the data.

Every trigger picks a timing and an event:

BEFORE — runs before the change is saved; can modify the incoming row via NEW.* or reject it.
AFTER — runs once the change is saved; ideal for logging because the data is already final.
The event is INSERT, UPDATE, or DELETE, and you usually run FOR EACH ROW.

Inside the body you get two special snapshots: OLD (the row before the change) and NEW (the row after). INSERT only has NEW; DELETE only has OLD; UPDATE has both.

🚪 Real-world analogy

A trigger is a security camera wired to the door. You don't ask it to record — opening the door (the data change) makes it record automatically. An AFTER trigger is the camera saving footage; a BEFORE trigger is a bouncer who can stop you at the door or stamp your hand first.

Here's the headline example: an audit trigger that logs every salary change to a history table — automatically.

trg_salary_audit — an AFTER UPDATE audit trail

Writes OLD and NEW salary to a history table on every change.

Try it Yourself »

SQL

-- A TRIGGER is code the database runs AUTOMATICALLY whenever a table
-- changes. You never CALL it — INSERT/UPDATE/DELETE fire it for you.
-- Classic use: an audit trail of who changed what.   (Syntax style: MySQL)

-- 1) A table to record the history
CREATE TABLE salary_audit (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    emp_id     INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_by VARCHAR(100),
    changed_at DATETIME
);

-- 2) Fire AFTER each UPDATE, once 
...

After running UPDATE employees SET salary = 7700 WHERE id = 1, you never touched salary_audit directly — yet the trigger has quietly recorded the change for you:

Result — one audit row, written automatically:

id	emp_id	old_salary	new_salary	changed_by	changed_at
1	1	7000	7700	root@localhost	2026-06-15 09:31:02

A BEFORE trigger is different: it can edit the row on its way in by assigning to NEW.*. That's the clean way to fill a derived column so callers can't forget it or get it wrong:

trg_order_total — a BEFORE INSERT derived column

Computes total = quantity × unit_price before the row is stored.

Try it Yourself »

SQL

-- A BEFORE trigger can edit the row on its way in by writing to NEW.*
-- Great for derived columns and timestamps.   (Syntax style: MySQL)

DELIMITER //
CREATE TRIGGER trg_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- Derive total from the row's own columns before it is stored:
    SET NEW.total      = NEW.quantity * NEW.unit_price;
    SET NEW.created_at = NOW();
END //
DELIMITER ;

-- The caller never supplies total — the trigger fills it in:
INSERT INTO orders (quantity, unit
...

Return-value gotcha. In PostgreSQL, a BEFORE row trigger must RETURN NEW (return NULL to silently skip the write), while an AFTER trigger's return value is ignored. In MySQL there is no RETURN — you simply assign to NEW.* in a BEFORE trigger and the row is saved. Same idea, different mechanics — another reason to check your engine's docs.

Your Turn: complete a trigger body

Finish the trigger so each deleted product is copied into an audit table. Think about which snapshot — OLD or NEW — holds the row that's being removed.

🎯 Your Turn: log deletions

Pick the right snapshot for an AFTER DELETE trigger.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — finish the trigger body so it logs deletions.
-- An AFTER DELETE trigger records every removed product in deleted_products.

DELIMITER //
CREATE TRIGGER trg_log_deletes
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO deleted_products (product_id, name, deleted_at)
    VALUES (___.id, ___.name, NOW());   -- 👉 which snapshot has the gone row? OLD
END //
DELIMITER ;

DELETE FROM products WHERE id = 7;

-- ✅ Expected: product 7 is removed from products AND a matching r
...

Common Errors (and the fix)

Logic hidden in triggers becomes un-debuggable. When a value changes "by magic", the next developer has no idea a trigger did it. Keep trigger logic small and obvious, name it clearly (trg_…), and document it — or the audit log itself will be a mystery.
Recursive / cascading triggers. A trigger on orders that updates orders can fire itself again; trigger A writing table B whose trigger writes table A can loop. Make a trigger change a different table, guard with a condition, and know your engine's recursion setting.
Assuming syntax is portable. SIGNAL SQLSTATE '45000' (MySQL) is RAISE EXCEPTION in PostgreSQL and THROW in SQL Server. Copy-pasting a routine between engines almost never just works — translate it.
Not handling errors in a procedure. If a validation check is missing, a procedure happily writes bad data. Guard inputs early (IF … THEN SIGNAL/RAISE) and, for multi-step writes, use a transaction so a failure rolls everything back.
Forgetting DELIMITER in MySQL. Without it the client sends the procedure body one line at a time and you get a syntax error at the first inner ;. Wrap the definition in DELIMITER // … DELIMITER ;.

📘 Quick Reference

Syntax	Purpose
CREATE PROCEDURE p(IN x INT) …	A named, callable block of SQL
IN / OUT / INOUT	Pass a value in / out / both ways
DECLARE v INT;	A local variable inside the routine
IF … THEN … ELSE … END IF;	Branch on a condition
WHILE … DO … END WHILE;	Loop while a condition holds
CALL p(args);	Run a stored procedure
CREATE FUNCTION f(...) RETURNS T	Scalar function — returns one value
RETURNS TABLE ( … )	Table-valued function — returns rows
CREATE TRIGGER t BEFORE/AFTER …	Run code on INSERT/UPDATE/DELETE
FOR EACH ROW	Fire the trigger once per affected row
OLD.col / NEW.col	Row before / after the change
SIGNAL / RAISE / THROW	Raise an error (MySQL/PG/SQL Server)

Frequently Asked Questions

Q: What's the real difference between a procedure and a function?

A function is built to return a value and can be used inside a query (SELECT f(x), SELECT * FROM tvf()). A procedure is built to do work — run several statements, update rows, control a transaction — and you start it with CALL. Functions usually shouldn't change data; procedures often do.

Q: When should I use a trigger instead of doing it in my app?

Use a trigger for rules that must hold no matter which client writes the data — auditing, derived columns, hard invariants. If only one app ever touches the table and the logic is complex, app code is often easier to test and debug. Triggers are powerful but invisible, so use them deliberately.

Q: OLD and NEW — which exists when?

INSERT has only NEW (there was no prior row). DELETE has only OLD (there's no resulting row). UPDATE has both. Reading the one that doesn't exist for that event is an error.

Q: Why does the same routine fail when I move it to another database?

Because procedural SQL is dialect-specific. The body language (PL/pgSQL vs T-SQL vs MySQL's), how you raise errors, how variables are declared, and even how you delimit the definition all differ. Treat each engine's procedures as its own mini-language.

Mini-Challenge: Block Negative Balances

Support is faded now — just a brief and the expected behaviour. Write the trigger, then copy it into a playground to confirm it both blocks the bad update and allows the good one.

🎯 Mini-Challenge

A BEFORE UPDATE trigger that rejects a negative balance.

Try it Yourself »

SQL

-- 🎯 MINI-CHALLENGE — enforce a business rule with a trigger
-- Rule: an account balance must never be allowed to go negative.
--
-- Write a BEFORE UPDATE trigger on the "accounts" table that:
--   1. Runs FOR EACH ROW
--   2. If NEW.balance < 0, raises an error and blocks the update
--      (MySQL: SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...';)
--   3. Otherwise lets the update through untouched
--
-- ✅ Expected behaviour:
--    UPDATE accounts SET balance = -5 WHERE id = 1;  -> ERROR, no 
...

🎉 Lesson Complete

✅ Stored procedures use IN/OUT/INOUT params, local variables, and control flow (IF, WHILE)
✅ Guard inputs early and raise errors (SIGNAL/RAISE) so bad data never lands
✅ Scalar functions return one value for a query; table-valued functions return rows you SELECT FROM
✅ AFTER triggers are ideal for audit trails; BEFORE triggers can fill derived columns via NEW.*
✅ OLD/NEW give you the row before/after — and procedural syntax varies by engine
✅ Next: Advanced Transactions — isolation levels and keeping multi-step writes safe

Advanced Stored Procedures, Functions & Triggers

What You'll Learn

Our Sample Table: employees

1. Stored Procedures — Parameters, Control Flow & Errors

give_raise — IN/OUT params + IF guard

Your Turn: complete a procedure

🎯 Your Turn: deactivate_user

2. User-Defined Functions — Scalar vs Table-Valued

annual_salary — a scalar function

employees_in — a table-valued function

3. Triggers — Code That Runs Automatically

trg_salary_audit — an AFTER UPDATE audit trail

trg_order_total — a BEFORE INSERT derived column

Your Turn: complete a trigger body

🎯 Your Turn: log deletions

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: Block Negative Balances

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `employees`