15-9. Creating a Java Database Trigger
Problem
You want to create a database trigger that uses a Java stored procedure to do its work.
Solution
Create a Java stored procedure that does the work you require, and publish it as a Java stored procedure, making it accessible to PL/SQL. Once it's published, write a standard PL/SQL trigger that calls the Java stored procedure.
For example, suppose you need a trigger to audit INSERT events on the EMPLOYEES table and record them in another table. First, you must create the table that will be used to record each of the logged events. The following DDL creates one:
CREATE TABLE EMPLOYEE_AUDIT_LOG (
employee_id NUMBER,
enter_date DATE);
Next, you will need to code the Java stored procedure that you want to have executed each time an INSERT occurs on the EMPLOYEES table. Add the following Java method to the Employees class of previous recipes in this chapter:
public static void employeeAudit(int empId){
try {
Connection conn = DriverManager.
getConnection("jdbc:default:connection:");
String sql = "INSERT INTO EMPLOYEE_AUDIT_LOG VALUES(" +
"?, sysdate)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, empId);
pstmt.executeUpdate();
pstmt.close();
conn.commit();
} catch (SQLException ex){
System.err.println("ERROR: " + ex);
}
}
Next, the PL/SQL call specification for the Java stored procedure must be created. The following is the code to implement the call specification:
CREATE OR REPLACE PROCEDURE emp_audit(emp_id NUMBER)
AS LANGUAGE JAVA
NAME 'Employees.employeeAudit(int)';
Finally, a trigger to call the EMP_AUDIT procedure must be created. The trigger will be executed on INSERT to the EMPLOYEES table. The following code will generate the trigger to call EMP_AUDIT:
CREATE OR REPLACE TRIGGER emp_audit_ins
AFTER INSERT ON EMPLOYEES
FOR EACH ROW
CALL emp_audit(:new.employee_id);
Once all these pieces have been successfully created within the database, the EMP_AUDIT_INS trigger will be executed each time there is an INSERT made to the EMPLOYEES table. In turn, the trigger will call the EMP_AUDIT PL/SQL procedure, which calls the Java method contained within the Employees class. The SQL*Plus output shown here demonstrates an INSERT into the EMPLOYEES table, followed by a query on the EMPLOYEE_AUDIT_LOG table to show that the trigger has been invoked:
SQL> insert into employees values(
employees_seq.nextval,
'Jane',
'Doe',
'[email protected]',
null,
sysdate,
'FI_MGR',
null,
null,
null,
null);
1 row created.
SQL> select * from employee_audit_log;
EMPLOYEE_ID ENTER_DAT
----------- ---------
265 02-NOV-10
How It Works
A Java-based trigger combines the power of Java code with the native ease of performing data manipulation using PL/SQL triggers. Although creating a Java trigger requires more steps than using native PL/SQL, the Java code is portable. If your application is supported on more than one database platform, this lets you write code once and deploy it in many environments. It also makes sense to code a trigger using Java if you require the use of Java libraries or technologies that are unavailable to PL/SQL.
In the solution to this recipe, a trigger was created that will insert a row into an audit table each time an INSERT is made on the EMPLOYEES table. The actual work is performed within a Java method that is added to a Java class and loaded into the database. For more information on loading Java into the database, please see Recipe 15-2. To invoke the stored Java method, you must create a PL/SQL call specification, which maps the Java method to a PL/SQL stored procedure. The call specification can accept zero, one, or many parameters, and it will seamlessly pass the parameters to the underlying Java method. The final step to creating a Java trigger is to code a PL/SQL trigger that invokes the PL/SQL stored procedure that was created.
Creating a Java-based trigger entails a series of steps. Each piece of code depends upon the others, and like a domino effect, the trigger will call the procedure that in turn executes the Java method. This solution opens the world of Java libraries and thousands of possibilities to the standard PL/SQL trigger.