db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1537593 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Date Thu, 31 Oct 2013 19:28:47 GMT
Author: mamta
Date: Thu Oct 31 19:28:47 2013
New Revision: 1537593

URL: http://svn.apache.org/r1537593
Log:
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 statement trigger gets fired incorrectly
for any column update rather than just the column specified in the UPDATE of column clause.
The fix is going to ensure that SYSTRIGGERS has the correct list of columns on which trigger
should be fired. Row level triggers work fine. The only workaround at this point is to drop
and recreate the statement triggers. 


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1537593&r1=1537592&r2=1537593&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
Thu Oct 31 19:28:47 2013
@@ -538,10 +538,9 @@ class CreateTriggerNode extends DDLState
 			referencedColInts = new int[triggerCols.size()];
 
 			//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
@@ -740,16 +739,6 @@ class CreateTriggerNode extends DDLState
             }
 
             start = tokEndOffset - offset + 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.
-            int numberOfColsInTriggerTable =
-                    triggerTableDescriptor.getNumberOfColumns();
-            referencedColInts = new int[numberOfColsInTriggerTable];
-            for (int j = 0; j < numberOfColsInTriggerTable; j++) {
-                referencedColInts[j] = j + 1;
-            }
         }
 
         newText.append(originalText, start, originalText.length());

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1537593&r1=1537592&r2=1537593&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Thu Oct 31 19:28:47 2013
@@ -120,6 +120,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