Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1068164 - in /db/derby/code/branches/10.7: ./
 java/engine/org/apache/derby/impl/sql/execute/
 java/testing/org/apache/derbyTesting/functionTests/tests/lang/
 java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/
Date: Mon, 07 Feb 2011 22:08:02 -0000
To: derby-commits@db.apache.org
From: mamta@apache.org
Message-Id: <20110207220802.2C46723888D2@eris.apache.org>

Author: mamta
Date: Mon Feb  7 22:08:01 2011
New Revision: 1068164

URL: http://svn.apache.org/viewvc?rev=1068164&view=rev
Log:
DERBY-4988 ALTER TABLE DROP COLUMN should make use of information in SYSTRIGGERS to detect column used through REFERENCING clause to find trigger dependencies

Backporting 1066290 from trunk to 10.7 codeline


Modified:
    db/derby/code/branches/10.7/   (props changed)
    db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
    db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java

Propchange: db/derby/code/branches/10.7/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Mon Feb  7 22:08:01 2011
@@ -1 +1 @@
-/db/derby/code/trunk:1035603,1036769,1038514,1038813,1039084,1039268,1040658,1041338,1043227,1043389,1044096,1051026,1053724,1055169,1059888,1060480,1062096,1063809,1065061,1067250
+/db/derby/code/trunk:1035603,1036769,1038514,1038813,1039084,1039268,1040658,1041338,1043227,1043389,1044096,1051026,1053724,1055169,1059888,1060480,1062096,1063809,1065061,1066290,1067250

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=1068164&r1=1068163&r2=1068164&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Mon Feb  7 22:08:01 2011
@@ -1530,24 +1530,96 @@ class AlterTableConstantAction extends D
                 lcc, columnDescriptor.getDefaultDescriptor(dd));
 		}
 
