User's Guide
PART 1. Working with Databases
CHAPTER 10. Using Procedures, Triggers, and Batches
Procedures and triggers are defined in the database, separate from any one database application. This separation provides a number of advantages.
Procedures and triggers allow standardization of any actions that are performed by more than one application program. The action is coded once and stored in the database. The applications need only CALL the procedure or fire the trigger to achieve the desired result. If the implementation of the action evolves over time, any changes are made in only one place, and all applications that use the action automatically acquire the new functionality.
When used in a database implemented on a network server, procedures and triggers are executed on the database server machine. They can access the data in the database without requiring network communication. This means that they execute faster and with less impact on network performance than if they had been implemented in an application on one of the client machines.
When a procedure or trigger is created, it is checked for correct syntax and then stored in the system tables. The first time it is required by any application, it is retrieved from the system tables and compiled into the virtual memory of the server, and executed from there. Subsequent executions of the same procedure or trigger will result in immediate execution, since the compiled copy is retained. A procedure or trigger can be used concurrently by several applications and recursively by one application. Only one copy is compiled and kept in virtual memory.
Procedures, including user-defined functions, execute with the permissions of the procedure owner but can be called by any user that has been granted permission to do so.
Triggers execute under the table permissions of the owner of the associated table but are fired by any user with permission to insert, update or delete rows in the table. This means that a procedure or trigger can (and usually does) have different permissions than the user ID that invoked it. Procedures and triggers provide security by allowing users limited access to data in tables that they cannot directly examine or modify.