Stored procedures, user-defined functions, and triggers can be used in many different ways and for many different reasons. The main categories include performance improvement, network traffic reduction, database security, and code reusability.
Stored routines can be used to improve application performance. Since they simply appear to be compiled code stored inside the RDBMS, they generally execute faster uncompiled SQL statements (that normally have to be compiled each time a user or program calls them).
Network traffic can also be significantly reduced because there is no need to send SQL statements as they are already compiled and stored on the server. Each individual statement is probably not large enough to improve the overall network performance, but, in a large system with thousands of users and tons of SQL statements, it can make a difference.
Stored procedures, functions, and triggers can be used for database security purposes.
A stored procedure (or function) is a separate database object with its own database privileges. That means you can design a stored procedure in such way that it would, for example, update only certain columns; or insert rows with NULL values for columns that a user who executes the procedure has no permission to update — s/he would only need the privilege to execute that particular stored procedure.
Triggers are even more useful for security implementation. For example, they can be designed in such way that certain actions performed by users on certain objects are written to special database tables or OS files. These records can be reviewed later.
Read more about using stored procedures and triggers to enforce database security in Chapter 12.
Another important thing about stored routines is code reusability — once compiled, a stored procedure or user-defined function can be used over and over again by multiple users (or applications), saving time on retyping large SQL statements and reducing the probability of human errors. Also, when a persistent module needs to be changed, the change won't affect the client programs that access it, as long as all the calling parameters remain the same.