db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1179653 [1/2] - in /db/derby/code/branches/10.8: ./ 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 Thu, 06 Oct 2011 14:51:11 GMT
Author: mamta
Date: Thu Oct  6 14:51:10 2011
New Revision: 1179653

URL: http://svn.apache.org/viewvc?rev=1179653&view=rev
Log:
DERBY-5044 ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their trigger action using the column being dropped

Backporting to 10.8


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

Propchange: db/derby/code/branches/10.8/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Thu Oct  6 14:51:10 2011
@@ -1,2 +1,2 @@
 /db/derby/code/branches/10.7:1061570,1061578,1082235
-/db/derby/code/trunk:1063809,1088633,1089795,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1095247,1096741,1096890,1096991,1097247,1097249,1097460,1097469,1097471,1098033,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1104365,1125305,1126358,1126468,1127825,1127883,1128243,1128942,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132747,1132860,1132928,1133304,1133317,1133741,1133752,1134139,1136363,1136371,1136397,1136844,1137213,1138201,1138341,1138444,1138787,1138795,1139449,1139451,1140222,1140744,1141924,1142583,1142635,1145057,1146644,1146915,1146962,1147219,1147242,1147335,1148344,1148354,1148429,1148658,1149054,1149090,1149270,1149482,1149662,1151101,1151612,1158108,1160593,1160597,1161208,1162737,1163131,1163616,1164358,1164370,1164495,1165221,1167017,1167470,1169692,1171665,1171672,1173446,1173647,1174436,1174646,1176633,1176636,1177589
+/db/derby/code/trunk:1063809,1088633,1089795,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1095247,1096741,1096890,1096991,1097247,1097249,1097460,1097469,1097471,1098033,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1104365,1125305,1126358,1126468,1127825,1127883,1128243,1128942,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132747,1132860,1132928,1133304,1133317,1133741,1133752,1134139,1136363,1136371,1136397,1136844,1137213,1138201,1138341,1138444,1138787,1138795,1139449,1139451,1140222,1140744,1141924,1142583,1142635,1145057,1146644,1146915,1146962,1147219,1147242,1147335,1148344,1148354,1148429,1148658,1149054,1149090,1149270,1149482,1149662,1151101,1151612,1158108,1160593,1160597,1161208,1162737,1163131,1163616,1164358,1164370,1164495,1165221,1166313,1167017,1167226,1167470,1169692,1171227,1171665,1171672,1173446,1173647,1174436,1174646,1176633,1176636,1177589

Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=1179653&r1=1179652&r2=1179653&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Thu Oct  6 14:51:10 2011
@@ -29,6 +29,7 @@ import java.util.Properties;
 import java.util.Vector;
 
 import org.apache.derby.catalog.DefaultInfo;
+import org.apache.derby.catalog.Dependable;
 import org.apache.derby.catalog.DependableFinder;
 import org.apache.derby.catalog.IndexDescriptor;
 import org.apache.derby.catalog.UUID;
@@ -1672,38 +1673,82 @@ class AlterTableConstantAction extends D
 		dd.addDescriptorArray(cdlArray, td,
 							  DataDictionary.SYSCOLUMNS_CATALOG_NUM, false, tc);
 
