The Core Idea
Moving Logic Into the Database Layer
A stored procedure is a saved, named block of SQL logic (often with parameters) stored inside the database itself, which an application calls by name rather than sending the full raw SQL every time. A trigger is similar saved logic, but instead of being called explicitly, it fires automatically whenever a specific event occurs on a specific table — most commonly a row being inserted, updated, or deleted.
Both move some business logic out of the application code and into the database layer itself, which can improve performance (the database doesn't need to receive and reparse a full SQL statement from the application every time) and can enforce rules that must apply no matter which application or user touches the data — but both also mean some of your business logic now lives inside the database rather than in one central, easily version-controlled application codebase.
💡 Memory Trick
A STORED PROCEDURE is a saved macro on your computer — you have to actively run it when you want it, but once saved, running it is much faster than retyping the whole set of steps every time. A TRIGGER is a smoke detector — you never manually 'run' it; it's just sitting there, wired to fire automatically the instant a specific event (smoke, in this case a row change) happens, without anyone needing to ask it to.
How Each One Works
Stored Procedures vs. Triggers in Practice
1
Stored Procedure — Called Explicitly
Written once, saved in the database, and invoked by name (often with parameters) whenever an application needs that specific logic executed — like CALL transfer_funds(account_a, account_b, 100). The database can precompile and optimize the execution plan once, making repeated calls faster than sending equivalent raw SQL each time.
Example: a stored procedure encapsulating the full multi-step logic of processing a refund, called identically from a website, a mobile app, and an internal admin tool.
2
Trigger — Fires Automatically on an Event
Attached to a specific table and a specific event type (BEFORE or AFTER an INSERT, UPDATE, or DELETE), a trigger's logic runs automatically every single time that event occurs on that table — no application code needs to remember to call it, which is exactly the point.
Example: a trigger that automatically logs every change made to a 'salaries' table into a separate audit_log table, regardless of which application or user made the change.
The Trade-offs
Why These Aren't Always the Default Choice
Stored procedures and triggers can improve performance and enforce rules consistently regardless of which application touches the data, but they come with real costs: the logic becomes harder to version-control and test alongside the rest of your application code (since it lives inside the database rather than in your regular codebase), it can be harder for new developers to discover ('why did this row change — was there a trigger I didn't know about?'), and it ties your business logic more tightly to a specific database vendor's specific procedural SQL dialect, which can complicate switching database systems later.
Triggers specifically have a reputation for making systems harder to reason about, precisely because they run silently and automatically — a developer debugging unexpected data changes might not think to check for a trigger at all, since nothing in the application code explicitly calls it. For this reason, some teams deliberately minimize trigger use, preferring to keep as much logic as possible visible and explicit in application code, reserving triggers for a small number of well-documented, genuinely cross-cutting concerns like auditing.
🖥️ Applied Scenario
You need every single change to a sensitive 'account_balance' column to be automatically logged with a timestamp and the old/new values, regardless of which of your three different applications (web, mobile, and an internal admin panel) made the change.
1
You consider adding logging code to each of the three applications separately, but recognize this means remembering to add and maintain identical logging logic in three separate codebases — a fragile, easy-to-miss approach.
2
You choose a trigger instead, attached directly to the account_balance table, firing automatically on every UPDATE regardless of which application, script, or even direct database console session made the change.
3
This guarantees the audit log can never be accidentally bypassed by forgetting to add logging code in some new application or admin script — the trigger fires at the database level, beneath all of them.
4
Conclusion: because this specific requirement (guaranteed, un-skippable auditing regardless of the calling application) is exactly what triggers are built for, the usual concerns about triggers' 'hidden logic' are outweighed by the specific reliability guarantee needed here.
📌 Exam Application
Exam questions frequently ask you to distinguish stored procedures from triggers by how they're invoked (explicit call vs. automatic event-firing), and to identify an appropriate use case for each given a described requirement. You may also be asked to explain a potential downside of relying heavily on triggers for business logic.
⚠️ Most Common Stored Procedures and Triggers Mistakes
The most common mistake is confusing WHEN each one runs — a stored procedure never runs on its own; something must explicitly call it. A trigger never needs to be called explicitly; it's tied to a specific event on a specific table and fires whenever that event occurs. Another frequent error is treating triggers as a default best practice for enforcing business rules — because triggers run silently without any explicit call in application code, overusing them can make a system's actual behavior significantly harder for developers to trace and debug, which is why many teams reserve triggers for a small number of clearly-documented, genuinely cross-cutting needs.
✓ Quick Self-Test
Can you explain, in one sentence, the key difference in how a stored procedure and a trigger are invoked? Given a described business requirement, can you identify whether a stored procedure or a trigger is the more appropriate tool, and explain why?
Next Lesson
Query Optimization
→
← All Databases Lessons