A stored procedure is a named block of SQL kept in the database and run with CALL (PostgreSQL, MySQL) or EXEC (SQL Server). It can take parameters, run several statements and use variables and IF. A trigger runs automatically when rows are inserted, updated or deleted in a table, for example to log every price change.
Support differs a lot, and the database on this site has neither, so these exercises are about reading and fixing code rather than running it:
- PostgreSQL: functions and procedures in PL/pgSQL; triggers call a trigger function.
- MySQL: procedures and triggers in its own procedural SQL.
- SQL Server: T-SQL procedures and triggers; parameters start with
@.
- Oracle: PL/SQL procedures, packages and triggers.
- Snowflake and BigQuery: stored procedures, but no table triggers (they use scheduled tasks and change streams instead).
- SQLite: triggers, but no stored procedures. DuckDB: neither; it has macros (named expressions) instead.
Interviews ask what they're for, and day-to-day work means reading ones someone else wrote.