-		List deps = dd.getProvidersDescriptorList(td.getObjectID().toString());
-		for (Iterator depsIterator = deps.listIterator(); 
-             depsIterator.hasNext();)
-		{
-			DependencyDescriptor depDesc = 
-                (DependencyDescriptor) depsIterator.next();
-
-			DependableFinder finder = depDesc.getProviderFinder();
-			if (finder instanceof DDColumnDependableFinder)
-			{
-				DDColumnDependableFinder colFinder = 
-                    (DDColumnDependableFinder) finder;
-				FormatableBitSet oldColumnBitMap = 
-                    new FormatableBitSet(colFinder.getColumnBitMap());
-				FormatableBitSet newColumnBitMap = 
-                    new FormatableBitSet(oldColumnBitMap);
-				newColumnBitMap.clear();
-				int bitLen = oldColumnBitMap.getLength();
-				for (int i = 0; i < bitLen; i++)
-				{
-					if (i < droppedColumnPosition && oldColumnBitMap.isSet(i))
-						newColumnBitMap.set(i);
-					if (i > droppedColumnPosition && oldColumnBitMap.isSet(i))
-						newColumnBitMap.set(i - 1);
+		// By this time, the column has been removed from the table descriptor.
+		// Now, go through all the triggers and regenerate their trigger action
+		// SPS and rebind the generated trigger action sql. If the trigger  
+		// action is using the dropped column, it will get detected here. If 
+		// not, then we will have generated the internal trigger action sql
+		// which matches the trigger action sql provided by the user.
+		//
+		// eg of positive test case
+		// create table atdc_16_tab1 (a1 integer, b1 integer, c1 integer);
+		// create table atdc_16_tab2 (a2 integer, b2 integer, c2 integer);
+		// create trigger atdc_16_trigger_1 
+		//    after update of b1 on atdc_16_tab1
+		//    REFERENCING NEW AS newt
+		//    for each row 
+		//    update atdc_16_tab2 set c2 = newt.c1
+		// The internal representation for the trigger action before the column
+		// is dropped is as follows
+		// 	 update atdc_16_tab2 set c2 = 
+		//   org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
+		//   getONewRow().getInt(3)
+		// After the drop column shown as below
+		//   alter table DERBY4998_SOFT_UPGRADE_RESTRICT drop column c11
+		// The above internal representation of tigger action sql is not 
+		// correct anymore because column position of c1 in atdc_16_tab1 has 
+		// now changed from 3 to 2. Following while loop will regenerate it and
+		// change it to as follows
+		// 	 update atdc_16_tab2 set c2 = 
+		//   org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
+		//   getONewRow().getInt(2)
+		//
+		// We could not do this before the actual column drop, because the 
+		// rebind would have still found the column being dropped in the
+		// table descriptor and hence use of such a column in the trigger
+		// action rebind would not have been caught.
+
+		//For the table on which ALTER TABLE is getting performed, find out
+		// all the SPSDescriptors that use that table as a provider. We are
+		// looking for SPSDescriptors that have been created internally for
+		// trigger action SPSes. Through those SPSDescriptors, we will be
+		// able to get to the triggers dependent on the table being altered
+		//Following will get all the dependent objects that are using
+		// ALTER TABLE table as provider
+		List depsOnAlterTableList = dd.getProvidersDescriptorList(td.getObjectID().toString());
+		for (Iterator depsOnAlterTableIterator = depsOnAlterTableList.listIterator(); 
+			depsOnAlterTableIterator.hasNext();)
+		{
+			//Go through all the dependent objects on the table being altered 
+			DependencyDescriptor depOnAlterTableDesc = 
+				(DependencyDescriptor) depsOnAlterTableIterator.next();
+			DependableFinder dependent = depOnAlterTableDesc.getDependentFinder();
+			//For the given dependent, we are only interested in it if it is a
+			// stored prepared statement.
+			if (dependent.getSQLObjectType().equals(Dependable.STORED_PREPARED_STATEMENT))
+			{
+				//Look for all the dependent objects that are using this 
+				// stored prepared statement as provider. We are only 
+				// interested in dependents that are triggers.
+				List depsTrigger = dd.getProvidersDescriptorList(depOnAlterTableDesc.getUUID().toString());
+				for (Iterator depsTriggerIterator = depsTrigger.listIterator();
+					depsTriggerIterator.hasNext();)
+				{
+					DependencyDescriptor depsTriggerDesc = 
+						(DependencyDescriptor) depsTriggerIterator.next();
+					DependableFinder providerIsTrigger = depsTriggerDesc.getDependentFinder();
+					//For the given dependent, we are only interested in it if
+					// it is a trigger
+					if (providerIsTrigger.getSQLObjectType().equals(Dependable.TRIGGER)) {
+						//Drop and recreate the trigger after regenerating 
+						// it's trigger action plan. If the trigger action
+						// depends on the column being dropped, it will be
+						// caught here.
+						TriggerDescriptor trdToBeDropped  = dd.getTriggerDescriptor(depsTriggerDesc.getUUID());
+						columnDroppedAndTriggerDependencies(trdToBeDropped,
+								cascade, columnName);
+					}
 				}
-				if (newColumnBitMap.equals(oldColumnBitMap))
-					continue;
-				dd.dropStoredDependency(depDesc, tc);
-				colFinder.setColumnBitMap(newColumnBitMap.getByteArray());
-				dd.addDescriptor(depDesc, null,
-								 DataDictionary.SYSDEPENDS_CATALOG_NUM,
-								 true, tc);
 			}
 		}
 		// Adjust the column permissions rows in SYSCOLPERMS to reflect the
@@ -1714,174 +1759,172 @@ class AlterTableConstantAction extends D
         // list in case we were called recursively in order to cascade-drop a
         // dependent generated column.
         tab_cdl.remove( td.getColumnDescriptor( columnName ) );
-
-        // By this time, the column has been removed from the table descriptor.
-        // Now, go through all the triggers and regenerate their trigger action
-        // SPS and rebind the generated trigger action sql. If the trigger  
-        // action is using the dropped column, it will get detected here. If 
-        // not, then we will have generated the internal trigger action sql
-        // which matches the trigger action sql provided by the user.
-        //
-        // eg of positive test case
-        // create table atdc_16_tab1 (a1 integer, b1 integer, c1 integer);
-        // create table atdc_16_tab2 (a2 integer, b2 integer, c2 integer);
-        // create trigger atdc_16_trigger_1 
-        //    after update of b1 on atdc_16_tab1
-        //    REFERENCING NEW AS newt
-        //    for each row 
-        //    update atdc_16_tab2 set c2 = newt.c1
-        // The internal representation for the trigger action before the column
-        // is dropped is as follows
-        // 	 update atdc_16_tab2 set c2 = 
-        //   org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
-        //   getONewRow().getInt(3)
-        // After the drop column shown as below
-        //   alter table DERBY4998_SOFT_UPGRADE_RESTRICT drop column c11
-        // The above internal representation of tigger action sql is not 
-        // correct anymore because column position of c1 in atdc_16_tab1 has 
-        // now changed from 3 to 2. Following while loop will regenerate it and
-        // change it to as follows
-        // 	 update atdc_16_tab2 set c2 = 
-        //   org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
-        //   getONewRow().getInt(2)
-        //
-        // We could not do this before the actual column drop, because the 
-        // rebind would have still found the column being dropped in the
-        // table descriptor and hence use of such a column in the trigger
-        // action rebind would not have been caught.
-		GenericDescriptorList tdlAfterColumnDrop = dd.getTriggerDescriptors(td);
-		Enumeration descsAfterColumnDrop = tdlAfterColumnDrop.elements();
-		while (descsAfterColumnDrop.hasMoreElements())
-		{
-			TriggerDescriptor trd = (TriggerDescriptor) descsAfterColumnDrop.nextElement();
-			dd.dropTriggerDescriptor(trd, tc);
+	}
+    
+	// For the trigger, get the trigger action sql provided by the user
+	// in the create trigger sql. This sql is saved in the system
+	// table. Since a column has been dropped from the trigger table,
+	// the trigger action sql may not be valid anymore. To establish
+	// that, we need to regenerate the internal representation of that 
+	// sql and bind it again.
+	private void columnDroppedAndTriggerDependencies(TriggerDescriptor trd,
+			boolean cascade, String columnName)
+	throws StandardException {
+		dd.dropTriggerDescriptor(trd, tc);
+
+		// Here we get the trigger action sql and use the parser to build
+		// the parse tree for it.
+		SchemaDescriptor compSchema;
+		compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
+		CompilerContext newCC = lcc.pushCompilerContext(compSchema);
+		Parser	pa = newCC.getParser();
+		StatementNode stmtnode = (StatementNode)pa.parseStatement(trd.getTriggerDefinition());
+		lcc.popCompilerContext(newCC);
+		// Do not delete following. We use this in finally clause to 
+		// determine if the CompilerContext needs to be popped.
+		newCC = null;
+		
+		try {
+			// We are interested in ColumnReference classes in the parse tree
+			CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
+			stmtnode.accept(visitor);
+			Vector refs = visitor.getList();
 			
-			// For the trigger, get the trigger action sql provided by the user
-			// in the create trigger sql. This sql is saved in the system
-			// table. Since a column has been dropped from the trigger table,
-			// the trigger action sql may not be valid anymore. To establish
-			// that, we need to regenerate the internal representation of that 
-			// sql and bind it again.
-
-			// Here we get the trigger action sql and use the parser to build
-			// the parse tree for it.
-			SchemaDescriptor compSchema;
-			compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
-			CompilerContext newCC = lcc.pushCompilerContext(compSchema);
-			Parser	pa = newCC.getParser();
-			StatementNode stmtnode = (StatementNode)pa.parseStatement(trd.getTriggerDefinition());
-			lcc.popCompilerContext(newCC);
-			// Do not delete following. We use this in finally clause to 
-			// determine if the CompilerContext needs to be popped.
-			newCC = null;
+			// Regenerate the internal representation for the trigger action 
+			// sql using the ColumnReference classes in the parse tree. It
+			// will catch dropped column getting used in trigger action sql
+			// through the REFERENCING clause(this can happen only for the
+			// the triggers created prior to 10.7. Trigger created with
+			// 10.7 and higher keep track of trigger action column used
+			// through the REFERENCING clause in system table and hence
+			// use of dropped column will be detected earlier in this 
+			// method for such triggers).
+			//
+			// We might catch errors like following during this step.
+			// Say that following pre-10.7 trigger exists in the system and
+			// user is dropping column c11. During the regeneration of the
+			// internal trigger action sql format, we will catch that 
+			// column oldt.c11 does not exist anymore
+			// 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
+
+			SPSDescriptor triggerActionSPSD = trd.getActionSPS(lcc);
+			int[] referencedColsInTriggerAction = new int[td.getNumberOfColumns()];
+			java.util.Arrays.fill(referencedColsInTriggerAction, -1);
+			triggerActionSPSD.setText(dd.getTriggerActionString(stmtnode, 
+				trd.getOldReferencingName(),
+				trd.getNewReferencingName(),
+				trd.getTriggerDefinition(),
+				trd.getReferencedCols(),
+				referencedColsInTriggerAction,
+				0,
+				trd.getTableDescriptor(),
+				trd.getTriggerEventMask(),
+				true
+				));
 			
-			try {
-				// We are interested in ColumnReference classes in the parse tree
-				CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
-				stmtnode.accept(visitor);
-				Vector refs = visitor.getList();
-				
-				// Regenerate the internal representation for the trigger action 
-				// sql using the ColumnReference classes in the parse tree. It
-				// will catch dropped column getting used in trigger action sql
-				// through the REFERENCING clause(this can happen only for the
-				// the triggers created prior to 10.7. Trigger created with
-				// 10.7 and higher keep track of trigger action column used
-				// through the REFERENCING clause in system table and hence
-				// use of dropped column will be detected earlier in this 
-				// method for such triggers).
-				//
-				// We might catch errors like following during this step.
-				// Say that following pre-10.7 trigger exists in the system and
-				// user is dropping column c11. During the regeneration of the
-				// internal trigger action sql format, we will catch that 
-				// column oldt.c11 does not exist anymore
-				// 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
-
-				SPSDescriptor triggerActionSPSD = trd.getActionSPS(lcc);
-				int[] referencedColsInTriggerAction = new int[td.getNumberOfColumns()];
-				java.util.Arrays.fill(referencedColsInTriggerAction, -1);
-				triggerActionSPSD.setText(dd.getTriggerActionString(stmtnode, 
-					trd.getOldReferencingName(),
-					trd.getNewReferencingName(),
-					trd.getTriggerDefinition(),
-					trd.getReferencedCols(),
-					referencedColsInTriggerAction,
-					0,
-					trd.getTableDescriptor(),
-					trd.getTriggerEventMask(),
-					true
-					));
-				
-				// Now that we have the internal format of the trigger action sql, 
-				// bind that sql to make sure that we are not using colunm being
-				// dropped in the trigger action sql directly (ie not through
-				// REFERENCING clause.
-				// eg
-				// create table atdc_12 (a integer, b integer);
-				// create trigger atdc_12_trigger_1 after update of a 
-				//     on atdc_12 for each row select a,b from atdc_12
-				// Drop one of the columns used in the trigger action
-				//   alter table atdc_12 drop column b
-				// Following rebinding of the trigger action sql will catch the use
-				// of column b in trigger atdc_12_trigger_1
-				compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
-				newCC = lcc.pushCompilerContext(compSchema);
-			    newCC.setReliability(CompilerContext.INTERNAL_SQL_LEGAL);
-				pa = newCC.getParser();
-				stmtnode = (StatementNode)pa.parseStatement(triggerActionSPSD.getText());
-				// need a current dependent for bind
-				newCC.setCurrentDependent(triggerActionSPSD.getPreparedStatement());
-				stmtnode.bindStatement();
-			} catch (StandardException se)
+			// Now that we have the internal format of the trigger action sql, 
+			// bind that sql to make sure that we are not using colunm being
+			// dropped in the trigger action sql directly (ie not through
+			// REFERENCING clause.
+			// eg
+			// create table atdc_12 (a integer, b integer);
+			// create trigger atdc_12_trigger_1 after update of a 
+			//     on atdc_12 for each row select a,b from atdc_12
+			// Drop one of the columns used in the trigger action
+			//   alter table atdc_12 drop column b
+			// Following rebinding of the trigger action sql will catch the use
+			// of column b in trigger atdc_12_trigger_1
+			compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
+			newCC = lcc.pushCompilerContext(compSchema);
+		    newCC.setReliability(CompilerContext.INTERNAL_SQL_LEGAL);
+			pa = newCC.getParser();
+			stmtnode = (StatementNode)pa.parseStatement(triggerActionSPSD.getText());
+			// need a current dependent for bind
+			newCC.setCurrentDependent(triggerActionSPSD.getPreparedStatement());
+			stmtnode.bindStatement();
+		} catch (StandardException se)
+		{
+			//Need to catch for few different kinds of sql states depending
+			// on what kind of trigger action sql is using the column being 
+			// dropped. Following are examples for different sql states
+			//
+			//SQLState.LANG_COLUMN_NOT_FOUND is thrown for following usage in
+			// trigger action sql of column being dropped atdc_12.b
+			//        create trigger atdc_12_trigger_1 after update 
+			//           of a on atdc_12 
+			//           for each row 
+			//           select a,b from atdc_12
+			//
+			//SQLState.LANG_COLUMN_NOT_FOUND_IN_TABLE is thrown for following
+			// usage in trigger action sql of column being dropped  
+			// atdc_14_tab2a2 with restrict clause
+			//        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
+			//
+			// SQLState.LANG_DB2_INVALID_COLS_SPECIFIED is thrown for following
+			//  usage in trigger action sql of column being dropped  
+			//  ATDC_13_TAB1_BACKUP.c11 with restrict clause
+			//         create trigger ATDC_13_TAB1_trigger_1 after update
+			//           on ATDC_13_TAB1 for each row
+			//           INSERT INTO ATDC_13_TAB1_BACKUP
+			//           SELECT C31, C32 from ATDC_13_TAB3
+			//
+			//SQLState.LANG_TABLE_NOT_FOUND is thrown for following scenario
+			//   create view ATDC_13_VIEW2 as select c12 from ATDC_13_TAB3 where c12>0
+			//Has following trigger defined
+			//         create trigger ATDC_13_TAB1_trigger_3 after update
+			//           on ATDC_13_TAB1 for each row
+			//           SELECT * from ATDC_13_VIEW2
+			// Ane drop column ATDC_13_TAB3.c12 is issued
+			if (se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND)||
+					(se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND_IN_TABLE) ||
+					(se.getMessageId().equals(SQLState.LANG_DB2_INVALID_COLS_SPECIFIED) ||
+					(se.getMessageId().equals(SQLState.LANG_TABLE_NOT_FOUND)))))
 			{
-				if (se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND))
+				if (cascade)
 				{
-					if (cascade)
-					{
-                        trd.drop(lcc);
-						activation.addWarning(
-							StandardException.newWarning(
-                                SQLState.LANG_TRIGGER_DROPPED, 
-                                trd.getName(), td.getName()));
-						continue;
-					}
-					else
-					{	// we'd better give an error if don't drop it,
-						throw StandardException.newException(
-                            SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
-                            dm.getActionString(DependencyManager.DROP_COLUMN),
-                            columnName, "TRIGGER",
-                            trd.getName() );
-					}
-				} else
-					throw se;
-			}
-			finally
-			{
-				if (newCC != null)
-					lcc.popCompilerContext(newCC);
-			}
-			
-			// If we are here, then it means that the column being dropped
-			// is not getting used in the trigger action. 
-			//
-			// We have recreated the trigger action SPS and recollected the 
-			// column positions for trigger columns and trigger action columns
-			// getting accessed through REFERENCING clause because
-			// drop column can affect the column positioning of existing
-			// columns in the table. We will save that in the system table.
-			dd.addDescriptor(trd, sd,
-					 DataDictionary.SYSTRIGGERS_CATALOG_NUM,
-					 false, tc);
-
+                    trd.drop(lcc);
+					activation.addWarning(
+						StandardException.newWarning(
+                            SQLState.LANG_TRIGGER_DROPPED, 
+                            trd.getName(), td.getName()));
+					return;
+				}
+				else
+				{	// we'd better give an error if don't drop it,
+					throw StandardException.newException(
+                        SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
+                        dm.getActionString(DependencyManager.DROP_COLUMN),
+                        columnName, "TRIGGER",
+                        trd.getName() );
+				}
+			} else
+				throw se;
+		}
+		finally
+		{
+			if (newCC != null)
+				lcc.popCompilerContext(newCC);
 		}
 		
