You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2010/06/22 04:41:49 UTC
svn commit: r956763 [1/2] - in /db/derby/code/trunk/java:
engine/org/apache/derby/catalog/ engine/org/apache/derby/catalog/types/
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/catalog/
engine/org/apache/derby/impl/sql/co...
Author: mamta
Date: Tue Jun 22 02:41:48 2010
New Revision: 956763
URL: http://svn.apache.org/viewvc?rev=956763&view=rev
Log:
DERBY-1482
Only read the needed columns for the update trigger when create trigger has identified the columns which will be used in trigger action through the REFERENCING clause. This will avoid reading columns that are not needed thus avoiding OOM problems if the underlying table has large LOBs.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/catalog/ReferencedColumns.java
db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/ReferencedColumnsDescriptorImpl.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.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/SYSTRIGGERSRowFactory.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/memory/TriggerTests.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/ReferencedColumns.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/ReferencedColumns.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/ReferencedColumns.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/ReferencedColumns.java Tue Jun 22 02:41:48 2010
@@ -39,4 +39,15 @@ public interface ReferencedColumns
* of the columns that are referenced in this check constraint.
*/
public int[] getReferencedColumnPositions();
+
+ /**
+ * Returns an array of 1-based column positions in the trigger table.
+ * These columns are the ones referenced in the trigger action through
+ * the old/new transition variables.
+ *
+ * @return An array of ints representing the 1-based column positions
+ * of the columns that are referenced in the trigger action
+ * through the old/new transition variables.
+ */
+ public int[] getTriggerActionReferencedColumnPositions();
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/ReferencedColumnsDescriptorImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/ReferencedColumnsDescriptorImpl.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/ReferencedColumnsDescriptorImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/ReferencedColumnsDescriptorImpl.java Tue Jun 22 02:41:48 2010
@@ -30,6 +30,28 @@ import java.io.ObjectInput;
import java.io.ObjectOutput;
import java.io.IOException;
+/**
+ * For triggers, ReferencedColumnsDescriptorImpl object has 3 possibilites
+ * 1)referencedColumns is not null but referencedColumnsInTriggerAction
+ * is null - meaning the trigger is defined on specific columns but trigger
+ * action does not reference any column through old/new transient variables.
+ * Another reason for referencedColumnsInTriggerAction to be null(even though
+ * trigger action does reference columns though old/new transient variables
+ * would be that we are in soft-upgrade mode for pre-10.7 databases and
+ * hence we do not want to write anything about
+ * referencedColumnsInTriggerAction for backward compatibility (DERBY-1482).
+ * eg create trigger tr1 after update of c1 on t1 for each row values(1);
+ * 2)referencedColumns is null but referencedColumnsInTriggerAction is not null
+ * - meaning the trigger is not defined on specific columns but trigger
+ * action references column through old/new transient variables
+ * eg create trigger tr1 after update on t1 referencing old as oldt
+ * for each row values(oldt.id);
+ * 3)referencedColumns and referencedColumnsInTriggerAction are not null -
+ * meaning the trigger is defined on specific columns and trigger action
+ * references column through old/new transient variables
+ * eg create trigger tr1 after update of c1 on t1 referencing old as oldt
+ * for each row values(oldt.id);
+ */
public class ReferencedColumnsDescriptorImpl
implements ReferencedColumns, Formatable
{
@@ -48,6 +70,7 @@ public class ReferencedColumnsDescriptor
********************************************************/
private int[] referencedColumns;
+ private int[] referencedColumnsInTriggerAction;
/**
* Constructor for an ReferencedColumnsDescriptorImpl
@@ -60,10 +83,25 @@ public class ReferencedColumnsDescriptor
this.referencedColumns = referencedColumns;
}
+ /**
+ * Constructor for an ReferencedColumnsDescriptorImpl
+ *
+ * @param referencedColumns The array of referenced columns.
+ * @param referencedColumnsInTriggerAction The array of referenced columns
+ * in trigger action through old/new transition variables.
+ */
+
+ public ReferencedColumnsDescriptorImpl( int[] referencedColumns,
+ int[] referencedColumnsInTriggerAction)
+ {
+ this.referencedColumns = referencedColumns;
+ this.referencedColumnsInTriggerAction = referencedColumnsInTriggerAction;
+ }
+
/** Zero-argument constructor for Formatable interface */
public ReferencedColumnsDescriptorImpl()
{
- }
+ }
/**
* @see ReferencedColumns#getReferencedColumnPositions
*/
@@ -71,36 +109,200 @@ public class ReferencedColumnsDescriptor
{
return referencedColumns;
}
+
+ /**
+ * @see ReferencedColumns#getTriggerActionReferencedColumnPositions
+ */
+ public int[] getTriggerActionReferencedColumnPositions()
+ {
+ return referencedColumnsInTriggerAction;
+ }
/* Externalizable interface */
/**
+ * For triggers, 3 possible scenarios
+ * 1)referencedColumns is not null but referencedColumnsInTriggerAction
+ * is null - then following will get read
+ * referencedColumns.length
+ * individual elements from referencedColumns arrary
+ * eg create trigger tr1 after update of c1 on t1 for each row values(1);
+ * 2)referencedColumns is null but referencedColumnsInTriggerAction is not
+ * null - then following will get read
+ * -1
+ * -1
+ * referencedColumnsInTriggerAction.length
+ * individual elements from referencedColumnsInTriggerAction arrary
+ * eg create trigger tr1 after update on t1 referencing old as oldt
+ * for each row values(oldt.id);
+ * 3)referencedColumns and referencedColumnsInTriggerAction are not null -
+ * then following will get read
+ * -1
+ * referencedColumns.length
+ * individual elements from referencedColumns arrary
+ * referencedColumnsInTriggerAction.length
+ * individual elements from referencedColumnsInTriggerAction arrary
+ * eg create trigger tr1 after update of c1 on t1 referencing old as oldt
+ * for each row values(oldt.id);
+ *
+ * Scenario 1 for triggers is possible for all different releases of dbs
+ * ie both pre-10.7 and 10.7(and higher). But scenarios 2 and 3 are only
+ * possible with database at 10.7 or higher releases. Prior to 10.7, we
+ * did not collect any trigger action column info and hence
+ * referencedColumnsInTriggerAction will always be null for triggers
+ * created prior to 10.7 release.
+ *
* @see java.io.Externalizable#readExternal
*
* @exception IOException Thrown on read error
*/
- public void readExternal(ObjectInput in) throws IOException
+ public void readExternal(ObjectInput in) throws IOException
{
- int rcLength = in.readInt();
- referencedColumns = new int[rcLength];
- for (int i = 0; i < rcLength; i++)
- {
- referencedColumns[i] = in.readInt();
- }
- }
+ int rcLength;
+ int versionNumber = in.readInt();
+
+ //A negative value for versionNumber means that the trigger
+ //action has column references through old/new transient
+ //variables. This will never happen with triggers created
+ //prior to 10.7 because prior to 10.7, we did not collect
+ //such information about trigger action columns.
+ if ( versionNumber < 0 ) {
+ //Now, check if there any trigger columns identified for
+ //this trigger.
+ rcLength = in.readInt();
+ if ( rcLength < 0 ) {
+ //No trigger columns selected for this trigger. This is
+ //trigger scenario 2(as described in method level comments)
+ rcLength = 0;
+ } else {
+ //This trigger has both trigger columns and trigger action
+ //columns. This is trigger scenario 3(as described in
+ //method level comments)
+ referencedColumns = new int[rcLength];
+ }
+ } else {
+ //this trigger only has trigger columns saved on the disc.
+ //This is trigger scenario 1(as described in method level
+ //comments)
+ rcLength = versionNumber;
+ referencedColumns = new int[rcLength];
+ }
+
+ for (int i = 0; i < rcLength; i++)
+ {
+ //if we are in this loop, then it means that this trigger has
+ //been defined on specific columns.
+ referencedColumns[i] = in.readInt();
+ }
+
+ if ( versionNumber < 0 )
+ {
+ //As mentioned earlier, a negative value for versionNumber
+ //means that this trigger action references columns through
+ //old/new transient variables.
+ int rctaLength = in.readInt();
+
+ referencedColumnsInTriggerAction = new int[rctaLength];
+ for (int i = 0; i < rctaLength; i++)
+ {
+ referencedColumnsInTriggerAction[i] = in.readInt();
+ }
+ }
+ }
/**
+ * For triggers, 3 possible scenarios
+ * 1)referencedColumns is not null but referencedColumnsInTriggerAction
+ * is null - then following gets written
+ * referencedColumns.length
+ * individual elements from referencedColumns arrary
+ *
+ * eg create trigger tr1 after update of c1 on t1 for each row values(1);
+ * This can also happen for a trigger like following if the database is
+ * at pre-10.7 level. This is for backward compatibility reasons because
+ * pre-10.7 releases do not collect/work with trigger action column info
+ * in system table. That functionality has been added starting 10.7 release
+ * eg create trigger tr1 after update on t1 referencing old as oldt
+ * for each row values(oldt.id);
+ * 2)referencedColumns is null but referencedColumnsInTriggerAction is not
+ * null - then following gets written
+ * -1
+ * -1
+ * referencedColumnsInTriggerAction.length
+ * individual elements from referencedColumnsInTriggerAction arrary
+ * eg create trigger tr1 after update on t1 referencing old as oldt
+ * for each row values(oldt.id);
+ * 3)referencedColumns and referencedColumnsInTriggerAction are not null -
+ * then following gets written
+ * -1
+ * referencedColumns.length
+ * individual elements from referencedColumns arrary
+ * referencedColumnsInTriggerAction.length
+ * individual elements from referencedColumnsInTriggerAction arrary
+ * eg create trigger tr1 after update of c1 on t1 referencing old as oldt
+ * for each row values(oldt.id);
+ *
* @see java.io.Externalizable#writeExternal
*
* @exception IOException Thrown on write error
*/
- public void writeExternal(ObjectOutput out) throws IOException
- {
- out.writeInt(referencedColumns.length);
- for (int i = 0; i < referencedColumns.length; i++)
- {
- out.writeInt(referencedColumns[i]);
- }
+ public void writeExternal(ObjectOutput out) throws IOException
+ {
+ //null value for referencedColumnsInTriggerAction means one of 2 cases
+ //1)We are working in soft-upgrade mode dealing with databases lower
+ //than 10.7. Prior to 10.7 release, we did not keep track of trigger
+ //action columns
+ //2)We are working with 10.7(and higher) release database and the
+ //trigger action does not reference any column through old/new
+ //transient variables
+
+ //versionNumber will be -1 if referencedColumnsInTriggerAction is not
+ //null, meaning, we are dealing with 10.7 and higher release database
+ //and the trigger has referenced columns in trigger action through
+ //old/new transient variables. Otherwise, versionNumber will be the
+ //length of the arrary referencedColumns. This arrary holds the columns
+ //on which trigger is defined. The detailed meaning of these 2 arrays
+ //is described at the class level comments(towards the beginning of
+ //this class.
+ int versionNumber = referencedColumnsInTriggerAction == null ? referencedColumns.length : -1;
+
+ if ( versionNumber < 0 ) {
+ out.writeInt( versionNumber );
+ //If we are here, then it means that trigger action references
+ //columns through old/new transient variables.
+ //First we will check if there are any trigger columns selected
+ //for this trigger. If yes, we will write information about
+ //trigger columns and if not, then we will write -1 to indicate
+ //that there are no trigger columns selected.
+ //After that, we will write info about trigger action columns.
+ if ( referencedColumns != null ) {
+ writeReferencedColumns(out);
+ } else {
+ out.writeInt(versionNumber);
+ }
+ //Write info about trigger action columns referenced through
+ //old/new transient variables
+ out.writeInt(referencedColumnsInTriggerAction.length);
+ for (int i = 0; i < referencedColumnsInTriggerAction.length; i++)
+ {
+ out.writeInt(referencedColumnsInTriggerAction[i]);
+ }
+ } else {
+ //If we are here, then it means there are no references in
+ //trigger action to old/new transient variables. But, three are
+ //trigger columns selected for this trigger. Write info about
+ //trigger columns
+ writeReferencedColumns(out);
+ }
+ }
+ private void writeReferencedColumns(ObjectOutput out) throws IOException
+ {
+ //trigger is defined on select columns. Write info about those columns
+ out.writeInt( referencedColumns.length );
+ for (int i = 0; i < referencedColumns.length; i++)
+ {
+ out.writeInt(referencedColumns[i]);
+ }
}
/* TypedFormat interface */
@@ -114,6 +316,9 @@ public class ReferencedColumnsDescriptor
*/
public String toString()
{
+ if (referencedColumns == null)
+ return "NULL";
+
StringBuffer sb = new StringBuffer(60);
sb.append('(');
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java Tue Jun 22 02:41:48 2010
@@ -357,6 +357,9 @@ public class DataDescriptorGenerator
* @param actionSPSId the spsid for the trigger action (may be null)
* @param creationTimestamp when was this trigger created?
* @param referencedCols what columns does this trigger reference (may be null)
+ * @param referencedColsInTriggerAction what columns does the trigger
+ * action reference through old/new transition variables
+ * (may be null)
* @param triggerDefinition The original user text of the trigger action
* @param referencingOld whether or not OLD appears in REFERENCING clause
* @param referencingNew whether or not NEW appears in REFERENCING clause
@@ -379,6 +382,7 @@ public class DataDescriptorGenerator
UUID actionSPSId,
Timestamp creationTimestamp,
int[] referencedCols,
+ int[] referencedColsInTriggerAction,
String triggerDefinition,
boolean referencingOld,
boolean referencingNew,
@@ -400,6 +404,7 @@ public class DataDescriptorGenerator
actionSPSId,
creationTimestamp,
referencedCols,
+ referencedColsInTriggerAction,
triggerDefinition,
referencingOld,
referencingNew,
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=956763&r1=956762&r2=956763&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 Tue Jun 22 02:41:48 2010
@@ -75,6 +75,7 @@ import java.io.IOException;
* <li> public TableDescriptor getTableDescriptor()
* <li> public ReferencedColumns getReferencedColumnsDescriptor()
* <li> public int[] getReferencedCols();
+ * <li> public int[] getReferencedColsInTriggerAction();
* <li> public boolean isEnabled();
* <li> public void setEnabled();
* <li> public void setDisabled();
@@ -115,6 +116,7 @@ public class TriggerDescriptor extends T
private SPSDescriptor whenSPS;
private boolean isEnabled;
private int[] referencedCols;
+ private int[] referencedColsInTriggerAction;
private Timestamp creationTimestamp;
private UUID triggerSchemaId;
private UUID triggerTableId;
@@ -141,6 +143,9 @@ public class TriggerDescriptor extends T
* @param actionSPSId the spsid for the trigger action (may be null)
* @param creationTimestamp when was this trigger created?
* @param referencedCols what columns does this trigger reference (may be null)
+ * @param referencedColsInTriggerAction what columns does the trigger
+ * action reference through old/new transition variables
+ * (may be null)
* @param triggerDefinition The original user text of the trigger action
* @param referencingOld whether or not OLD appears in REFERENCING clause
* @param referencingNew whether or not NEW appears in REFERENCING clause
@@ -162,6 +167,7 @@ public class TriggerDescriptor extends T
UUID actionSPSId,
Timestamp creationTimestamp,
int[] referencedCols,
+ int[] referencedColsInTriggerAction,
String triggerDefinition,
boolean referencingOld,
boolean referencingNew,
@@ -181,6 +187,7 @@ public class TriggerDescriptor extends T
this.whenSPSId = whenSPSId;
this.isEnabled = isEnabled;
this.referencedCols = referencedCols;
+ this.referencedColsInTriggerAction = referencedColsInTriggerAction;
this.creationTimestamp = creationTimestamp;
this.triggerDefinition = triggerDefinition;
this.referencingOld = referencingOld;
@@ -397,6 +404,16 @@ public class TriggerDescriptor extends T
}
/**
+ * Get the referenced column array for the trigger action columns.
+ *
+ * @return the referenced column array
+ */
+ public int[] getReferencedColsInTriggerAction()
+ {
+ return referencedColsInTriggerAction;
+ }
+
+ /**
* Is this trigger enabled
*
* @return true if it is enabled
@@ -787,6 +804,15 @@ public class TriggerDescriptor extends T
referencedCols[i] = in.readInt();
}
}
+ length = in.readInt();
+ if (length != 0)
+ {
+ referencedColsInTriggerAction = new int[length];
+ for (int i = 0; i < length; i++)
+ {
+ referencedColsInTriggerAction[i] = in.readInt();
+ }
+ }
triggerDefinition = (String)in.readObject();
referencingOld = in.readBoolean();
referencingNew = in.readBoolean();
@@ -852,6 +878,18 @@ public class TriggerDescriptor extends T
out.writeInt(referencedCols[i]);
}
}
+ if (referencedColsInTriggerAction == null)
+ {
+ out.writeInt(0);
+ }
+ else
+ {
+ out.writeInt(referencedColsInTriggerAction.length);
+ for (int i = 0; i < referencedColsInTriggerAction.length; i++)
+ {
+ out.writeInt(referencedColsInTriggerAction[i]);
+ }
+ }
out.writeObject(triggerDefinition);
out.writeBoolean(referencingOld);
out.writeBoolean(referencingNew);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTRIGGERSRowFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTRIGGERSRowFactory.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTRIGGERSRowFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTRIGGERSRowFactory.java Tue Jun 22 02:41:48 2010
@@ -181,8 +181,9 @@ public class SYSTRIGGERSRowFactory exten
enabled = triggerDescriptor.isEnabled() ? "E" : "D";
tuuid = triggerDescriptor.getTableDescriptor().getUUID();
int[] refCols = triggerDescriptor.getReferencedCols();
- rcd = (refCols != null) ? new
- ReferencedColumnsDescriptorImpl(refCols) : null;
+ int[] refColsInTriggerAction = triggerDescriptor.getReferencedColsInTriggerAction();
+ rcd = (refCols != null || refColsInTriggerAction != null) ? new
+ ReferencedColumnsDescriptorImpl(refCols, refColsInTriggerAction) : null;
actionSPSID = triggerDescriptor.getActionId();
whenSPSID = triggerDescriptor.getWhenClauseId();
@@ -377,7 +378,7 @@ public class SYSTRIGGERSRowFactory exten
// 12th column is REFERENCEDCOLUMNS user type org.apache.derby.catalog.ReferencedColumns
col = row.getColumn(12);
rcd = (ReferencedColumns) col.getObject();
-
+
// 13th column is TRIGGERDEFINITION (longvarhar)
col = row.getColumn(13);
triggerDefinition = col.getString();
@@ -412,6 +413,7 @@ public class SYSTRIGGERSRowFactory exten
actionSPSID,
createTime,
(rcd == null) ? (int[])null : rcd.getReferencedColumnPositions(),
+ (rcd == null) ? (int[])null : rcd.getTriggerActionReferencedColumnPositions(),
triggerDefinition,
referencingOld,
referencingNew,
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=956763&r1=956762&r2=956763&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 Tue Jun 22 02:41:48 2010
@@ -70,7 +70,132 @@ public class CreateTriggerNode extends D
private SchemaDescriptor triggerSchemaDescriptor;
private SchemaDescriptor compSchemaDescriptor;
+
+ /*
+ * The following arrary will include columns that will cause the trigger to
+ * fire. This information will get saved in SYSTRIGGERS.
+ *
+ * The array will be null for all kinds of insert and delete triggers but
+ * it will be non-null for a subset of update triggers.
+ *
+ * For update triggers, the array will be null if no column list is
+ * supplied in the CREATE TRIGGER trigger column clause as shown below.
+ * The UPDATE trigger below will fire no matter which column in table1
+ * gets updated.
+ * eg
+ * CREATE TRIGGER tr1 AFTER UPDATE ON table1
+ * REFERENCING OLD AS oldt NEW AS newt
+ * FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+ *
+ * For update triggers, this array will be non-null if specific trigger
+ * column(s) has been specified in the CREATE TRIGGER sql. The UPDATE
+ * trigger below will fire when an update happens on column c12 in table1.
+ * 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;
+ *
+ * Array referencedColInts along with referencedColsInTriggerAction will
+ * be used to determine which columns from the triggering table need to
+ * be read in when the trigger fires, thus making sure that we do not
+ * read the columns from the trigger table that are not required for
+ * trigger execution.
+ */
private int[] referencedColInts;
+
+ /*
+ * The following array (which was added as part of DERBY-1482) will
+ * include columns referenced in the trigger action through the
+ * REFERENCING clause(old/new transition variables), in other trigger
+ * action columns. This information will get saved in SYSTRIGGERS
+ * (with the exception of triggers created in pre-10.7 dbs. For
+ * pre-10.7 dbs, this information will not get saved in SYSTRIGGERS
+ * in order to maintain backward compatibility.
+ *
+ * Unlike referencedColInts, this array can be non-null for all 3 types
+ * of triggers, namely, INSERT, UPDATE AND DELETE triggers. This array
+ * will be null if no columns in the trigger action are referencing
+ * old/new transition variables
+ *
+ * eg of a trigger in 10.7 and higher dbs which will cause
+ * referencedColsInTriggerAction to be null
+ * CREATE TRIGGER tr1 NO CASCADE BEFORE UPDATE of c12 ON table1
+ * SELECT c24 FROM table2 WHERE table2.c21 = 1
+ *
+ * eg of a trigger in 10.7 and higher dbs which will cause
+ * referencedColsInTriggerAction to be non-null
+ * For the trigger below, old value of column c14 from trigger table is
+ * used in the trigger action through old/new transition variables. A
+ * note of this requirement to read c14 will be made in
+ * referencedColsInTriggerAction array.
+ * eg
+ * CREATE TRIGGER tr1 AFTER UPDATE ON table1
+ * REFERENCING OLD AS oldt NEW AS newt
+ * FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+ *
+ * The exception to the rules above for trigger action columns information
+ * in referencedColsInTriggerAction is a trigger that was created with
+ * pre-10.7 release. Prior to 10.7, we did not collect any information
+ * about trigger action columns. So, any of the 2 kinds of trigger shown
+ * above prior to 10.7 will not have any trigger action column info on
+ * them in SYSTRIGGERS table. In order to cover the pre-existing pre-10.7
+ * triggers and all the other kinds of triggers, we will follow following
+ * 4 rules during trigger execution.
+ * Rule1)If trigger column information is null, then read all the
+ * columns from trigger table into memory irrespective of whether
+ * there is any trigger action column information. 2 egs of such
+ * triggers
+ * create trigger tr1 after update on t1 for each row values(1);
+ * create trigger tr1 after update on t1 referencing old as oldt
+ * for each row insert into t2 values(2,oldt.j,-2);
+ * Rule2)If trigger column information is available but no trigger
+ * action column information is found and no REFERENCES clause is
+ * used for the trigger, then only read the columns identified by
+ * the trigger column. eg
+ * create trigger tr1 after update of c1 on t1
+ * for each row values(1);
+ * Rule3)If trigger column information and trigger action column
+ * information both are not null, then only those columns will be
+ * read into memory. This is possible only for triggers created in
+ * release 10.7 or higher. Because prior to that we did not collect
+ * trigger action column informatoin. eg
+ * create trigger tr1 after update of c1 on t1
+ * referencing old as oldt for each row
+ * insert into t2 values(2,oldt.j,-2);
+ * Rule4)If trigger column information is available but no trigger
+ * action column information is found but REFERENCES clause is used
+ * for the trigger, then read all the columns from the trigger
+ * table. This will cover soft-upgrade and hard-upgrade scenario
+ * for triggers created pre-10.7. This rule prevents us from having
+ * special logic for soft-upgrade. Additionally, this logic makes
+ * invalidation of existing triggers unnecessary during
+ * hard-upgrade. The pre-10.7 created triggers will work just fine
+ * even though for some triggers, they would have trigger action
+ * columns missing from SYSTRIGGERS. A user can choose to drop and
+ * recreate such triggers to take advantage of Rule 3 which will
+ * avoid unnecessary column reads during trigger execution.
+ * eg trigger created prior to 10.7
+ * create trigger tr1 after update of c1 on t1
+ * referencing old as oldt for each row
+ * insert into t2 values(2,oldt.j,-2);
+ * To reiterate, Rule4) is there to cover triggers created with
+ * pre-10,7 releases but now that database has been
+ * hard/soft-upgraded to 10.7 or higher version. Prior to 10.7,
+ * we did not collect any information about trigger action columns.
+ *
+ * The only place we will need special code for soft-upgrade is during
+ * trigger creation. If we are in soft-upgrade mode, we want to make sure
+ * that we do not save information about trigger action columns in
+ * SYSTRIGGERS because the releases prior to 10.7 do not understand
+ * trigger action column information.
+ *
+ * Array referencedColInts along with referencedColsInTriggerAction will
+ * be used to determine which columns from the triggering table needs to
+ * be read in when the trigger fires, thus making sure that we do not
+ * read the columns from the trigger table that are not required for
+ * trigger execution.
+ */
+ private int[] referencedColsInTriggerAction;
private TableDescriptor triggerTableDescriptor;
private UUID actionCompSchemaId;
@@ -336,7 +461,8 @@ public class CreateTriggerNode extends D
}
/*
- ** BIND OLD/NEW TRANSITION TABLES/VARIABLES
+ ** BIND OLD/NEW TRANSITION TABLES/VARIABLES AND collect TRIGGER ACTION
+ ** COLUMNS referenced through REFERECING CLAUSE in CREATE TRIGGER statement
**
** 1) validate the referencing clause (if any)
**
@@ -354,7 +480,15 @@ public class CreateTriggerNode extends D
** variables (since they are no longer 'normal' column references
** that will be checked during bind)
**
- ** 4) reparse the new action text
+ ** 4) 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.
+ **
+ ** 5) reparse the new action text
**
** You might be wondering why we regenerate the text and reparse
** instead of just reworking the tree to have the nodes we want.
@@ -364,7 +498,40 @@ public class CreateTriggerNode extends D
** triggers so it would be quite arduous to figure out that an
** sps is a trigger and munge up its query tree after figuring
** out what its OLD/NEW tables are, etc. Also, it is just plain
- ** easier to just generate the sql and rebind.
+ ** 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
{
@@ -373,16 +540,122 @@ 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;
int start = 0;
if (isRow)
{
- /*
- ** For a row trigger, we find all column references. If
- ** they are referencing NEW or OLD we turn them into
- ** getTriggerExecutionContext().getOldRow().getInt('C');
- */
+ 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.
+ //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
+ // 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();
@@ -390,6 +663,111 @@ public class CreateTriggerNode extends D
*/
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)
+ {
+ 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;
+ }
+ }
+
+ //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];
@@ -434,14 +812,68 @@ public class CreateTriggerNode extends D
}
regenNode = true;
- checkInvalidTriggerReference(tableName.getTableName());
String colName = ref.getColumnName();
int columnLength = ref.getEndOffset() - ref.getBeginOffset() + 1;
newText.append(originalActionText.substring(start, tokBeginOffset-actionOffset));
- newText.append(genColumnReferenceSQL(dd, colName, tableName.getTableName(), tableName.getTableName().equals(oldTableName)));
+ 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);
}
else
{
@@ -489,9 +921,18 @@ public class CreateTriggerNode extends D
newText.append(baseTableName).append(" ");
}
start=tokEndOffset-actionOffset+1;
+ //If we are dealing with statement trigger, then we will read
+ //all the columns from the trigger table since trigger will be
+ //fired for any of the columns in the trigger table.
+ referencedColInts= new int[numberOfColsInTriggerTable];
+ for (int j=0; j < numberOfColsInTriggerTable; j++)
+ referencedColInts[j]=j+1;
}
}
+ if (referencedColsInTriggerAction != null)
+ java.util.Arrays.sort(referencedColsInTriggerAction);
+
/*
** Parse the new action text with the substitutions.
** Also, we reset the actionText to this new value. This
@@ -511,6 +952,33 @@ public class CreateTriggerNode extends D
}
/*
+ * 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) {
+ 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;
+ }
+ /*
** Sort the refs into array.
*/
private QueryTreeNode[] sortRefs(Vector refs, boolean isRow)
@@ -628,7 +1096,8 @@ public class CreateTriggerNode extends D
DataDictionary dd,
String colName,
String tabName,
- boolean isOldTable
+ boolean isOldTable,
+ int colPositionInRuntimeResultSet
) throws StandardException
{
ColumnDescriptor colDesc = null;
@@ -672,7 +1141,7 @@ public class CreateTriggerNode extends D
"CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
methodCall.append(".getObject(");
- methodCall.append(colDesc.getPosition());
+ methodCall.append(colPositionInRuntimeResultSet);
methodCall.append(") AS ");
/*
@@ -720,7 +1189,7 @@ public class CreateTriggerNode extends D
"org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
methodCall.append(".getString(");
- methodCall.append(colDesc.getPosition());
+ methodCall.append(colPositionInRuntimeResultSet);
methodCall.append(") AS CLOB) PRESERVE WHITESPACE ) ");
return methodCall.toString();
@@ -853,6 +1322,7 @@ public class CreateTriggerNode extends D
actionCompSchemaId,
(Timestamp)null, // creation time
referencedColInts,
+ referencedColsInTriggerAction,
originalActionText,
oldTableInReferencingClause,
newTableInReferencingClause,
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java Tue Jun 22 02:41:48 2010
@@ -716,8 +716,8 @@ public class DeleteNode extends DMLModSt
Vector conglomVector = new Vector();
relevantTriggers = new GenericDescriptorList();
- FormatableBitSet columnMap = DeleteNode.getDeleteReadMap(baseTable, conglomVector, relevantTriggers, needsDeferredProcessing );
-
+ FormatableBitSet columnMap = DeleteNode.getDeleteReadMap(baseTable,conglomVector, relevantTriggers, needsDeferredProcessing);
+
markAffectedIndexes( conglomVector );
adjustDeferredFlag( needsDeferredProcessing[0] );
@@ -983,7 +983,8 @@ public class DeleteNode extends DMLModSt
while (descs.hasMoreElements())
{
TriggerDescriptor trd = (TriggerDescriptor) descs.nextElement();
- //Does this trigger have REFERENCING clause defined on it
+ //Does this trigger have REFERENCING clause defined on it.
+ //If yes, then read all the columns from the trigger table.
if (!trd.getReferencingNew() && !trd.getReferencingOld())
continue;
else
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java Tue Jun 22 02:41:48 2010
@@ -938,15 +938,55 @@ public final class UpdateNode extends DM
* 4) finds all constraints which overlap the updated columns
* and adds the constrained columns to the bitmap
* 5) finds all triggers which overlap the updated columns.
- * 6) if there are any UPDATE triggers, then do one of the following
- * a)If all of the triggers have MISSING referencing clause, then that
- * means that the trigger actions do not have access to before and
- * after values. In that case, there is no need to blanketly decide
- * to include all the columns in the read map just because there are
- * triggers defined on the table.
- * b)Since one/more triggers have REFERENCING clause on them, get all
- * the columns because we don't know what the user will ultimately
- * reference.
+ * 6) Go through all those triggers from step 5 and for each one of
+ * those triggers, follow the rules below to decide which columns
+ * should be read.
+ * Rule1)If trigger column information is null, then read all the
+ * columns from trigger table into memory irrespective of whether
+ * there is any trigger action column information. 2 egs of such
+ * triggers
+ * create trigger tr1 after update on t1 for each row values(1);
+ * create trigger tr1 after update on t1 referencing old as oldt
+ * for each row insert into t2 values(2,oldt.j,-2);
+ * Rule2)If trigger column information is available but no trigger
+ * action column information is found and no REFERENCES clause is
+ * used for the trigger, then read all the columns identified by
+ * the trigger column. eg
+ * create trigger tr1 after update of c1 on t1
+ * for each row values(1);
+ * Rule3)If trigger column information and trigger action column
+ * information both are not null, then only those columns will be
+ * read into memory. This is possible only for triggers created in
+ * release 10.7 or higher. Because prior to that we did not collect
+ * trigger action column informatoin. eg
+ * create trigger tr1 after update of c1 on t1
+ * referencing old as oldt for each row
+ * insert into t2 values(2,oldt.j,-2);
+ * Rule4)If trigger column information is available but no trigger
+ * action column information is found but REFERENCES clause is used
+ * for the trigger, then read all the columns from the trigger
+ * table. This will cover soft-upgrade and hard-upgrade scenario
+ * for triggers created pre-10.7. This rule prevents us from having
+ * special logic for soft-upgrade. Additionally, this logic makes
+ * invalidation of existing triggers unnecessary during
+ * hard-upgrade. The pre-10.7 created triggers will work just fine
+ * even though for some triggers, they would have trigger action
+ * columns missing. A user can choose to drop and recreate such
+ * triggers to take advantage of Rule 3 which will avoid unnecssary
+ * column reads during trigger execution.
+ * eg trigger created prior to 10.7
+ * create trigger tr1 after update of c1 on t1
+ * referencing old as oldt for each row
+ * insert into t2 values(2,oldt.j,-2);
+ * To reiterate, Rule4) is there to cover triggers created with
+ * pre-10,7 releases but now that database has been
+ * hard/soft-upgraded to 10.7 or higher version. Prior to 10.7,
+ * we did not collect any information about trigger action columns.
+ * Rule5)The only place we will need special code for soft-upgrade
+ * is during trigger creation. If we are in soft-upgrade mode, we
+ * want to make sure that we do not save information about trigger
+ * action columns in SYSTRIGGERS because the releases prior to 10.7
+ * do not understand trigger action column information.
* 7) adds the triggers to an evolving list of triggers
* 8) finds all generated columns whose generation clauses mention
* the updated columns and adds all of the mentioned columns
@@ -1039,44 +1079,65 @@ public final class UpdateNode extends DM
addGeneratedColumnPrecursors( baseTable, affectedGeneratedColumns, columnMap );
/*
- ** If we have any UPDATE triggers, then do one of the following
- ** 1)If all of the triggers have MISSING referencing clause, then that
- ** means that the trigger actions do not have access to before and
- ** after values. In that case, there is no need to blanketly decide to
- ** include all the columns in the read map just because there are
- ** triggers defined on the table.
- ** 2)Since one/more triggers have REFERENCING clause on them, get all
- ** the columns because we don't know what the user will ultimately reference.
+ * If we have any UPDATE triggers, then we will follow the 4 rules
+ * mentioned in the comments at the method level.
*/
baseTable.getAllRelevantTriggers( StatementType.UPDATE, changedColumnIds, relevantTriggers );
if (relevantTriggers.size() > 0)
- {
+ {
needsDeferredProcessing[0] = true;
-
- boolean needToIncludeAllColumns = false;
Enumeration descs = relevantTriggers.elements();
while (descs.hasMoreElements())
{
TriggerDescriptor trd = (TriggerDescriptor) descs.nextElement();
- //Does this trigger have REFERENCING clause defined on it
- if (!trd.getReferencingNew() && !trd.getReferencingOld())
- continue;
- else
- {
- needToIncludeAllColumns = true;
- break;
- }
- }
+
+ int[] referencedColsInTriggerAction = trd.getReferencedColsInTriggerAction();
+ int[] triggerCols = trd.getReferencedCols();
+ if (triggerCols == null || triggerCols.length == 0) {
+ for (int i=0; i < columnCount; i++) {
+ columnMap.set(i+1);
+ }
+ //no need to go through the test of the trigger because
+ //we have already decided to read all the columns
+ //because no trigger action columns were found for the
+ //trigger that we are considering right now.
+ break;
+ } else {
+ if (referencedColsInTriggerAction == null ||
+ referencedColsInTriggerAction.length == 0) {
+ //Does this trigger have REFERENCING clause defined on it
+ if (!trd.getReferencingNew() && !trd.getReferencingOld()) {
+ for (int ix = 0; ix < triggerCols.length; ix++)
+ {
+ columnMap.set(triggerCols[ix]);
+ }
+ } else {
+ for (int i=0; i < columnCount; i++) {
+ columnMap.set(i+1);
+ }
+ //no need to go through the test of the trigger because
+ //we have already decided to read all the columns
+ //because no trigger action columns were found for the
+ //trigger that we are considering right now.
+ break;
+ }
+ } else {
+ for (int ix = 0; ix < triggerCols.length; ix++)
+ {
+ columnMap.set(triggerCols[ix]);
+ }
+ for (int ix = 0; ix < referencedColsInTriggerAction.length; ix++)
+ {
+ columnMap.set(referencedColsInTriggerAction[ix]);
+ }
+ }
+ }
- if (needToIncludeAllColumns) {
- for (int i = 1; i <= columnCount; i++)
- {
- columnMap.set(i);
- }
}
}
+
return columnMap;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java Tue Jun 22 02:41:48 2010
@@ -81,6 +81,7 @@ class CreateTriggerConstantAction extend
private UUID spsCompSchemaId;
private Timestamp creationTimestamp;
private int[] referencedCols;
+ private int[] referencedColsInTriggerAction;
// CONSTRUCTORS
@@ -104,6 +105,9 @@ class CreateTriggerConstantAction extend
* @param creationTimestamp when was this trigger created? if null, will be
* set to the time that executeConstantAction() is invoked
* @param referencedCols what columns does this trigger reference (may be null)
+ * @param referencedColsInTriggerAction what columns does the trigger
+ * action reference through old/new transition variables
+ * (may be null)
* @param originalActionText The original user text of the trigger action
* @param referencingOld whether or not OLD appears in REFERENCING clause
* @param referencingNew whether or not NEW appears in REFERENCING clause
@@ -126,6 +130,7 @@ class CreateTriggerConstantAction extend
UUID spsCompSchemaId,
Timestamp creationTimestamp,
int[] referencedCols,
+ int[] referencedColsInTriggerAction,
String originalActionText,
boolean referencingOld,
boolean referencingNew,
@@ -148,6 +153,7 @@ class CreateTriggerConstantAction extend
this.spsCompSchemaId = spsCompSchemaId;
this.creationTimestamp = creationTimestamp;
this.referencedCols = referencedCols;
+ this.referencedColsInTriggerAction = referencedColsInTriggerAction;
this.originalActionText = originalActionText;
this.referencingOld = referencingOld;
this.referencingNew = referencingNew;
@@ -304,6 +310,7 @@ class CreateTriggerConstantAction extend
actionSPSId,
creationTimestamp == null ? new Timestamp(System.currentTimeMillis()) : creationTimestamp,
referencedCols,
+ referencedColsInTriggerAction,
originalActionText,
referencingOld,
referencingNew,
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java Tue Jun 22 02:41:48 2010
@@ -952,6 +952,9 @@ public class GenericConstantActionFactor
* @param creationTimestamp when was this trigger created? if null, will be
* set to the time that executeConstantAction() is invoked
* @param referencedCols what columns does this trigger reference (may be null)
+ * @param referencedColsInTriggerAction what columns does the trigger
+ * action reference through old/new transition variables
+ * (may be null)
* @param originalActionText The original user text of the trigger action
* @param referencingOld whether or not OLD appears in REFERENCING clause
* @param referencingNew whether or not NEW appears in REFERENCING clause
@@ -974,6 +977,7 @@ public class GenericConstantActionFactor
UUID spsCompSchemaId,
Timestamp creationTimestamp,
int[] referencedCols,
+ int[] referencedColsInTriggerAction,
String originalActionText,
boolean referencingOld,
boolean referencingNew,
@@ -984,7 +988,7 @@ public class GenericConstantActionFactor
return new CreateTriggerConstantAction(triggerSchemaName, triggerName,
eventMask, isBefore, isRow, isEnabled, triggerTable, whenSPSId,
whenText, actionSPSId, actionText, spsCompSchemaId, creationTimestamp,
- referencedCols, originalActionText,
+ referencedCols, referencedColsInTriggerAction, originalActionText,
referencingOld, referencingNew, oldReferencingName, newReferencingName);
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out Tue Jun 22 02:41:48 2010
@@ -2040,7 +2040,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out Tue Jun 22 02:41:48 2010
@@ -2040,7 +2040,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out Tue Jun 22 02:41:48 2010
@@ -2033,7 +2033,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
@@ -4215,7 +4215,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
@@ -4933,7 +4933,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out?rev=956763&r1=956762&r2=956763&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out Tue Jun 22 02:41:48 2010
@@ -2033,7 +2033,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
@@ -4215,7 +4215,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
@@ -4933,7 +4933,7 @@ E
T8
null
<systemid>
-null
+(1,2)
select * from oldtable
true
false
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=956763&r1=956762&r2=956763&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 Tue Jun 22 02:41:48 2010
@@ -439,6 +439,94 @@ public class TriggerTest extends BaseJDB
s.executeUpdate("INSERT INTO TRADE VALUES(1, 1, 10)");
commit();
}
+
+ //DERBY-1482
+ public void testReadRequiredColumnsOnlyFromTriggerTable() throws SQLException, IOException {
+ Statement s = createStatement();
+
+ s.executeUpdate("CREATE TABLE table1 (c11 int, c12 int, c13 int, c14 int, c15 int)");
+ s.executeUpdate("INSERT INTO table1 VALUES(1,2,3,4,5)");
+ s.executeUpdate("CREATE TABLE table2 (c21 int, c22 int, c23 int, c24 int, c25 int)");
+ s.executeUpdate("INSERT INTO table2 VALUES(2,2,3,-1,5)");
+ //Notice that following trigger references columns from trigger table
+ //randomly ie columns c12 and c14 are not the 1st 2 columns in trigger
+ //table but they will be the first 2 columns in the resultset generated
+ //for the trigger. The internal code generation for CreateTriggerNode
+ //has been written to handle this mismatch of column numbering
+ //between trigger table and trigger runtime resultset
+ s.executeUpdate("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");
+ commit();
+
+ s.executeUpdate("update table1 set c12 = -9 where c11=1");
+ ResultSet rs = s.executeQuery("SELECT * FROM table2");
+ String[][] result = {
+ {"2","2","3","4","5"},
+ };
+ JDBC.assertFullResultSet(rs, result);
+
+ //couple negative test
+ //give invalid column in trigger column
+ String triggerStmt = "CREATE TRIGGER tr1 AFTER UPDATE OF c12xxx ON table1 " +
+ " REFERENCING OLD AS oldt NEW AS newt" +
+ " FOR EACH ROW UPDATE table2 SET c24=oldt.c14";
+ assertStatementError("42X14", s, triggerStmt);
+
+ //give invalid column in trigger action
+ triggerStmt = "CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 " +
+ " REFERENCING OLD AS oldt NEW AS newt" +
+ " FOR EACH ROW UPDATE table2 SET c24=oldt.c14xxx";
+ assertStatementError("42X04", s, triggerStmt);
+
+ //Test case involving before and after values of LOB columns
+ s.executeUpdate("create table derby1482_lob1 (str1 Varchar(80), " +
+ "c_lob CLOB(50M))");
+ s.executeUpdate("create table derby1482_lob1_log(oldvalue CLOB(50M), " +
+ "newvalue CLOB(50M), " +
+ "chng_time timestamp default current_timestamp)");
+ s.executeUpdate("create trigger tr1_derby1482_lob1 after update of c_lob " +
+ "on derby1482_lob1 REFERENCING OLD AS old NEW AS new " +
+ "FOR EACH ROW MODE DB2SQL "+
+ "insert into derby1482_lob1_log(oldvalue, newvalue) values " +
+ "(old.c_lob, new.c_lob)");
+ s.executeUpdate("INSERT INTO derby1482_lob1 VALUES ('1',null)");
+ s.executeUpdate("update derby1482_lob1 set c_lob = null");
+ rs = s.executeQuery("SELECT oldvalue, newvalue FROM derby1482_lob1_log");
+ result = new String [][] {{null, null}};
+ JDBC.assertFullResultSet(rs, result);
+
+ //Test case involving a trigger which updates the trigger table
+ s.executeUpdate("create table derby1482_selfUpdate (i int, j int)");
+ s.executeUpdate("insert into derby1482_selfUpdate values (1,10)");
+ s.executeUpdate("create trigger tr_derby1482_selfUpdate " +
+ "after update of i on derby1482_selfUpdate " +
+ "referencing old as old for each row " +
+ "update derby1482_selfUpdate set j = old.j+1");
+ s.executeUpdate("update derby1482_selfUpdate set i=i+1");
+ rs = s.executeQuery("SELECT * FROM derby1482_selfUpdate");
+ result = new String [][] {{"2","11"}};
+ JDBC.assertFullResultSet(rs, result);
+
+ //Test case where trigger definition uses REFERENCING clause but does
+ //not use those columns in trigger action
+ s.executeUpdate("create table t1_noTriggerActionColumn "+
+ "(id int, status smallint)");
+ s.executeUpdate("insert into t1_noTriggerActionColumn values(11,1)");
+ s.executeUpdate("create table t2_noTriggerActionColumn " +
+ "(id int, updates int default 0)");
+ s.executeUpdate("insert into t2_noTriggerActionColumn values(1,1)");
+ s.executeUpdate("create trigger tr1_noTriggerActionColumn " +
+ "after update of status on t1_noTriggerActionColumn " +
+ "referencing new as n_row for each row " +
+ "update t2_noTriggerActionColumn set " +
+ "updates = updates + 1 " +
+ "where t2_noTriggerActionColumn.id = 1");
+ s.executeUpdate("update t1_noTriggerActionColumn set status=-1");
+ rs =s.executeQuery("SELECT * FROM t2_noTriggerActionColumn");
+ result = new String [][] {{"1","2"}};
+ JDBC.assertFullResultSet(rs, result);
+ }
/**
* Test for DERBY-3238 trigger fails with IOException if triggering table has large lob.
@@ -501,7 +589,7 @@ public class TriggerTest extends BaseJDB
s.executeUpdate("drop table lob1");
s.executeUpdate("drop table t_lob1_log");
-
+
// now referencing the lob column
trig = " create trigger t_lob1 after update of c_lob on lob1 ";
trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";