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/12 18:18:16 UTC
svn commit: r1541170 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Author: mamta
Date: Tue Nov 12 17:18:15 2013
New Revision: 1541170
URL: http://svn.apache.org/r1541170
Log:
DERBY-6383(Update trigger defined on one column fires on update of other columns)
Adding more junit tests for checking the affects of adding/dropping columns to the table with update triggers defined on it.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
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=1541170&r1=1541169&r2=1541170&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 Tue Nov 12 17:18:15 2013
@@ -133,7 +133,7 @@ public class TriggerTest extends BaseJDB
* @throws SQLException
*
*/
- public void testDerby6383StatementTriggerBug() throws SQLException
+ public void testDerby6383StatementTriggerBugTst1() throws SQLException
{
Statement s = createStatement();
s.executeUpdate("CREATE TABLE DERBY_6368_TAB1 (X INTEGER, Y INTEGER)");
@@ -154,7 +154,7 @@ public class TriggerTest extends BaseJDB
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)");
+ "INSERT INTO DERBY_6368_TAB2(x) values(old_row.x)");
//Following should not fire any trigger since following UPDATE is on
// column "Y" whereas triggers are defined on column "X"
@@ -166,18 +166,75 @@ public class TriggerTest extends BaseJDB
s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
assertTableRowCount("DERBY_6368_TAB2", 2);
- //drop statement trigger
+ //Create statement trigger at table level for DERBY_6368_TAB1
+ s.executeUpdate("CREATE TRIGGER t3 AFTER UPDATE "+
+ "ON DERBY_6368_TAB1 REFERENCING old table AS old " +
+ "INSERT INTO DERBY_6368_TAB2 SELECT * FROM old");
+
+ //Following should fire trigger t3 which is supposed to fire for
+ // any column update
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 3);
+
+ //Following should fire all the 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", 6);
+
+ //Add a new column to table
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB1 ADD COLUMN Z int");
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB2 ADD COLUMN Z int");
+
+ //Following should fire trigger t3 since any column update should fire
+ // trigger t3
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 7);
+
+ //Following should fire trigger t3 since any column update should fire
+ // trigger t3
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 8);
+
+ //Following should fire all the 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", 11);
+
+ //drop statement trigger defined on specific column
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"
+ //Following should only fire trigger t3 since any column update should
+ // fire trigger t3
s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
- assertTableRowCount("DERBY_6368_TAB2", 2);
+ assertTableRowCount("DERBY_6368_TAB2", 12);
- //Following should fire trigger since following UPDATE is on
+ //Following should fire triggers t2 and t3 since following UPDATE is on
+ // column "X" which has row trigger defined on it and a statement
+ // trigger(at table level) defined on it
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 14);
+
+ //Following should fire trigger t3 since any column update should fire
+ // trigger t3
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 15);
+
+ //Drop a column from the table. Following will drop trigger t3
+ // because it depends on column being dropped. But trigger t2
+ // will remain intact since it does not have dependency on
+ // column being dropped. So only trigger left at this point
+ // will be t2 after the following column drop
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB1 DROP COLUMN Y");
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB2 DROP COLUMN Y");
+
+ //Following should fire triggers t2 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);
+ assertTableRowCount("DERBY_6368_TAB2", 16);
+
+ //Following should not fire trigger t2
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 16);
//clean up after the test
s.executeUpdate("drop table DERBY_6368_TAB1");
@@ -185,6 +242,60 @@ 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)
+ * After an update statement level trigger is defined at the table level,
+ * when a new column is added, trigger should fire on update of that
+ * newly added column
+ */
+ public void testDerby6383StatementTriggerBugTst2() 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 at table level for DERBY_6368_TAB1
+ s.executeUpdate("CREATE TRIGGER t1 AFTER UPDATE "+
+ "ON DERBY_6368_TAB1 REFERENCING old table AS old " +
+ "INSERT INTO DERBY_6368_TAB2 SELECT * FROM old");
+ assertTableRowCount("DERBY_6368_TAB2", 0);
+
+ //Following should fire trigger since any column update should fire
+ // trigger t1
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 1);
+
+ //Following should fire trigger since any column update should fire
+ // trigger t1
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 2);
+
+ //Add a new column to table
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB1 ADD COLUMN Z int");
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB2 ADD COLUMN Z int");
+ //Following should fire trigger since any column update should fire
+ // trigger t1
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 1");
+ assertTableRowCount("DERBY_6368_TAB2", 3);
+
+ //Drop a column from the table. this will drop the statement
+ // trigger defined at the table level for DERBY_6368_TAB1
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB1 DROP COLUMN X");
+ s.executeUpdate("ALTER TABLE DERBY_6368_TAB2 DROP COLUMN X");
+ //No triggers left to fire
+ s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 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