Differences between revisions 8 and 9
Revision 8 as of 2006-05-02 22:26:38
Size: 5536
Editor: DanDebrunner
Comment:
Revision 9 as of 2009-09-20 22:11:47
Size: 5538
Editor: localhost
Comment: converted to 1.6 markup
Deletions are marked like this. Additions are marked like this.
Line 80: Line 80:
 * [http://issues.apache.org/jira/browse/DERBY-1258 DERBY-1258] Since accessing columns is through JDBC for row triggers, columns with identical upper-cased names are not handled correctly. Fixed by using column position to fetch row's column value.  * [[http://issues.apache.org/jira/browse/DERBY-1258|DERBY-1258]] Since accessing columns is through JDBC for row triggers, columns with identical upper-cased names are not handled correctly. Fixed by using column position to fetch row's column value.

Triggers

Action Statement Rewrite

Re-write to Java expressions

The action statement of a trigger is re-written if it contains references to the old or new rows or columns. This occurs at CREATE TRIGGER time and is in the class org.apache.derby.impl.sql.compile.CreateTriggerNode.

For row triggers each column is re-written to use Derby's internal support for Java expressions in SQL.

old.x  -->> cast (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject('X') AS <type> )
new.x  -->> cast (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject('X') AS <type> )

For 10.2 onwards (svn revision 397959, DERBY-1258), value fetched by column position.

old.x  -->> CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(<n>) AS <type> )
new.x  -->> CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(<n>) AS <type> )

For statement triggers the reference to the new or old table is re-written to use Derby's internal support for Virtual Tables. TriggerOldTransitionRows and TriggerNewTransitionRows classes implement java.sql.ResultSet and thus can be used anywhere a table expression can.

old_table  -->> new org.apache.derby.catalog.TriggerOldTransitionRows()
new_table  -->> new org.apache.derby.catalog.TriggerNewTransitionRows() 

These classes provide access to the new or old set of rows.

Examples

Row trigger accessing one column in the action statement:

delete from parent where a = OLD.a

Re-write

delete from parent where a = cast (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject('A') AS INTEGER)


Row trigger accessing one old and one new column in the action statement:

insert into removed values (newrow.x + oldrow.x)

Re-write

insert into removed values (cast (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject('X') AS INTEGER)  + cast (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject('X') AS INTEGER) )


Statement trigger selecting three columns from the new set of rows being inserted or updated.

insert into y select x, y, z from newtab

Re-write

insert into y select x, y, z from new org.apache.derby.catalog.TriggerNewTransitionRows() NEWTAB

JDBC ResultSet implementations

For the row triggers the getNewRow and getOldRow return a JDBC ResultSet that wraps a Derby language ResultSet. This JDBC ResultSet is obtained from the embedded driver using the ConnectionContext.!getResultSet method. This code is in InternalTriggerExecutionContext.!getOldRowSet and !getNewRowSet.

For the statement triggers the VTI JDBC ResultSet is an instance of TriggerOldTransitionRows or TriggerNewTransitionRows which are JDBC 1.2 ResultSets that wrap the JDBC ResultSet obtained as for row triggers. So the complete wrapping is:

TriggerOldTransitionRows > EmbedResultSet* > Language ResultSet

History

These classes (TriggerOldTransitionRows, TriggerNewTransitionRows, and TriggerExecutionContext) used to be part of the public api for Cloudscape 5.x and earlier releases. These were removed as part of the public api when Derby was open-sourced as they are non-standard and the column and rows values can be correctly accessed using standard SQL.

Issues

  • The VTI classes (TriggerOldTransitionRows and TriggerNewTransitionRows) implement java.sql.ResultSet directly and wrap a ResultSet obtained from the embedded JDBC driver. This has performance and functionality impact. The TriggerOldTransitionRows and TriggerNewTransitionRows classes must be updated to match each JDBC release, currently they are stuck at JDBC 1.2. Changing them to implement java.sql.PreparedStatement would mean they could instead return the wrapped ResultSet directly. For example getBlob is not currently supported for statement triggers.

  • Any access to any column in a row trigger requires going through getObject, thus creating Objects for every value.
  • Accessing multiple columns leads to multiple accesses getNewRow() and/or getOldRow() methods.
  • DERBY-1258 Since accessing columns is through JDBC for row triggers, columns with identical upper-cased names are not handled correctly. Fixed by using column position to fetch row's column value.

Possible Changes

  • Re-write triggers to not add the indirection through JDBC ResultSet. Have old/new references in action statements compile to specific QueryTree nodes that access the data directly from a Derby language ResultSet. Would need to investigate how to handle existing triggers at upgrade time.

  • Continue with JDBC approach and add code as required to fix issues, e.g. a internal specific !findColumn implementation that doesn't match JDBC but instead matches column on absolute name. Might still require a wrapper JDBC object.
  • Change (TriggerOldTransitionRows and TriggerNewTransitionRows) to implement java.sql.PreparedStatement and thus use the embedded ResultSet class directly instead of wrapping it. This allows CLOB/BLOB support with statement triggers. Change being investigated with 10.2 under DEBRY-438.

  • Use column position rather than column name to fetch a column value for a row trigger. (Change made)

TriggerImplementation (last edited 2009-09-20 22:11:47 by localhost)