[Db-derby Wiki] Update of "TriggerImplementation" by DanDebrunner

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by DanDebrunner:
http://wiki.apache.org/db-derby/TriggerImplementation

New page:
= 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 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
}}}

=== 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 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.
 * (NEED TO VERIFY) Since accessing columns is through JDBC for row triggers, columns with identical upper-cased names are not handled correctly.