January 1, 2008
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‑treeSQL 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‑treeACE Java & .NET 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 6 (1.6.0) or later. See the FairCom Knowledgebase for version information.
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:
- Archive any deleted record into the archive table.
- Maintain a count of every addition to the student table.
- 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 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. TheREFERENCING 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!
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‑treeACE Java & .NET Stored Procedures and Triggers Guide for complete details.
Advanced Debugging
Additional Java debug settings were 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.