Procedural Objects

Procedural objects are the building blocks of a database application. Database Development builds the skeleton for procedural objects and provides tools to populate the body of the object with the appropriate SQL.

Procedural objects standardize actions performed by more than one application program. By coding an 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. Because 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 procedural object, it is automatically checked for correct syntax and stored in the system tables. The first time any application calls or fires a procedural object, 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 some differences.

Table 1. Procedural object types
Procedural object type	Description
Stored procedures	A stored procedure is a collection of SQL statements and optional control-of-flow statements. A stored procedure can use parameters to accept values and return values to the calling environment. A stored procedure can also return result sets or invoke other procedures.
Triggers	A trigger is a special form of stored procedure that executes when a user attempts to change table or column data using a command such as insert, delete, or update. Triggers can call stored procedures and functions and can fire other triggers. Triggers are often used to enforce referential integrity and can cascade changes through related tables, roll back transactions, enforce complex restrictions, and perform simple analyses. Triggers are available in SQL Anywhere and Adaptive Server Enterprise data servers only.
Event handlers (SQL Anywhere and Sybase IQ only)	Event handlers are software routines that manage predefined system events. When an event condition is satisfied and an event handler executes, one or more actions are performed. These actions may 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 to stored procedures in programming constructs used. Event handlers differ from stored procedures in that event handlers do not take any arguments.
User-defined functions (SQL Anywhere and Sybase IQ only)	User-defined functions return 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.

You can develop the procedural objects that your database and server type support.

Table 2. Procedural object support in Sybase servers
Procedural object type	Adaptive Server Enterprise	SQL Anywhere	Sybase IQ
Stored procedures	Yes	Yes	Yes
Triggers	Yes	Yes	No
Event handlers	No	Yes	Yes
User-defined functions	No	Yes	Yes, though performance considerations may apply

Note: For more information on user-defined functions and event handlers, see the SQL Anywhere documentation located at the main level of the Sybase WorkSpace online bookshelf. If this server documentation is not installed, the documentation is available on the Sybase Product Manuals Web site at http://sybooks.sybase.com.