Special Cloudscape Programming
Page 6 of 7

Programming Trigger Actions

Cloudscape allows you to create triggers. When you create a trigger, you define a an action or set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger action, is executed whenever someone deletes a row or rows from the table.

The CREATE TRIGGER statement in the Cloudscape Reference Manual goes into detail of the complete CREATE TRIGGER syntax. This section provides information on only one aspect of creating triggers, defining the trigger action itself, which is the very last part of the CREATE TRIGGER statement.

The statement can call complex Java methods. This section refers to the statement and all work performed by methods it calls as the trigger actions.

Trigger Action Overview

A trigger action can be a simple SQL-J statement, or it can invoke a Java method to perform more complex actions. For example:

CREATE TRIGGER . . .
DELETE FROM HotelAvailability
WHERE hotel_id IN (SELECT hotel_id FROM OLD)

CREATE TRIGGER . . .
CALL (CLASS JBMSTours.triggers.TriggerActions).deleteOrRedoHotelBooking(
    getCurrentConnection(), CURRENT_JDATE())

A trigger action does have some limitations, though; for example, it cannot contain dynamic parameters or alter the table on which the trigger is defined. See TriggerAction in the Cloudscape Reference Manual for details.

Performing Referential Actions

One of the most common uses of a trigger is to perform a referential action. For example, you may want to create a trigger that performs a cascading delete. If Table A's primary key columns are referenced by Table B's foreign key columns, then you would not be able to delete rows from Table A if any rows in Table B depend on A. With a cascading delete, you could configure the system to delete the related rows in Table B that depend on Table A before completing the delete of the primary key rows in Table A.

Although cascading deletes are not built in to Cloudscape, you can implement this functionality yourself through triggers. Most of the examples in this section perform cascading deletes.

Accessing Before and After Rows

Many trigger actions need to access the values of the rows being changed. Such trigger actions need to know one or both of the following:

  • the "before" values of the rows being changed (their values before the database event that caused the trigger to fire)
  • the "after" values of the rows being changed (the values to which the database event is setting them)

Cloudscape provides a few different ways for a trigger action to access these values:

  • Transition variables and transition tables.

    See Referencing Old and New Values: The Referencing Clause in the Cloudscape Reference Manual.

  • Transition Rows VTIs.

    Usable from both SQL and Java methods called by the trigger actions. The VTI classes COM.cloudscape.triggers.TriggerOldTransitionRows and COM.cloudscape.triggers.TriggerNewTransitionRows return a before or after image of all the rows being changed by the currently executing trigger.

    Cloudscape provides built-in aliases for these classes, TriggerOldTransitionRows and TriggerNewTransitionRows.

NOTE: For statement triggers, these VTIs correspond to the OLD and NEW transition tables. For row triggers, these VTIs contain only a single row and correspond to the OLD and NEW transition variables.

  • An instance of COM.cloudscape.database.TriggerExecutionContext.

    Typically used by methods executed by the trigger action.You retrieve an instance of this class by calling the method COM.cloudscape.database.Factory.getTriggerExecutionContext() from within your trigger action. (Cloudscape provides a built-in alias for Factory.) (Also usable from SQL.)

    This object provides varied information about the trigger, not just the before and after image of a row. For information, see the javadoc for COM.cloudscape.database.TriggerExecutionContext.

    To see the before or after image of the particular row being altered, call the getOldRow() and getNewRow() or getOldRowSet() and getNewRowSet() methods. The xxxRow() methods return a single-row ResultSet in position (you do not have to call a next() on them).

Examples

You will find source code for all Java methods referred to in the following examples in JBMSTours.triggers.TriggerActions.

The following three trigger actions, though coded differently, all accomplish the same thing; when rows are deleted from the Cities table, they delete related rows in the Hotels table. The first trigger uses the OLD transition variable, which is the easiest way for the trigger to find out which rows it needs to delete from the Hotels table. The second trigger shows how to accomplish the same thing with the TriggerExecutionContext; the third trigger shows how to accomplish the same thing with the TriggerOldTransitionRows. Normally, however, you would not need to use those constructs unless you were unable to use the transition tables or transition variables, which is the case from within a Java method. (Later examples in this section demonstrate that.)

-- before deleting a city, delete related hotels
-- row trigger using transition variable
CREATE TRIGGER DeleteRelatedHotels1
BEFORE DELETE
ON Cities
REFERENCING OLD ROW AS DeletedCities
FOR EACH ROW
DELETE FROM Hotels
    WHERE city_id = DeletedCities.city_id

-- Demonstrates how to use the TriggerExecutionContext
-- (but it's easier to use the transition table,
-- as in the previous example)
-- before deleting a city, delete related hotels
CREATE TRIGGER DeleteRelatedHotels2
BEFORE DELETE
ON Cities
FOR EACH ROW
DELETE FROM Hotels
    WHERE city_id = Factory.getTriggerExecutionContext().
    getOldRow().getString('city_id')

