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.
You can develop the procedural objects your server type supports.
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 precompiled 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.
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.
Use the Adaptive Server Anywhere help topics and cheat sheets to develop procedural objects for Sybase IQ.
For more information on user-defined functions and event handlers, see the Adaptive Server Anywhere documentation in the Sybase WorkSpace Server Administration collection.
Send your feedback on this help topic to Sybase Tech Pubs: pubs@sybase.com