Oracle and PL/SQL Recipes: A Problem-Solution Approach

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 ...