-		// need to deal with triggers if has referencedColumns
+		//Now go through each trigger on this table and see if the column 
+		//being dropped is part of it's trigger columns or trigger action 
+		//columns which are used through REFERENCING clause
 		GenericDescriptorList tdl = dd.getTriggerDescriptors(td);
 		Enumeration descs = tdl.elements();
 		while (descs.hasMoreElements())
 		{
 			TriggerDescriptor trd = (TriggerDescriptor) descs.nextElement();
+			//If we find that the trigger is dependent on the column being 
+			//dropped because column is part of trigger columns list, then
+			//we will give a warning or drop the trigger based on whether
+			//ALTER TABLE DROP COLUMN is RESTRICT or CASCADE. In such a
+			//case, no need to check if the trigger action columns referenced
+			//through REFERENCING clause also used the column being dropped.
+			boolean triggerDroppedAlready = false;
+
 			int[] referencedCols = trd.getReferencedCols();
-			if (referencedCols == null)
+			if (referencedCols != null) {
+				int refColLen = referencedCols.length, j;
+				boolean changed = false;
+				for (j = 0; j < refColLen; j++)
+				{
+					if (referencedCols[j] > droppedColumnPosition)
+	                {
+						//Trigger is not defined on the column being dropped
+						//but the column position of trigger column is changing
+						//because the position of the column being dropped is
+						//before the the trigger column
+						changed = true;
+	                }
+					else if (referencedCols[j] == droppedColumnPosition)
+					{
+						//the trigger is defined on the column being dropped
+						if (cascade)
+						{
+	                        trd.drop(lcc);
+	                        triggerDroppedAlready = true;
+							activation.addWarning(
+								StandardException.newWarning(
+	                                SQLState.LANG_TRIGGER_DROPPED, 
+	                                trd.getName(), td.getName()));
+						}
+						else
+						{	// we'd better give an error if don't drop it,
+							// otherwsie there would be unexpected behaviors
+							throw StandardException.newException(
+	                            SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
+	                            dm.getActionString(DependencyManager.DROP_COLUMN),
+	                            columnName, "TRIGGER",
+	                            trd.getName() );
+						}
+						break;
+					}
+				}
+
+				// change triggers to refer to columns in new positions
+				if (j == refColLen && changed)
+				{
+					dd.dropTriggerDescriptor(trd, tc);
+					for (j = 0; j < refColLen; j++)
+					{
+						if (referencedCols[j] > droppedColumnPosition)
+							referencedCols[j]--;
+					}
+					dd.addDescriptor(trd, sd,
+									 DataDictionary.SYSTRIGGERS_CATALOG_NUM,
+									 false, tc);
+				}
+			}
+
+			//If the trigger under consideration already got dropped through 
+			//the referencedCols loop above, then move to next trigger
+			if (triggerDroppedAlready) continue;
+			
+			//None of the triggers use column being dropped as a trigger 
+			//column. Check if the column being dropped is getting used 
+			//inside the trigger action through REFERENCING clause.
+			int[] referencedColsInTriggerAction = trd.getReferencedColsInTriggerAction();
+			if (referencedColsInTriggerAction == null)
 				continue;
-			int refColLen = referencedCols.length, j;
-			boolean changed = false;
-			for (j = 0; j < refColLen; j++)
+
+			int refColInTriggerActionLen = referencedColsInTriggerAction.length, j;
+			boolean changedColPositionInTriggerAction = false;
+			for (j = 0; j < refColInTriggerActionLen; j++)
 			{
-				if (referencedCols[j] > droppedColumnPosition)
-                {
-					changed = true;
-                }
-				else if (referencedCols[j] == droppedColumnPosition)
+				if (referencedColsInTriggerAction[j] > droppedColumnPosition)
+				{
+					changedColPositionInTriggerAction = true;
+				}
+				else if (referencedColsInTriggerAction[j] == droppedColumnPosition)
 				{
 					if (cascade)
 					{
@@ -1559,7 +1631,7 @@ class AlterTableConstantAction extends D
 					}
 					else
 					{	// we'd better give an error if don't drop it,
-						// otherwsie there would be unexpected behaviors
+						// otherwise there would be unexpected behaviors
 						throw StandardException.newException(
                             SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
                             dm.getActionString(DependencyManager.DROP_COLUMN),
@@ -1569,20 +1641,6 @@ class AlterTableConstantAction extends D
 					break;
 				}
 			}
-
-			// change triggers to refer to columns in new positions
-			if (j == refColLen && changed)
-			{
-				dd.dropTriggerDescriptor(trd, tc);
-				for (j = 0; j < refColLen; j++)
-				{
-					if (referencedCols[j] > droppedColumnPosition)
-						referencedCols[j]--;
-				}
-				dd.addDescriptor(trd, sd,
-								 DataDictionary.SYSTRIGGERS_CATALOG_NUM,
-								 false, tc);
-			}
 		}
 
 		ConstraintDescriptorList csdl = dd.getConstraintDescriptors(td);

Modified: db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1068164&r1=1068163&r2=1068164&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java (original)
+++ db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java Mon Feb  7 22:08:01 2011
@@ -2206,31 +2206,214 @@ public final class AlterTableTest extend
 
         assertStatementError("42X05", st, "select * from atdc_vw_5a_2");
 
-        // drop column restrict should fail because column is used 
-        // in a trigger:
-
+        // Another test
+        // drop column restrict should fail because trigger is defined to 
+        // fire on the update of the column. But cascade should succeed
+        // and drop the dependent trigger
         st.executeUpdate("create table atdc_6 (a integer, b integer)");
-
         st.executeUpdate(
                 " create trigger atdc_6_trigger_1 after update of b " +
                 "on atdc_6 for each row values current_date");
 
         assertStatementError("X0Y25", st,
                 " alter table atdc_6 drop column b restrict");
-
         rs =
                 st.executeQuery(
                 " select triggername from sys.systriggers where " +
                 "triggername='ATDC_6_TRIGGER_1'");
         JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_6_TRIGGER_1"}});
 
+        //CASCADE will drop the dependent trigger
         st.executeUpdate("alter table atdc_6 drop column b cascade");
         checkWarning(st, "01502");
-
         JDBC.assertEmpty(st.executeQuery(
                 " select triggername from sys.systriggers where " +
                 "triggername='ATDC_6_TRIGGER_1'"));
 
+        // Another test
+        // drop column restrict should fail because trigger is defined to 
+        // fire on the update of the column and it is also used in trigger
+        // action. But cascade should succeed and drop the dependent trigger
+        st.executeUpdate("create table atdc_11 (a integer, b integer)");
+        st.executeUpdate(
+                " create trigger atdc_11_trigger_1 after update of b " +
+                "on atdc_11 for each row select a,b from atdc_11");
+
+        assertStatementError("X0Y25", st,
+                " alter table atdc_11 drop column b restrict");
+        rs =
+                st.executeQuery(
+                " select triggername from sys.systriggers where " +
+                "triggername='ATDC_11_TRIGGER_1'");
+        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_11_TRIGGER_1"}});
+
+        //CASCADE will drop the dependent trigger
+        st.executeUpdate("alter table atdc_11 drop column b cascade");
+        checkWarning(st, "01502");
+        JDBC.assertEmpty(st.executeQuery(
+                " select triggername from sys.systriggers where " +
+                "triggername='ATDC_11_TRIGGER_1'"));
+
+        // Another test
+        // drop column restrict should fail because trigger uses the column 
+        // inside the trigger action. DERBY-4887. Currently, Derby does not
+        // look at the columns being used inside the trigger action unless
+        // they are being used through the REFERENCING clause and hence does 
+        // not catch the trigger dependencies
+        st.executeUpdate("create table atdc_12 (a integer, b integer)");
+        //Following is not going to be caught by the information available
+        //in systriggers even in 10.7 and higher. We only keep the information
+        //about the columns used through REFERENCING clause.
+        st.executeUpdate(
+                " create trigger atdc_12_trigger_1 after update of a " +
+                "on atdc_12 for each row select a,b from atdc_12");
+        //Following will be caught because of the information available in 
+        //systriggers in 10.7 and higher because we keep the information 
+        //about the columns used through REFERENCING clause.
+        st.executeUpdate(
+                " create trigger atdc_12_trigger_2 " +
+                " after update of a on atdc_12" +
+                " REFERENCING NEW AS newt OLD AS oldt "+
+                " for each row select oldt.b from atdc_12");
+
+        // We got an error because Derby detected the dependency on 
+        // atdc_12_trigger_2
+        assertStatementError("X0Y25", st,
+        		"alter table atdc_12 drop column b restrict");
+        rs =
+                st.executeQuery(
+                " select triggername from sys.systriggers where " +
+                "triggername in ('ATDC_12_TRIGGER_1', 'ATDC_12_TRIGGER_2')");
+        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_12_TRIGGER_1"},
+        		{"ATDC_12_TRIGGER_2"}});
+
+        //Now try ALTER TABLE DROP COLUMN CASCADE where the column being
+        //dropped is in trigger action but is not part of the trigger
+        //column list
+        st.executeUpdate("alter table atdc_12 drop column b");
+        checkWarning(st, "01502");
+        // the 2 triggers should have been dropped as a result of cascade but
+        // only one gets dropped. Derby does not recognize the dependency of 
+        // trigger action column where the column is not getting referenced
+        // through REFERENCING clause
+        rs =
+            st.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername = 'ATDC_12_TRIGGER_1'");
+            JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_12_TRIGGER_1"}});
+
+        // Another test
+        // drop column restrict should fail because there is a table level
+        // trigger defined with the column being dropped in it's trigger
+        // action. Currently, Derby does not look at the columns being used
+        // inside the trigger action and hence does not catch the trigger 
+        // dependency unless they are being referenced through REFERENCING
+        // clause. Similarly, drop column cascade should drop this table
+        // level trigger because it is using the colunm in it's trigger 
+        // action but Derby does not catch that. DERBY-4887.
+        st.executeUpdate("create table atdc_13 (a integer, b integer)");
+        st.executeUpdate(
+                " create trigger atdc_13_trigger_1 after update " +
+                "on atdc_13 for each row select a,b from atdc_13");
+        st.executeUpdate(
+                " create trigger atdc_13_trigger_2 after insert " +
+                "on atdc_13 for each row select a,b from atdc_13");
+        st.executeUpdate(
+                " create trigger atdc_13_trigger_3 after delete " +
+                "on atdc_13 for each row select a,b from atdc_13");
+        st.executeUpdate(
+                " create trigger atdc_13_trigger_4 after update on atdc_13 " +
+                " REFERENCING NEW AS newt OLD AS oldt "+
+                " for each row select oldt.b, newt.b from atdc_13");
+        st.executeUpdate(
+                " create trigger atdc_13_trigger_5 after insert on atdc_13 " +
+                " REFERENCING NEW AS newt "+
+                " for each row select newt.b from atdc_13");
+        st.executeUpdate(
+                " create trigger atdc_13_trigger_6 after delete on atdc_13 " +
+                " REFERENCING OLD AS oldt "+
+                " for each row select oldt.b from atdc_13");
+
+        assertStatementError("X0Y25", st,
+        		"alter table atdc_13 drop column b restrict");
+        rs =
+                st.executeQuery(
+                " select triggername from sys.systriggers where " +
+                "triggername in ('ATDC_13_TRIGGER_1', "+
+                "'ATDC_13_TRIGGER_2', 'ATDC_13_TRIGGER_3'," +
+                "'ATDC_13_TRIGGER_4', 'ATDC_13_TRIGGER_5'," +
+                "'ATDC_13_TRIGGER_6')");
+        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_13_TRIGGER_1"},
+            	{"ATDC_13_TRIGGER_2"}, {"ATDC_13_TRIGGER_3"},
+            	{"ATDC_13_TRIGGER_4"}, {"ATDC_13_TRIGGER_5"},
+            	{"ATDC_13_TRIGGER_6"}});
+        
+        // following is not the right behavior. Derby should have dropped
+        // all the 6 triggers but it drops only 3. Other 3 didn't get
+        // dropped because Derby does not recognize the dependency of 
+        // trigger action column where the column is not getting referenced
+        // through REFERENCING clause
+        st.executeUpdate("alter table atdc_13 drop column b");
+        checkWarning(st, "01502");
+        // the triggers should have been dropped as a result of cascade but
+        // Derby does not recognize the dependency of trigger action column
+        rs =
+            st.executeQuery(
+            " select triggername from sys.systriggers where " +
+            "triggername in ('ATDC_13_TRIGGER_1', "+
+            "'ATDC_13_TRIGGER_2', 'ATDC_13_TRIGGER_3')");
+        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_13_TRIGGER_1"},
+            	{"ATDC_13_TRIGGER_2"}, {"ATDC_13_TRIGGER_3"}});
+        
+        // Another test
+        //Following test case involves two tables. The trigger is defined 
+        //on table 1 and it uses the column from table 2 in it's trigger  
+    	//action. This dependency of the trigger on a column from another 
+        //table is not detected by Derby.
+        st.executeUpdate("create table atdc_14_tab1 (a1 integer, b1 integer)");
+        st.executeUpdate("create table atdc_14_tab2 (a2 integer, b2 integer)");        
+        st.executeUpdate("insert into atdc_14_tab1 values(1,11)");
+        st.executeUpdate("insert into atdc_14_tab2 values(1,11)");
+        st.executeUpdate(
+                " create trigger atdc_14_trigger_1 after update " +
+                "on atdc_14_tab1 REFERENCING NEW AS newt " +
+                "for each row " +
+                "update atdc_14_tab2 set a2 = newt.a1");
+
+        // following is not the right behavior. we should have gotten an error
+        // because column being dropped is getting used in a trigger action 
+        st.executeUpdate("alter table atdc_14_tab2 drop column a2 restrict");
+        rs =
+                st.executeQuery(
+                " select triggername from sys.systriggers where " +
+                "triggername = 'ATDC_14_TRIGGER_1' ");
+        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_14_TRIGGER_1"}});
+
+        //Now try ALTER TABLE DROP COLUMN CASCADE where the column being
+        //dropped is in trigger action of trigger defined on a different table
+        st.executeUpdate("drop trigger atdc_14_trigger_1");
+        st.executeUpdate("drop table atdc_14_tab1");
+        st.executeUpdate("drop table atdc_14_tab2");
+        st.executeUpdate("create table atdc_14_tab1 (a1 integer, b1 integer)");
+        st.executeUpdate("create table atdc_14_tab2 (a2 integer, b2 integer)");
+        st.executeUpdate("insert into atdc_14_tab1 values(1,11)");
+        st.executeUpdate("insert into atdc_14_tab2 values(1,11)");
+        st.executeUpdate(
+                " create trigger atdc_14_trigger_1 after update " +
+                "on atdc_14_tab1 REFERENCING NEW AS newt " +
+                "for each row " +
+                "update atdc_14_tab2 set a2 = newt.a1");
+
+        // following is not the right behavior. we should have gotten an error
+        // because column being dropped is getting used in a trigger action 
+        st.executeUpdate("alter table atdc_14_tab2 drop column a2");
+        rs =
+                st.executeQuery(
+                " select triggername from sys.systriggers where " +
+                "triggername = 'ATDC_14_TRIGGER_1' ");
+        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_14_TRIGGER_1"}});
+        
+
         st.executeUpdate(
                 " create table atdc_7 (a int, b int, c int, primary key (a))");
 

