Calling Java from Database Triggers

A database trigger is a stored program associated with a specific table or view. Oracle executes (fires) the trigger automatically whenever a given DML operation affects the table or view.

A trigger has three parts: a triggering event (DML operation), an optional trigger constraint, and a trigger action. When the event occurs, the trigger fires and either a PL/SQL block or a CALL statement performs the action. A statement trigger fires once, before or after the triggering event. A row trigger fires once for each row affected by the triggering event.

Within a database trigger, you can reference the new and old values of changing rows using the correlation names new and old. In the trigger-action block or CALL statement, column names must be prefixed with :new or :old.

To create a database trigger, you use the SQL CREATE TRIGGER statement. For the syntax of that statement, see the Oracle8i SQL Reference. For a full discussion of database triggers, see the Oracle8i Application Developer's Guide - Fundamentals.

Example 1

Suppose you want to create a database trigger that uses the following Java class to log out-of-range salary increases:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

public class DBTrigger {
  public static void logSal (int empID, float oldSal, float newSal)
  throws SQLException {
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "INSERT INTO sal_audit VALUES (?, ?, ?)";
    try {
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, empID);
      pstmt.setFloat(2, oldSal);
      pstmt.setFloat(3, newSal);
      pstmt.executeUpdate(); 
      pstmt.close();
    } catch (SQLException e) {System.err.println(e.getMessage());}
  }
}

The class DBTrigger has one method, which inserts a row into the database table sal_audit. Because logSal is a void method, you publish it as a procedure:

CREATE OR REPLACE PROCEDURE log_sal (
  emp_id NUMBER, old_sal NUMBER, new_sal NUMBER)
AS LANGUAGE JAVA
NAME 'DBTrigger.logSal(int, float, float)';

Next, you create the database table sal_audit, as follows:

CREATE TABLE sal_audit (
  empno  NUMBER, 
  oldsal NUMBER, 
  newsal NUMBER);

Finally, you create the database trigger, which fires when a salary increase exceeds twenty percent:

CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal > 1.2 * old.sal)
CALL log_sal(:new.empno, :old.sal, :new.sal);

When you execute the UPDATE statement below, it updates all rows in the table emp. For each row that meets the trigger's WHEN clause condition, the trigger fires and the Java method inserts a row into the table sal_audit.

SQL> UPDATE emp SET sal = sal + 300;

SQL> SELECT * FROM sal_audit;

     EMPNO     OLDSAL     NEWSAL
---------- ---------- ----------
      7369        800       1100
      7521       1250       1550
      7654       1250       1550
      7876       1100       1400
      7900        950       1250
      7934       1300       1600

6 rows selected.

Example 2

Suppose you want to create a trigger that inserts rows into a database view defined as follows:

CREATE VIEW emps AS
  SELECT empno, ename, 'Sales' AS dname FROM sales
  UNION ALL
  SELECT empno, ename, 'Marketing' AS dname FROM mktg;

where the database tables sales and mktg are defined as:

CREATE TABLE sales (empno NUMBER(4), ename VARCHAR2(10));
CREATE TABLE mktg (empno NUMBER(4), ename VARCHAR2(10));

You must write an INSTEAD OF trigger because rows cannot be inserted into a view that uses set operators such as UNION ALL. Instead, your trigger will insert rows into the base tables.

First, you add the following Java method to the class DBTrigger (defined in the previous example):

public static void addEmp (
  int empNo, String empName, String deptName)
throws SQLException {
  Connection conn = new OracleDriver().defaultConnection();
  String tabName = (deptName.equals("Sales") ? "sales" : "mktg");
  String sql = "INSERT INTO " + tabName + " VALUES (?, ?)";
  try {
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, empNo);
    pstmt.setString(2, empName);
    pstmt.executeUpdate(); 
    pstmt.close();
  } catch (SQLException e) {System.err.println(e.getMessage());}
}

The method addEmp inserts a row into the table sales or mktg depending on the value of the parameter deptName. You write the call spec for this method as follows:

CREATE OR REPLACE PROCEDURE add_emp (
  emp_no NUMBER, emp_name VARCHAR2, dept_name VARCHAR2)
AS LANGUAGE JAVA 
NAME 'DBTrigger.addEmp(int, java.lang.String, java.lang.String)';

Then, you create the INSTEAD OF trigger:

CREATE OR REPLACE TRIGGER emps_trig 
INSTEAD OF INSERT ON emps
FOR EACH ROW
CALL add_emp(:new.empno, :new.ename, :new.dname);

When you execute each of the following INSERT statements, the trigger fires and the Java method inserts a row into the appropriate base table:

SQL> INSERT INTO emps VALUES (8001, 'Chand', 'Sales');
SQL> INSERT INTO emps VALUES (8002, 'Van Horn', 'Sales');
SQL> INSERT INTO emps VALUES (8003, 'Waters', 'Sales');
SQL> INSERT INTO emps VALUES (8004, 'Bellock', 'Marketing');
SQL> INSERT INTO emps VALUES (8005, 'Perez', 'Marketing');
SQL> INSERT INTO emps VALUES (8006, 'Foucault', 'Marketing');

SQL> SELECT * FROM sales;

     EMPNO ENAME
---------- ----------
      8001 Chand
      8002 Van Horn
      8003 Waters

SQL> SELECT * FROM mktg;

     EMPNO ENAME
---------- ----------
      8004 Bellock
      8005 Perez
      8006 Foucault

SQL> SELECT * FROM emps;

     EMPNO ENAME      DNAME
---------- ---------- ---------
      8001 Chand      Sales
      8002 Van Horn   Sales
      8003 Waters     Sales
      8004 Bellock    Marketing
      8005 Perez      Marketing
      8006 Foucault   Marketing

---