Procedural Routine Objects
Routine objects are the building blocks of a
database application. Database Development builds the skeleton for
routine objects and provides tools to populate
the body of the object with the appropriate SQL.
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
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
Table 1. Object types
||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
||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
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.
(SQL Anywhere and Sybase IQ
||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.
Table 2. Procedural object support in Sybase servers
||Adaptive Server Enterprise
||Yes, though performance considerations
For more information on user-defined functions and event handlers,
SQL Anywhere documentation located at the
main level of the Sybase online bookshelf. If this server
documentation is not installed, the documentation is available on the Sybase
Product Manuals Web site at