Stored Procedures Advanced

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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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.

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 .

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.

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:

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.

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.

First, a scalar function that turns a monthly salary into a yearly 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 :

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.

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.

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.

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:

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:

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary