Java & .NET Stored Procedures Guide

Next Topic

Introduction to Stored Procedures, Triggers, and User Defined Functions

Stored procedures and triggers provide the ability to write Java and .NET routines that contain SQL statements and store those routines within a c-treeACE SQL database. Tools and applications can then execute the procedures.

A stored procedure is a snippet of code embedded in an SQL CREATE PROCEDURE statement. The snippet can use all standard Java or .NET features as well as c-treeACE SQL supplied Java classes for processing any number of SQL statements.

A trigger is a special type of stored procedure that helps ensure referential integrity for a database. Like stored procedures, triggers also contain Java or .NET code (embedded in a CREATE TRIGGER statement) and classes. However, triggers are automatically invoked (“fired”) by certain SQL operations (an insert, update, or delete operation) on the trigger’s target table.

User defined functions (UDFs) extend the availability of built-in scalar functions with modules written by the user. The user can transform data with custom routines for their unique business needs. These procedures are created with the c-treeACE SQL CREATE FUNCTION statement.

Advantages of Stored Procedures

Stored procedures and triggers provide a flexible, general mechanism to store a collection of SQL statements and program constructs in a database enforcing business rules and performing administrative tasks.

The ability to write stored procedures and triggers expands the flexibility and performance of applications that access a c-treeACE SQL environment:

  • In a client/server environment, applications make only a single client/server request for the entire procedure, instead of one or more requests for each c-treeACE SQL statement in the stored procedure or trigger.
  • Stored procedures and triggers are stored in compiled form (as well as source-code form), so they execute much faster than a corresponding c-treeACE SQL script.
  • 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.