Java Programming with Oracle SQLJ

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