1. Java開発者ガイド
  2. ストアド・プロシージャのコール
  3. データベース・トリガーからのJavaのコール

7.2 データベース・トリガーからのJavaのコール

データベース・トリガーは、特定の表またはビューに関連付けられたストアド・プログラムです。Oracle Databaseでは、データ操作言語(DML)操作によって表またはビューが影響されると、トリガーが自動的に実行されます。

トリガー・イベントが発生すると、トリガーが実行され、PL/SQLブロックまたはCALL文によってアクションが実行されます。トリガー・イベントの前または後に、文トリガーが1回実行されます。トリガー・イベントの影響を受けた各行に対して、行トリガーが1回実行されます。

データベース・トリガー内では、相関名newおよびoldを使用して、変化する行の新旧の値を参照できます。トリガー・アクション・ブロックまたはCALL文では、列名に:newまたは:oldを接頭辞として付ける必要があります。

次に、データベース・トリガーからのJavaストアド・プロシージャのコールの例を示します。

例7-3 データベース・トリガーからのJavaストアド・プロシージャのコール - I

次のJavaクラスを使用して、一定以上の給与増加を記録するデータベース・トリガーを作成するとします。

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

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());
    }
  }
}

DBTriggerクラスには、sal_audit表に行を挿入するlogSal()メソッドが1つあります。logSal()はvoidメソッドであるため、次のようにプロシージャとして公開する必要があります。 

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

次に、sal_audit表を次のように作成します。 

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

最後に、給与の増加が20パーセントを超えたときに起動するデータベース・トリガーを作成します。

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

次のUPDATE文を実行すると、employees表のすべての行が更新されます。 

SQL> UPDATE employee SET salary = salary + 300;

トリガーのWHEN句の条件セットを満たす各行に対してトリガーが実行され、Javaメソッドがsal_audit表に行を挿入します。 

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.

例7-4 データベース・トリガーからのJavaストアド・プロシージャのコール - II

次のように定義されたデータベース・ビューに行を挿入するトリガーを作成するとします。

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

データベース表salesおよびmktgは次のように定義されています。 

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

UNION ALLなどの集合演算子を使用するビューには行を挿入できないため、INSTEAD OFトリガーを作成する必要があります。このトリガーを作成しないと、複数の行が実表に挿入されます。

最初に、次のJavaメソッドを例7-3で定義したDBTriggerクラスに追加します。 

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());
  }
}

addEmp()メソッドは、deptNameパラメータの値に応じて、sales表またはmktg表に行を挿入します。このメソッドのコール仕様は、次のように作成します。 

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)';

次に、INSTEAD OFトリガーを次のように作成します。 

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

次の各INSERT文を実行すると、トリガーが実行され、Javaメソッドによって、行が適切な実表に挿入されます。 

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