Stored Procedures are SQL-PL routines which can be used to extend the facilities of the database. SQL-PL allows two kinds of stored procedures:
- DB procedures can be called directly by an application process, like an SQL statement.
- Triggers are always bound to a definite table. They are called implicitly by the database after an INSERT, UPDATE or DELETE statement.
DB procedures are used
- to improve the efficiency, since a DB procedure is processed by the database server which reduces the need for communication.
- to simplify the programming, since complex sequences of SQL statements can be replaced by a single call of a DB procedure and can be used centrally from several application programs.
- to simplify authorization, since it is not necessary to grant privileges for the addressed database objects in addition to the execute privilege for a DB procedure.
Triggers are a useful means
- to formulate conditions for which the options of the CONSTRAINT clause do not suffice.
- to keep information in other tables consistent.