db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1044748 - in /db/derby/code/branches/10.7: ./ java/engine/org/apache/derby/iapi/sql/dictionary/ java/engine/org/apache/derby/impl/sql/catalog/ java/engine/org/apache/derby/impl/sql/compile/ java/storeless/org/apache/derby/impl/storeless/ j...
Date Sun, 12 Dec 2010 00:42:15 GMT
Author: mamta
Date: Sun Dec 12 00:42:14 2010
New Revision: 1044748

URL: http://svn.apache.org/viewvc?rev=1044748&view=rev
Log:
Backporting to 10.7 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


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

Propchange: db/derby/code/branches/10.7/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Sun Dec 12 00:42:14 2010
@@ -1 +1 @@
-/db/derby/code/trunk:1035603,1036769,1038514,1038813,1039084,1039268,1040658,1041338,1043227,1043389
+/db/derby/code/trunk:1035603,1036769,1038514,1038813,1039084,1039268,1040658,1041338,1043227,1043389,1044096

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=1044748&r1=1044747&r2=1044748&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Sun Dec 12 00:42:14 2010
@@ -44,6 +44,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;
@@ -1170,6 +1172,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/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java?rev=1044748&r1=1044747&r2=1044748&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java Sun Dec 12 00:42:14 2010
@@ -517,6 +517,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/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=1044748&r1=1044747&r2=1044748&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java Sun Dec 12 00:42:14 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/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1044748&r1=1044747&r2=1044748&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Sun Dec 12 00:42:14 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;
@@ -4275,6 +4282,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,
@@ -4283,6 +4291,7 @@ public final class	DataDictionaryImpl
 			{
 
 				updCols = new int[] {SYSSTATEMENTSRowFactory.SYSSTATEMENTS_VALID,
+						 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_TEXT,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_LASTCOMPILED,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_USINGTEXT,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_CONSTANTSTATE };
@@ -4582,6 +4591,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/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1044748&r1=1044747&r2=1044748&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Sun Dec 12 00:42:14 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/branches/10.7/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=1044748&r1=1044747&r2=1044748&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original)
+++ db/derby/code/branches/10.7/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Sun Dec 12 00:42:14 2010
@@ -64,6 +64,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!
@@ -481,6 +482,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/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1044748&r1=1044747&r2=1044748&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java (original)
+++ db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java Sun Dec 12 00:42:14 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