Informix Functions

Stored Procedures and Triggers

Stored procedures and triggers are two essential functions that make a successful database server and also a successful database management system. Not only do stored procedures offer a viable means to reduce processing and network traffic, but they also help remove general and repetitive functionality from the different client applications.

Stored procedures are considered separate database entities, and because they are separate, users must have the appropriate privileges to create, edit, and execute them. What is nice about stored procedures is that they can have access to specific areas of the database that users are not able to see. However, these same users might have the ability to run the stored procedure, which in turn performs specific functions in restricted areas. Therefore, the stored procedure enables users to go into restricted areas, but does not let them have full access to run wild. For example, a table contains all employees' salary and bonus information. A stored procedure is executed when a user enters information about a sale that earned commission. The stored procedure checks to see whether it's a valid commission, and then adds that amount to the appropriate person's salary. The user has no access to the table that contains the salary information, and if he or she tried to add the commission to the table without using the stored procedure, or perform any other activity on the table, it would fail.

Triggers provide the means to automatically process a task when other events occur in the database, such as specific data access or creation. By combining triggers with stored procedures, you can build a library of processes to manage data security and auditing. Chapter 14, "Managing Data with Stored Procedures and Triggers," describes in detail how to use stored procedures and triggers to manage security.