Stored procedures and triggers standardize actions performed by more than one application program. By coding the action once and storing it in the database for future use, applications need only execute the procedure or fire the trigger to achieve the desired result repeatedly. And since changes occur in only one place, all applications using the action automatically acquire the new functionality if the implementation of the action changes. When you create a procedure or trigger, it is automatically checked for correct syntax, and then stored in the system tables. The first time any application calls or fires a procedure or trigger, it is compiled from the system tables into the server's virtual memory and executed from there. User-defined functions and event handlers are similar to stored procedures with the differences noted below.
Stored procedures are collections of SQL statements and optional control-of-flow statements stored under a name. They can use parameters to accept values and return values to the calling environment. Procedures can return result sets to the caller, or call other procedures. They greatly enhance the user's ability to write fast, efficient procedures because they are pre-compiled when first executed
Triggers: Triggers store SQL statements cued by activities on specific database tables. They fire automatically whenever someone inserts, updates or deletes rows of a designated table. Triggers can call stored procedures, functions, and fire other triggers. Triggers are often used to maintain referential integrity. They can cascade changes through related tables, roll back transactions, enforce complex restrictions, and perform simple "what if" analyses.
Adaptive Server Enterprise supports creation of stored procedures and triggers. Adaptive Server Anywhere supports creation of event handlers and user-defined functions in addition to stored procedures and triggers. ASIQ supports stored procedures, event handlers, and user-defined functions.
User-defined functions are a type of procedural object that returns a single value to the calling environment. User-defined functions do not modify parameters passed to them. Instead, they broaden the scope of functions available to queries and other SQL statements.
Event handlers are typically software routines that manage predefined system events. When an event condition is satisfied and an event handler executes, one or more actions are carried out. These actions vary and can include sending an e-mail message, performing a backup, or writing to a file. Event handlers, whether for scheduled events or for system event handling, contain compound statements and are similar in many ways to stored procedures. You can add loops, conditional execution, and so on, and you can use the debugger to debug event handlers. One difference between event handlers and stored procedures is that event handlers do not take any arguments. For more information on event handlers, see the ASA/ASIQ documentation.
Send your feedback on this help topic to Sybase Tech Pubs: pubs@sybase.com