-	}
+		// If we are here, then it means that the column being dropped
+		// is not getting used in the trigger action. 
+		//
+		// We have recreated the trigger action SPS and recollected the 
+		// column positions for trigger columns and trigger action columns
+		// getting accessed through REFERENCING clause because
+		// drop column can affect the column positioning of existing
+		// columns in the table. We will save that in the system table.
+		dd.addDescriptor(trd, sd,
+				 DataDictionary.SYSTRIGGERS_CATALOG_NUM,
+				 false, tc);
+    }
 
     private void modifyColumnType(int ix)
             throws StandardException {

Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1179653&r1=1179652&r2=1179653&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java Thu Oct  6 14:51:10 2011
@@ -1790,7 +1790,7 @@ public final class AlterTableTest extend
         st.executeUpdate(
                 "rename column renc_schema_2.renc_8.b to b2");
     }
-
+    
     // DERBY-5120 Make sure that sysdepends will catch trigger
     //  table changes and cause the triggers defined on that
     //  table to recompile when they fire next time
@@ -1859,6 +1859,188 @@ public final class AlterTableTest extend
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
     }
+    
+    //A test for ALTER TABLE DROP COLUMN with synonyms and trigger combination.
+    // Trigger uses synonym in it's trigger action and when a column is 
+    // dropped(in cascade mode), the trigger gets dropped because the synonym 
+    // in it's trigger action relied on that column.
+    public void testTriggersAndSynonyms() throws Exception {
+        Statement st = createStatement();
+        
+        st.executeUpdate("create table atdcSynonymTab_1 (c11 integer, c12 integer)");
+        st.executeUpdate("create table atdcSynonymTab_2 (c21 integer, c22 integer)");
+		st.executeUpdate("CREATE SYNONYM synonymTab2 FOR atdcSynonymTab_2");
+        st.executeUpdate(
+                "create trigger syn_tr1t1 after update of c11 on atdcSynonymTab_1 " +
+                "for each row mode db2sql " +
+                "insert into atdcSynonymTab_2(c21, c22) values(9,9)");
+
+        //Verify there is no data in tables before the start of the test
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdcSynonymTab_1"));
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+        st.executeUpdate(
+                " insert into atdcSynonymTab_1 values(11,12)");
+        //Followng will fire the trigger and insert a row in table on which
+        // there is a synonym defined
+        st.executeUpdate(
+                " update atdcSynonymTab_1 set c11=99");
+        //A new row in the table with synonym defined on it
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from synonymTab2"),
+                new String[][]{{"9","9"}});
+        //delete data to get ready for next test which will attempt to do
+        // ALTER TABLE DROP COLUMN RESTRICT and fail because there is a
+        // trigger using the column being dropped
+        st.executeUpdate(
+                " delete from atdcSynonymTab_1");
+        st.executeUpdate(
+                " delete from synonymTab2");
+        
+        //Following will fail because there is a trigger using that 
+        // column
+        assertStatementError("X0Y25", st,
+                " alter table atdcSynonymTab_2 drop column c21 restrict");
+        //Run through the trigger firing test again to see that trigger is
+        // still intact
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdcSynonymTab_1"));
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+        st.executeUpdate(
+                " insert into atdcSynonymTab_1 values(11,12)");
+        //Followng will fire the trigger and insert a row in table on which
+        // there is a synonym defined
+        st.executeUpdate(
+                " update atdcSynonymTab_1 set c11=99");
+        //A new row in the table with synonym defined on it
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from synonymTab2"),
+                new String[][]{{"9","9"}});
+        //delete data to get ready for next test which will attempt to do
+        // ALTER TABLE DROP COLUMN and will dropped the trigger using the 
+        // column being dropped
+        st.executeUpdate(
+                " delete from atdcSynonymTab_1");
+        st.executeUpdate(
+                " delete from synonymTab2");
+        
+        //Following will drop three triggers using the column being dropped
+        st.executeUpdate(
+                " alter table atdcSynonymTab_2 drop column c21");
+        //Run through the trigger firing test again and we will see the trigger
+        // is not there anymore since no new row gets inserted through the
+        // trigger
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdcSynonymTab_1"));
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+        st.executeUpdate(
+                " insert into atdcSynonymTab_1 values(11,12)");
+        st.executeUpdate(
+                " update atdcSynonymTab_1 set c11=99");
+        //Will still be empty because trigger which would have caused a row
+        // insertion got dropped as a result of ALTER TABLE DROP COLUMN
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+    }
+
+    // Column being dropped is getting used in two triggers. A trigger defined
+    //  on the table whose column is getting dropped and a trigger defined on
+    //  another table but using the table whose column is getting dropped in
+    //  it's trigger action
+    public void testDropColumnTriggerDependency() throws Exception {
+        Statement st = createStatement();
+        createTestObjects(st);
+
+        st.executeUpdate("create table atdctd_1 (c11 integer, c12 integer)");
+        st.executeUpdate("create table atdctd_2 (c21 integer, c22 integer)");
+        st.executeUpdate("create table atdctd_3 (c31 integer, c32 integer)");
+
+        st.executeUpdate(
+                "create trigger tr1t1 after update of c11 on atdctd_1 " +
+                "for each row mode db2sql " +
+                "insert into atdctd_3(c31, c32) values(9,9)");
+
+        st.executeUpdate(
+                "create trigger tr1t2 after insert on atdctd_2 " +
+                "for each row mode db2sql " +
+                "insert into atdctd_3(c31, c32) " +
+                "select c11, c12 from atdctd_1");
+        st.executeUpdate(
+                "create trigger tr2t2 after insert on atdctd_2 " +
+                "for each row mode db2sql " +
+                "insert into atdctd_3(c31) " +
+                "select c11 from atdctd_1");
+
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+        st.executeUpdate(
+                " insert into atdctd_1 values(11,12)");
+        st.executeUpdate(
+                " update atdctd_1 set c11=99");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3"),
+                new String[][]{{"9","9"}});
+        st.executeUpdate(
+                " insert into atdctd_2 values(21,22)");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3 order by c32"),
+                new String[][]{{"9","9"}, {"99","12"},{"99",null}});
+        st.executeUpdate(
+                " delete from atdctd_3");
+        st.executeUpdate(
+                " delete from atdctd_1");
+        st.executeUpdate(
+                " delete from atdctd_2");
+        
+        //Following will fail because there are three triggers using that 
+        // column
+        assertStatementError("X0Y25", st,
+                " alter table atdctd_1 drop column c11 restrict");
+        JDBC.assertEmpty(st.executeQuery(
+        		" select * from atdctd_3"));
+        st.executeUpdate(
+        		" insert into atdctd_1 values(11,12)");
+        st.executeUpdate(
+        		" update atdctd_1 set c11=99");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3"),
+                new String[][]{{"9","9"}});
+        st.executeUpdate(
+                " insert into atdctd_2 values(21,22)");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3 order by c32"),
+                new String[][]{{"9","9"}, {"99","12"},{"99",null}});
+        st.executeUpdate(
+                " delete from atdctd_3");
+        st.executeUpdate(
+                " delete from atdctd_1");
+        st.executeUpdate(
+                " delete from atdctd_2");
+        
+        //Following will drop three triggers using the column being dropped
+        st.executeUpdate(
+                " alter table atdctd_1 drop column c11");
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+        st.executeUpdate(
+                " insert into atdctd_1 values(12)");
+        st.executeUpdate(
+                " update atdctd_1 set c12=99");
+        //Will still be empty because trigger which would have added a row into
+        // atdctd_3 got dropped as a result of ALTER TABLE DROP COLUMN earlier
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+        st.executeUpdate(
+                " insert into atdctd_2 values(21,22)");
+        //Will still be empty because triggers which would have added a row 
+        // each into atdctd_3 got dropped as a result of ALTER TABLE DROP 
+        // COLUMN earlier
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+    }
 
     // alter table tests for ALTER TABLE DROP COLUMN. The 
     // overall syntax is:    ALTER TABLE tablename DROP [ 
