Oracle8i Java Stored Procedures Developer's Guide Release 2 (8.1.6) Part Number A81358-01 |
Library | Service | Contents | Index

## 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 =
          DriverManager.getConnection("jdbc:default:connection:");
        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 =
        DriverManager.getConnection("jdbc:default:connection:");
      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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index