|
 |
|
| |
Procedural Routine Objects
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.
Table 1. Object types
| 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.
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 online bookshelf. If this server
documentation is not installed, the documentation is available on the Sybase
Product Manuals Web site at
https://sybooks.sybase.com.
|
|
|
