Feedback
Did this article resolve your question/issue?

   

Article

What are Stored Procedures and Triggers?

« Go Back

Information

 
TitleWhat are Stored Procedures and Triggers?
URL NameP108292
Article Number000127732
EnvironmentProduct: Progress
Version: 9.1x
Product: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All supported platforms
Other: Java, SQL
Question/Problem Description
What are Stored Procedures and Triggers?
Definitions of Java Stored Procedures and Triggers
Advantages of Stored Procedures
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution

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.

Workaround
Notes

References to Written Documentation:

OpenEdge® Data Management: SQL Development : Stored Procedures and Triggers : Basics of Java stored procedures

P20617,  Sample Stored Procedures for SQL92 Engine

Last Modified Date12/13/2019 2:09 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.