db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1539074 - in /db/derby/code/branches/10.10/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ testing/org/apache/derbyT...
Date Tue, 05 Nov 2013 17:24:00 GMT
Author: mamta
Date: Tue Nov  5 17:23:59 2013
New Revision: 1539074

URL: http://svn.apache.org/r1539074
Log:
DERBY-6383(Update trigger defined on one column fires on update of other columns)

Backporting following 2 revisions from trunk to 10.10. Had to hand do the changes because
of conflicts during svn merge.
1537593, 1538854 


Modified:
    db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java

Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1539074&r1=1539073&r2=1539074&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
(original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
Tue Nov  5 17:23:59 2013
@@ -528,10 +528,9 @@ public class CreateTriggerNode extends D
 			ResultColumn rc;
 			ColumnDescriptor cd;
 			//This is the most interesting case for us. If we are here, 
-			//then it means that the trigger is defined at the row level
-			//and a set of trigger columns are specified in the CREATE
-			//TRIGGER statement. This can only happen for an UPDATE
-			//trigger.
+			//then it means that a set of trigger columns are specified
+			//in the CREATE TRIGGER statement. This can only happen for
+			//an UPDATE trigger.
 			//eg
 			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
 			//    REFERENCING OLD AS oldt NEW AS newt
@@ -591,8 +590,7 @@ public class CreateTriggerNode extends D
 		else
 		{
 			//This is a table level trigger	        
-			//Total Number of columns in the trigger table
-			int numberOfColsInTriggerTable = triggerTableDescriptor.getNumberOfColumns();
+
 			StringBuffer newText = new StringBuffer();
 			/*
 			** For a statement trigger, we find all FromBaseTable nodes.  If
@@ -636,12 +634,6 @@ public class CreateTriggerNode extends D
 					newText.append(baseTableName).append(" ");
 				}
 				start=tokEndOffset-actionOffset+1;
-				//If we are dealing with statement trigger, then we will read 
-				//all the columns from the trigger table since trigger will be
-				//fired for any of the columns in the trigger table.
-				referencedColInts= new int[numberOfColsInTriggerTable];
-				for (int j=0; j < numberOfColsInTriggerTable; j++)
-					referencedColInts[j]=j+1;
 			}
 			if (start < originalActionText.length())
 			{

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out?rev=1539074&r1=1539073&r2=1539074&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
Tue Nov  5 17:23:59 2013
@@ -2123,7 +2123,7 @@ E
 T8
 null
 <systemid>
-(1,2)
+null
 select * from oldtable
 true
 false

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out?rev=1539074&r1=1539073&r2=1539074&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
Tue Nov  5 17:23:59 2013
@@ -2123,7 +2123,7 @@ E
 T8
 null
 <systemid>
-(1,2)
+null
 select * from oldtable
 true
 false

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out?rev=1539074&r1=1539073&r2=1539074&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
Tue Nov  5 17:23:59 2013
@@ -2116,7 +2116,7 @@ E
 T8
 null
 <systemid>
-(1,2)
+null
 select * from oldtable
 true
 false
@@ -4381,7 +4381,7 @@ E
 T8
 null
 <systemid>
-(1,2)
+null
 select * from oldtable
 true
 false

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out?rev=1539074&r1=1539073&r2=1539074&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
Tue Nov  5 17:23:59 2013
@@ -2116,7 +2116,7 @@ E
 T8
 null
 <systemid>
-(1,2)
+null
 select * from oldtable
 true
 false
@@ -4381,7 +4381,7 @@ E
 T8
 null
 <systemid>
-(1,2)
+null
 select * from oldtable
 true
 false

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1539074&r1=1539073&r2=1539074&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Tue Nov  5 17:23:59 2013
@@ -119,6 +119,71 @@ public class TriggerTest extends BaseJDB
     }
 
     /**
+     * DERBY-6383(Update trigger defined on one column fires on update 
+     * of other columns). This regression is caused by DERBY-4874(Trigger 
+     * does not recognize new size of VARCHAR column expanded with 
+     * ALTER TABLE. It fails with ERROR 22001: A truncation error was 
+     * encountered trying to shrink VARCHAR)
+     *  The regression is for Statement level triggers. The trigger
+     *  gets fired for any column update rather than just the column
+     *  specified in the UPDATE of column clause. Following test
+     *  confirms that fix for DERBY-6383 fixes the issue.
+     * 
+     * @throws SQLException 
+     * 
+     */
+    public void testDerby6383StatementTriggerBug() throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("CREATE TABLE DERBY_6368_TAB1 (X INTEGER, Y INTEGER)");
+        s.executeUpdate("CREATE TABLE DERBY_6368_TAB2 (X INTEGER, Y INTEGER)");
+        s.executeUpdate("INSERT INTO  DERBY_6368_TAB1 VALUES(1, 2)");
+        //Create statement trigger on a specific column "X" on DERBY_6368_TAB1
+        s.executeUpdate("CREATE TRIGGER t1 AFTER UPDATE OF x "+
+            "ON DERBY_6368_TAB1 REFERENCING old table AS old " +
+            "INSERT INTO DERBY_6368_TAB2 SELECT * FROM old");
+        assertTableRowCount("DERBY_6368_TAB2", 0);
+        
+        //Following should not fire the trigger since following UPDATE is on
+        // column "Y" whereas trigger is defined on column "X"
+        s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
+        assertTableRowCount("DERBY_6368_TAB2", 0);
+
+        //Create row trigger on a specific column "X" on DERBY_6368_TAB1
+        s.executeUpdate("CREATE TRIGGER t2 AFTER UPDATE OF x "+
+            "ON DERBY_6368_TAB1 REFERENCING old AS old_row " +
+            "for each row " +
+            "INSERT INTO DERBY_6368_TAB2 values(old_row.x, old_row.y)");
+
+        //Following should not fire any trigger since following UPDATE is on
+        // column "Y" whereas triggers are defined on column "X"
+        s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
+        assertTableRowCount("DERBY_6368_TAB2", 0);
+
+        //Following should fire both triggers since following UPDATE is on
+        // column "X" which has two triggers defined on it
+        s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
+        assertTableRowCount("DERBY_6368_TAB2", 2);
+
+        //drop statement trigger
+        s.executeUpdate("drop TRIGGER T1");
+
+        //Following should not fire any trigger since following UPDATE is on
+        // column "Y" whereas trigger is defined on column "X"
+        s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
+        assertTableRowCount("DERBY_6368_TAB2", 2);
+
+        //Following should fire trigger since following UPDATE is on
+        // column "X" which has row trigger defined on it
+        s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
+        assertTableRowCount("DERBY_6368_TAB2", 3);
+
+        //clean up after the test
+        s.executeUpdate("drop table DERBY_6368_TAB1");
+        s.executeUpdate("drop table DERBY_6368_TAB2");
+    }
+
+    /**
      * Test that invalidating stored statements marks the statement invalid
      *  in SYS.SYSSTATEMENTS. And when one of those invalid statements is
      *  executed next, it is recompiled and as part of that process, it gets



Mime
View raw message