@@ -2475,8 +2657,7 @@ public final class AlterTableTest extend
         // Another test DERBY-5044
         //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.
+    	//action. 
         createTableAndInsertData(st, "ATDC_14_TAB1", "A1", "B1");
         createTableAndInsertData(st, "ATDC_14_TAB2", "A2", "B2");
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2487,42 +2668,73 @@ public final class AlterTableTest extend
                 "update atdc_14_tab2 set a2 = newt.a1");
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
-        // 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");
+        assertStatementError("X0Y25", st,
+		"alter table atdc_14_tab2 drop column a2 restrict");
         triggersExist(st, new String[][]{{"ATDC_14_TRIGGER_1"}});
-        // following is wrong. 
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
 
         //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");
-        createTableAndInsertData(st, "ATDC_14_TAB1", "A1", "B1");
-        createTableAndInsertData(st, "ATDC_14_TAB2", "A2", "B2");
+        st.executeUpdate("alter table atdc_14_tab2 drop column a2");
+        checkWarning(st, "01502");
+        // the trigger will get dropped as a result of cascade
+        JDBC.assertEmpty(st.executeQuery(
+        		" select triggername from sys.systriggers where " +
+        		"triggername in ('ATDC_14_TRIGGER_1')"));
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
+        st.executeUpdate("drop table ATDC_14_TAB1");
+        st.executeUpdate("drop table ATDC_14_TAB2");
+
+        // Start of another test for DERBY-5044
+        createTableAndInsertData(st, "ATDC_13_TAB1", "C11", "C12");
+        createTableAndInsertData(st, "ATDC_13_TAB1_BACKUP", "C11", "C12");
+        createTableAndInsertData(st, "ATDC_13_TAB2", "C21", "C22");
+        createTableAndInsertData(st, "ATDC_13_TAB3", "C31", "C32");
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
         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");
