You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2013/11/05 18:24:00 UTC
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...
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