Stored Functions, Procedures, and Triggers: Put Logic in the Right Layer

Similar names, different jobs

A stored function is typically called as part of an expression or query and returns a value or table-like result. A stored procedure is invoked as an operation and may accept input/output parameters or perform a multi-step database task. Exact capabilities vary by database and version; in particular, transaction control allowed inside routines is engine-specific.

A trigger runs implicitly when a table event occurs. This can be useful for narrow audit or invariant-maintenance tasks, but hidden side effects make it harder to understand what a write does by reading the application call site alone.

Choose by ownership and observability

Use declarative constraints for rules such as uniqueness, referential integrity, and simple value checks. They are visible in schema metadata and apply to every writer. Use a stored routine when the database operation is a deliberate, stable API that benefits from server-side execution or coordinated SQL. Use application services for workflows involving remote calls, user interaction, orchestration, or domain policies that should remain visible in versioned application code.

Avoid placing business logic in triggers merely because it is close to the table. Document trigger dependencies, include them in migration review, and ensure tests exercise the resulting state rather than only checking the direct statement.

Transaction boundaries differ

PostgreSQL functions execute within the caller's transaction; procedures have distinct invocation and transaction-control rules. MySQL stored programs have their own transaction restrictions and implicit-commit behavior around some DDL. Oracle PL/SQL procedures execute within the caller's transaction unless code explicitly commits or rolls back, and commits hidden inside routines can make caller-level atomicity difficult.

Do not add a COMMIT inside a reusable routine without a clear contract. It can make a larger operation impossible to roll back and complicate retries. Keep transaction ownership explicit at the application or job boundary.

Portability and deployment

Routine languages, exception handling, result sets, identifier casing, permissions, and DDL syntax differ across engines. Version the source, deploy through migrations, grant only required execute permissions, and test rollback and failure paths against the target engine. A procedure that compiles in one database is not automatically portable SQL.

Review checklist

Ask who owns the transaction, whether callers can see all side effects, how errors are translated, and how the routine is versioned and tested. Prefer the simplest layer that can enforce the invariant without hiding behavior from operators and maintainers.