Stored Procedures, Triggers

c-treeACE SQL includes support for Java stored procedures, triggers and user defined functions. Stored procedures and triggers and user defined functions provide the ability to write Java routines that contain SQL statements and store those routines within a database.

Java Stored Procedures

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

Stored procedures are compiled and stored in bytecode format for direct execution without recompilation and parsing. Java stored procedures can return result sets to calling applications or procedures.

Java Triggers

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

Java User Defined Functions

Scalar functions are an integral part of the support provided by c-treeACE SQL for query expression. c-treeACE SQL provides many built-in scalar functions that transform data in different ways. Sometimes, however, there is a need for a custom-transformation of the data - a transformation that is not available with any of the provided functions. This is solved by user-defined scalar functions (UDFs) - a scalar function that is defined by the user. c-treeACE SQL UDFs allow the user to define their own functions to transform data in some custom manner.

The user defines functions by creating Java Stored Functions, modules written in Java that are similar to the ones written for stored procedures and triggers. The java code snippet contained in the User Defined Scalar Function definition is processed by c-treeACE SQL into a Java class definition and stored in the database in text and compiled form.