+                " create trigger ATDC_13_TAB1_trigger_1 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP " +
+                " SELECT C31, C32 from ATDC_13_TAB3");
+        st.executeUpdate(
+                " create trigger ATDC_13_TAB1_trigger_2 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP " +
+                " SELECT * from ATDC_13_TAB3");
+        countAfter2Triggers = numberOfRowsInSysdepends(st);
+        st.executeUpdate(
+                " create trigger ATDC_13_TAB1_trigger_3 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP VALUES(1,1)");
+        int countAfter3rdTrigger = numberOfRowsInSysdepends(st);
+        st.executeUpdate(
+                " create trigger ATDC_13_TAB1_trigger_4 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP(C11) " +
+                " SELECT C21 from ATDC_13_TAB2");
+        int countAfter4thTrigger = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        st.executeUpdate("update ATDC_13_TAB1 set c12=11");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        rs = st.executeQuery("select * from ATDC_13_TAB1_BACKUP ORDER BY C11, C12");
+        JDBC.assertFullResultSet(rs, new String[][]{
+        		{"1","1"}, {"1","11"}, {"1","11"}, {"1","11"}, {"1",null} });
+        st.executeUpdate("delete from ATDC_13_TAB1_BACKUP");
 
-        // following is not the right behavior. we should have dropped 
-        // trigger ATDC_14_TRIGGER_1 because of DROP COLUMN CASCADE
-        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"}});
-        // following is wrong. 
+        assertStatementError("X0Y25", st,
+		"alter table ATDC_13_TAB2 drop column c21 restrict");
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
+            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
+            	{"ATDC_13_TAB1_TRIGGER_4"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        st.executeUpdate("drop table ATDC_14_TAB1");
-        st.executeUpdate("drop table ATDC_14_TAB2");
+        st.executeUpdate("drop table ATDC_13_TAB1_BACKUP");
+        st.executeUpdate("drop table ATDC_13_TAB1");
+        st.executeUpdate("drop table ATDC_13_TAB2");
+        st.executeUpdate("drop table ATDC_13_TAB3");
         
         // Start of another test for DERBY-5044. Test INSERT/DELETE/UPDATE
         // inside the trigger action from base tables
@@ -2556,67 +2768,46 @@ public final class AlterTableTest extend
                 " SELECT C21 from ATDC_13_TAB2");
         countAfter4Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