Modified: db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java?rev=1068164&r1=1068163&r2=1068164&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java (original)
+++ db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java Mon Feb  7 22:08:01 2011
@@ -23,6 +23,7 @@ package org.apache.derbyTesting.function
 import org.apache.derbyTesting.junit.SupportFilesSetup;
 
 import java.sql.SQLException;
+import java.sql.SQLWarning;
 import java.sql.Statement;
 import java.sql.ResultSet;
 import java.util.HashSet;
@@ -151,6 +152,290 @@ public class Changes10_7 extends Upgrade
         rs.close();
         return types;
     }
+    
+    /**
+     * Make sure that DERBY-4998 changes do not break backward compatibility.
+     * 
+     * It creates triggers in old release with trigger action columns getting
+     * used through the REFERENCING clause. Those triggers in soft upgrade
+     * mode, post soft upgrade mode and hard upgrade mode do not get detected 
+     * by ALTER TABLE DROP COLUMN because prior to 10.7, we did not keep 
+     * information about trigger action columns getting used through the 
+     * REFERENCING clause.
+     */
+    public void testAlterTableDropColumnAndTriggerAction() throws Exception
+    {
+    	if (!oldAtLeast(10, 3)) return;
+    	
+        Statement s = createStatement();
+        ResultSet rs;
+
+        switch ( getPhase() )
+        {
+        case PH_CREATE: // create with old version
+        	//Create 2 tables for each of the upgrade phases which are 
+        	//a)soft upgrade b)post soft upgrade and c)hard upgrade
+        	//For each of the upgrade phase, one table will be used for 
+        	//ALTER TABLE DROP COLUMN RESTRICT and the second table will
+        	//be used for ALTER TABLE DROP COLUMN CASCADE
+
+        	//Following 2 tables and triggers are for soft upgrade mode check
+        	s.execute("CREATE TABLE DERBY4998_SOFT_UPGRADE_RESTRICT(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_SOFT_UPGRADE_RESTRICT VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_SOFT_UPGRADE_RESTRICT_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_SOFT_UPGRADE_RESTRICT REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_RESTRICT");
+            s.executeUpdate("UPDATE DERBY4998_SOFT_UPGRADE_RESTRICT SET c12=c12+1");
+
+            s.execute("CREATE TABLE DERBY4998_soft_upgrade_cascade(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_soft_upgrade_cascade VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_soft_upgrade_cascade_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_soft_upgrade_cascade REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_soft_upgrade_cascade");
+            s.executeUpdate("UPDATE DERBY4998_soft_upgrade_cascade SET c12=c12+1");
+
+        	//Following 2 tables and triggers are for post-soft upgrade mode
+            //check
+            s.execute("CREATE TABLE DERBY4998_postsoft_upgrade_restrict(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_postsoft_upgrade_restrict VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_postsoft_upgrade_restrict_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_postsoft_upgrade_restrict REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_postsoft_upgrade_restrict");
+            s.executeUpdate("UPDATE DERBY4998_postsoft_upgrade_restrict SET c12=c12+1");
+
+            s.execute("CREATE TABLE DERBY4998_postsoft_upgrade_cascade(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_postsoft_upgrade_cascade VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_postsoft_upgrade_cascade_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_postsoft_upgrade_cascade REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_postsoft_upgrade_cascade");
+            s.executeUpdate("UPDATE DERBY4998_postsoft_upgrade_cascade SET c12=c12+1");
+
+        	//Following 2 tables and triggers are for hard upgrade mode check
+            s.execute("CREATE TABLE DERBY4998_hard_upgrade_restrict(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_hard_upgrade_restrict VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_hard_upgrade_restrict_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_hard_upgrade_restrict REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_hard_upgrade_restrict");
+            s.executeUpdate("UPDATE DERBY4998_hard_upgrade_restrict SET c12=c12+1");
+
+            s.execute("CREATE TABLE DERBY4998_hard_upgrade_cascade(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_hard_upgrade_cascade VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_hard_upgrade_cascade_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_hard_upgrade_cascade REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_hard_upgrade_cascade");
+            s.executeUpdate("UPDATE DERBY4998_hard_upgrade_cascade SET c12=c12+1");
+            break;
+
+        case PH_SOFT_UPGRADE: // boot with new version and soft-upgrade
+        	//The tables created with 10.6 and prior versions will exhibit
+        	//incorrect behavoir
+        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_RESTRICT",
+        			"DERBY4998_SOFT_UPGRADE_RESTRICT_TR1", "RESTRICT");
+        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_CASCADE",
+        			"DERBY4998_SOFT_UPGRADE_CASCADE_TR1", "CASCADE");
+
+        	//Even though we are in soft upgrade mode using Derby 10.7 release,
+        	//newly created triggers will still not keep track of trigger
+        	//action columns referenced through REFERENCING clause because
+        	//that will break the backward compatibility when this db gets
+        	//used with earlier Derby version again after soft upgrade.
+        	//Show this with an example
+            s.execute("CREATE TABLE DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT");
+            s.executeUpdate("UPDATE DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT SET c12=c12+1");
+        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
+        			"DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", "RESTRICT");
+        	
+            s.execute("CREATE TABLE DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE");
+            s.executeUpdate("UPDATE DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE SET c12=c12+1");
+        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE",
+        			"DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", "RESTRICT");
+            break;
+
+        case PH_POST_SOFT_UPGRADE: 
+        	// soft-downgrade: boot with old version after soft-upgrade
+
+        	//The tables created with 10.6 and prior versions will continue to 
+        	//exhibit incorrect behavoir
+        	incorrectBehaviorForDropColumn("DERBY4998_POSTSOFT_UPGRADE_RESTRICT",
+        			"DERBY4998_POSTSOFT_UPGRADE_RESTRICT_TR1", "RESTRICT");
+        	incorrectBehaviorForDropColumn("DERBY4998_POSTSOFT_UPGRADE_CASCADE",
+        			"DERBY4998_POSTSOFT_UPGRADE_CASCADE_TR1", "CASCADE");
+        	
+        	//We are back to pre-10.7 version after the soft upgrade. 
+        	//ALTER TABLE DROP COLUMN will continue to behave incorrectly
+        	//and will not detect the trigger actions referencing the column
+        	//being dropped through the REFERENCING clause
+            s.execute("CREATE TABLE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT");
+            s.executeUpdate("UPDATE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT SET c12=c12+1");
+        	incorrectBehaviorForDropColumn("DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
+        			"DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", "RESTRICT");
+        	
+            s.execute("CREATE TABLE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE");
+            s.executeUpdate("UPDATE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE SET c12=c12+1");
+        	incorrectBehaviorForDropColumn("DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE",
+        			"DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", "RESTRICT");
+            break;
+
+        case PH_HARD_UPGRADE: // boot with new version and hard-upgrade
+        	//The tables created with 10.6 and prior versions will exhibit
+        	//incorrect behavior. Even though the database is at 10.7 level,
+        	//the triggers created with prior Derby releases did not keep
+        	//track of trigger action columns referenced through REFERENCING
+        	//clause.
+        	incorrectBehaviorForDropColumn("DERBY4998_HARD_UPGRADE_RESTRICT",
+        			"DERBY4998_HARD_UPGRADE_RESTRICT_TR1", "RESTRICT");
+        	incorrectBehaviorForDropColumn("DERBY4998_HARD_UPGRADE_CASCADE",
+        			"DERBY4998_HARD_UPGRADE_CASCADE_TR1", "CASCADE");
+        	
+        	//Create 2 new tables now that the database has been upgraded to 
+        	//10.7 Notice that newly created tables will be able to detect
+        	//trigger action reference to column through REFERENCING clause.
+        	//This is because starting 10.7, for new triggers, we have
+        	//started keeping track of trigger action columns referenced 
+        	//through REFERENCING clause.
+            s.execute("CREATE TABLE DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT_tr1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT");
+            s.executeUpdate("UPDATE DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT SET c12=c12+1");
+            assertStatementError("X0Y25", s,
+            		" alter table DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT " +
+            		" drop column c11 restrict");
+            rs = s.executeQuery(
+            " select triggername from sys.systriggers where " +
+            "triggername='DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs, 
+            		new String[][]{{"DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1"}});
+            s.execute("CREATE TABLE DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE(c11 int, c12 int)");
+            s.execute("INSERT INTO DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE VALUES (1,10)");
+            //Create a trigger in the older release where the database has been
+            //created. The trigger action uses a column in trigger action
+            //through REFERENCING clause
+            s.execute("CREATE TRIGGER DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1 " +
+            		"AFTER UPDATE OF c12 " +
+            		"ON DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE REFERENCING OLD AS oldt " +
+            		"FOR EACH ROW " +
+                    "SELECT oldt.c11 from DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE");
+            s.executeUpdate("UPDATE DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE SET c12=c12+1");
+            s.executeUpdate("alter table DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE " +
+            		" drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            JDBC.assertEmpty(s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1'"));
+            break;
+        }
+    }
+
+    //ALTER TABLE DROP COLUMN in not detected the trigger column dependency for
+    //columns being used through the REFERENCING clause for triggers created
+    //prior to 10.7 release
+    private void incorrectBehaviorForDropColumn(String tableName,
+    		String triggerName, String restrictOrCascade) throws SQLException {
+        Statement s = createStatement();
+        ResultSet rs;
+        
+        //ALTER TABLE DROP COLUMN of a column used in the trigger action
+        //through REFERENCING clause does not detect the trigger 
+        //dependency in older releases.
+        //RESTRICT won't give any error for dependent trigger and will
+        //drop column c11 even though it is getting used in trigger action
+        //and will leave the invalid trigger in the system. 
+        //CASCADE won't give any warning for dependent trigger and will
+        //drop column c11 even though it is getting used in trigger action
+        //and will leave the invalid trigger in the system. 
+        s.executeUpdate("ALTER TABLE " + tableName + " DROP COLUMN c11 " +
+        		restrictOrCascade);
+        rs =
+            s.executeQuery(
+            " select triggername from sys.systriggers where " +
+            "triggername='" + triggerName + "'");
+        JDBC.assertFullResultSet(rs, new String[][]{{triggerName}});
+    }
+
+    private void checkWarning(Statement st, String expectedWarning)
+            throws Exception {
+        SQLWarning sqlWarn = null;
+
+        sqlWarn = st.getWarnings();
+        if (sqlWarn == null) {
+            sqlWarn = getConnection().getWarnings();
+        }
+        assertNotNull("Expected warning but found none", sqlWarn);
+        assertSQLState(expectedWarning, sqlWarn);
+    }
 
     /**
      * Make sure that DERBY-1482 changes do not break backward compatibility