-- Demonstrates how to use the TriggerOldTransitionRows()
-- (but it's easier to use the transition table,
-- as in the previous example)
-- statement trigger referring to the old rows VTI
-- before deleting a city, delete related hotels
CREATE TRIGGER DeleteRelatedHotels3
BEFORE DELETE
ON Cities
FOR EACH STATEMENT
DELETE FROM Hotels WHERE city_id IN
    (SELECT city_id FROM NEW TriggerOldTransitionRows()
    AS Old_Cities)

For more complex logic, you can call a Java method.

Here is an example of a trigger action that calls a Java method that accesses the TriggerExecutionContext.

CREATE TRIGGER DeleteRelatedHotels4
BEFORE DELETE
ON Cities
FOR EACH STATEMENT
    CALL (CLASS JBMSTours.triggers.TriggerActions).deleteRelatedHotels(
        getCurrentConnection())

And the source code for the method:

/* 
* Demonstrate using the TriggerExecutionContext to reference
* the old values for the row being changed.
*/ 
public static void deleteRelatedHotels(Connection conn) 
    throws Throwable 
{ 
    TriggerExecutionContext context =
Factory.getTriggerExecutionContext();
PreparedStatement ps = conn.prepareStatement( "DELETE FROM hotels WHERE city_id = ?"); /* ** Here we iterate through all the rows that have ** been deleted. ** We obtain the deleted rows from the trigger ** execution context. */ ResultSet oldRows = context.getOldRowSet(); while (oldRows.next()) { ps.setString(1, oldRows.getString("city_id")); ps.executeUpdate(); } oldRows.close(); ps.close(); }

Here's another example of calling a Java method in a trigger. This one refers to the TriggerOldTransitionRows VTI from within an SQL-J statement.

CREATE TRIGGER DeleteRelatedHotels5
BEFORE DELETE
ON Cities
FOR EACH STATEMENT
    CALL (CLASS JBMSTours.triggers.TriggerActions).deleteRelatedHotels2(
        getCurrentConnection());

/* 
 * Use the TriggerOldTransitionRows VTI to do a cascading delete
 * in one fell swoop.<p>
 */ 
public static void deleteRelatedHotels2(Connection conn) 
    throws Throwable 
{
    PreparedStatement ps = conn.prepareStatement(
        "DELETE FROM hotels WHERE city_id IN "+ 
        "(SELECT city_id FROM NEW TriggerOldTransitionRows() " +
        "AS OLDROWS)");
    ps.executeUpdate();
    ps.close();
}

This example calls a Java method that access the TriggerNewTransitionRowsVTI directly instead of from within an SQL-J statement.

CREATE TRIGGER ShowNewBookings
AFTER INSERT
ON HotelBookings
FOR EACH STATEMENT CALL (CLASS JBMSTours.triggers.TriggerActions).showNewBookings(
    getCurrentConnection())

And the source of the method being called:

/* 
 * Use the TriggerNewTransitionRowsVTI from within a method call,
 * not directly in the trigger statement. Use it to
 * do a system out of rows inserted into the HotelBookings table.
 */ 
public static void showNewBookings(Connection conn) 
    throws SQLException 
{
    Statement s = conn.createStatement();
    Util.println("New hotel bookings:");
    ResultSet rs = s.executeQuery(
        "SELECT * FROM new TriggerNewTransitionRows() AS EQ");
    while (rs.next()) {
        Util.println(rs.getInt(1) + " " + 
             rs.getDate(3) + " " + rs.getDate(4));
    }
    rs.close();
    s.close();
}

Here's another example:

CREATE TRIGGER AuditCities
BEFORE DELETE
ON Cities
FOR EACH STATEMENT
    CALL (CLASS JBMSTours.triggers.TriggerActions).auditCities(
        getCurrentConnection(), CURRENT_USER)

And the source of the method being called:

/* 
 * Get a connection to the History database,
 * which we are using as kind of an audit database, 
 * insert a row for each city that is deleted.
 * Also, send email for each deleted city. <p>
 */ 
public static void auditCities(Connection conn1, String user) 
    throws Throwable 
{ 
    Connection conn2=null; 
    try 
    {
        // the statement querying the special vti must
        // be against the original connection
        Statement s = conn1.createStatement();
        ResultSet deletedRows = s.executeQuery(
            "SELECT city FROM NEW TriggerOldTransitionRows() " +
        "AS oldCities");
        // make a new connection to the History database 
        // for storing the audit information
        conn2 = DriverManager.getConnection("jdbc:cloudscape:History");
        PreparedStatement ps = conn2.prepareStatement(
            "INSERT INTO deletedCities VALUES (?, ?)"); 
        ps.setString(1, user);
        while (deletedRows.next()) 
        {
            City city = (City)deletedRows.getObject("CITY"); 
            sendMsg("deleted city "+  city.getName()); 
            ps.setObject(2, city); 
            ps.execute(); 
        }
        deletedRows.close();
        ps.close();
        s.close();
    } 
    finally 
    { 
        if (conn2 != null) 
            conn2.close(); 
    } 
} 

Another example:

CREATE TRIGGER hotelsAltered
AFTER UPDATE OF normal_rate, high_season_rate
ON Hotels
FOR EACH STATEMENT
    CALL (CLASS JBMSTours.triggers.TriggerActions).hotelChanged(
    getCurrentConnection())