-/*
-        DERBY-5120
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         st.executeUpdate("update ATDC_13_TAB1 set c12=11");
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         rs = st.executeQuery("select * from ATDC_13_TAB1_BACKUP ORDER BY C11, C12");
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         JDBC.assertFullResultSet(rs, new String[][]{
         		{"1","1"}, {"1","11"}, {"1","11"}, {"1","11"}, {"1",null} });
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         st.executeUpdate("delete from ATDC_13_TAB1_BACKUP");
-*/
-        // 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_13_TAB2 drop column c21 restrict");
+        //We will get an error because column being dropped is getting used 
+        // in a trigger action 
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB2 drop column c21 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
             	{"ATDC_13_TAB1_TRIGGER_4"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent triggers  
         st.executeUpdate("alter table ATDC_13_TAB2 drop column c21");
+        checkWarning(st, "01502");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
-            	{"ATDC_13_TAB1_TRIGGER_4"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),countAfter3Triggers);
         st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
         
-        // 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_13_TAB1_BACKUP drop column c11 restrict");
+        //We will get an error because column being dropped is getting used 
+        // in a trigger action 
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB1_BACKUP drop column c11 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
-            	{"ATDC_13_TAB1_TRIGGER_4"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c11 int");
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
+        		numberOfRowsInSysdepends(st),countAfter3Triggers);
+        
+        // We will drop the dependent triggers  
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c11");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
-            	{"ATDC_13_TAB1_TRIGGER_4"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c11 int");
-        //Done testing triggers with trigger action doing INSERT
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_3");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_4");
         
         //Test triggers with trigger action doing UPDATE
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2639,75 +2830,47 @@ public final class AlterTableTest extend
         countAfter3Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
