Fire Off Your Next Solution With c-treeSQL Triggers

c-treeSQL is an advanced SQL engine encapsulating the high performance of the c-tree Server with industry-standard SQL. Offering wide cross-platform support and its remarkably small footprint, c-treeSQL is exceptionally easy to deploy and install in nearly any environment.

c-treeSQL also offers sophisticated stored procedures, triggers and user defined functions. Leveraging the power and ease of the portable Java language, c-treeSQL brings true write-once run-anywhere reality to your business logic.

Advantages of c-treeSQL Triggers

Stored procedures and triggers provide a flexible, general mechanism to store a collection of SQL statements and Java 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-treeSQL 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-treeSQL statement in the stored procedure or trigger.
• Stored procedures and triggers are stored in compiled form (as well as source-code form), so execute much faster than a corresponding c-treesSQL 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.

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

To demonstrate the power of c-treeSQL Triggers, a database archival and auditing example is presented below. Before we start, let's quickly review a few features and structures of c-treeSQL Triggers. Be sure to also review the c-treeSQL Stored Procedures and Triggers Guide for complete details.

The c-treeSQL Java Environment

To use c-treeSQL Triggers you will need to ensure you have a functioning Java installation on your c-treeSQL Server environment. FairCom recommends Java 1.5.0 or later. The following link describes this necessity in detail: Leverage the Power of Java and c-treeSQL.

Structure of a c-treeSQL Trigger

A c-treeSQL Trigger is composed of several elements:

A Database Archival and Audit Example

Consider the potential to archive every deleted record automatically from your database. In addition to archiving these records, it can be of great value to audit specific changes made to the database. With c-treeSQL Triggers this is easy! In this example we will demonstrate how quickly you can implement this functionality into your c-treeSQL database.

We will demonstrate this with an imaginary student information database, in this example, a simple table of students. We will use additional archive and audit tables to record the changes to the master table. c-treeSQL triggers implement the business logic of auditing and archiving changes to the student table.

Business Logic

Let's examine our business rules:

1. Archive any deleted record into the archive table.
2. Maintain a count of every addition to the student table.
3. Maintain a count of every update to a grade in the student table.

The Tables

Our student table is a simple table consisting of the student name, age and grade. Our archive table is a duplicate of this table schema. The following c-treeSQL CREATE statements will build these tables:

CREATE TABLE students
   (name VARCHAR(20), age INTEGER, grade INTEGER);

CREATE TABLE archive
   (name VARCHAR(20), age INTEGER, grade INTEGER);

The audit table has fields for the table name that was changed and the action count (add or update) maintaining an incremental count of the number of changes made. The following c-treeSQL CREATE statements will build this table:

CREATE TABLE audit
   (tbl CHAR(64), addcnt INTEGER, updatecnt INTEGER);

INSERT INTO audit VALUES ('students', 0, 0);

We have purposefully kept this example brief and you can be easily expand your options to log the user making the change, dates and times, etc. c-treeSQL triggers and stored procedures offer amazing power to your database challenges!

Archiving

To archive deleted records we will define the following c-treeSQL Trigger on the students table:

archive Trigger

CREATE TRIGGER archive

	BEFORE DELETE 

	ON students

	REFERENCING OLDROW

	FOR EACH ROW

IMPORT

import java.sql.*; 

BEGIN

