svn commit: r1529145 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java

[ka...@apache.org]

Author: kahatlen
Date: Fri Oct  4 12:35:50 2013
New Revision: 1529145

URL: http://svn.apache.org/r1529145
Log:
DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

Add a test case to verify that the WHEN clause SPS is invalidated and
recompiled if one of its dependencies requests a recompilation.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java?rev=1529145&r1=1529144&r2=1529145&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java Fri Oct  4 12:35:50 2013
@@ -22,6 +22,7 @@
 package org.apache.derbyTesting.functionTests.tests.lang;
 
 import java.sql.Connection;
+import java.sql.PreparedStatement;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.ArrayList;
@@ -348,4 +349,43 @@ public class TriggerWhenClauseTest exten
                 + "when ((select true from sysibm.sysdummy where ibmreqd = ?)) "
                 + "call int_proc(1)");
     }
+
+    /**
+     * Verify that the SPS of a WHEN clause is invalidated when one of its
+     * dependencies is changed in a way that requires recompilation.
+     */
+    public void testWhenClauseInvalidation() throws SQLException {
+        // Statement that checks the validity of the WHEN clause SPS.
+        PreparedStatement spsValid = prepareStatement(
+                "select valid from sys.sysstatements "
+                + "where stmtname like 'TRIGGERWHEN%'");
+
+        Statement s = createStatement();
+        s.execute("create table t1(x int)");
+        s.execute("create table t2(x int)");
+        s.execute("create table t3(x int)");
+        s.execute("insert into t1 values 1");
+
+        s.execute("create trigger tr after insert on t2 "
+                + "referencing new as new for each row "
+                + "when (exists (select * from t1 where x = new.x)) "
+                + "insert into t3 values new.x");
+
+        // SPS is initially valid.
+        JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true");
+
+        // Compressing the table referenced in the WHEN clause should
+        // invalidate the SPS.
+        PreparedStatement compress = prepareStatement(
+                "call syscs_util.syscs_compress_table(?, 'T1', 1)");
+        compress.setString(1, TestConfiguration.getCurrent().getUserName());
+        compress.execute();
+        JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "false");
+
+        // Invoking the trigger should recompile the SPS.
+        s.execute("insert into t2 values 0,1,2");
+        JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select * from t3"), "1");
+    }
 }