db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1044096 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ storeless/org/apache/derby/impl/storeless/ testing/org/apache/derbyTes...
Date Thu, 09 Dec 2010 19:19:43 GMT
Author: mamta
Date: Thu Dec  9 19:19:42 2010
New Revision: 1044096

URL: http://svn.apache.org/viewvc?rev=1044096&view=rev
Log:
DERBY-4874 Trigger does not recognize new size of VARCHAR column expanded with ALTER TABLE. It fails with ERROR 22001: A truncation error was encountered trying to shrink VARCHAR

The trigger action associated with a trigger gets converted as shown in the example below. This transformation happens if the trigger action has REFERENCEs clause.
	update xr.repositoryobjectversion set uname = upper( n.name )
	  where name = n.name and uname <> upper( n.name );
	turns into
	update xr.repositoryobjectversion set uname = 
	  upper( CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3) AS VARCHAR(128)) ) 
	  where name = CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3) AS VARCHAR(128))
As can be seen above, there is a CASTing involved which uses the length of the column in trigger table. 

If say that length is changed by ALTER TABLE after the trigger has been created, that change in the length does not get reflected in the sql associated with the trigger action in the form on SPSDescriptor. In order to fix this, I have made changes which willcause us to regenerate the sql from the trigger action for the SPSDescriptor if we are working with an invalidated row level trigger which uses the REFERENCEs clause.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=1044096&r1=1044095&r2=1044096&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Thu Dec  9 19:19:42 2010
@@ -35,6 +35,8 @@ import org.apache.derby.catalog.TypeDesc
 import org.apache.derby.catalog.UUID;
 import org.apache.derby.iapi.services.uuid.UUIDFactory;
 
+import org.apache.derby.impl.sql.compile.StatementNode;
+
 import java.sql.Types;
 import java.util.List;
 import java.util.Hashtable;
