Logic held in the database.
WHAT A VIEW IS
A stored query, used like a table.
WHAT IT PROVIDES
Simplifying complex queries Presenting a restricted subset Consistent definitions across applications
WHAT IT DOES NOT PROVIDE
Performance, by itself.
WHY
It is expanded when used, not precomputed.
WHAT A MATERIALISED VIEW IS
A stored result, refreshed periodically.
WHAT MYSQL LACKS
Native support, requiring a table maintained manually.
WHAT A STORED PROCEDURE IS
A routine held in the database and called by name.
WHAT A FUNCTION IS
A routine returning a value, usable in queries.
WHAT A TRIGGER IS
Logic running automatically on insert, update or delete.
WHAT TRIGGERS ARE USEFUL FOR
Audit trails Maintaining derived values
WHY THEY DESERVE CAUTION
They execute invisibly, and diagnosing behaviour becomes harder.
WHAT TO AVOID
Business logic spread between application and database Triggers calling other triggers Routines nobody has in version control
WHY THAT LAST POINT MATTERS
They are easily lost in a restore, and frequently omitted from dumps by default.
WHAT TO INCLUDE IN BACKUPS EXPLICITLY
Routines and triggers.
WHAT TO KEEP
Their definitions in version control, alongside the application.
