![]() |
Special Cloudscape Programming
|
Reference Manual |
Programming Trigger ActionsCloudscape 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 OverviewA 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 . . .
CREATE TRIGGER . . . 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 ActionsOne 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 RowsMany trigger actions need to access the values of the rows being changed. Such trigger actions need to know one or both of the following:
Cloudscape provides a few different ways for a trigger action to access these values:
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.
ExamplesYou 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
-- Demonstrates how to use the TriggerExecutionContext
-- Demonstrates how to use the TriggerOldTransitionRows() 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 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 = 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 /* * 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 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(); }
CREATE TRIGGER AuditCities 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(); } }
CREATE TRIGGER hotelsAltered /* * 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 SQLExceptionAs 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.
CREATE TRIGGER alterCity /* * 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 ActionsExceptions 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 TransactionsYou 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 Transactionally Independent Trigger ActionsIt 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 ActionsTriggers 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. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |