6.6 Using Database Triggers
A database trigger is a procedure that is run, or fired, when a certain action takes place on a database table. Triggers can fire in response to a SQL INSERT, UPDATE, or DELETE statement. They can fire either before or after the SQL statement. They can also fire once for each row affected by the SQL statement, or once for the entire statement. A trigger that fires once for each row affected by a SQL statement is known as a row trigger , and a trigger that fires once for the statement as a whole is known as a statement trigger. Triggers may have an optional constraint that limits the circumstances under which the trigger runs. Triggers have a variety of uses; one common use is to record changes to rows in a table.
Assume you want to record any change to the price column of the products table that causes the price of a product to increase by 10% or more. You can do this using a trigger. Whenever a price change of 10% or more occurs for a product, your trigger can record the ID, the old price, and the new price of the product in an audit table. In the examples I show in this chapter, the audit table is named price_audit and is defined as follows in the fundamental_user schema:
CREATE TABLE price_audit ( product_id NUMBER CONSTRAINT price_audit_fk_products REFERENCES products(id), old_price NUMBER(*, 2), new_price NUMBER(*, 2) );
The following command creates a trigger named product_price_update_trigger that implements our audit logic. Whenever a price change of auditable magnitude occurs, the trigger adds a row to the price_audit table and writes a message to an output buffer, thus recording the change that was made. The :old and :new aliases that you see in the trigger code allow you to access old and new values for rows being changed by an UPDATE statement. You access the price before the update using :old.price, and the price after the update using :new.price.
CREATE OR REPLACE TRIGGER product_price_update_trigger BEFORE UPDATE OF price ON products FOR EACH ROW WHEN (new.price > old.price * 1.1) BEGIN dbms_output.put_line('For product id ' || :old.id); dbms_output.put_line('Old price = ' || :old.price); dbms_output.put_line('New price = ' || :new.price); dbms_output.put_line('The new price is 10% greater than the old price - auditing change'); -- insert row into the price_audit table INSERT INTO price_audit VALUES ( :old.id, :old.price, :new.price ); END; / Now that the trigger has been created, assume that the products table contains the following IDs and prices:
SQL> SELECT id, price FROM products; ID PRICE ---------- ---------- 1 19.95 2 30 3 25.99 4 13.95 5 49.99 6 14.95 7 13.49 8 12.99 9 10.99 10 15.99 11 14.99 12 13.49
Given this data, consider what happens if you update the price for both products 1 and 4 to 26.00. In both cases, that increase is greater than 10%, so the trigger fires for both rows:
SQL> UPDATE products SET price = 26 WHERE id IN (1, 4); For product id 4 Old price = 13.95 New price = 26 The new price is 10% greater than the old price - auditing change For product id 1 Old price = 19.95 New price = 26 The new price is 10% greater than the old price - auditing change
After making the update shown here, you can query the price_audit table and verify that the trigger has added two rows to record the two price changes:
SQL> SELECT * FROM price_audit; PRODUCT_ID OLD_PRICE NEW_PRICE ---------- ---------- ---------- 4 13.95 26 1 19.95 26
You can use SQLJ to replace PL/SQL code in database triggers. This section shows you how to rewrite the body of the trigger named product_price_update_trigger using SQLJ. The steps to do this are as follows:
-
Create a SQLJ program containing a method that performs the same basic operations as the PL/SQL body of the trigger.
-
Translate the SQLJ program into class files.
-
Load the class files into the database.
-
Publish the class method using a wrapper.
-
Recreate the trigger with a call to the wrapper.
I'll now discuss these steps in detail.
6.6.1 Step 1: Create a SQLJ program
The program ServerExample2.sqlj, shown in Example 6-2, implements the logic of the trigger named product_price_update_trigger using SQLJ instead of PL/SQL. The ServerExample2 class contains one method, named productPriceUpdate( ), that performs the same basic operations as the PL/SQL body of the trigger.
Example 6-2. ServerExample2.sqlj
/* The program ServerExample2.sqlj is designed to run in the Oracle JServer Java Virtual Machine. The program rewrites the body of the trigger product_price_update_trigger using SQLJ. */ import java.sql.*; public class ServerExample2 { public static void productPriceUpdate ( int product_id, double old_price, double new_price ) { try { #sql { CALL dbms_java.set_output(1000) }; System.out.println("For product id " + product_id); System.out.println("Old price = " + old_price); System.out.println("New price = " + new_price); System.out.println("The new price is 10% greater than the " + "old price - auditing change"); // insert row into the price_audit table #sql { INSERT INTO price_audit VALUES ( :product_id, :old_price, :new_price ) }; } catch (SQLException e) { System.out.println("SQLException " + e); System.exit(1); } } // end of productPriceUpdate( ) } 6.6.2 Step 2: Translate the SQLJ program into class files
Once you've written a SQLJ program to implement your trigger logic, your next step is to translate that program into one or more class files. The following command translates the ServerExample2.sqlj program using the sqlj command-line utility:
sqlj -ser2class ServerExample2.sqlj
This translation produces the following three class files:
Your next step is to load these class files into the database.
6.6.3 Step 3: Load the class files into the database
Use the loadjava command-line utility to load the class files generated in Step 2 into the database. The following command loads the three previously generated class files into the fundamental_user schema:
loadjava -user fundamental_user/fundamental_password -resolve ServerExample2.class ServerExample2_SJProfileKeys.class ServerExample2_SJProfile0.class
6.6.4 Step 4: Publish the class method using a wrapper
After loading your class files into the database, your next task is to publish the method that implements your trigger logic so the method is callable from PL/SQL. The following example creates a PL/SQL wrapper procedure named product_price_update( ) for the productPriceUpdate( ) method in the ServerExample2 class:
CREATE OR REPLACE PROCEDURE product_price_update( product_id NUMBER, old_price NUMBER, new_price NUMBER ) AS LANGUAGE JAVA NAME 'ServerExample2.productPriceUpdate(int, double, double)'; /
With the method published, you can easily invoke it from the PL/SQL body of a trigger.
6.6.5 Step 5: Recreate the trigger with a call to the wrapper
After publishing the method that implements your trigger logic, your final task is to create (or recreate) a trigger that invokes the wrapper procedure for your method. The following example recreates the trigger named product_price_update_trigger, replacing the PL/SQL body of the trigger with a call to the product_price_update( ) wrapper procedure:
CREATE OR REPLACE TRIGGER product_price_update_trigger BEFORE UPDATE OF price ON products FOR EACH ROW WHEN (new.price > old.price * 1.1) CALL product_price_update(:old.id, :old.price, :new.price); /
That's it! You have just implemented a database trigger using SQLJ.
