Using Embedded Java Triggers

Like stored procedures, triggers written in SQL still achieve the maximum performance boost, but Java triggers support a richer programming language, third-party libraries and an easy migration path from client-side code to server-side extension.

Format

A Java method used as a trigger must have these features to be successfully bound:

Method Type

static

Return Type

void

Argument #1

java.sql.Connection

Argument #2

java.sql.ResultSet

Argument #3

java.sql.ResultSet

Argument #2 represents the "old" row before the trigger event and Argument #3 represents the "new" row after the trigger event. Argument #2 and Argument #3 can be NULL, depending on the type of trigger (BEFORE INSERT/AFTER DELETE/etc..) If they are not NULL, it is assumed they contain one single row. Argument #3 can be updated, while Argument #2 is read-only.

Example

Step 1 Create Java class

package com.mycompany;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class MyFirstJavaTrigger {
    public static void logInsert(Connection conn, ResultSet oldRow, ResultSet newRow) throws SQLException {
        String s = String.format(
                "** Inserting a=\"" + newRow.getString("A") + "\" b=" + newRow.getString("B") );

        System.out.println(s);

        PreparedStatement pstmt = conn.prepareStatement("insert into user.logtable values (?)");
        pstmt.setString(1, s);
        pstmt.execute();
    }
}

Step 2 Compile the File

Compile the file created in Step 1 and package it into a JAR archive.

$ javac com/mycompany/MyFirstJavaTrigger.java
$ jar cf myfirstjavatrigger.jar com/mycompany/MyFirstJavaTrigger.class

Step 3 Create Java class in NuoDB SQL

To load the Java class into memory, run the following command in nuosql (authenticated as a user with the DBA role), where myfirstjavaproc.jar is the JAR file compiled in Step 2.

SQL> CREATE JAVACLASS classid FROM 'path/to/myfirstjavaproc.jar';

For a full description of syntax and parameters, see CREATE JAVACLASS.

Step 4 Create tables mytable and logtable

SQL> CREATE TABLE mytable(a STRING, b INTEGER);
SQL> CREATE TABLE logtable(stmt STRING);

Step 5 Create a NuoDB SQL Trigger

SQL> CREATE TRIGGER tr1 FOR mytable ACTIVE AFTER INSERT
    LANGUAGE JAVA EXTERNAL 'classid:com.mycompany.MyFirstJavaTrigger.logInsert';

For a full description of syntax and parameters, see CREATE TRIGGER.

Step 5 Insert into mytable to fire the Trigger

SQL> INSERT INTO mytable VALUES ('abc',1);

** Inserting a="abc" b=1

SQL> ``SELECT * FROM logtable;``

           STMT
 ------------------------
 ** Inserting a="abc" b=1

Step 6 Unload the Java Class

When the jar file isn’t needed anymore, you can unload it by running the following NuoDB SQL command:

SQL> DROP JAVACLASS classid IF EXISTS;

See DROP JAVACLASS.

See Unsupported Methods in Embedded Java API for important information information about differences between the NuoDB JDBC driver API and the Embedded Java API used by Java stored procedures and triggers.

Use extreme caution when setting triggers on system tables, that is, NuoDB tables defined in the SYSTEM schema. In particular, be sure to drop the trigger (DROP TRIGGER) before dropping the JAVACLASS (DROP JAVACLASS).