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

Possible Changes

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