db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "TriggerImplementation" by DanDebrunner
Date Wed, 26 Apr 2006 23:22:31 GMT
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.

Mime
View raw message