@@ -1162,6 +1164,133 @@ public interface DataDictionary
 				throws StandardException;
 
 	/**
+	 * This method does the job of transforming the trigger action plan text
+	 * as shown below. 
+	 * 	DELETE FROM t WHERE c = old.c
+	 * turns into
+	 *  DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
+	 *  	getTriggerExecutionContext().getOldRow().
+	 *      getInt(columnNumberFor'C'inRuntimeResultset);
+	 * In addition to that, for CREATE TRIGGER time, it does the job of 
+	 * collecting the column positions of columns referenced in trigger 
+	 * action plan through REFERENCEs clause. This information will get
+	 * saved in SYSTRIGGERS table by the caller in CREATE TRIGGER case.
+	 *  
+	 * It gets called either 
+	 * 1)at the trigger creation time for row level triggers or
+	 * 2)if the trigger got invalidated by some other sql earlier and the 
+	 * current sql needs that trigger to fire. For such a trigger firing 
+	 * case, this method will get called only if it is row level trigger 
+	 * with REFERENCES clause. This work was done as part of DERBY-4874. 
+	 * Before DERBY-4874, once the stored prepared statement for trigger 
+	 * action plan was generated, it was never updated ever again. But, 
+	 * one case where the trigger action plan needs to be regenerated is say
+	 * when the column length is changed for a column which is REFERENCEd as
+	 * old or new column value. eg of such a case would be say the Alter
+	 * table has changed the length of a varchar column from varchar(30) to
+	 * varchar(64) but the stored prepared statement associated with the 
+	 * trigger action plan continued to use varchar(30). To fix varchar(30) 
+	 * in stored prepared statement for trigger action sql to varchar(64), 
+	 * we need to regenerate the trigger action sql. This new trigger 
+	 * action sql will then get updated into SYSSTATEMENTS table.
+	 * 
+	 * If we are here for case 1) above, then we will collect all column 
+	 * references in trigger action through new/old transition variables. 
+	 * Information about them will be saved in SYSTRIGGERS table DERBY-1482
+	 * (if we are dealing with pre-10.7 db, then we will not put any 
+	 * information about trigger action columns in the system table to ensure 
+	 * backward compatibility). This information along with the trigger 
+	 * columns will decide what columns from the trigger table will be
+	 * fetched into memory during trigger execution.
+	 * 
+	 * If we are here for case 2) above, then all the information about 
+	 * column references in trigger action has already been collected during
+	 * CREATE TRIGGER time and hence we can use that available information 
+	 * about column positions to do the transformation of OLD/NEW transient 
+	 * references.
+	 * 
+	 * More information on case 1) above. 
+	 * DERBY-1482 One of the work done by this method for row level triggers
+	 * is to find the columns which are referenced in the trigger action 
+	 * through the REFERENCES clause ie thro old/new transition variables.
+	 * This information will be saved in SYSTRIGGERS so it can be retrieved
+	 * during the trigger execution time. The purpose of this is to recognize
+	 * what columns from the trigger table should be read in during trigger
+	 * execution. Before these code changes, during trigger execution, Derby
+	 * was opting to read all the columns from the trigger table even if they
+	 * were not all referenced during the trigger execution. This caused Derby
+	 * to run into OOM at times when it could really be avoided.
+	 *
+	 * We go through the trigger action text and collect the column positions
+	 * of all the REFERENCEd columns through new/old transition variables. We
+	 * keep that information in SYSTRIGGERS. At runtime, when the trigger is
+	 * fired, we will look at this information along with trigger columns from
+	 * the trigger definition and only fetch those columns into memory rather
+	 * than all the columns from the trigger table.
+	 * This is especially useful when the table has LOB columns and those
+	 * columns are not referenced in the trigger action and are not recognized
+	 * as trigger columns. For such cases, we can avoid reading large values of
+	 * LOB columns into memory and thus avoiding possibly running into OOM
+	 * errors.
+	 * 
+	 * If there are no trigger columns defined on the trigger, we will read all
+	 * the columns from the trigger table when the trigger fires because no
+	 * specific columns were identified as trigger column by the user. The
+	 * other case where we will opt to read all the columns are when trigger
+	 * columns and REFERENCING clause is identified for the trigger but there
+	 * is no trigger action column information in SYSTRIGGERS. This can happen
+	 * for triggers created prior to 10.7 release and later that database got
+	 * hard/soft-upgraded to 10.7 or higher release.
+	 *
+	 * @param actionStmt This is needed to get access to the various nodes
+	 * 	generated by the Parser for the trigger action sql. These nodes will be
+	 * 	used to find REFERENCEs column nodes.
+	 * 
+	 * @param oldReferencingName The name specified by the user for REFERENCEs
+	 * 	to old row columns
+	 * 
+	 * @param newReferencingName The name specified by the user for REFERENCEs
+	 * 	to new row columns
+	 * 
+	 * @param triggerDefinition The original trigger action text provided by
+	 * 	the user during CREATE TRIGGER time.
+	 * 
+	 * @param referencedCols Trigger is defined on these columns (will be null
+	 *   in case of INSERT AND DELETE Triggers. Can also be null for DELETE
+	 *   Triggers if UPDATE trigger is not defined on specific column(s))
+	 *   
+	 * @param referencedColsInTriggerAction	what columns does the trigger 
+	 * 	action reference through old/new transition variables (may be null)
+	 * 
+	 * @param actionOffset offset of start of action clause
+	 * 
+	 * @param triggerTableDescriptor Table descriptor for trigger table
+	 * 
+	 * @param triggerEventMask TriggerDescriptor.TRIGGER_EVENT_XXX
+	 * 
+	 * @param createTriggerTime True if here for CREATE TRIGGER,
+	 * 	false if here because an invalidated row level trigger with 
+	 *  REFERENCEd columns has been fired and hence trigger action
+	 *  sql associated with SPSDescriptor may be invalid too.
+	 * 
+	 * @return Transformed trigger action sql
+	 * @throws StandardException
+	 */
+	public String getTriggerActionString(
+			StatementNode actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int[] referencedColsInTriggerAction,
+			int actionOffset,
+			TableDescriptor triggerTableDescriptor,
+			int triggerEventMask,
+			boolean createTriggerTime)
+	throws StandardException;
+	
+
+	/**
 	 * Load up the trigger descriptor list for this table
 	 * descriptor and return it.  If the descriptor list
 	 * is already loaded up, it is retuned without further

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java?rev=1044096&r1=1044095&r2=1044096&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java Thu Dec  9 19:19:42 2010
@@ -122,7 +122,7 @@ public class SPSDescriptor extends Tuple
     private final String name;
     private final UUID compSchemaId;
     private final char type;
-    private final String text;
+    private String text;
     private final String usingText;
     private final UUID uuid;
 
@@ -530,6 +530,25 @@ public class SPSDescriptor extends Tuple
 	}
 
 	/**
+	 * It is possible that when a trigger is invalidated, the generated trigger
+	 * action sql associated with it needs to be regenerated. One example
+	 * of such a case would be when ALTER TABLE on the trigger table
+	 * changes the length of a column. The need for this code was found
+	 * as part of DERBY-4874 where the Alter table had changed the length 
+	 * of a varchar column from varchar(30) to varchar(64) but the generated 
+	 * trigger action plan continued to use varchar(30). To fix varchar(30) in
+	 * in trigger action sql to varchar(64), we need to regenerate the 
+	 * trigger action sql which is saved as stored prepared statement. This 
+	 * new trigger action sql will then get updated into SYSSTATEMENTS table.
+	 * DERBY-4874
+	 * 
+	 * @param newText
+	 */
+	public final void setText(String newText)
+	{
+		text = newText;
+	}
+	/**
 	 * Get the text of the USING clause used on CREATE
 	 * or ALTER statement.
 	 *

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=1044096&r1=1044095&r2=1044096&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java Thu Dec  9 19:19:42 2010
@@ -30,7 +30,6 @@ import java.sql.Timestamp;
 
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.services.sanity.SanityManager;
-import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.catalog.DependableFinder;
 import org.apache.derby.catalog.Dependable;
@@ -40,7 +39,9 @@ import org.apache.derby.iapi.sql.conn.La
 import org.apache.derby.iapi.store.access.TransactionController;
 import org.apache.derby.iapi.services.context.ContextService;
 
-import org.apache.derby.impl.sql.execute.DropTriggerConstantAction;
+import org.apache.derby.iapi.sql.compile.CompilerContext;
+import org.apache.derby.iapi.sql.compile.Parser;
+import org.apache.derby.impl.sql.compile.StatementNode;
 
 import java.io.ObjectOutput;
 import java.io.ObjectInput;
@@ -308,8 +309,15 @@ public class TriggerDescriptor extends T
 	}
 
 	/**
-	 * Get the trigger action sps
+	 * Get the trigger action sps from SYSSTATEMENTS. If we find that
+	 * the sps is invalid and the trigger is defined at row level and it
+	 * has OLD/NEW transient variables through REFERENCES clause, then
+	 * the sps from SYSSTATEMENTS may not be valid anymore. In such a 
+	 * case, we regenerate the trigger action sql and use that for the
+	 * sps and update SYSSTATEMENTS using this new sps. This update of
+	 * SYSSTATEMENTS was introduced with DERBY-4874
 	 *
+	 * @param lcc	The LanguageConnectionContext to use.
 	 * @return the trigger action sps
 	 *
 	 * @exception StandardException on error
@@ -328,6 +336,47 @@ public class TriggerDescriptor extends T
 			actionSPS = getDataDictionary().getSPSDescriptor(actionSPSId);
 			lcc.commitNestedTransaction();
 		}
+		
+		//We need to regenerate the trigger action sql if 
+		//1)the trigger is found to be invalid, 
+		//2)the trigger is defined at row level (that is the only kind of 
+		//  trigger which allows reference to individual columns from 
+		//  old/new row)
+		//3)the trigger action plan has columns that reference 
+		//  old/new row columns
+		//This code was added as part of DERBY-4874 where the Alter table 
+		//had changed the length of a varchar column from varchar(30) to 
+		//varchar(64) but the trigger action plan continued to use varchar(30).
+		//To fix varchar(30) in trigger action sql to varchar(64), we need
+		//to regenerate the trigger action sql. This new trigger action sql
+		//will then get updated into SYSSTATEMENTS table.
+		if((!actionSPS.isValid() ||
+				 (actionSPS.getPreparedStatement() == null)) && 
+				 isRow &&
+				 referencedColsInTriggerAction != null) 
+		{
+			SchemaDescriptor compSchema;
+			compSchema = getDataDictionary().getSchemaDescriptor(triggerSchemaId, null);
+			CompilerContext newCC = lcc.pushCompilerContext(compSchema);
+			Parser	pa = newCC.getParser();
+			StatementNode stmtnode = (StatementNode)pa.parseStatement(triggerDefinition);
+			lcc.popCompilerContext(newCC);
+					
+			actionSPS.setText(getDataDictionary().getTriggerActionString(stmtnode, 
+					oldReferencingName,
+					newReferencingName,
+					triggerDefinition,
+					referencedCols,
+					referencedColsInTriggerAction,
+					0,
+					td,
+					-1,
+					false
+					));
+			//By this point, we are finished transforming the trigger action if
+			//it has any references to old/new transition variables.
+		}
+		
 		return actionSPS;
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1044096&r1=1044095&r2=1044096&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Thu Dec  9 19:19:42 2010
@@ -70,6 +70,12 @@ import org.apache.derby.iapi.sql.diction
 
 import org.apache.derby.iapi.sql.depend.DependencyManager;
 
+import org.apache.derby.impl.sql.compile.CollectNodesVisitor;
+import org.apache.derby.impl.sql.compile.ColumnReference;
+import org.apache.derby.impl.sql.compile.FromBaseTable;
+import org.apache.derby.impl.sql.compile.QueryTreeNode;
+import org.apache.derby.impl.sql.compile.StatementNode;
+import org.apache.derby.impl.sql.compile.TableName;
 import org.apache.derby.impl.sql.depend.BasicDependencyManager;
 
 import org.apache.derby.iapi.sql.execute.ExecIndexRow;
@@ -85,6 +91,7 @@ import org.apache.derby.iapi.types.SQLCh
 import org.apache.derby.iapi.types.SQLLongint;
 import org.apache.derby.iapi.types.SQLVarchar;
 import org.apache.derby.iapi.types.StringDataValue;
+import org.apache.derby.iapi.types.TypeId;
 import org.apache.derby.iapi.types.UserType;
 import org.apache.derby.iapi.types.DataTypeDescriptor;
 import org.apache.derby.iapi.types.DataValueDescriptor;
@@ -150,6 +157,7 @@ import java.util.Hashtable;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.Properties;
+import java.util.Vector;
 
 import java.util.List;
 import java.util.Iterator;
@@ -4273,6 +4281,7 @@ public final class	DataDictionaryImpl
 			if(firstCompilation)
 			{
 				updCols = new int[] {SYSSTATEMENTSRowFactory.SYSSTATEMENTS_VALID,
+						 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_TEXT,
 									 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_LASTCOMPILED,
 									 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_USINGTEXT,
 									 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_CONSTANTSTATE,
@@ -4281,6 +4290,7 @@ public final class	DataDictionaryImpl
 			{
 
 				updCols = new int[] {SYSSTATEMENTSRowFactory.SYSSTATEMENTS_VALID,
+						 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_TEXT,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_LASTCOMPILED,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_USINGTEXT,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_CONSTANTSTATE };
@@ -4580,6 +4590,602 @@ public final class	DataDictionaryImpl
 	}
 
 	/**
+	 * Get the trigger action string associated with the trigger after the
+	 * references to old/new transition tables/variables in trigger action
+	 * sql provided by CREATE TRIGGER have been transformed eg
+	 *		DELETE FROM t WHERE c = old.c
+	 * turns into
+	 *	DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
+	 *		getTriggerExecutionContext().getOldRow().
+	 *      getInt(columnNumberFor'C'inRuntimeResultset)
+	 * or
+	 *	DELETE FROM t WHERE c in (SELECT c FROM OLD)
+	 * turns into
+	 *	DELETE FROM t WHERE c in 
+	 *		(SELECT c FROM new TriggerOldTransitionTable OLD)
+	 *
+	 * @param actionStmt This is needed to get access to the various nodes
+	 * 	generated by the Parser for the trigger action sql. These nodes will be
+	 * 	used to find REFERENCEs column nodes.
+	 * 
+	 * @param oldReferencingName The name specified by the user for REFERENCEs
+	 * 	to old row columns
+	 * 
+	 * @param newReferencingName The name specified by the user for REFERENCEs
+	 * 	to new row columns
+	 * 
+	 * @param triggerDefinition The original trigger action text provided by
+	 * 	the user during CREATE TRIGGER time.
+	 * 
+	 * @param referencedCols Trigger is defined on these columns (will be null
+	 *   in case of INSERT AND DELETE Triggers. Can also be null for DELETE
+	 *   Triggers if UPDATE trigger is not defined on specific column(s))
+	 *   
+	 * @param referencedColsInTriggerAction	what columns does the trigger 
+	 * 	action reference through old/new transition variables (may be null)
+	 * 
+	 * @param actionOffset offset of start of action clause
+	 * 
+	 * @param triggerTableDescriptor Table descriptor for trigger table
+	 * 
+	 * @param triggerEventMask TriggerDescriptor.TRIGGER_EVENT_XXX
+	 * 
+	 * @param createTriggerTime True if here for CREATE TRIGGER,
+	 * 	false if here because an invalidated row level trigger with 
+	 *  REFERENCEd columns has been fired and hence trigger action
+	 *  sql associated with SPSDescriptor may be invalid too.
+	 * 
+	 * @return Transformed trigger action sql
+	 * @throws StandardException
+	 */
+	public String getTriggerActionString(
+			StatementNode actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int[] referencedColsInTriggerAction,
+			int actionOffset,
+			TableDescriptor triggerTableDescriptor,
+			int triggerEventMask,
+			boolean createTriggerTime
+			) throws StandardException
+	{
+		boolean in10_7_orHigherVersion =
+			checkVersion(DataDictionary.DD_VERSION_DERBY_10_7,null);
+		
+		StringBuffer newText = new StringBuffer();
+		int start = 0;
+
+		//Total Number of columns in the trigger table
+		int numberOfColsInTriggerTable = triggerTableDescriptor.getNumberOfColumns();
+		
+		//The purpose of following array(triggerColsAndTriggerActionCols)
+		//is to identify all the trigger columns and all the columns from
+		//the trigger action which are referenced though old/new 
+		//transition variables(in other words, accessed through the
+		//REFERENCING clause section of CREATE TRIGGER sql). This array 
+		//will be initialized to -1 at the beginning. By the end of this
+		//method, all the columns referenced by the trigger action 
+		//through the REFERENCING clause and all the trigger columns will
+		//have their column positions in the trigger table noted in this
+		//array.
+		//eg
+		//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+		//    REFERENCING OLD AS oldt NEW AS newt
+		//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+		//For the trigger above, triggerColsAndTriggerActionCols will
+		//finally have [-1,2,-1,4,-1] This list will include all the
+		//columns that need to be fetched into memory during trigger
+		//execution. All the columns with their entries marked -1 will
+		//not be read into memory because they are not referenced in the
+		//trigger action through old/new transition variables and they are
+		//not recognized as trigger columns.
+		int[] triggerColsAndTriggerActionCols = new int[numberOfColsInTriggerTable];
+
+		if (referencedCols == null) {
+			//This means that even though the trigger is defined at row 
+			//level, it is either an INSERT/DELETE trigger. Or it is an
+			//UPDATE trigger with no specific column(s) identified as the
+			//trigger column(s). In these cases, Derby is going to read all
+			//the columns from the trigger table during trigger execution.
+			//eg of an UPDATE trigger with no specific trigger column(s) 
+			// CREATE TRIGGER tr1 AFTER UPDATE ON table1 
+			//    REFERENCING OLD AS oldt NEW AS newt
+			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+			for (int i=0; i < numberOfColsInTriggerTable; i++) {
+				triggerColsAndTriggerActionCols[i]=i+1;
+			}
+		} else {
+			//This means that this row level trigger is an UPDATE trigger
+			//defined on specific column(s).
+			java.util.Arrays.fill(triggerColsAndTriggerActionCols, -1);
+			for (int i=0; i < referencedCols.length; i++){
+				//Make a note of this trigger column's column position in
+				//triggerColsAndTriggerActionCols. This will tell us that 
+				//this column needs to be read in when the trigger fires.
+				//eg for the CREATE TRIGGER below, we will make a note of
+				//column c12's position in triggerColsAndTriggerActionCols
+				//eg
+				//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+				//    REFERENCING OLD AS oldt NEW AS newt
+				//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+				triggerColsAndTriggerActionCols[referencedCols[i]-1] = referencedCols[i];
+			}
+		}
+
+		CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
+		actionStmt.accept(visitor);
+		Vector refs = visitor.getList();
+		/* we need to sort on position in string, beetle 4324
+		 */
+		QueryTreeNode[] cols = sortRefs(refs, true);
+		
+		if (createTriggerTime) {
+			//The purpose of following array(triggerActionColsOnly) is to
+			//identify all the columns from the trigger action which are
+			//referenced though old/new transition variables(in other words,
+			//accessed through the REFERENCING clause section of
+			//CREATE TRIGGER sql). This array will be initialized to -1 at the
+			//beginning. By the end of this method, all the columns referenced
+			//by the trigger action through the REFERENCING clause will have
+			//their column positions in the trigger table noted in this array.
+			//eg
+			//CREATE TABLE table1 (c11 int, c12 int, c13 int, c14 int, c15 int);
+			//CREATE TABLE table2 (c21 int, c22 int, c23 int, c24 int, c25 int);
+			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+			//    REFERENCING OLD AS oldt NEW AS newt
+			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+			//For the trigger above, triggerActionColsOnly will finally have 
+			//[-1,-1,-1,4,-1]. We will note all the entries for this array
+			//which are not -1 into SYSTRIGGERS(-1 indiciates columns with
+			//those column positions from the trigger table are not being
+			//referenced in the trigger action through the old/new transition
+			//variables.
+			int[] triggerActionColsOnly = new int[numberOfColsInTriggerTable];
+			java.util.Arrays.fill(triggerActionColsOnly, -1);
+						
+			//By this time, we have collected the positions of the trigger
+			//columns in array triggerColsAndTriggerActionCols. Now we need
+			//to start looking at the columns in trigger action to collect
+			//all the columns referenced through REFERENCES clause. These
+			//columns will be noted in triggerColsAndTriggerActionCols and
+			//triggerActionColsOnly arrays.
+			
+			//At the end of the for loop below, we will have both arrays
+			//triggerColsAndTriggerActionCols & triggerActionColsOnly
+			//filled up with the column positions of the columns which are
+			//either trigger columns or triger action columns which are
+			//referenced through old/new transition variables. 
+			//eg
+			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+			//    REFERENCING OLD AS oldt NEW AS newt
+			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+			//For the above trigger, before the for loop below, the contents
+			//of the 2 arrays will be as follows
+			//triggerActionColsOnly [-1,-1,-1,-1,-1]
+			//triggerColsAndTriggerActionCols [-1,2,-1,-1,-1]
+			//After the for loop below, the 2 arrays will look as follows
+			//triggerActionColsOnly [-1,-1,-1,4,-1]
+			//triggerColsAndTriggerActionCols [-1,2,-1,4,-1]
+			//If the database is at 10.6 or earlier version(meaning we are in
+			//soft-upgrade mode), then we do not want to collect any 
+			//information about trigger action columns. The collection and 
+			//usage of trigger action columns was introduced in 10.7 DERBY-1482
+			for (int i = 0; i < cols.length; i++)
+			{
+				ColumnReference ref = (ColumnReference) cols[i];
+				/*
+				** Only occurrences of those OLD/NEW transition tables/variables 
+				** are of interest here.  There may be intermediate nodes in the 
+				** parse tree that have its own RCL which contains copy of 
+				** column references(CR) from other nodes. e.g.:  
+				**
+				** CREATE TRIGGER tt 
+				** AFTER INSERT ON x
+				** REFERENCING NEW AS n 
+				** FOR EACH ROW
+				**    INSERT INTO y VALUES (n.i), (999), (333);
+				** 
+				** The above trigger action will result in InsertNode that 
+				** contains a UnionNode of RowResultSetNodes.  The UnionNode
+				** will have a copy of the CRs from its left child and those CRs 
+				** will not have its beginOffset set which indicates they are 
+				** not relevant for the conversion processing here, so we can 
+				** safely skip them. 
+				*/
+				if (ref.getBeginOffset() == -1) 
+				{
+					continue;
+				}
+
+				TableName tableName = ref.getTableNameNode();
+				if ((tableName == null) ||
+					((oldReferencingName == null || !oldReferencingName.equals(tableName.getTableName())) &&
+					(newReferencingName == null || !newReferencingName.equals(tableName.getTableName()))))
+				{
+					continue;
+				}
+
+				if (tableName.getBeginOffset() == -1)
+				{
+					continue;
+				}
+
+				checkInvalidTriggerReference(tableName.getTableName(),
+						oldReferencingName,
+						newReferencingName,
+						triggerEventMask);
+				String colName = ref.getColumnName();
+
+				ColumnDescriptor triggerColDesc;
+				//Following will catch the case where an invalid column is
+				//used in trigger action through the REFERENCING clause. The
+				//following tigger is trying to use oldt.c13 but there is no
+				//column c13 in trigger table table1
+				//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+				//    REFERENCING OLD AS oldt NEW AS newt
+				//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14567;
+				if ((triggerColDesc = triggerTableDescriptor.getColumnDescriptor(colName)) == 
+	                null) {
+					throw StandardException.newException(
+			                SQLState.LANG_COLUMN_NOT_FOUND, tableName+"."+colName);
+					}
+
+				if (in10_7_orHigherVersion) {
+					int triggerColDescPosition = triggerColDesc.getPosition();
+					triggerColsAndTriggerActionCols[triggerColDescPosition-1]=triggerColDescPosition;
+					triggerActionColsOnly[triggerColDescPosition-1]=triggerColDescPosition;
+					referencedColsInTriggerAction[triggerColDescPosition-1] = triggerColDescPosition;
+				}
+			}
+		} else {
+			//We are here because we have come across an invalidated trigger 
+			//which is being fired. This code gets called for such a trigger
+			//only if it is a row level trigger with REFERENCEs clause
+			if (referencedCols != null){
+				for (int i = 0; i < referencedColsInTriggerAction.length; i++)
+				{
+					triggerColsAndTriggerActionCols[referencedColsInTriggerAction[i]-1] = referencedColsInTriggerAction[i];
+				}
+			}
+		}
+					
+		//Now that we know what columns we need for trigger columns and
+		//trigger action columns, we can get rid of remaining -1 entries
+		//for the remaining columns from trigger table.
+		//eg
+		//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+		//    REFERENCING OLD AS oldt NEW AS newt
+		//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+		//For the above trigger, before the justTheRequiredColumns() call,
+		//the content of triggerColsAndTriggerActionCols array were as
+		//follows [-1, 2, -1, 4, -1]
+		//After the justTheRequiredColumns() call below, 
+		//triggerColsAndTriggerActionCols will have [2,4]. What this means
+		//that, at run time, during trigger execution, these are the only
+		//2 column positions that will be read into memory from the
+		//trigger table. The columns in other column positions are not
+		//needed for trigger execution.
+		triggerColsAndTriggerActionCols = justTheRequiredColumns(
+				triggerColsAndTriggerActionCols, triggerTableDescriptor);
+
+		//This is where we do the actual transformation of trigger action
+		//sql. An eg of that is
+		//	DELETE FROM t WHERE c = old.c
+		// turns into
+		//	DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
+		//	  getTriggerExecutionContext().getOldRow().
+		//    getInt(columnNumberFor'C'inRuntimeResultset)
+		// or
+		//	DELETE FROM t WHERE c in (SELECT c FROM OLD)
+		// turns into
+		//	DELETE FROM t WHERE c in 
+		//		(SELECT c FROM new TriggerOldTransitionTable OLD)
+		for (int i = 0; i < cols.length; i++)
+		{
+			ColumnReference ref = (ColumnReference) cols[i];				
+			/*
+			** Only occurrences of those OLD/NEW transition tables/variables 
+			** are of interest here.  There may be intermediate nodes in the 
+			** parse tree that have its own RCL which contains copy of 
+			** column references(CR) from other nodes. e.g.:  
+			**
+			** CREATE TRIGGER tt 
+			** AFTER INSERT ON x
+			** REFERENCING NEW AS n 
+			** FOR EACH ROW
+			**    INSERT INTO y VALUES (n.i), (999), (333);
+			** 
+			** The above trigger action will result in InsertNode that 
+			** contains a UnionNode of RowResultSetNodes.  The UnionNode
+			** will have a copy of the CRs from its left child and those CRs 
+			** will not have its beginOffset set which indicates they are 
+			** not relevant for the conversion processing here, so we can 
+			** safely skip them. 
+			*/
+			if (ref.getBeginOffset() == -1) 
+			{
+				continue;
+			}
+			
+			TableName tableName = ref.getTableNameNode();
+			if ((tableName == null) ||
+				((oldReferencingName == null || !oldReferencingName.equals(tableName.getTableName())) &&
+				(newReferencingName == null || !newReferencingName.equals(tableName.getTableName()))))
+			{
+				continue;
+			}
+				
+			int tokBeginOffset = tableName.getBeginOffset();
+			int tokEndOffset = tableName.getEndOffset();
+			if (tokBeginOffset == -1)
+			{
+				continue;
+			}
+
+			String colName = ref.getColumnName();
+			int columnLength = ref.getEndOffset() - ref.getBeginOffset() + 1;
+
+			newText.append(triggerDefinition.substring(start, tokBeginOffset-actionOffset));
+			int colPositionInRuntimeResultSet = -1;
+			ColumnDescriptor triggerColDesc = triggerTableDescriptor.getColumnDescriptor(colName);
+			int colPositionInTriggerTable = triggerColDesc.getPosition();
+
+			//This part of code is little tricky and following will help
+			//understand what mapping is happening here.
+			//eg
+			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+			//    REFERENCING OLD AS oldt NEW AS newt
+			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+			//For the above trigger, triggerColsAndTriggerActionCols will 
+			//have [2,4]. What this means that, at run time, during trigger
+			//execution, these are the only 2 column positions that will be
+			//read into memory from the trigger table. The columns in other
+			//column positions are not needed for trigger execution. But
+			//even though column positions in original trigger table are 2
+			//and 4, their relative column positions in the columns read at
+			//execution time is really [1,2]. At run time, when the trigger
+			//gets fired, column position 2 from the trigger table will be
+			//read as the first column and column position 4 from the
+			//trigger table will be read as the second column. And those
+			//relative column positions at runtime is what should be used
+			//during trigger action conversion from
+			//UPDATE table2 SET c24=oldt.c14
+			//to
+			//UPDATE table2 SET c24=
+			//  org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
+			//  getOldRow().getInt(2)
+			//Note that the generated code above refers to column c14 from
+			//table1 by position 2 rather than position 4. Column c14's
+			//column position in table1 is 4 but in the relative columns
+			//that will be fetched during trigger execution, it's position
+			//is 2. That is what the following code is doing.
+			if (in10_7_orHigherVersion && triggerColsAndTriggerActionCols != null){
+				for (int j=0; j<triggerColsAndTriggerActionCols.length; j++){
+					if (triggerColsAndTriggerActionCols[j] == colPositionInTriggerTable)
+						colPositionInRuntimeResultSet=j+1;
+				}
+			} else
+				colPositionInRuntimeResultSet=colPositionInTriggerTable;
+
+			newText.append(genColumnReferenceSQL(triggerTableDescriptor, colName, 
+					tableName.getTableName(), 
+					tableName.getTableName().equals(oldReferencingName),
+					colPositionInRuntimeResultSet));
+			start = tokEndOffset- actionOffset + columnLength + 2;
+		}
+		//By this point, we are finished transforming the trigger action if
+		//it has any references to old/new transition variables.
+		if (start < triggerDefinition.length())
+		{
+			newText.append(triggerDefinition.substring(start));
+		}
+		return newText.toString();
+	}
+
+	/*
+	 * The arrary passed will have either -1 or a column position as it's 
+	 * elements. If the array only has -1 as for all it's elements, then
+	 * this method will return null. Otherwise, the method will create a
+	 * new arrary with all -1 entries removed from the original arrary.
+	 */
+	private int[] justTheRequiredColumns(int[] columnsArrary,
+			TableDescriptor triggerTableDescriptor) {
+		int countOfColsRefedInArray = 0;
+		int numberOfColsInTriggerTable = triggerTableDescriptor.getNumberOfColumns();
+
+		//Count number of non -1 entries
+		for (int i=0; i < numberOfColsInTriggerTable; i++) {
+			if (columnsArrary[i] != -1)
+				countOfColsRefedInArray++;
+		}
+
+		if (countOfColsRefedInArray > 0){
+			int[] tempArrayOfNeededColumns = new int[countOfColsRefedInArray];
+			int j=0;
+			for (int i=0; i < numberOfColsInTriggerTable; i++) {
+				if (columnsArrary[i] != -1)
+					tempArrayOfNeededColumns[j++] = columnsArrary[i];
+			}
+			return tempArrayOfNeededColumns;
+		} else
+			return null;
+	}
+
+	/*
+	** Check for illegal combinations here: insert & old or
+	** delete and new
+	*/
+	private void checkInvalidTriggerReference(String tableName,
+			String oldReferencingName,
+			String newReferencingName,
+			int triggerEventMask) throws StandardException
+	{
+		if (tableName.equals(oldReferencingName) && 
+			(triggerEventMask & TriggerDescriptor.TRIGGER_EVENT_INSERT) == TriggerDescriptor.TRIGGER_EVENT_INSERT)
+		{
+			throw StandardException.newException(SQLState.LANG_TRIGGER_BAD_REF_MISMATCH, "INSERT", "new");
+		}
+		else if (tableName.equals(newReferencingName) && 
+			(triggerEventMask & TriggerDescriptor.TRIGGER_EVENT_DELETE) == TriggerDescriptor.TRIGGER_EVENT_DELETE)
+		{
+			throw StandardException.newException(SQLState.LANG_TRIGGER_BAD_REF_MISMATCH, "DELETE", "old");
+		}
+	}
+
+	/*
+	** Make sure the given column name is found in the trigger
+	** target table.  Generate the appropriate SQL to get it.
+	**
+	** @return a string that is used to get the column using
+	** getObject() on the desired result set and CAST it back
+	** to the proper type in the SQL domain. 
+	**
+	** @exception StandardException on invalid column name
+	*/
+	private String genColumnReferenceSQL(
+			TableDescriptor td,
+			String			colName,
+			String			tabName,
+			boolean			isOldTable,
+			int				colPositionInRuntimeResultSet
+			) throws StandardException
+	{
+		ColumnDescriptor colDesc = null;
+		if ((colDesc = td.getColumnDescriptor(colName)) == 
+	            null)
+		{
+			throw StandardException.newException(
+	            SQLState.LANG_COLUMN_NOT_FOUND, tabName+"."+colName);
+		}
+
+		/*
+		** Generate something like this:
+		**
+		** 		CAST (org.apache.derby.iapi.db.Factory::
+		**			getTriggerExecutionContext().getNewRow().
+		**				getObject(<colPosition>) AS DECIMAL(6,2))
+	    **
+	    ** Column position is used to avoid the wrong column being
+	    ** selected problem (DERBY-1258) caused by the case insensitive
+	    ** JDBC rules for fetching a column by name.
+		**
+		** The cast back to the SQL Domain may seem redundant
+		** but we need it to make the column reference appear
+		** EXACTLY like a regular column reference, so we need
+		** the object in the SQL Domain and we need to have the
+		** type information.  Thus a user should be able to do 
+		** something like
+		**
+		**		CREATE TRIGGER ... INSERT INTO T length(Column), ...
+	    **
+	    */
+
+		DataTypeDescriptor  dts     = colDesc.getType();
+		TypeId              typeId  = dts.getTypeId();
+
+	    if (!typeId.isXMLTypeId())
+	    {
+
+	        StringBuffer methodCall = new StringBuffer();
+	        methodCall.append(
+	            "CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
+	        methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
+	        methodCall.append(".getObject(");
+	        methodCall.append(colPositionInRuntimeResultSet);
+	        methodCall.append(") AS ");
+
+	        /*
+	        ** getSQLString() returns <typeName> 
+	        ** for user types, so call getSQLTypeName in that
+	        ** case.
+	        */
+	        methodCall.append(
+	            (typeId.userType() ? 
+	                 typeId.getSQLTypeName() : dts.getSQLstring()));
+	        
+	        methodCall.append(") ");
+
+	        return methodCall.toString();
+	    }
+	    else
+	    {
+	        /*  DERBY-2350
+	        **
+	        **  Triggers currently use jdbc 1.2 to access columns.  The default
+	        **  uses getObject() which is not supported for an XML type until
+	        **  jdbc 4.  In the meantime use getString() and then call 
+	        **  XMLPARSE() on the string to get the type.  See Derby issue and
+	        **  http://wiki.apache.org/db-derby/TriggerImplementation , for
+	        **  better long term solutions.  Long term I think changing the
+	        **  trigger architecture to not rely on jdbc, but instead on an
+	        **  internal direct access interface to execution nodes would be
+	        **  best future direction, but believe such a change appropriate
+	        **  for a major release, not a bug fix.
+	        **
+	        **  Rather than the above described code generation, use the 
+	        **  following for XML types to generate an XML column from the
+	        **  old or new row.
+	        ** 
+	        **          XMLPARSE(DOCUMENT
+	        **              CAST (org.apache.derby.iapi.db.Factory::
+	        **                  getTriggerExecutionContext().getNewRow().
+	        **                      getString(<colPosition>) AS CLOB)  
+	        **                        PRESERVE WHITESPACE)
+	        */
+
+	        StringBuffer methodCall = new StringBuffer();
+	        methodCall.append("XMLPARSE(DOCUMENT CAST( ");
+	        methodCall.append(
+	            "org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
+	        methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
+	        methodCall.append(".getString(");
+	        methodCall.append(colPositionInRuntimeResultSet);
+	        methodCall.append(") AS CLOB) PRESERVE WHITESPACE ) ");
+
+	        return methodCall.toString();
+	    }
+	}
+
+	/*
+	** Sort the refs into array.
+	*/
+	private QueryTreeNode[] sortRefs(Vector refs, boolean isRow)
+	{
+		int size = refs.size();
+		QueryTreeNode[] sorted = (QueryTreeNode[])refs.toArray(new QueryTreeNode[size]);
+		int i = 0;
+		/* bubble sort
+		 */
+		QueryTreeNode temp;
+		for (i = 0; i < size - 1; i++)
+		{
+			temp = null;
+			for (int j = 0; j < size - i - 1; j++)
+			{
+				if ((isRow && 
+					 sorted[j].getBeginOffset() > 
+					 sorted[j+1].getBeginOffset()
+					) ||
+					(!isRow &&
+					 ((FromBaseTable) sorted[j]).getTableNameField().getBeginOffset() > 
+					 ((FromBaseTable) sorted[j+1]).getTableNameField().getBeginOffset()
+					))
+				{
+					temp = sorted[j];
+					sorted[j] = sorted[j+1];
+					sorted[j+1] = temp;
+				}
+			}
+			if (temp == null)		// sorted
+				break;
+		}
+
+		return sorted;
+	}
+
+	/**
 	 * Get a TriggerDescriptor given its UUID.
 	 *
 	 * @param uuid	The UUID

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1044096&r1=1044095&r2=1044096&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Thu Dec  9 19:19:42 2010
@@ -411,7 +411,6 @@ public class CreateTriggerNode extends D
 		*/
 		if (triggerCols != null && triggerCols.size() != 0)
 		{
-			referencedColInts = new int[triggerCols.size()];
 			Hashtable columnNames = new Hashtable();
 			int tcSize = triggerCols.size();
 			for (int i = 0; i < tcSize; i++)
@@ -431,12 +430,7 @@ public class CreateTriggerNode extends D
 																rc.getName(),
 																tableName);
 				}
-
-				referencedColInts[i] = cd.getPosition();
 			}