-        // 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_13_TAB3 drop column c31 restrict");
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB3 drop column c31 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c31 int");
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB3 drop column c31");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter2Triggers
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),countAfter2Triggers);
         // After DERBY-5044 is fixed, following should be rewritten
         st.executeUpdate("alter table ATDC_13_TAB3 add column c31 int");
 
-        // 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_13_TAB2 drop column c21 restrict");
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB2 drop column c21 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
+        		numberOfRowsInSysdepends(st),countAfter2Triggers);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB2 drop column c21");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
 
-        // 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_13_TAB1_BACKUP drop column c12 restrict");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
         // After DERBY-5044 is fixed, following should be rewritten
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
-        //Done testing triggers with trigger action doing UPDATE
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_3");
 
         //Test triggers with trigger action doing DELETE
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2725,54 +2888,29 @@ public final class AlterTableTest extend
         countAfter2Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
-        // 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_13_TAB3 drop column c32 restrict");
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB3 drop column c32 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB3 drop column c32");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
 
-        // 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_13_TAB1_BACKUP drop column c12 restrict");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}});
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
-
-        //Done testing triggers with trigger action doing DELETE
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
-
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
         st.executeUpdate("drop table ATDC_13_TAB1");
         st.executeUpdate("drop table ATDC_13_TAB1_BACKUP");
         st.executeUpdate("drop table ATDC_13_TAB2");
@@ -2786,12 +2924,14 @@ public final class AlterTableTest extend
         createTableAndInsertData(st, "ATDC_13_TAB1", "C11", "C12");
         createTableAndInsertData(st, "ATDC_13_TAB2", "C11", "C12");
         createTableAndInsertData(st, "ATDC_13_TAB3", "C11", "C12");
+        
         st.executeUpdate("create view ATDC_13_VIEW1 as " +
         		"select c11 from ATDC_13_TAB2");
+        st.executeUpdate("create view ATDC_13_VIEW3 as " +
+        		"select * from ATDC_13_TAB2");
         st.executeUpdate("create view ATDC_13_VIEW2 as " +
         		"select c12 from ATDC_13_TAB3 where c12>0");
-        st.executeUpdate("create view ATDC_13_VIEW3 as " +
-		"select * from ATDC_13_TAB2");
+        
         //Test triggers with trigger action using views
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
         st.executeUpdate(
@@ -2802,60 +2942,49 @@ public final class AlterTableTest extend
         st.executeUpdate(
                 " create trigger ATDC_13_TAB1_trigger_2 after update " +
                 "on ATDC_13_TAB1 for each row " +
-                "SELECT * from ATDC_13_VIEW2 ");
+                "SELECT * from ATDC_13_VIEW3");
         countAfter2Triggers = numberOfRowsInSysdepends(st);
         st.executeUpdate(
                 " create trigger ATDC_13_TAB1_trigger_3 after update " +
                 "on ATDC_13_TAB1 for each row " +
-                "SELECT * from ATDC_13_VIEW3");
+                "SELECT * from ATDC_13_VIEW2 ");
         countAfter3Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
         // DROP COLUMN RESTRICT fails because there is a view using the column
         assertStatementError("X0Y23", st,
-		"alter table ATDC_13_TAB3 drop column c12 restrict");
+        		"alter table ATDC_13_TAB3 drop column c12 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers while dropping dependent view
         st.executeUpdate("alter table ATDC_13_TAB3 drop column c12");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         // One row from sysdepends got dropped because of a view getting
-        // dropped
-        sysdependsRowCountAfterCreateTrigger = sysdependsRowCountAfterCreateTrigger-1;
+        // dropped and that is why we are checking for countAfter2Triggers-1
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
+        		numberOfRowsInSysdepends(st),countAfter2Triggers-1);
 
-        // DROP COLUMN RESTRICT fails because there is a view using the column
+        // DROP COLUMN RESTRICT fails as there are 2 views using the column
         assertStatementError("X0Y23", st,
 		"alter table ATDC_13_TAB2 drop column c11 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),countAfter2Triggers-1);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers while dropping dependent view
+        // We have dropped dependent triggers while dropping dependent view
         st.executeUpdate("alter table ATDC_13_TAB2 drop column c11");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         // Two rows from sysdepends got dropped because of 2 views getting
-        // dropped
-        sysdependsRowCountAfterCreateTrigger = sysdependsRowCountAfterCreateTrigger-2;
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
+        // dropped from the drop column c11 from ATDC_13_TAB2. Additionally,
+        // another view was dropped from drop of c12 from ATDC_13_TAB3.
+        // So 3 dependencies altogether got lost from sysdepends in
+        // addition to the dependencies that triggers had required.
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c11 int");
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger-3);
 
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
         st.executeUpdate("drop table ATDC_13_TAB1");
         st.executeUpdate("drop table ATDC_13_TAB2");
         st.executeUpdate("drop table ATDC_13_TAB3");

Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=1179653&r1=1179652&r2=1179653&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Thu Oct  6 14:51:10 2011
@@ -10361,5 +10361,206 @@ public final class GrantRevokeDDLTest ex
         st_mamta1.execute("drop view v_4502");
         st_mamta1.execute("drop schema mamta1 restrict");
     }