try {

    SQLIStatement archive_stmt = new SQLIStatement
        ("INSERT INTO archive VALUES (?, ?, ?) ");

    archive_stmt.setParam
         (1, OLDROW.getValue(1,VARCHAR));

    archive_stmt.setParam
         (2, OLDROW.getValue(2,INTEGER));

    archive_stmt.setParam
         (3, OLDROW.getValue(3,INTEGER));

    archive_stmt.execute();

} catch (DhSQLException e) {

  String errmesg = 
      e.getDiagnostics(DhSQLException.MESSAGE_TEXT); 

  log ( "archive trigger encountered the following 
          exception: " + errmesg);

  throw e;

}

END

This trigger "fires" before any DELETE operation on the student table ensuring the deleted rows are securely archived. The REFERENCING OLDROW clause allows us to access the fields of the deleted record. The FOR EACH ROW allows us to repeat the archive operation for every deleted row, say from a DELETE operation that deleted a selected group of rows.

This trigger uses the c-treeSQL Java class SQLIStatement to build the immediate statement to execute. For this example, we used a parameterized statement, filling in the parameters from OLDROW.

Notice the use of the log() function. Should an error occur, we will log an event to the sql_server.log file which is maintained by the c-treeSQL Server.

Auditing

To maintain the audit table the following triggers are defined on the students table to increment the add and update counters in the audit table:

add_count Trigger

CREATE TRIGGER add_count

	AFTER INSERT 

	ON students

	FOR EACH ROW

IMPORT

import java.sql.*; 

BEGIN

try {

   SQLIStatement add_stmt = new SQLIStatement(
       "update audit set addcnt = addcnt + 1 
          where tbl='students' ");

   add_stmt.execute();

} catch (DhSQLException e) {

   throw e;

}

END

update_count Trigger

CREATE TRIGGER update_count

	AFTER UPDATE OF ("grade")

	ON students

	FOR EACH ROW

IMPORT

import java.sql.*; 

BEGIN

try {

   SQLIStatement update_stmt = new SQLIStatement(
     "update audit set updatecnt = updatecnt + 1
         where tbl='students' ");

   update_stmt.execute();

} catch (DhSQLException e) {

   throw e;

}

END

That's it! We can now capture any deletions from the database, and maintain an audit count of changes to the database; anytime a record is added the add count is incremented. Any changes to a grade will update the update count. Of course, this is a simplistic example. c-treeSQL triggers and stored procedures allow extremely complex business rules to be implemented for many other database purposes, including security auditing and relational integrity. The powerful full-featured Java language brings superb cross-platform ease of maintenance to all of your deployments. Take a look at the advantages c-treeSQL can bring to your application today! Download your copy Today!

The REFERENCING clause

The OLDROW and NEWROW objects provide a mechanism for c-treeSQL to pass row values as input parameters to the stored procedure in a trigger that executes once for each affected row. If the CREATE TRIGGER statement contains the REFERENCING clause, the c-treeSQL Server implicitly instantiates an OLDROW or NEWROW object (or both, depending on the arguments to the REFERENCING clause) when it creates the Java class.

This allows the Java code in the snippet to use the getValue() method of those objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables:

• The OLDROW object contains values of a row as it exists in the database before an update or delete operation. It is instantiated when triggers specify an UPDATE…REFERENCING OLDROW or DELETE…REFERENCING OLDROW clause. It is meaningless and not available for insert operations.
• The NEWROW object contains values of a row as specified in an INSERT or UPDATE statement. It is instantiated when triggers specify an UPDATE…REFERENCING NEWROW or INSERT…REFERENCING NEWROW clause. It is meaningless and not available for delete operations.

UPDATE is the only triggering statement that allows both NEWROW and OLDROW in the REFERENCING clause.

Debugging your Triggers

If there's a Java compilation error, the c-treeSQL Server returns the error at create time and does not create the procedure.

Note: If the compilation of the procedure fails, the c-treeSQL Server returns only the first error to the calling application.

At run time, the c-treeSQL Server creates a log file, sql_server.log. You can write custom messages to the log file with the common methods log() and err(), which write a character-string message to sql_server.log. You can include the values of variables or return values of methods in the string using the standard Java concatenation operator (+).

c-treeSQL Java Classes

c-treeSQL Triggers declare and use provided c-treeSQL Java classes for statement handling, cursors and errors. Here is a brief description of some of the most useful classes used in this example:

• SQLIStatement (String statement) - Methods of the SQLIStatement class provide for immediate (one-time) execution of c-treeSQL statements that do not generate a result set. Common methods called include:
  • SQLIStatement.execute() - Execute the c-treeSQL statement.
  • SQLIStatement.setParam() - Set the statement's input parameter.
  • SQLIStatement.rowCount() - Retrieve the number of affected rows by the c-treeSQL statement.
• SQLPStatement (String statement) - Methods of the SQLPStatement class provide for prepared (repeated) execution of c-treeSQL statements that do not generate a result set. Common methods include the same as for SQLIStatement.
• DhSQLException - The DhSQLException class extends the general java.lang.Exception class to provide detail about errors in c-treeSQL statement execution. Any such errors raise an exception with an argument that is an SQLException class object.
  • DhSQLException.getDiagnostics() - retrieves details of the error.
  • DhSQLException.MESSAGETXT - Actual text of the c-treeSQL Exception encountered.
• SQLCursor - Methods of the SQLCursor class retrieve rows of data from a database or another stored procedure's result set. Common methods of this class are:
  • SQLCursor.open() - Opens the result set specified by the SELECT or CALL statement.
  • SQLCursor.fetch() - Fetches the next record in a result set, if there is one.
  • SQLCursor.found() - Checks whether a fetch operation returned a record.

Java to SQL implicit conversions

When the c-treeSQL creates a trigger, it converts the type of any input and output parameters from the c-treeSQL data types to Java wrapper types.

The Java.lang package defines classes for all the primitive Java types that "wrap" values of the corresponding primitive type in an object. c-treeSQL converts the c-treeSQL data types declared for input and output parameters to one of these wrapper types.

You must be sure to use wrapper types when declaring procedure variables to use as arguments to the getValue(), setParam(), and set methods. These methods take objects as arguments and will generate compilation errors if you pass a primitive type to them.

Again, review the c-treeSQL Stored Procedures and Triggers Guide for complete details.

Advanced Debugging in c-treeSQL V8.27

Additional Java debug settings have been added in V8.27. An environment variable, DEBUG_JVM, has been introduced to allow c-treeSQL to interact with the standard Java debugging interface. A 'Y' enables the environment as a "debugging client" and an 'S' as a "debugging" server. As a server, this allows starting the c-treeSQL Server and then attaching the Java debugger. The following c-treeSQL Server configuration keyword will enable this behavior:

SETENV DEBUG_JVM Y

When DEBUG_JVM is specified, the stored procedure is compiled with debug information and the Java source file actually compiled by the server as well as the class file are not removed from the working directory.

c-treeSQL V8.27 is available to all FairCom customers in good standing on our maintenance program. Call FairCom today and ask for our latest version!

Related Items

Be sure to check out these related items to harness the full power of c-treeSQL and Java in your next application.

c-treeSQL User Defined Functions

JDBC and c-treeSQL

Leverage the Power of Java and c-treeSQL

Best of Both Worlds: Existing ISAM Applications and c-treeSQL

c-treeSQL Java Stored Procedures and Triggers Guide