- 
-			// sort the list
-			java.util.Arrays.sort(referencedColInts);
 		}
 
 		//If attempting to reference a SESSION schema table (temporary or permanent) in the trigger action, throw an exception
@@ -467,14 +461,16 @@ public class CreateTriggerNode extends D
 	** 1) validate the referencing clause (if any)
 	**
 	** 2) convert trigger action text.  e.g. 
-	**		DELETE FROM t WHERE c = old.c
+	**	DELETE FROM t WHERE c = old.c
 	** turns into
-	**		DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
-	**					getTriggerExecutionContext().getOldRow().getInt('C');
+	**	DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
+	**		getTriggerExecutionContext().getOldRow().
+	**      getInt(columnNumberFor'C'inRuntimeResultset);
 	** or
-	**		DELETE FROM t WHERE c in (SELECT c FROM OLD)
+	**	DELETE FROM t WHERE c in (SELECT c FROM OLD)
 	** turns into
-	**		DELETE FROM t WHERE c in (SELECT c FROM new TriggerOldTransitionTable OLD)
+	**	DELETE FROM t WHERE c in (
+	**      SELECT c FROM new TriggerOldTransitionTable OLD)
 	**
 	** 3) check all column references against new/old transition 
 	**	variables (since they are no longer 'normal' column references
@@ -500,38 +496,6 @@ public class CreateTriggerNode extends D
 	** out what its OLD/NEW tables are, etc.  Also, it is just plain
 	** easier to just generate the sql and rebind.
 	**
-	** More information on step 4 above. 
-	** DERBY-1482 One of the work done by this method for row level triggers
-	** is to find the columns which are referenced in the trigger action 
-	** through the REFERENCES clause ie thro old/new transition variables.
-	** This information will be saved in SYSTRIGGERS so it can be retrieved
-	** during the trigger execution time. The purpose of this is to recognize
-	** what columns from the trigger table should be read in during trigger
-	** execution. Before these code change, during trigger execution, Derby
-	** was opting to read all the columns from the trigger table even if they
-	** were not all referenced during the trigger execution. This caused Derby
-	** to run into OOM at times when it could really be avoided.
-	**
-	** We go through the trigger action text and collect the column positions
-	** of all the REFERENCEd columns through new/old transition variables. We
-	** keep that information in SYSTRIGGERS. At runtime, when the trigger is
-	** fired, we will look at this information along with trigger columns from
-	** the trigger definition and only fetch those columns into memory rather
-	** than all the columns from the trigger table.
-	** This is especially useful when the table has LOB columns and those
-	** columns are not referenced in the trigger action and are not recognized
-	** as trigger columns. For such cases, we can avoid reading large values of
-	** LOB columns into memory and thus avoiding possibly running into OOM 
-	** errors.
-	** 
-	** If there are no trigger columns defined on the trigger, we will read all
-	** the columns from the trigger table when the trigger fires because no
-	** specific columns were identified as trigger column by the user. The 
-	** other case where we will opt to read all the columns are when trigger
-	** columns and REFERENCING clause is identified for the trigger but there
-	** is no trigger action column information in SYSTRIGGERS. This can happen
-	** for triggers created prior to 10.7 release and later that database got
-	** hard/soft-upgraded to 10.7 or higher release.
 	*/
 	private boolean bindReferencesClause(DataDictionary dd) throws StandardException
 	{
@@ -539,344 +503,83 @@ public class CreateTriggerNode extends D
 
         // the actions of before triggers may not reference generated columns
         if ( isBefore ) { forbidActionsOnGenCols(); }
-        
-		//Total Number of columns in the trigger table
-		int numberOfColsInTriggerTable = triggerTableDescriptor.getNumberOfColumns();
 
-		StringBuffer newText = new StringBuffer();
-		boolean regenNode = false;
+		String transformedActionText;
 		int start = 0;
-		if (isRow)
-		{
+		if (triggerCols != null && triggerCols.size() != 0) {
+			//If the trigger is defined on speific columns, then collect
+			//their column positions and ensure that those columns do
+			//indeed exist in the trigger table.
+			referencedColInts = new int[triggerCols.size()];
 			ResultColumn rc;
-			
-			//The purpose of following array(triggerActionColsOnly) is to
-			//identify all the columns from the trigger action which are
-			//referenced though old/new transition variables(in other words,
-			//accessed through the REFERENCING clause section of
-			//CREATE TRIGGER sql). This array will be initialized to -1 at the
-			//beginning. By the end of this method, all the columns referenced
-			//by the trigger action through the REFERENCING clause will have
-			//their column positions in the trigger table noted in this array.
+			ColumnDescriptor cd;
+			//This is the most interesting case for us. If we are here, 
+			//then it means that the trigger is defined at the row level
+			//and a set of trigger columns are specified in the CREATE
+			//TRIGGER statement. This can only happen for an UPDATE
+			//trigger.
 			//eg
-			//CREATE TABLE table1 (c11 int, c12 int, c13 int, c14 int, c15 int);
-			//CREATE TABLE table2 (c21 int, c22 int, c23 int, c24 int, c25 int);
 			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
 			//    REFERENCING OLD AS oldt NEW AS newt
 			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-			//For the trigger above, triggerActionColsOnly will finally have 
-			//[-1,-1,-1,4,-1]. We will note all the entries for this array
-			//which are not -1 into SYSTRIGGERS(-1 indiciates columns with
-			//those column positions from the trigger table are not being
-			//referenced in the trigger action through the old/new transition
-			//variables.
-			int[] triggerActionColsOnly = new int[numberOfColsInTriggerTable];
-			for (int i=0; i < numberOfColsInTriggerTable; i++)
-				triggerActionColsOnly[i]=-1;
 			
-			//The purpose of following array(triggerColsAndTriggerActionCols)
-			//is to identify all the trigger columns and all the columns from
-			//the trigger action which are referenced though old/new 
-			//transition variables(in other words, accessed through the
-			//REFERENCING clause section of CREATE TRIGGER sql). This array 
-			//will be initialized to -1 at the beginning. By the end of this
-			//method, all the columns referenced by the trigger action 
-			//through the REFERENCING clause and all the trigger columns will
-			//have their column positions in the trigger table noted in this
-			//array.
-			//eg
-			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
-			//    REFERENCING OLD AS oldt NEW AS newt
-			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-			//For the trigger above, triggerColsAndTriggerActionCols will
-			//finally have [-1,2,-1,4,-1] This list will include all the
-			//columns that need to be fetched into memory during trigger
-			//execution. All the columns with their entries marked -1 will
-			//not be read into memory because they are not referenced in the
-			//trigger action through old/new transition variables and they are
-			//not recognized as trigger columns.
-			int[] triggerColsAndTriggerActionCols = new int[numberOfColsInTriggerTable];
-			for (int i=0; i < numberOfColsInTriggerTable; i++) 
-				triggerColsAndTriggerActionCols[i]=-1;
-			
-			if (triggerCols == null || triggerCols.size() == 0) {
-				//This means that even though the trigger is defined at row 
-				//level, it is either an INSERT/DELETE trigger. Or it is an
-				//UPDATE trigger with no specific column(s) identified as the
-				//trigger column(s). In these cases, Derby is going to read all
-				//the columns from the trigger table during trigger execution.
-				//eg of an UPDATE trigger with no specific trigger column(s) 
-				// CREATE TRIGGER tr1 AFTER UPDATE ON table1 
+			for (int i=0; i < triggerCols.size(); i++){
+				rc = (ResultColumn)triggerCols.elementAt(i);
+				cd = triggerTableDescriptor.getColumnDescriptor(rc.getName());
+				//Following will catch the case where an invalid trigger column
+				//has been specified in CREATE TRIGGER statement.
+				//CREATE TRIGGER tr1 AFTER UPDATE OF c1678 ON table1 
 				//    REFERENCING OLD AS oldt NEW AS newt
 				//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-				for (int i=0; i < numberOfColsInTriggerTable; i++) {
-					triggerColsAndTriggerActionCols[i]=i+1;
-				}
-			} else {
-				//This is the most interesting case for us. If we are here, 
-				//then it means that the trigger is defined at the row level
-				//and a set of trigger columns are specified in the CREATE
-				//TRIGGER statement. This can only happen for an UPDATE
-				//trigger.
-				//eg
-				//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
-				//    REFERENCING OLD AS oldt NEW AS newt
-				//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-				
-				for (int i=0; i < triggerCols.size(); i++){
-					rc = (ResultColumn)triggerCols.elementAt(i);
-					ColumnDescriptor cd = triggerTableDescriptor.getColumnDescriptor(rc.getName());
-					//Following will catch the case where an invalid trigger column
-					//has been specified in CREATE TRIGGER statement.
-					//CREATE TRIGGER tr1 AFTER UPDATE OF c1678 ON table1 
-					//    REFERENCING OLD AS oldt NEW AS newt
-					//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-					if (cd == null)
-					{
-						throw StandardException.newException(SQLState.LANG_COLUMN_NOT_FOUND_IN_TABLE, 
-																	rc.getName(),
-																	tableName);
-					}
-					//Make a note of this trigger column's column position in
-					//triggerColsAndTriggerActionCols. This will tell us that 
-					//this column needs to be read in when the trigger fires.
-					//eg for the CREATE TRIGGER below, we will make a note of
-					//column c12's position in triggerColsAndTriggerActionCols
-					//eg
-					//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
-					//    REFERENCING OLD AS oldt NEW AS newt
-					//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-					triggerColsAndTriggerActionCols[cd.getPosition()-1]=cd.getPosition();				
-				}
-			}
-			//By this time, we have collected the positions of the trigger
-			//columns in array triggerColsAndTriggerActionCols. Now we need
-			//to start looking at the columns in trigger action to collect
-			//all the columns referenced through REFERENCES clause. These
-			//columns will be noted in triggerColsAndTriggerActionCols and
-			//triggerActionColsOnly arrays.
-
-			CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
-			actionNode.accept(visitor);
-			Vector refs = visitor.getList();
-			/* we need to sort on position in string, beetle 4324
-			 */
-			QueryTreeNode[] cols = sortRefs(refs, true);
-
-			//At the end of the for loop below, we will have both arrays
-			//triggerColsAndTriggerActionCols & triggerActionColsOnly
-			//filled up with the column positions of the columns which are
-			//either trigger columns or triger action columns which are
-			//referenced through old/new transition variables. 
-			//eg
-			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
-			//    REFERENCING OLD AS oldt NEW AS newt
-			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-			//For the above trigger, before the for loop below, the contents
-			//of the 2 arrays will be as follows
-			//triggerActionColsOnly [-1,-1,-1,-1,-1]
-			//triggerColsAndTriggerActionCols [-1,2,-1,-1,-1]
-			//After the for loop below, the 2 arrays will look as follows
-			//triggerActionColsOnly [-1,-1,-1,4,-1]
-			//triggerColsAndTriggerActionCols [-1,2,-1,4,-1]
-			//If the database is at 10.6 or earlier version(meaning we are in
-			//soft-upgrade mode), then we do not want to collect any 
-			//information about trigger action columns. The collection and 
-			//usage of trigger action columns was introduced in 10.7 DERBY-1482
-			boolean in10_7_orHigherVersion =
-					getLanguageConnectionContext().getDataDictionary().checkVersion(
-							DataDictionary.DD_VERSION_DERBY_10_7,null);
-			for (int i = 0; i < cols.length; i++)
-			{
-				ColumnReference ref = (ColumnReference) cols[i];
-				/*
-				** Only occurrences of those OLD/NEW transition tables/variables 
-				** are of interest here.  There may be intermediate nodes in the 
-				** parse tree that have its own RCL which contains copy of 
-				** column references(CR) from other nodes. e.g.:  
-				**
-				** CREATE TRIGGER tt 
-				** AFTER INSERT ON x
-				** REFERENCING NEW AS n 
-				** FOR EACH ROW
-				**    INSERT INTO y VALUES (n.i), (999), (333);
-				** 
-				** The above trigger action will result in InsertNode that 
-				** contains a UnionNode of RowResultSetNodes.  The UnionNode
-				** will have a copy of the CRs from its left child and those CRs 
-				** will not have its beginOffset set which indicates they are 
-				** not relevant for the conversion processing here, so we can 
-				** safely skip them. 
-				*/
-				if (ref.getBeginOffset() == -1) 
-				{
-					continue;
-				}
-
-				TableName tableName = ref.getTableNameNode();
-				if ((tableName == null) ||
-					((oldTableName == null || !oldTableName.equals(tableName.getTableName())) &&
-					(newTableName == null || !newTableName.equals(tableName.getTableName()))))
-				{
-					continue;
-				}
-
-				if (tableName.getBeginOffset() == -1)
+				if (cd == null)
 				{
-					continue;
-				}
-
-				checkInvalidTriggerReference(tableName.getTableName());
-				String colName = ref.getColumnName();
-
-				ColumnDescriptor triggerColDesc;
-				//Following will catch the case where an invalid column is
-				//used in trigger action through the REFERENCING clause. The
-				//following tigger is trying to use oldt.c13 but there is no
-				//column c13 in trigger table table1
-				//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
-				//    REFERENCING OLD AS oldt NEW AS newt
-				//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14567;
-				if ((triggerColDesc = triggerTableDescriptor.getColumnDescriptor(colName)) == 
-	                null) {
-					throw StandardException.newException(
-			                SQLState.LANG_COLUMN_NOT_FOUND, tableName+"."+colName);
-					}
-
-				if (in10_7_orHigherVersion) {
-					int triggerColDescPosition = triggerColDesc.getPosition();
-					triggerColsAndTriggerActionCols[triggerColDescPosition-1]=triggerColDescPosition;
-					triggerActionColsOnly[triggerColDescPosition-1]=triggerColDescPosition;
+					throw StandardException.newException(SQLState.LANG_COLUMN_NOT_FOUND_IN_TABLE, 
+																rc.getName(),
+																tableName);
 				}
+				referencedColInts[i] = cd.getPosition();
 			}
-
-			//Now that we know what columns we need for trigger columns and
-			//trigger action columns, we can get rid of remaining -1 entries
-			//for the remaining columns from trigger table.
-			//eg
-			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
-			//    REFERENCING OLD AS oldt NEW AS newt
-			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-			//For the above trigger, before the justTheRequiredColumns() call,
-			//the content of triggerColsAndTriggerActionCols array were as
-			//follows [-1, 2, -1, 4, -1]
-			//After the justTheRequiredColumns() call below, 
-			//triggerColsAndTriggerActionCols will have [2,4]. What this means
-			//that, at run time, during trigger execution, these are the only
-			//2 column positions that will be read into memory from the
-			//trigger table. The columns in other column positions are not
-			//needed for trigger execution.
-			triggerColsAndTriggerActionCols = justTheRequiredColumns(triggerColsAndTriggerActionCols);
-
-			for (int i = 0; i < cols.length; i++)
-			{
-				ColumnReference ref = (ColumnReference) cols[i];
-				
-				/*
-				** Only occurrences of those OLD/NEW transition tables/variables 
-				** are of interest here.  There may be intermediate nodes in the 
-				** parse tree that have its own RCL which contains copy of 
-				** column references(CR) from other nodes. e.g.:  
-				**
-				** CREATE TRIGGER tt 
-				** AFTER INSERT ON x
-				** REFERENCING NEW AS n 
-				** FOR EACH ROW
-				**    INSERT INTO y VALUES (n.i), (999), (333);
-				** 
-				** The above trigger action will result in InsertNode that 
-				** contains a UnionNode of RowResultSetNodes.  The UnionNode
-				** will have a copy of the CRs from its left child and those CRs 
-				** will not have its beginOffset set which indicates they are 
-				** not relevant for the conversion processing here, so we can 
-				** safely skip them. 
-				*/
-				if (ref.getBeginOffset() == -1) 
-				{
-					continue;
-				}
-				
-				TableName tableName = ref.getTableNameNode();
-				if ((tableName == null) ||
-					((oldTableName == null || !oldTableName.equals(tableName.getTableName())) &&
-					(newTableName == null || !newTableName.equals(tableName.getTableName()))))
-				{
-					continue;
-				}
-					
-				int tokBeginOffset = tableName.getBeginOffset();
-				int tokEndOffset = tableName.getEndOffset();
-				if (tokBeginOffset == -1)
-				{
-					continue;
-				}
-
-				regenNode = true;
-				String colName = ref.getColumnName();
-				int columnLength = ref.getEndOffset() - ref.getBeginOffset() + 1;
-
-				newText.append(originalActionText.substring(start, tokBeginOffset-actionOffset));
-				int colPositionInRuntimeResultSet = -1;
-				ColumnDescriptor triggerColDesc = triggerTableDescriptor.getColumnDescriptor(colName);
-				int colPositionInTriggerTable = triggerColDesc.getPosition();
-
-				//This part of code is little tricky and following will help
-				//understand what mapping is happening here.
-				//eg
-				//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
-				//    REFERENCING OLD AS oldt NEW AS newt
-				//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
-				//For the above trigger, triggerColsAndTriggerActionCols will 
-				//have [2,4]. What this means that, at run time, during trigger
-				//execution, these are the only 2 column positions that will be
-				//read into memory from the trigger table. The columns in other
-				//column positions are not needed for trigger execution. But
-				//even though column positions in original trigger table are 2
-				//and 4, their relative column positions in the columns read at
-				//execution time is really [1,2]. At run time, when the trigger
-				//gets fired, column position 2 from the trigger table will be
-				//read as the first column and column position 4 from the
-				//trigger table will be read as the second column. And those
-				//relative column positions at runtime is what should be used
-				//during trigger action conversion from
-				//UPDATE table2 SET c24=oldt.c14
-				//to
-				//UPDATE table2 SET c24=org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getInt(2)
-				//Note that the generated code above refers to column c14 from
-				//table1 by position 2 rather than position 4. Column c14's
-				//column position in table1 is 4 but in the relative columns
-				//that will be fetched during trigger execution, it's position
-				//is 2. That is what the following code is doing.
-				if (in10_7_orHigherVersion && triggerColsAndTriggerActionCols != null){
-					for (int j=0; j<triggerColsAndTriggerActionCols.length; j++){
-						if (triggerColsAndTriggerActionCols[j] == colPositionInTriggerTable)
-							colPositionInRuntimeResultSet=j+1;
-					}
-				} else
-					colPositionInRuntimeResultSet=colPositionInTriggerTable;
-
-				newText.append(genColumnReferenceSQL(dd, colName, 
-						tableName.getTableName(), 
-						tableName.getTableName().equals(oldTableName),
-						colPositionInRuntimeResultSet));
-				start = tokEndOffset- actionOffset + columnLength + 2;
-			}
-			//By this point, we are finished transforming the trigger action if
-			//it has any references to old/new transition variables.
-
-			//Now that we know what columns we need for trigger action columns,
-			//we can get rid of -1 entries for the remaining columns from
-			//trigger table.
-			//The final step is to put all the column positions from the 
-			//trigger table of the columns which are referenced in the trigger
-			//action through old/new transition variables. This information
-			//will be saved in SYSTRIGGERS and will be used at trigger 
-			//execution time to decide which columns need to be read into
-			//memory for trigger action
-			referencedColsInTriggerAction = justTheRequiredColumns(triggerActionColsOnly);
+			// sort the list
+			java.util.Arrays.sort(referencedColInts);
+		}
+		
+		if (isRow)
+		{
+			//Create an array for column positions of columns referenced in 
+			//trigger action. Initialize it to -1. The call to 
+			//DataDictoinary.getTriggerActionSPS will find out the actual 
+			//columns, if any, referenced in the trigger action and put their
+			//column positions in the array.
+			referencedColsInTriggerAction = new int[triggerTableDescriptor.getNumberOfColumns()];
+			java.util.Arrays.fill(referencedColsInTriggerAction, -1);
+			//Now that we have verified that are no invalid column references
+			//for trigger columns, let's go ahead and transform the OLD/NEW
+			//transient table references in the trigger action sql.
+			transformedActionText = getDataDictionary().getTriggerActionString(actionNode, 
+					oldTableName,
+					newTableName,
+					originalActionText,
+					referencedColInts,
+					referencedColsInTriggerAction,
+					actionOffset,
+					triggerTableDescriptor,
+					triggerEventMask,
+					true
+					);			
+			//Now that we know what columns we need for REFERENCEd columns in
+			//trigger action, we can get rid of -1 entries for the remaining 
+			//columns from trigger table. This information will be saved in
+			//SYSTRIGGERS and will be used at trigger execution time to decide 
+			//which columns need to be read into memory for trigger action
+			referencedColsInTriggerAction = justTheRequiredColumns(
+					referencedColsInTriggerAction);
 		}
 		else
 		{
+			//This is a table level trigger	        
+			//Total Number of columns in the trigger table
+			int numberOfColsInTriggerTable = triggerTableDescriptor.getNumberOfColumns();
+			StringBuffer newText = new StringBuffer();
 			/*
 			** For a statement trigger, we find all FromBaseTable nodes.  If
 			** the from table is NEW or OLD (or user designated alternates
@@ -906,7 +609,6 @@ public class CreateTriggerNode extends D
 
 				checkInvalidTriggerReference(baseTableName);
 
-				regenNode = true;
 				newText.append(originalActionText.substring(start, tokBeginOffset-actionOffset));
 				newText.append(baseTableName.equals(oldTableName) ?
 								"new org.apache.derby.catalog.TriggerOldTransitionRows() " :
@@ -928,6 +630,11 @@ public class CreateTriggerNode extends D
 				for (int j=0; j < numberOfColsInTriggerTable; j++)
 					referencedColInts[j]=j+1;
 			}
+			if (start < originalActionText.length())
+			{
+				newText.append(originalActionText.substring(start));
+			}
+			transformedActionText = newText.toString();
 		}
 
 		if (referencedColsInTriggerAction != null)
@@ -938,13 +645,11 @@ public class CreateTriggerNode extends D
 		** Also, we reset the actionText to this new value.  This
 		** is what we are going to stick in the system tables.
 		*/
-		if (regenNode)
+		boolean regenNode = false;
+		if (!transformedActionText.equals(actionText))
 		{
-			if (start < originalActionText.length())
-			{
-				newText.append(originalActionText.substring(start));
-			}
-			actionText = newText.toString();
+			regenNode = true;
+			actionText = transformedActionText;
 			actionNode = parseStatement(actionText, true);
 		}
 
@@ -978,22 +683,15 @@ public class CreateTriggerNode extends D
 		} else
 			return null;
 	}
+
 	/*
 	** Sort the refs into array.
 	*/
 	private QueryTreeNode[] sortRefs(Vector refs, boolean isRow)
 	{
 		int size = refs.size();
-		QueryTreeNode[] sorted = new QueryTreeNode[size];
+		QueryTreeNode[] sorted = (QueryTreeNode[]) refs.toArray(new QueryTreeNode[size]);
 		int i = 0;
-		for (Enumeration e = refs.elements(); e.hasMoreElements(); )
-		{
-			if (isRow)
-				sorted[i++] = (ColumnReference)e.nextElement();
-			else
-				sorted[i++] = (FromBaseTable)e.nextElement();
-		}
-
 		/* bubble sort
 		 */
 		QueryTreeNode temp;
@@ -1080,121 +778,6 @@ public class CreateTriggerNode extends D
             return left.equals( right );
         }
     }
-    
-	/*
-	** Make sure the given column name is found in the trigger
-	** target table.  Generate the appropriate SQL to get it.
-	**
-	** @return a string that is used to get the column using
-	** getObject() on the desired result set and CAST it back
-	** to the proper type in the SQL domain. 
-	**
-	** @exception StandardException on invalid column name
-	*/
-	private String genColumnReferenceSQL
-	(
-		DataDictionary	dd, 
-		String			colName, 
-		String			tabName,
-		boolean			isOldTable,
-		int				colPositionInRuntimeResultSet
-	) throws StandardException
-	{
-		ColumnDescriptor colDesc = null;
-		if ((colDesc = triggerTableDescriptor.getColumnDescriptor(colName)) == 
-                null)
-		{
-			throw StandardException.newException(
-                SQLState.LANG_COLUMN_NOT_FOUND, tabName+"."+colName);
-		}
-
-		/*
-		** Generate something like this:
-		**
-		** 		CAST (org.apache.derby.iapi.db.Factory::
-		**			getTriggerExecutionContext().getNewRow().
-		**				getObject(<colPosition>) AS DECIMAL(6,2))
-        **
-        ** Column position is used to avoid the wrong column being
-        ** selected problem (DERBY-1258) caused by the case insensitive
-        ** JDBC rules for fetching a column by name.
-		**
-		** The cast back to the SQL Domain may seem redundant
-		** but we need it to make the column reference appear
-		** EXACTLY like a regular column reference, so we need
-		** the object in the SQL Domain and we need to have the
-		** type information.  Thus a user should be able to do 
-		** something like
-		**
-		**		CREATE TRIGGER ... INSERT INTO T length(Column), ...
-        **
-        */
-
-		DataTypeDescriptor  dts     = colDesc.getType();
-		TypeId              typeId  = dts.getTypeId();
-
-        if (!typeId.isXMLTypeId())
-        {
-
-            StringBuffer methodCall = new StringBuffer();
-            methodCall.append(
-                "CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
-            methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
-            methodCall.append(".getObject(");
-            methodCall.append(colPositionInRuntimeResultSet);
-            methodCall.append(") AS ");
-
-            /*
-            ** getSQLString() returns <typeName> 
-            ** for user types, so call getSQLTypeName in that
-            ** case.
-            */
-            methodCall.append(
-                (typeId.userType() ? 
-                     typeId.getSQLTypeName() : dts.getSQLstring()));
-            
-            methodCall.append(") ");
-
-            return methodCall.toString();
-        }
-        else
-        {
-            /*  DERBY-2350
-            **
-            **  Triggers currently use jdbc 1.2 to access columns.  The default
-            **  uses getObject() which is not supported for an XML type until
-            **  jdbc 4.  In the meantime use getString() and then call 
-            **  XMLPARSE() on the string to get the type.  See Derby issue and
-            **  http://wiki.apache.org/db-derby/TriggerImplementation , for
-            **  better long term solutions.  Long term I think changing the
-            **  trigger architecture to not rely on jdbc, but instead on an
-            **  internal direct access interface to execution nodes would be
-            **  best future direction, but believe such a change appropriate
-            **  for a major release, not a bug fix.
-            **
-            **  Rather than the above described code generation, use the 
-            **  following for XML types to generate an XML column from the
-            **  old or new row.
-            ** 
-            **          XMLPARSE(DOCUMENT
-            **              CAST (org.apache.derby.iapi.db.Factory::
-            **                  getTriggerExecutionContext().getNewRow().
-            **                      getString(<colPosition>) AS CLOB)  
-            **                        PRESERVE WHITESPACE)
-            */
-
-            StringBuffer methodCall = new StringBuffer();
-            methodCall.append("XMLPARSE(DOCUMENT CAST( ");
-            methodCall.append(
-                "org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
-            methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
-            methodCall.append(".getString(");
-            methodCall.append(colPositionInRuntimeResultSet);
-            methodCall.append(") AS CLOB) PRESERVE WHITESPACE ) ");
-
-            return methodCall.toString();
-        }
-	}
 
 	/*
 	** Check for illegal combinations here: insert & old or
@@ -1213,7 +796,7 @@ public class CreateTriggerNode extends D
 			throw StandardException.newException(SQLState.LANG_TRIGGER_BAD_REF_MISMATCH, "DELETE", "old");
 		}
 	}
-
+    
 	/*
 	** Make sure that the referencing clause is legitimate.
 	** While we are at it we set the new/oldTableName to

Modified: db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=1044096&r1=1044095&r2=1044096&view=diff
==============================================================================
--- db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original)
+++ db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Thu Dec  9 19:19:42 2010
@@ -63,6 +63,7 @@ import org.apache.derby.iapi.types.DataT
 import org.apache.derby.iapi.types.DataValueFactory;
 import org.apache.derby.iapi.types.NumberDataValue;
 import org.apache.derby.iapi.types.RowLocation;
+import org.apache.derby.impl.sql.compile.StatementNode;
 
 /**
  * DataDictionary implementation that does nothing!
@@ -480,6 +481,22 @@ public class EmptyDictionary implements 
 
 	}
 
+	public String getTriggerActionString(
+			StatementNode actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int[] referencedColsInTriggerAction,
+			int actionOffset,
+			TableDescriptor td,
+			int triggerEventMask,
+			boolean createTriggerTime)
+	throws StandardException {
+		// TODO Auto-generated method stub
+		return null;
+	}
+
 	public TriggerDescriptor getTriggerDescriptor(UUID uuid)
 			throws StandardException {
 		// TODO Auto-generated method stub

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1044096&r1=1044095&r2=1044096&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java Thu Dec  9 19:19:42 2010
@@ -107,6 +107,54 @@ public class TriggerTest extends BaseJDB
     }
     
     /**
+     * Altering the column length should regenerate the trigger
+     * action plan which is saved in SYSSTATEMENTS. DERBY-4874
+     * 
+     * @throws SQLException 
+     * 
+     */
+    public void testAlerColumnLength() throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("CREATE TABLE TestAlterTable( " +
+        		"element_id INTEGER NOT NULL, "+
+        		"altered_id VARCHAR(30) NOT NULL, "+
+        		"counter SMALLINT NOT NULL DEFAULT 0, "+
+        		"timets TIMESTAMP NOT NULL)");
+        s.executeUpdate("CREATE TRIGGER mytrig "+
+        		"AFTER UPDATE ON TestAlterTable "+
+        		"REFERENCING NEW AS newt OLD AS oldt "+
+        		"FOR EACH ROW MODE DB2SQL "+
+        		"  UPDATE TestAlterTable set "+
+        		"  TestAlterTable.counter = CASE WHEN "+
+        		"  (oldt.counter < 32767) THEN (oldt.counter + 1) ELSE 1 END "+
+        		"  WHERE ((newt.counter is null) or "+
+        		"  (oldt.counter = newt.counter)) " +
+        		"  AND newt.element_id = TestAlterTable.element_id "+
+        		"  AND newt.altered_id = TestAlterTable.altered_id");
+        s.executeUpdate("ALTER TABLE TestAlterTable ALTER altered_id "+
+        		"SET DATA TYPE VARCHAR(64)");
+        s.executeUpdate("insert into TestAlterTable values (99, "+
+        		"'012345678901234567890123456789001234567890',"+
+        		"1,CURRENT_TIMESTAMP)");
+
+        ResultSet rs = s.executeQuery("SELECT element_id, counter "+
+        		"FROM TestAlterTable");
+        JDBC.assertFullResultSet(rs, 
+        		new String[][] {{"99", "1"}});
+        
+        s.executeUpdate("update TestAlterTable "+
+        		"set timets = CURRENT_TIMESTAMP "+
+        		"where ELEMENT_ID = 99");
+        rs = s.executeQuery("SELECT element_id, counter "+
+        		"FROM TestAlterTable");
+        JDBC.assertFullResultSet(rs, 
+        		new String[][] {{"99", "2"}});
+
+        s.executeUpdate("DROP TABLE TestAlterTable");
+    }
+    
+    /**
      * Test the firing order of triggers. Should be:
      * 
      * Before operations



Mime
View raw message