+
+    // DERBY-5044 During alter table drop column, we recompile all the 
+    //  dependent trigger's action plans to see if they are dependent
+    //  on the column being dropped. The database may have been created
+    //  with authorization on and hence different actions might require
+    //  relevant privileges. This test will ensure that during the
+    //  recompile of trigger action, we will not loose the privilege
+    //  requirements for the triggers
+    public void testAlterTablePrivilegesIntace() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        
+        Connection user1Connection = openUserConnection("user1");
+        Statement st_user1Connection = user1Connection.createStatement();
+        
+        st = createStatement();
+        
+        st_user1Connection.executeUpdate(
+        		"create table user1.t11 (c111 int, c112 int, c113 int)");
+        st_user1Connection.executeUpdate(
+        		"create table user1.t12 (c121 int, c122 int)");
+        st_user1Connection.executeUpdate(
+        		"create table user1.t13 (c131 int, c132 int)");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,2,3)");
+        st_user1Connection.executeUpdate(
+                " grant trigger on user1.t12 to user2");
+        st_user1Connection.executeUpdate(
+                " grant update(c112, c113) on user1.t11 to user2");
+        st_user1Connection.executeUpdate(
+                " grant select on user1.t11 to user2");
+        st_user1Connection.executeUpdate(
+                " grant insert on user1.t13 to user2");
+
+        Connection user2Connection = openUserConnection("user2");
+        Statement st_user2Connection = user2Connection.createStatement();
+        st_user2Connection.executeUpdate(
+                "create trigger tr1t12 after insert on user1.t12 " +
+                "for each row mode db2sql " +
+                "update user1.t11 set c112=222");
+        st_user2Connection.executeUpdate(
+                "create trigger tr2t12 after insert on user1.t12 " +
+                "for each row mode db2sql " +
+                "insert into user1.t13(c131, c132) " +
+                "select c111, c113 from user1.t11");
+
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","2","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(91,91)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","222","3"}});
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t13"),
+                new String[][]{{"1","3"}});
+        st_user1Connection.executeUpdate(
+                "delete from user1.t11");        
+        st_user1Connection.executeUpdate(
+                "delete from user1.t13");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,2,3)");
+  
+        assertStatementError("X0Y25", st_user1Connection,
+                "alter table t11 drop column c112 restrict");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","2","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(92,92)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","222","3"}});
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t13"),
+                new String[][]{{"1","3"}});
+        st_user1Connection.executeUpdate(
+                "delete from user1.t11");        
+        st_user1Connection.executeUpdate(
+                "delete from user1.t13");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,2,3)");
+        
+        st_user1Connection.executeUpdate("alter table t11 drop column c112");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(93,93)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t13"),
+                new String[][]{{"1","3"}});
+        st_user1Connection.executeUpdate(
+                "delete from user1.t11");        
+        st_user1Connection.executeUpdate(
+                "delete from user1.t13");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,3)");
+        
+        st_user1Connection.executeUpdate(
+        		"revoke insert on table user1.t13 from user2");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(94,94)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+        		" select * from user1.t13"));
+        st_user1Connection.executeUpdate(
+        		"drop table user1.t11");
+        st_user1Connection.executeUpdate(
+        		"drop table user1.t12");
+        st_user1Connection.executeUpdate(
+        		"drop table user1.t13");
+    }
+
+    // DERBY-5044 During alter table drop column, we recompile all the 
+    //  dependent trigger's action plans to see if they are dependent
+    //  on the column being dropped. Some of these triggers may have
+    //  been created by a user different than one doing the alter table.
+    //  The test below shows that we are able to handle such a case
+    //  and able to detect trigger dependencies even if they are created
+    //  by a different user
+    public void testAlterTableWithPrivileges() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        
+        Connection user1Connection = openUserConnection("user1");
+        Statement st_user1Connection = user1Connection.createStatement();
+        
+        st = createStatement();
+        
+        st_user1Connection.executeUpdate(
+        		"create table user1.t11 (c111 int, c112 int)");
+        st_user1Connection.executeUpdate(
+        		"create table user1.t12 (c121 int, c122 int)");
+        
+        Connection user2Connection = openUserConnection("user2");
+        Statement st_user2Connection = user2Connection.createStatement();
+  
+        // following create trigger fails because it is getting created on 
+        //  non-granted object
+        assertStatementError("42500", st_user2Connection,
+            "create trigger tr1t12 after insert on user1.t12 for each row " +
+            "mode db2sql insert into user1.t11(c112) values (1)");
+        
+        st_user1Connection.executeUpdate(
+        		" grant insert on user1.t11 to user2");
+        st_user1Connection.executeUpdate(
+        		" grant trigger on user1.t12 to user2");
+        
+        // following create trigger should pass because user2 now has necessary
+        //  privileges
+        st_user2Connection.executeUpdate(
+                "create trigger tr1t12 after insert on user1.t12 " +
+                "for each row mode db2sql " +
+                "insert into user1.t11(c112) values (1)");
+        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t12 values(91,91)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{null, "1"}});
+        
+        // following should fail because there is a dependent trigger on 
+        //  t11.c112 and drop column is getting done in restrict mode
+        assertStatementError("X0Y25", st_user1Connection,
+                "alter table t11 drop column c112 restrict");
+        st_user1Connection.executeUpdate(
+                " insert into user1.t12 values(92,92)");
+        JDBC.assertFullResultSet(
+                st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{null, "1"}, {null,"1"}});
+        // following should pass because drop column is getting done in 
+        //  cascade mode and so the dependent trigger will be dropped
+        st_user1Connection.executeUpdate(
+                "alter table t11 drop column c112");        
+        //No new row will be inserted into user1.t11 because the trigger has
+        //  been dropped
+        st_user1Connection.executeUpdate(
+                " insert into user1.t12 values(93,93)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{null}, {null}});
+        st_user1Connection.executeUpdate(
+                "drop table user1.t11");
+        st_user1Connection.executeUpdate(
+                "drop table user1.t12");
+    }
+
     
 }



Mime
View raw message