Stored Procedures, Triggers, and Database Programming

Database programming places executable logic beside the data it reads and changes. A database engine stores the program definition, checks or compiles it according to its dialect, and runs it inside a database session. The main program units are procedures, functions, and triggers.

A stored procedure is a named program that a client, another routine, or an administrative job invokes. It accepts parameters and may change data, return output parameters, produce result sets, or report status. Exact return rules differ by database.

A stored function is also named and parameterized, but callers normally use it where an expression is allowed. A function returns a value or a table-shaped result. Engines often restrict functions more than procedures because a function can appear inside a query.

A trigger is attached to an event. The database invokes it when the event occurs, without an explicit call from the application. Data triggers commonly respond to inserts, updates, or deletes. Some engines also support triggers for data-definition, login, or other server events.

The execution path

An explicit procedure call follows a visible path:

client request -> CALL or EXECUTE -> parameter binding
               -> stored program body -> SQL statements
               -> result, output values, or error

Stored Procedures, Triggers, and Database Programming

The execution path

Relevant careers