Procedural Routine objects are the building blocks of a database application. Database Development builds the skeleton for procedural routine objects and provides tools to populate the body of the object with the appropriate SQL.
Procedural Routine 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 routine 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 an object, it is automatically checked for correct syntax and stored in the system tables. The first time any application calls or fires the 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.
| 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 object types that your database and server support.
| 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 |