Kbase P108292: What are Stored Procedures and Triggers?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
14/09/2006 |
|
Status: Unverified
GOAL:
What are Stored Procedures and Triggers?
GOAL:
Definitions of Java Stored Procedures and Triggers
GOAL:
Advantages of Stored Procedures
FIX:
Definitions of Java Stored Procedures and Triggers
Stored procedures and triggers are Java routines that are executed by SQL-92 server processes. A trigger is a procedure that is invoked automatically when certain database events occur. A stored procedure is a procedure that is explicitly invoked by a client application, another stored procedure, or a trigger procedure.
A stored procedure is a snippet of Java code embedded in an CREATE PROCEDURE statement. The Java snippet can use all standard Java features as well as use Progress SQL-supplied Java classes for processing any number of SQL statements.
A trigger is a special type of stored procedure that helps to ensure referential integrity for a database. Like stored procedures, triggers also contain Java code embedded in a CREATE TRIGGER statement and use Progress SQL Java classes. However, triggers are automatically invoked (fired) by certain SQL operations: an INSERT, UPDATE, or DELETE operation on the trigger's target table.
Advantages of Stored Procedures
Stored procedures provide a very flexible, general mechanism to store a collection of SQL-92 statements and Java program constructs that enforce business rules and perform administrative tasks in a database. The ability to write stored procedures and triggers expands the flexibility and performance of applications that access the Progress SQL-92 environment.
The following is a list of additional advantages of utilizing stored procedures:
In a client server environment, client applications make a single request for the entire procedure, instead of one or more requests for each SQL statement in the stored procedure or trigger.
Stored procedures and triggers are stored in compiled form as well as in source text form, so execution is faster than a corresponding SQL script would be.
Stored procedures can implement elaborate algorithms to enforce complex business rules. The details of the procedure implementation can change without requiring changes in an application that calls the procedure.