8.2 データベース・トリガーからのJavaのコール
データベース・トリガーは、特定の表またはビューに関連付けられたストアド・プログラムです。Oracle Databaseでは、データ操作言語(DML)操作によって表またはビューが影響されると、トリガーが自動的に実行されます。
トリガー・イベントが発生すると、トリガーが実行され、PL/SQLブロックまたはCALL
文によってアクションが実行されます。トリガー・イベントの前または後に、文トリガーが1回実行されます。トリガー・イベントの影響を受けた各行に対して、行トリガーが1回実行されます。
データベース・トリガー内では、相関名new
およびold
を使用して、変化する行の新旧の値を参照できます。トリガー・アクション・ブロックまたはCALL
文では、列名に:new
または:old
を接頭辞として付ける必要があります。
次に、データベース・トリガーからのJavaストアド・プロシージャのコールの例を示します。
例8-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.
例8-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メソッドを例8-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