What are Stored Procedures and Triggers, and when should you avoid them?
A Stored Procedure is a prepared set of SQL statements and control logic compiled and stored on the database server, executed on demand via `CALL`.
A Trigger is code automatically executed by the database engine in response to specific DML events (`INSERT`, `UPDATE`, or `DELETE`) on a table.
While they reduce network round-trips and enforce strict constraints, overuse creates hidden business logic that is difficult to version control, debug, unit test, and scale horizontally across application servers.
-- Trigger that automatically logs price adjustments
CREATE TRIGGER trg_audit_price_change
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
BEGIN
INSERT INTO ProductPriceAudit (product_id, old_price, new_price, changed_at)
VALUES (NEW.id, OLD.price, NEW.price, NOW());
END;Every time a product's price is updated, this trigger automatically records the historical price change into the audit table, ensuring tamper-proof compliance regardless of which service triggered the update.
Ready for CTEs (Common Table Expressions)?
Take a quick test to evaluate yourself, or move straight to the next module.