/* 
 * Demonstrate doing a join with a triggers transition tables,
 * send mail based on result
 */ 
public static void hotelChanged(Connection conn) throws Throwable 
    
{
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery(
        "SELECT NewHotels.hotel_name, OldHotels.normal_rate, " +
        "OldHotels.high_season_rate, "+
        "NewHotels.normal_rate, NewHotels.high_season_rate " +
        "FROM NEW TriggerNewTransitionRows() AS NewHotels, " +
        "NEW TriggerOldTransitionRows() AS OldHotels " +
        "WHERE NewHotels.hotel_id = OldHotels.hotel_id");
    while (rs.next()) {
        sendMsg(rs.getString(1) + "'s rates have changed: from $" +
            rs.getBigDecimal(2, 2) + " and $" +
            rs.getBigDecimal(3, 2) +
            " to $" + rs.getBigDecimal(4, 2) + " and $" +
             rs.getBigDecimal(5,2));
        }
    s.close();
}

Triggers and Exceptions

Example of User-Defined SQLException

As stated in User-Defined SQLExceptions, you can throw your own SQLExceptions from any database-side method, including methods called by triggers, for methods that perform some kind of integrity checking. The SQLException should be in the range of 38001-38999.

For example:

CREATE TRIGGER alterCity
BEFORE UPDATE
ON Cities
FOR EACH ROW
    CALL TriggerActions.cityChanged()

/* 
 * Demonstrate referencing the TriggerExecutionContext to find out
 * information other than before or after values. Also, throw
 * a user-defined SQLException to indicate an integrity violation
 */ 
public static void cityChanged() throws Throwable 
{ 
    TriggerExecutionContext context =
        Factory.getTriggerExecutionContext(); 
    // don't let anyone change the CITY_ID
    if (context.wasColumnModified("CITY_ID")) 
    { 
        // this exception will cause the update statement
        //to be rolled back 
        throw new SQLException(
            "changes to CITY_ID are prohibited", "38001")
    } 
}

Exceptions in Triggers Actions

Exceptions raised by triggers have a statement severity; that is, they roll back the statement that caused the trigger to fire. For example: Suppose that you have defined a row trigger on an update statement that does some work within a Java method. Suppose that an update statement successfully updates 10 rows but an exception is raised when it updates the 11th row. The entire update statement and any work done by the trigger using the current connection are rolled back. However, any work done within that transaction prior to the update statement is not rolled back.

The same rules apply for nested triggers (triggers that are fired by other triggers). If a trigger action raises an exception (and it is not caught), the transaction on the current connection is rolled back to the point before the triggering event. For example, suppose Trigger A causes Trigger B to fire. If Trigger B throws an exception, the current connection is rolled back to the point before to the statement in Trigger A that caused Trigger B to fire. Trigger A is then free to catch the exception thrown by Trigger B and continue with its work. If Trigger A does not throw an exception, the statement that caused Trigger A as well as any work done in Trigger A continue until the transaction in the current connection is either committed or rolled back. However, if Trigger A does not catch the exception from Trigger B, it is as if Trigger A had thrown the exception. In that case, the statement that caused Trigger A to fire is rolled back, along with any work done by both of the triggers.

Aborting Statements and Transactions

You may want a trigger action to be able to abort the triggering statement or even the entire transaction. Triggers that use the current connection are not permitted to commit or roll back the connection, so how do you do that? The answer is: have the trigger throw an exception, which is by default a statement-level exception (which rolls back the statement). The application-side code that contains the statement that caused the trigger to fire can then roll back the entire connection if desired. Programming triggers in this respect is no different from programming any database-side JDBC method.

For example, imagine a program that contains a statement that causes the trigger shown in the section Example of User-Defined SQLException. This trigger sometimes throws an Exception. Here is some sample application code that contains a statement that may cause the trigger to fire:

conn.commit();
Statement s = conn.createStatement();
try {
    s.executeUpdate(userStatementString);
}
catch (SQLException e) {
// the statement will automatically be aborted since an exception
// was thrown.
// Roll back the whole transaction if desired. For example: // conn.rollback(); }

Transactionally Independent Trigger Actions

It is possible for a trigger action to use a different connection than the one used by the statement that caused it to fire. Any work performed in a different connection is potentially transactionally independent of work done in the original connection, because you can commit or roll back work on the new connection before completing the execution of the trigger. Working with a separate connection/transaction is sometimes useful. For example, you may want to audit and record information about attempts to update a table. Working in a separate transaction allows you to record the attempt to update the table even if the update statement itself is rolled back.

NOTE: Opening a new connection to the same database can cause locking conflicts.

In most cases, your triggers work in the same connection (and transaction) as the statement that caused them to fire.

Long-Running Trigger Actions

Triggers are executed synchronously; long-running trigger actions can adversely affect performance. If you need trigger actions to perform many tasks and are worried about performance, have the Java method called by the trigger start a new thread to do its work.

NOTE: A method can access the TriggerExecutionContext only while the trigger is active. Once the event that caused the trigger to fire completes, the TriggerExecutionContext is invalidated.