db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1579768 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/lang/ util/
Date Thu, 20 Mar 2014 21:38:21 GMT
Author: dag
Date: Thu Mar 20 21:38:20 2014
New Revision: 1579768

URL: http://svn.apache.org/r1579768
Log:
DERBY-6520 Convert lang/triggerGeneral.sql to JUnit TriggerGeneralTest

Patch derby-6520-3.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerGeneralTest.java
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral_app.properties
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral_derby.properties
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/Triggers.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=1579768&r1=1579767&r2=1579768&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Thu Mar 20 21:38:20 2014
@@ -22,7 +22,6 @@ lang/setOpPlan.sql
 lang/specjPlans.sql
 lang/subquery2.sql
 lang/subqueryFlattening.sql
-lang/triggerGeneral.sql
 lang/triggerRecursion.sql
 lang/triggerRefClause.sql
 lang/wisconsin.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerGeneralTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerGeneralTest.java?rev=1579768&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerGeneralTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerGeneralTest.java Thu Mar 20 21:38:20 2014
@@ -0,0 +1,2403 @@
+/**
+ *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.TriggerGeneralTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.io.ByteArrayOutputStream;
+import java.io.PrintStream;
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import junit.framework.Test;
+import org.apache.derby.iapi.db.Factory;
+import org.apache.derby.iapi.db.TriggerExecutionContext;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SecurityManagerSetup;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * General trigger test
+ */
+public final class TriggerGeneralTest extends BaseJDBCTestCase {
+
+    /**
+     * Pattern for a hexadecimal digit,
+     * cf. http://docs.oracle.com/javase/6/docs/api/java/util/regex/Pattern.html
+     */
+    public static final String HEX_DIGIT = "\\p{XDigit}";
+    public static final String LANG_DUPLICATE_KEY_CONSTRAINT = "23505";
+    public static final String LANG_SYNTAX_ERROR = "42X01";
+    public static final String LANG_COLUMN_NOT_FOUND_IN_TABLE = "42X14";
+    public static final String LANG_NO_USER_DDL_IN_SYSTEM_SCHEMA = "42X62";
+    public static final String LANG_OBJECT_NOT_FOUND = "42X94";
+    public static final String LANG_NO_PARAMS_IN_TRIGGER_ACTION = "42Y27";
+    public static final String LANG_DUPLICATE_COLUMN_IN_TRIGGER_UPDATE = "42Y40";
+    public static final String LANG_OBJECT_DOES_NOT_EXIST = "42Y55";
+    public static final String LANG_INVALID_OPERATION_ON_VIEW = "42Y62";
+    public static final String LANG_TRIGGER_RECURSION_EXCEEDED = "54038";
+    public static final String LANG_PROVIDER_HAS_DEPENDENT_OBJECT = "X0Y25";
+    public static final String LANG_OBJECT_ALREADY_EXISTS_IN_OBJECT = "X0Y32";
+    public static final String LANG_SCHEMA_NOT_EMPTY = "X0Y54";
+    public static final String LANG_INVALID_OPERATION_ON_SYSTEM_TABLE = "X0Y56";
+
+    private static PrintStream out;
+    private static ByteArrayOutputStream outs;
+
+    /**
+     * Public constructor required for running test as standalone JUnit.
+     * @param name test fixture name
+     */
+    public TriggerGeneralTest(String name)
+    {
+        super(name);
+    }
+
+    public static Test suite()
+    {
+        Test t = TestConfiguration.defaultSuite(TriggerGeneralTest.class);
+        t = new SupportFilesSetup(t,
+                   new String[] {
+                   "functionTests/tests/lang/dcl_emc1.jar",
+                   });
+        return SecurityManagerSetup.noSecurityManager(t);
+    }
+
+    public void testTriggersInGeneral() throws Exception
+    {
+        ResultSet rs;
+        ResultSetMetaData rsmd;
+
+        final Statement st = createStatement();
+
+        String [][] expRS;
+        String [] expColNames;
+        outs = new ByteArrayOutputStream();
+        out = new PrintStream(outs);
+
+        st.executeUpdate("create function triggerFiresMin(s varchar(128)) "
+            + "returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL"
+            + "  EXTERNAL NAME "
+            + "'org.apache.derbyTesting.functionTests.tests.lang."
+            + "TriggerGeneralTest.triggerFiresMinimal'");
+
+        st.executeUpdate("create function triggerFires(s varchar(128)) "
+            + "returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL"
+            + "  EXTERNAL NAME "
+            + "'org.apache.derbyTesting.functionTests.tests.lang."
+            + "TriggerGeneralTest.triggerFires'");
+
+        assertStatementError(LANG_OBJECT_DOES_NOT_EXIST, st,
+            "drop table x");
+
+        st.executeUpdate("create table x (x int, y int, z int, constraint ck1 "
+            + "check (x > 0))");
+
+        st.executeUpdate("create view v as select * from x");
+
+        // ok
+
+        st.executeUpdate("create trigger t1 NO CASCADE before update of x,y "
+            + "on x for each row values 1");
+
+        // trigger already exists
+
+        assertStatementError(LANG_OBJECT_ALREADY_EXISTS_IN_OBJECT, st,
+            "create trigger t1 NO CASCADE before update of x,y "
+            + "on x for each row values 1");
+
+        // trigger already exists
+
+        assertStatementError(LANG_OBJECT_ALREADY_EXISTS_IN_OBJECT, st,
+            "create trigger app.t1 NO CASCADE before update of "
+            + "x,y on x for each row values 1");
+
+        // make sure system tables look as we expect
+
+        rs = st.executeQuery("select cast(triggername as char(10)), event, "
+            + "firingtime, type, state, referencedcolumns from "
+            + "sys.systriggers");
+
+        expColNames = new String [] {
+            "1", "EVENT", "FIRINGTIME", "TYPE", "STATE", "REFERENCEDCOLUMNS"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"T1", "U", "B", "R", "E", "(1,2)"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select cast(triggername as char(10)), CAST "
+            + "(TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from "
+            + "sys.systriggers t, sys.sysstatements s "
+            + "     where s.stmtid = t.actionstmtid");
+
+        expColNames = new String [] {"1", "2", "STMTNAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+        rs.next();
+        assertEquals("T1        ", rs.getString(1));
+        assertEquals("values 1", rs.getString(2));
+        assertTrue(matchUUIDs(rs.getString(3)));
+        assertFalse(rs.next());
+
+        rs = st.executeQuery("select cast(triggername as char(10)), tablename "
+            + "from sys.systriggers t, sys.systables tb"
+            + "     where t.tableid = tb.tableid");
+
+        expColNames = new String [] {"1", "TABLENAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"T1", "X"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "values SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', 'SYSTRIGGERS')");
+
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop trigger t1");
+
+        // not in sys schema
+
+        assertStatementError(LANG_NO_USER_DDL_IN_SYSTEM_SCHEMA, st,
+            "create trigger sys.tr NO CASCADE before insert on x "
+            + "for each row values 1");
+
+        // not on table in sys schema
+
+        assertStatementError(LANG_INVALID_OPERATION_ON_SYSTEM_TABLE, st,
+            "create trigger tr NO CASCADE before insert on "
+            + "sys.systables for each row values 1");
+
+        // duplicate columns, not allowed
+
+        assertStatementError(LANG_DUPLICATE_COLUMN_IN_TRIGGER_UPDATE, st,
+            "create trigger tr NO CASCADE before update of x, x "
+            + "on x for each row values 1");
+
+        // no params in column list
+
+        assertStatementError(LANG_SYNTAX_ERROR, st,
+            "create trigger tr NO CASCADE before update of x, ? "
+            + "on x for each row values 1");
+
+        // invalid column
+
+        assertStatementError(LANG_COLUMN_NOT_FOUND_IN_TABLE, st,
+            "create trigger tr NO CASCADE before update of "
+            + "doesnotexist on x for each row values 1");
+
+        // not on view
+
+        assertStatementError(LANG_INVALID_OPERATION_ON_VIEW, st,
+            "create trigger tr NO CASCADE before insert on v for "
+            + "each row values 1");
+
+        // error to use table qualifier
+
+        assertStatementError(LANG_SYNTAX_ERROR, st,
+            "create trigger tr NO CASCADE before update of x.x "
+            + "on x for each row values 1");
+
+        // error to use schema.table qualifier
+
+        assertStatementError(LANG_SYNTAX_ERROR, st,
+            "create trigger tr NO CASCADE before update of "
+            + "app.x.x on x for each row values 1");
+
+        // no params in trigger action bad
+
+        assertStatementError(LANG_NO_PARAMS_IN_TRIGGER_ACTION, st,
+            "create trigger tr NO CASCADE before delete on x for "
+            + "each row select * from x where x = ?");
+
+        st.executeUpdate("create trigger stmttrigger NO CASCADE before delete "
+            + "on x for each statement values 1");
+
+        rs = st.executeQuery(
+            "select triggername, type from sys.systriggers where "
+            + "triggername = 'STMTTRIGGER'");
+
+        expColNames = new String [] {"TRIGGERNAME", "TYPE"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"STMTTRIGGER", "S"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop trigger stmttrigger");
+
+        st.executeUpdate("create trigger rowtrigger NO CASCADE before delete "
+            + "on x for each row values 1");
+
+        rs = st.executeQuery(
+            "select triggername, type from sys.systriggers where "
+            + "triggername = 'ROWTRIGGER'");
+
+        expColNames = new String [] {"TRIGGERNAME", "TYPE"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"ROWTRIGGER", "R"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop trigger rowtrigger");
+
+        // fool around with depedencies CREATE TRIGGER
+
+        st.executeUpdate("create trigger t2 NO CASCADE before update of x,y "
+            + "on x for each row values 1");
+
+        st.executeUpdate("alter table x add constraint ck2 check(x > 0)");
+        st.executeUpdate("drop view v");
+        st.executeUpdate("create view v as select * from x");
+        st.executeUpdate("create index ix on x(x)");
+        st.executeUpdate("drop trigger t2");
+        st.executeUpdate("drop index ix");
+        st.executeUpdate("alter table x drop constraint ck2");
+
+        // MAKE SURE TRIGGER SPS IS RECOMPILED IF TABLE IS ALTERED.
+
+        st.executeUpdate("create table y (x int, y int, z int)");
+
+        st.executeUpdate("create trigger tins after insert on x referencing "
+            + "new_table as newtab for each statement insert into "
+            + "y select x, y, z from newtab");
+
+        st.executeUpdate("insert into x values (1, 1, 1)");
+
+        st.executeUpdate("alter table x add column w int default 100");
+
+        st.executeUpdate(
+            "alter table x add constraint nonulls check (w is not null)");
+
+        st.executeUpdate("insert into x values (2, 2, 2, 2)");
+
+        rs = st.executeQuery("select * from y");
+
+        expColNames = new String [] {"X", "Y", "Z"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1", "1", "1"},
+            {"2", "2", "2"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop trigger tins");
+        st.executeUpdate("drop table y");
+
+        // prove that by dropping the underlying table, we have
+        // dropped the trigger first, lets create a few other triggers
+
+        st.executeUpdate("create trigger t2 NO CASCADE before update of x,y "
+            + "on x for each row values 1");
+
+        st.executeUpdate("create trigger t3 after update of x,y on x for each "
+            + "statement values 1");
+
+        st.executeUpdate("create trigger t4 after delete on x for each "
+            + "statement values 1");
+
+        rs = st.executeQuery("select cast(triggername as char(10)), tablename "
+            + "from sys.systriggers t, sys.systables  tb"
+            + "     where t.tableid = tb.tableid order by 1");
+
+        expColNames = new String [] {"1", "TABLENAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"T2", "X"},
+            {"T3", "X"},
+            {"T4", "X"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop view v");
+        st.executeUpdate("drop table x");
+
+        rs = st.executeQuery("select cast(triggername as char(10)), tablename "
+            + "from sys.systriggers t, sys.systables  tb"
+            + "     where t.tableid = tb.tableid order by 1");
+
+        expColNames = new String [] {"1", "TABLENAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        // schema testing
+
+        st.executeUpdate("create table x (x int, y int, z int)");
+        st.executeUpdate("create schema test");
+        st.executeUpdate("create trigger test.t1 NO CASCADE before delete on "
+            + "x for each row values 1");
+        st.executeUpdate("set schema test");
+        st.executeUpdate("create trigger t2 NO CASCADE before delete on app.x "
+            + "for each row values 1");
+
+        rs = st.executeQuery(
+            "select schemaname, triggername from sys.systriggers "
+            + "t, sys.sysschemas s"
+            + " where s.schemaid = t.schemaid");
+
+        expColNames = new String [] {"SCHEMANAME", "TRIGGERNAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"TEST", "T1"},
+            {"TEST", "T2"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("set schema app");
+
+        assertStatementError(LANG_SCHEMA_NOT_EMPTY, st,
+            "drop schema test restrict");
+
+        st.executeUpdate("drop trigger test.t2");
+
+        assertStatementError(LANG_SCHEMA_NOT_EMPTY, st,
+            "drop schema test restrict");
+
+        st.executeUpdate("set schema test");
+        st.executeUpdate("drop trigger t1");
+        st.executeUpdate("set schema app");
+
+        // ok this time
+
+        st.executeUpdate("drop schema test restrict");
+        st.executeUpdate("create table t (x int, y int, c char(1))");
+
+        // try multiple values, make sure result sets don't get
+        // screwed up this time we'll print out result sets
+
+        st.executeUpdate("create trigger t1 after insert on t for each row"
+            + " values app.triggerFires('3rd')");
+
+        st.executeUpdate("create trigger t2 no cascade before insert on t for "
+            + "each statement"
+            + " values app.triggerFires('1st')");
+
+        st.executeUpdate("create trigger t3 after insert on t for each row"
+            + " values app.triggerFires('4th')");
+
+        st.executeUpdate(
+            "create trigger t4 no cascade before insert on t for each row"
+            + " values app.triggerFires('2nd')");
+
+        st.executeUpdate(
+            "create trigger t5 after insert on t for each statement"
+            + " values app.triggerFires('5th')");
+
+        st.executeUpdate("insert into t values " +
+                "(2,2,'2')," +
+                "(3,3,'3')," +
+                "(4,4,'4')");
+
+        assertTriggerOutput(
+             "TRIGGER: <1st> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" + 
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{2,2,2}\n" +
+             "\t{3,3,3}\n" +
+             "\t{4,4,4}\n" +
+             "\n" +
+             "TRIGGER: <2nd> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{2,2,2}\n" +
+             "\n" +
+             "TRIGGER: <2nd> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{3,3,3}\n" +
+             "\n" +
+             "TRIGGER: <2nd> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{4,4,4}\n" +
+             "\n" +
+             "TRIGGER: <3rd> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{2,2,2}\n" +
+             "\n" +
+             "TRIGGER: <3rd> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{3,3,3}\n" +
+             "\n" +
+             "TRIGGER: <3rd> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{4,4,4}\n" +
+             "\n" +
+             "TRIGGER: <4th> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{2,2,2}\n" +
+             "\n" +
+             "TRIGGER: <4th> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{3,3,3}\n" +
+             "\n" +
+             "TRIGGER: <4th> on statement insert into t values " + 
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{4,4,4}\n" +
+             "\n" +
+             "TRIGGER: <5th> on statement insert into t values " +
+             "(2,2,'2'),(3,3,'3'),(4,4,'4')\n" +
+             "BEFORE RESULT SET\n" +
+             "<NULL>\n" +
+             "\n" +
+             "AFTER RESULT SET\n" +
+             "\t X,Y,C\n" +
+             "\t - - -\n" +
+             "\t{2,2,2}\n" +
+             "\t{3,3,3}\n" +
+             "\t{4,4,4}\n\n");
+   
+        assertUpdateCount(st, 3, "delete from t");
+
+        st.executeUpdate("drop trigger t1");
+        st.executeUpdate("drop trigger t2");
+        st.executeUpdate("drop trigger t3");
+        st.executeUpdate("drop trigger t4");
+        st.executeUpdate("drop trigger t5");
+        st.executeUpdate("drop table x");
+        st.executeUpdate("drop table t");
+
+        // Prove that we are firing the proper triggers based on
+        // the columns we are changing;
+
+        st.executeUpdate("create table t (c1 int, c2 int)");
+
+        st.executeUpdate("create trigger tins after insert on t for each row"
+            + " values app.triggerFiresMin('insert')");
+
+        st.executeUpdate("create trigger tdel after delete on t for each row"
+            + " values app.triggerFiresMin('delete')");
+
+        st.executeUpdate(
+            "create trigger tupc1 after update of c1 on t for each row"
+            + " values app.triggerFiresMin('update c1')");
+
+        st.executeUpdate(
+            "create trigger tupc2 after update of c2 on t for each row"
+            + " values app.triggerFiresMin('update c2')");
+
+        st.executeUpdate("create trigger tupc1c2 after update of c1,c2 on t "
+            + "for each row"
+            + " values app.triggerFiresMin('update c1,c2')");
+
+        st.executeUpdate("create trigger tupc2c1 after update of c2,c1 on t "
+            + "for each row"
+            + " values app.triggerFiresMin('update c2,c1')");
+
+        st.executeUpdate("insert into t values (1,1)");
+        assertTriggerOutput("TRIGGER: <insert>\n");
+
+        st.executeUpdate("update t set c1 = 1");
+        assertTriggerOutput(
+            "TRIGGER: <update c1>\n" +
+            "TRIGGER: <update c1,c2>\n" +
+            "TRIGGER: <update c2,c1>\n");
+
+        st.executeUpdate("update t set c2 = 1");
+        assertTriggerOutput(
+            "TRIGGER: <update c2>\n" +
+            "TRIGGER: <update c1,c2>\n" +
+            "TRIGGER: <update c2,c1>\n");
+
+        st.executeUpdate("update t set c2 = 1, c1 = 1");
+        assertTriggerOutput(
+            "TRIGGER: <update c1>\n" +
+            "TRIGGER: <update c2>\n" +
+            "TRIGGER: <update c1,c2>\n" +
+            "TRIGGER: <update c2,c1>\n");
+
+        st.executeUpdate("update t set c1 = 1, c2 = 1");
+        assertTriggerOutput(
+            "TRIGGER: <update c1>\n" +
+            "TRIGGER: <update c2>\n" +
+            "TRIGGER: <update c1,c2>\n" +
+            "TRIGGER: <update c2,c1>\n");
+
+        st.executeUpdate("delete from t");
+        assertTriggerOutput("TRIGGER: <delete>\n");
+
+        // Make sure that triggers work with delimited identifiers
+        // Make sure that text munging works correctly
+
+        st.executeUpdate("create table trigtable(\"cOlUmN1\" int, \"cOlUmN2  "
+            + "\" int, \"cOlUmN3\"\"\"\"  \" int)");
+
+        st.executeUpdate("create table trighistory(\"cOlUmN1\" int, \"cOlUmN2 "
+            + " \" int, \"cOlUmN3\"\"\"\"  \" int)");
+
+        st.executeUpdate("insert into trigtable values (1, 2, 3)");
+
+        st.executeUpdate("create trigger \"tt1\" after insert on trigtable "
+            + "referencing NEW as NEW for each row "
+            + "insert into trighistory (\"cOlUmN1\", \"cOlUmN2  "
+            + "\", \"cOlUmN3\"\"\"\"  \") values (new.\"cOlUmN1\" "
+            + "+ 5, \"NEW\".\"cOlUmN2  \" * new.\"cOlUmN3\"\"\"\"  \", 5)");
+
+        rs = st.executeQuery("select cast(triggername as char(10)), CAST "
+            + "(TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from "
+            + "sys.systriggers t, sys.sysstatements s "
+            + "     where s.stmtid = t.actionstmtid and triggername = 'tt1'");
+
+        expColNames = new String [] {"1", "2", "STMTNAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        rs.next();
+        assertEquals("tt1       ", rs.getString(1));
+        assertEquals(
+            "insert into trighistory (\"cOlUmN1\", \"cOlUmN2  \", " +
+                "\"cOlUmN3\"\"\"\"  \") " +
+            "values (new.\"cOlUmN1\" + 5, \"NEW\".\"cOlUmN2  \" * "
+                + "new.\"cOlUmN3\"\"\"\"  \", 5)",
+            rs.getString(2));
+        assertTrue(matchUUIDs(rs.getString(3)));
+        assertFalse(rs.next());
+
+        st.executeUpdate("insert into trigtable values (1, 2, 3)");
+
+        rs = st.executeQuery("select * from trighistory");
+
+        expColNames = new String [] {"cOlUmN1", "cOlUmN2  ", "cOlUmN3\"\"  "};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"6", "6", "5"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop trigger \"tt1\"");
+
+        st.executeUpdate("create trigger \"tt1\" after insert on trigtable "
+            + "referencing new as new for each row "
+            + "insert into trighistory (\"cOlUmN1\", \"cOlUmN2  "
+            + "\", \"cOlUmN3\"\"\"\"  \") values (new.\"cOlUmN1\" "
+            + "+ new.\"cOlUmN1\", \"NEW\".\"cOlUmN2  \" * "
+            + "new.\"cOlUmN3\"\"\"\"  \", new.\"cOlUmN2  \" * 3)");
+
+        rs = st.executeQuery("select cast(triggername as char(10)), CAST "
+            + "(TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from "
+            + "sys.systriggers t, sys.sysstatements s "
+            + "     where s.stmtid = t.actionstmtid and triggername = 'tt1'");
+
+        expColNames = new String [] {"1", "2", "STMTNAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        rs.next();
+        assertEquals("tt1       ", rs.getString(1));
+        assertEquals("insert into trighistory (\"cOlUmN1\", \"cOlUmN2 "
+                + " \", \"cOlUmN3\"\"\"\"  \") values "
+                + "(new.\"cOlUmN1\" + new.\"cOlUmN1\", "
+                + "\"NEW\".\"cOlUmN2  \" * new.\"cOlUmN3\"\"\"\"  "
+                + "\", new.\"cOlUmN2  \" * 3)",
+            rs.getString(2));
+        assertTrue(matchUUIDs(rs.getString(3)));
+        assertFalse(rs.next());
+
+        st.executeUpdate("insert into trigtable values (1, 2, 3)");
+
+        rs = st.executeQuery("select * from trighistory");
+
+        expColNames = new String [] {"cOlUmN1", "cOlUmN2  ", "cOlUmN3\"\"  "};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"6", "6", "5"},
+            {"2", "6", "6"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop table trigtable");
+        st.executeUpdate("drop table trighistory");
+
+        // trigger bug that got fixed mysteriously between xena
+        // and buffy
+
+        st.executeUpdate("create table trigtable1(c1 int, c2 int)");
+        st.executeUpdate("create table trighistory(trigtable char(30), " + 
+                         "c1 int, c2 int)");
+
+        st.executeUpdate(
+            "create trigger trigtable1 after update on trigtable1 "
+            + "referencing OLD as oldtable "
+            + "for each row "
+            + "insert into trighistory values ('trigtable1', "
+            + "oldtable.c1, oldtable.c2)");
+
+        st.executeUpdate("insert into trigtable1 values (1, 1)");
+
+        assertUpdateCount(st, 1,
+            "update trigtable1 set c1 = 11, c2 = 11");
+
+        rs = st.executeQuery("select * from trighistory");
+
+        expColNames = new String [] {"TRIGTABLE", "C1", "C2"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"trigtable1", "1", "1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop table trigtable1");
+        st.executeUpdate("drop table trighistory");
+
+        // Test for bug 3495 - triggers were causing deferred
+        // insert, which caused the insert to use a
+        // TemporaryRowHolderImpl. This was not being
+        // re-initialized properly when closed, and it was trying
+        // to re-insert the row from the first insert.
+
+        setAutoCommit(false);
+
+        st.executeUpdate("drop table t");
+
+        st.executeUpdate("create table t (x int)");
+        st.executeUpdate("create trigger tr after insert on t for each "
+                         + "statement values 1");
+        final PreparedStatement pSt = prepareStatement(
+            "insert into t values (?)");
+
+        rs = st.executeQuery("values (1)");
+
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+
+        BaseJDBCTestCase.assertUpdateCount(pSt, 1);
+
+        rs = st.executeQuery("values (2)");
+
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+
+        BaseJDBCTestCase.assertUpdateCount(pSt, 1);
+
+        rs = st.executeQuery("select * from t");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // Test MODE DB2SQL not as reserved keyword. beetle 4546
+
+        assertStatementError(LANG_OBJECT_DOES_NOT_EXIST, st,
+            "drop table db2sql");
+
+        assertStatementError(LANG_OBJECT_DOES_NOT_EXIST, st,
+            "drop table db2sql2");
+
+        st.executeUpdate(
+            "create table db2sql  (db2sql int, mode int, yipng int)");
+
+        st.executeUpdate("create table db2sql2 (db2sql2 int)");
+
+        // Test MODE DB2SQL on trigger.  beetle 4546
+
+        assertStatementError(LANG_OBJECT_NOT_FOUND, st,
+            "drop trigger db2sqltr1");
+
+        st.executeUpdate("create trigger db2sqltr1 after insert on db2sql "
+            + "for each row "
+            + "MODE DB2SQL "
+            + "insert into db2sql2 values (1)");
+
+        // Test optimizer plan of trigger action. Beetle 4826
+
+        setAutoCommit(true);
+
+        assertStatementError(LANG_OBJECT_DOES_NOT_EXIST, st,
+            "drop table parent");
+
+        st.executeUpdate("create table t1(a int not null primary key, b int)");
+
+        st.executeUpdate(
+            "create table parent (a int not null primary key, b int)");
+
+        st.executeUpdate("create trigger trig1 AFTER DELETE on t1 "
+            + "referencing OLD as OLD for each row "
+            + "delete from parent where a = OLD.a");
+
+        st.executeUpdate("insert into t1 values (0, 1)");
+        st.executeUpdate("insert into t1 values (1, 1)");
+        st.executeUpdate("insert into t1 values (2, 1)");
+        st.executeUpdate("insert into t1 values (3, 1)");
+
+        st.executeUpdate("insert into parent values (0, 1)");
+        st.executeUpdate("insert into parent values (1, 1)");
+        st.executeUpdate("insert into parent values (2, 1)");
+        st.executeUpdate("insert into parent values (3, 1)");
+        st.executeUpdate("insert into parent values (4, 1)");
+
+        setAutoCommit(false);
+
+        assertUpdateCount(st, 1,
+            "delete from t1 where a = 3");
+
+        rs = st.executeQuery("select type, mode, tablename from "
+            + "syscs_diag.lock_table order by tablename, type");
+
+        expColNames = new String [] {"TYPE", "MODE", "TABLENAME"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"ROW", "X", "PARENT"},
+            {"TABLE", "IX", "PARENT"},
+            {"ROW", "X", "T1"},
+            {"TABLE", "IX", "T1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rollback();
+
+        setAutoCommit(true);
+
+        st.executeUpdate("drop table t1");
+        st.executeUpdate("drop table parent");
+
+        // Test use of old AND new referencing names within the
+        // same trigger (beetle 5725).
+
+        st.executeUpdate("create table x(x int)");
+        st.executeUpdate("insert into x values (2), (8), (78)");
+
+        st.executeUpdate("create table removed (x int)");
+
+        // statement trigger
+
+        st.executeUpdate("create trigger t1 after update of x on x referencing"
+            + " old_table as old new_table as new for each "
+            + "statement insert into"
+            + " removed select x from old where x not in (select x from "
+            + " new where x < 10)");
+
+        rs = st.executeQuery("select * from x");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"2"},
+            {"8"},
+            {"78"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from removed");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        assertUpdateCount(st, 1,
+            "update x set x=18 where x=8");
+
+        rs = st.executeQuery("select * from x");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"2"},
+            {"18"},
+            {"78"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from removed");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"8"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // row trigger
+
+        st.executeUpdate("create trigger t2 after update of x on x referencing"
+            + " old as oldrow new as newrow for each row insert into"
+            + " removed values (newrow.x + oldrow.x)");
+
+        assertUpdateCount(st, 1,
+            "update x set x=28 where x=18");
+
+        rs = st.executeQuery("select * from x");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"2"},
+            {"28"},
+            {"78"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from removed");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"8"},
+            {"18"},
+            {"46"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // do an alter table, then make sure triggers recompile
+        // correctly.
+
+        st.executeUpdate("alter table x add column y int");
+
+        assertUpdateCount(st, 1,
+            "update x set x=88 where x > 44");
+
+        rs = st.executeQuery("select * from x");
+
+        expColNames = new String [] {"X", "Y"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"2", null},
+            {"28", null},
+            {"88", null}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from removed");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"8"},
+            {"18"},
+            {"46"},
+            {"78"},
+            {"166"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop table x");
+        st.executeUpdate("drop table removed");
+
+        // after
+        st.executeUpdate("create table x (x int, constraint ck check (x > 0))");
+        st.executeUpdate("create trigger tgood after insert on x for each "
+                         + "statement insert into x values 666");
+
+        assertStatementError(LANG_TRIGGER_RECURSION_EXCEEDED, st,
+            "insert into x values 1");
+
+        rs = st.executeQuery("select * from x");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        st.executeUpdate("drop trigger tgood");
+
+        st.executeUpdate("create trigger tgood after insert on x for each "
+            + "statement delete from x");
+
+        st.executeUpdate("insert into x values 1");
+
+        rs = st.executeQuery("select * from x");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        st.executeUpdate("drop trigger tgood");
+
+        st.executeUpdate("create trigger tgood after insert on x for each "
+            + "statement update x set x = x+100");
+
+        st.executeUpdate("insert into x values 1");
+
+        rs = st.executeQuery("select * from x");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"101"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop trigger tgood");
+
+        assertUpdateCount(st, 1,
+            "delete from x");
+
+        st.executeUpdate("create trigger tgood after insert on x "
+            + "for each statement insert into x values (666), (999), (333)");
+
+        assertStatementError(LANG_TRIGGER_RECURSION_EXCEEDED, st,
+            "insert into x values 1");
+
+        rs = st.executeQuery("select * from x order by 1");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        st.executeUpdate("drop trigger tgood");
+
+        assertUpdateCount(st, 0,
+            "delete from x");
+
+        st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into x values (n.x)");
+
+        assertStatementError(LANG_TRIGGER_RECURSION_EXCEEDED, st,
+            "insert into x values 7");
+
+        rs = st.executeQuery("select * from x order by 1");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        st.executeUpdate("drop trigger tgood");
+
+        assertUpdateCount(st, 0,
+            "delete from x");
+
+        st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into x values (333), (999), (333)");
+
+        assertStatementError(LANG_TRIGGER_RECURSION_EXCEEDED, st,
+            "insert into x values 1");
+
+        rs = st.executeQuery("select * from x order by 1");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        st.executeUpdate("drop trigger tgood");
+
+        st.executeUpdate("drop table x");
+
+        // Derby-388: When a set of inserts/updates is performed
+        // on a table and each update fires a trigger that in turn
+        // performs other updates, Derby will sometimes try to
+        // recompile the trigger in the middle of the update
+        // process and will throw an NPE when doing so.
+
+        st.executeUpdate("create procedure d388 () language java parameter "
+            + "style java modifies sql data"
+            + " external name "
+            + "'org.apache.derbyTesting.functionTests.tests.lang."
+            + "TriggerGeneralTest.derby388'");
+
+        // Just call the procedure; it will do the rest.
+
+        final CallableStatement cSt = prepareCall("call d388()");
+        BaseJDBCTestCase.assertUpdateCount(cSt, 0);
+
+
+        // Derby-85: It turns out that if a table t1 exists in a
+        // non-default schema and the default schema (e.g.,
+        // "SOMEUSER") doesn't exist yet (because no  objects have
+        // been created in that schema), then attempts to create a
+        // trigger on t1 using its qualified name will lead to a
+        // null pointer  exception in the Derby engine.
+
+        Connection c1 = openDefaultConnection("someuser", "pw");
+
+        c1.setAutoCommit(false);
+        Statement c1_st = c1.createStatement();
+        c1_st.executeUpdate("create table myschema.mytable (i int)");
+
+        c1_st.executeUpdate("create trigger mytrigger after update on "
+            + "myschema.mytable for each row select * from sys.systables");
+
+        c1.rollback();
+        c1.close();
+
+        // DERBY-438 - Working triggers with BLOB columns
+
+        setAutoCommit(true);
+
+        st.executeUpdate(
+            "create table t438 (id int,  cost decimal(6,2), bl blob)");
+
+        st.executeUpdate("create table t438_t (id int, bl blob, l int, nc "
+            + "decimal(6,2), oc decimal(6,2))");
+
+        st.executeUpdate("create trigger tr_438 after update on t438 "
+            + "referencing new as n old as o "
+            + "for each row "
+            + "insert into t438_t(id, bl, l, nc, oc) values (n.id, "
+            + "n.bl, length(n.bl), n.cost, o.cost)");
+
+        // initially just some small BLOB values.
+
+        st.executeUpdate("insert into t438 values (1, 34.53, cast "
+            + "(X'124594322143423214ab35f2e34c' as blob))");
+
+        st.executeUpdate("insert into t438 values (0, 95.32, null)");
+
+        st.executeUpdate(
+            "insert into t438 values (2, 22.21, cast (X'aa' as blob))");
+
+        rs = st.executeQuery(
+            "select id, cost, length(bl) from t438 order by 1");
+
+        expColNames = new String [] {"ID", "COST", "3"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", "95.32", null},
+            {"1", "34.53", "14"},
+            {"2", "22.21", "1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertUpdateCount(st, 3,
+            "update t438 set cost = cost + 1.23");
+
+        rs = st.executeQuery(
+            "select id, length(bl), l, nc, oc from t438_t order by 1,5,4");
+
+        expColNames = new String [] {"ID", "2", "L", "NC", "OC"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", null, null, "96.55", "95.32"},
+            {"1", "14", "14", "35.76", "34.53"},
+            {"2", "1", "1", "23.44", "22.21"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select id, cast (bl as blob(20)) from t438 order by 1");
+
+        expColNames = new String [] {"ID", "2"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", null},
+            {"1", "124594322143423214ab35f2e34c"},
+            {"2", "aa"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select id, cast (bl as blob(20)) from t438_t order by 1");
+
+        expColNames = new String [] {"ID", "2"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", null},
+            {"1", "124594322143423214ab35f2e34c"},
+            {"2", "aa"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop table t438");
+
+        st.executeUpdate("drop table t438_t");
+
+        // now re-start with CLOB types
+
+        st.executeUpdate(
+            "create table t438 (id int,  cost decimal(6,2), cl clob)");
+
+        st.executeUpdate("create table t438_t (id int, cl clob, l int, nc "
+            + "decimal(6,2), oc decimal(6,2))");
+
+        st.executeUpdate("create trigger tr_438 after update on t438 "
+            + "referencing new as n old as o "
+            + "for each row "
+            + "insert into t438_t(id, cl, l, nc, oc) values (n.id, "
+            + "n.cl, length(n.cl), n.cost, o.cost)");
+
+        // initially just some small CLOB values.
+
+        st.executeUpdate("insert into t438 values (1, 34.53, cast ('Italy''s "
+            + "centre-left leader Romano Prodi insists his poll "
+            + "victory is valid as contested ballots are checked.' "
+            + "as clob))");
+
+        st.executeUpdate(
+            "insert into t438 values (0, 95.32, null)");
+
+        st.executeUpdate(
+            "insert into t438 values (2, 22.21, cast ('free' as clob))");
+
+        rs = st.executeQuery(
+            "select id, cost, length(cl) from t438 order by 1");
+
+        expColNames = new String [] {"ID", "COST", "3"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", "95.32", null},
+            {"1", "34.53", "107"},
+            {"2", "22.21", "4"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertUpdateCount(st, 3,
+            "update t438 set cost = cost + 1.23");
+
+        rs = st.executeQuery(
+            "select id, length(cl), l, nc, oc from t438_t order by 1,5,4");
+
+        expColNames = new String [] {"ID", "2", "L", "NC", "OC"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", null, null, "96.55", "95.32"},
+            {"1", "107", "107", "35.76", "34.53"},
+            {"2", "4", "4", "23.44", "22.21"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select id, cast (cl as clob(60)) from t438 order by 1");
+
+        expColNames = new String [] {"ID", "2"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", null},
+            {"1", "Italy's centre-left leader Romano Prodi insists his poll " +
+                  "vic"},
+            {"2", "free"}
+        };
+
+        JDBC.assertFullResultSet(
+            rs, expRS, new String[]{"01004", "01004", "01004"});
+
+        rs = st.executeQuery(
+            "select id, cast (cl as clob(60)) from t438_t order by 1");
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", null},
+            {"1", "Italy's centre-left leader Romano Prodi insists his poll " +
+                  "vic"},
+            {"2", "free"}
+        };
+
+        JDBC.assertFullResultSet(
+            rs, expRS, new String[]{"01004", "01004", "01004"});
+
+        st.executeUpdate("drop table t438");
+        st.executeUpdate("drop table t438_t");
+
+        // Testcase showing DERBY-1258
+
+        st.executeUpdate("create table tsn (I integer, \"i\" integer)");
+        st.executeUpdate("create table tsn_t (a integer, b integer)");
+        st.executeUpdate("create trigger tr_sn after insert on tsn "
+                         + "referencing new as n "
+                         + "for each row "
+                         + "insert into tsn_t(a, b) values (n.I, n.\"i\")");
+
+        st.executeUpdate("insert into tsn values (1, 234)");
+
+        rs = st.executeQuery("select * from tsn");
+
+        expColNames = new String [] {"I", "i"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1", "234"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // Should have 1,234 as data in tsn_t
+
+        rs = st.executeQuery("select * from tsn_t");
+
+        expColNames = new String [] {"A", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1", "234"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop table tsn");
+        st.executeUpdate("drop table tsn_t");
+
+        // Testcase showing DERBY-1064
+
+        st.executeUpdate("CREATE TABLE T10641 ( X INT PRIMARY KEY )");
+        st.executeUpdate("CREATE TABLE T10641_DELETIONS ( X INT )");
+        st.executeUpdate("CREATE TABLE T10642 ("
+            + "    Y INT,"
+            + "    CONSTRAINT Y_AND_X FOREIGN KEY(Y) REFERENCES "
+            + "T10641(X) ON DELETE CASCADE)");
+
+        st.executeUpdate("CREATE TABLE T10642_DELETIONS ( Y INT )");
+
+        st.executeUpdate("CREATE TRIGGER TRIGGER_T10641"
+            + "    AFTER DELETE ON T10641"
+            + "    REFERENCING OLD AS OLD_ROW"
+            + "    FOR EACH ROW"
+            + "    INSERT INTO T10641_DELETIONS VALUES (OLD_ROW.X)");
+
+        st.executeUpdate("CREATE TRIGGER TRIGGER_T10642"
+            + "    AFTER DELETE ON T10642"
+            + "    REFERENCING OLD AS OLD_ROW"
+            + "    FOR EACH ROW"
+            + "    INSERT INTO T10642_DELETIONS VALUES (OLD_ROW.Y)");
+
+        st.executeUpdate("INSERT INTO T10641 VALUES (0)");
+        st.executeUpdate("INSERT INTO T10642 VALUES (0)");
+        st.executeUpdate("INSERT INTO T10641 VALUES (1)");
+        st.executeUpdate("INSERT INTO T10642 VALUES (1)");
+
+        rs = st.executeQuery("SELECT * FROM T10641");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("SELECT * FROM T10642");
+
+        expColNames = new String [] {"Y"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertUpdateCount(st, 2,
+            "DELETE FROM T10641");
+
+        rs = st.executeQuery("SELECT * FROM T10641");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery("SELECT * FROM T10642");
+
+        expColNames = new String [] {"Y"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery("SELECT * FROM T10641_DELETIONS");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("SELECT * FROM T10642_DELETIONS");
+
+        expColNames = new String [] {"Y"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // DERBY-1652
+
+        st.executeUpdate("create table test (testid integer not null "
+            + "    generated always as identity (start with 1, "
+            + "increment by 1), "
+            + "    info integer not null, ts timestamp not null "
+            + "default '1980-01-01-00.00.00.000000')");
+
+        st.executeUpdate("create trigger update_test "
+            + "    after update on test "
+            + "    referencing old as old "
+            + "    for each row "
+            + "    update test set ts=current_timestamp where "
+            + "testid=old.testid");
+
+        st.executeUpdate("insert into test(info) values (1),(2),(3)");
+
+        assertStatementError(LANG_TRIGGER_RECURSION_EXCEEDED, st,
+            "UPDATE TEST SET INFO = 1 WHERE TESTID = 2");
+
+        st.executeUpdate("drop table test");
+
+        // DERBY-1621 creating and dropping index on the table in
+        // the trigger action
+
+        st.executeUpdate("create table t1 (i int)");
+        st.executeUpdate("create table t2 (i int)");
+        st.executeUpdate("create trigger tt after insert on t1 for each "
+                         + "statement insert into t2 values 1");
+
+        st.executeUpdate("insert into t1 values 1");
+
+        st.executeUpdate("create unique index tu on t2(i)");
+
+        assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, st,
+            "insert into t1 values 1");
+
+        rs = st.executeQuery("select * from t2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, st,
+            "insert into t1 values 1");
+
+        rs = st.executeQuery("select * from t2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop index tu");
+
+        rs = st.executeQuery("select * from t2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("insert into t1 values 1");
+
+        rs = st.executeQuery("select * from t2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop trigger tt");
+
+        // dropping a table which the trigger references, should
+        // fail (DERBY-2041)
+
+        st.executeUpdate("create table t3 (i int)");
+
+        st.executeUpdate("create table t4 (i int)");
+
+        st.executeUpdate("create trigger tt2 after insert on t3 for each "
+            + "statement insert into t4 values 1");
+
+        st.executeUpdate("insert into t3 values 1");
+
+        rs = st.executeQuery("select * from t4");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertStatementError(LANG_PROVIDER_HAS_DEPENDENT_OBJECT, st,
+            "drop table t4");
+
+        st.executeUpdate("insert into t3 values 1");
+
+        rs = st.executeQuery("select * from t4");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // dropping a function which the trigger references
+
+        st.executeUpdate("create function max_value(x int, y int) returns int "
+            + "language java parameter style java external name "
+            + "'java.lang.Math.max'");
+
+        st.executeUpdate("create table test(a integer)");
+
+        st.executeUpdate("create trigger test_trigger AFTER insert on test "
+            + "FOR EACH ROW values max_value(2,4)");
+
+        st.executeUpdate("insert into test values(1)");
+
+        //- drop function should fail (DERBY-2041)
+
+        assertStatementError(LANG_PROVIDER_HAS_DEPENDENT_OBJECT, st,
+            "drop function max_value");
+
+        st.executeUpdate("insert into test values(2)");
+        st.executeUpdate("insert into test values(1)");
+
+        // dropping a view which the trigger references
+
+        st.executeUpdate("create table t11TriggerTest (c111 int not null "
+            + "primary key, c112 int)");
+
+        st.executeUpdate("insert into t11TriggerTest values(1,1)");
+        st.executeUpdate("insert into t11TriggerTest values(2,2)");
+
+        // create a view based on table t11TriggerTest
+
+        st.executeUpdate(
+            "create view v21ViewTest as select * from t11TriggerTest");
+
+        // get ready to create a trigger. Trigger is created on
+        // t31TriggerTest and it inserts into t32TriggerTest
+
+        st.executeUpdate("create table t31TriggerTest (c311 int)");
+        st.executeUpdate("create table t32TriggerTest (c321 int)");
+
+        st.executeUpdate("create trigger tr31t31TriggerTest after insert on "
+            + "t31TriggerTest for each statement"
+            + "   insert into t32TriggerTest values (select c111 "
+            + "from v21ViewTest where c112=1)");
+
+        // try an insert which will fire the trigger
+
+        st.executeUpdate("insert into t31TriggerTest values(1)");
+
+        rs = st.executeQuery("select * from t31TriggerTest");
+
+        expColNames = new String [] {"C311"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // we know the trigger got fired if there is one row in
+        // t32TriggerTest
+
+        rs = st.executeQuery("select * from t32TriggerTest");
+
+        expColNames = new String [] {"C321"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // drop the view used by the trigger. should fail after
+        // DERBY-2041.
+
+        assertStatementError(LANG_PROVIDER_HAS_DEPENDENT_OBJECT, st,
+            "drop view v21ViewTest");
+
+        // try an insert which would cause insert trigger to fire.
+
+        st.executeUpdate("insert into t31TriggerTest values(1)");
+
+        rs = st.executeQuery("select * from t31TriggerTest");
+
+        expColNames = new String [] {"C311"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from t32TriggerTest");
+
+        expColNames = new String [] {"C321"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // DERBY-630 NPE in CREATE TRIGGER when compilation schema
+        // is other than APP.
+
+        Connection user1_c = openDefaultConnection("user1", "pw");
+
+        Statement u1_st = user1_c.createStatement();
+
+        u1_st.executeUpdate("create table ippo.t1 (i int)");
+        u1_st.executeUpdate("create table ippo.t2 (i int)");
+        u1_st.executeUpdate("create index ippo.idx2 on t2(i)");
+
+        u1_st.executeUpdate(
+            "create trigger ippo.tr1 after insert on ippo.t1 for "
+            + "each row mode db2sql "
+            + "   insert into ippo.t2 values 1");
+
+        u1_st.executeUpdate("insert into ippo.t1 values 1");
+
+        u1_st.executeUpdate("insert into ippo.t1 values 1");
+
+        ResultSet u1_rs = u1_st.executeQuery("select * from ippo.t2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(u1_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(u1_rs, expRS, true);
+
+        u1_st.executeUpdate("drop index ippo.idx2");
+        u1_st.executeUpdate("insert into ippo.t1 values 1");
+
+        u1_rs = u1_st.executeQuery("select * from ippo.t2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(u1_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"},
+            {"1"}
+        };
+
+        JDBC.assertFullResultSet(u1_rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger ippo.tr1");
+        u1_st.executeUpdate("drop table ippo.t2");
+        u1_st.executeUpdate("drop table ippo.t1");
+        u1_st.executeUpdate("drop schema ippo restrict");
+
+        // DERBY-1953 if neither FOR EACH STATEMENT or FOR EACH
+        // ROW is specified, FOR EACH STATEMENT is implicit.
+
+        u1_st.executeUpdate("create table topt1 (i int)");
+        u1_st.executeUpdate("insert into topt1 values 1,2,3");
+
+        u1_st.executeUpdate("create table topt2 (i int)");
+
+        // expect error
+
+        assertStatementError(LANG_SYNTAX_ERROR, u1_st,
+            "create trigger tropt after insert on topt1 for each "
+            + "mode db2sql insert into topt2 values 1");
+
+        // ok
+
+        u1_st.executeUpdate(
+            "create trigger tropt after insert on topt1 insert "
+            + "into topt2 values 1");
+
+        u1_st.executeUpdate("insert into topt1 values 4,5,6");
+
+        // expect 1 row
+
+        rs = u1_st.executeQuery("select * from topt2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][]{{"1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger tropt");
+
+        assertUpdateCount(u1_st, 1,
+            "delete from topt2");
+
+        u1_st.executeUpdate("create trigger tropt after insert on topt1 "
+            + "referencing new_table as new_opt1 insert into topt2 "
+            + "select * from new_opt1");
+
+        u1_st.executeUpdate("insert into topt1 values 7,8,9");
+
+        // expect 3 rows
+
+        rs = u1_st.executeQuery("select * from topt2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"7"},
+            {"8"},
+            {"9"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger tropt");
+
+        assertUpdateCount(u1_st, 3,
+            "delete from topt2");
+
+        u1_st.executeUpdate("create trigger tropt after insert on topt1 "
+            + "referencing new_table as new_opt1 for each "
+            + "statement insert into topt2 select * from new_opt1");
+
+        u1_st.executeUpdate("insert into topt1 values 10,11,12");
+
+        // expect 3 rows
+
+        rs = u1_st.executeQuery("select * from topt2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"10"},
+            {"11"},
+            {"12"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger tropt");
+
+        assertUpdateCount(u1_st, 3,
+            "delete from topt2");
+
+        u1_st.executeUpdate("create trigger tropt after update on topt1 "
+            + "referencing old as oldrow for each row insert into "
+            + "topt2 values oldrow.i");
+
+        assertUpdateCount(u1_st, 12,
+            "update topt1 set i=100");
+
+        // expect 12 rows
+
+        rs = u1_st.executeQuery("select * from topt2");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"},
+            {"3"},
+            {"4"},
+            {"5"},
+            {"6"},
+            {"7"},
+            {"8"},
+            {"9"},
+            {"10"},
+            {"11"},
+            {"12"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger tropt");
+        u1_st.executeUpdate("drop table topt2");
+        u1_st.executeUpdate("drop table topt1");
+
+        // DERBY-1204 trigger causes
+        // StringIndexOutOfBoundsException which half closes
+        // connection and causes rest of test to fail. Enable this
+        // trigger test case to resolve 1204.
+
+        u1_st.executeUpdate("create table x (x int)");
+
+        // ok
+
+        u1_st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into x values (n.x), (999), (333)");
+
+        assertStatementError(LANG_TRIGGER_RECURSION_EXCEEDED, u1_st,
+            "insert into x values 1");
+
+        rs = u1_st.executeQuery("select * from x order by 1");
+
+        expColNames = new String [] {"X"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        u1_st.executeUpdate("drop trigger tgood");
+        u1_st.executeUpdate("drop table x");
+
+        u1_st.executeUpdate("create table x (i int)");
+        u1_st.executeUpdate("create table y (i int)");
+
+        // ok
+
+        u1_st.executeUpdate("create trigger tgood after insert on x "
+            + "for each statement insert into y values (666), (999), (333)");
+
+        u1_st.executeUpdate("drop trigger tgood");
+
+        // ok
+
+        u1_st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into y values (n.i)");
+
+        u1_st.executeUpdate("drop trigger tgood");
+
+        // ok
+
+        u1_st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into y values (333), (999), (333)");
+
+        u1_st.executeUpdate("drop trigger tgood");
+
+        // ok.  This used to throw StringIndexOutOfBoundsException
+
+        u1_st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into y values (n.i), (999), (333)");
+
+        u1_st.executeUpdate("insert into x values (888)");
+
+        rs = u1_st.executeQuery("select * from y");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"888"},
+            {"999"},
+            {"333"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger tgood");
+
+        assertUpdateCount(u1_st, 1,
+            "delete from x");
+
+        assertUpdateCount(u1_st, 3,
+            "delete from y");
+
+        u1_st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into y values (n.i), (n.i+1), (n.i+2)");
+
+        u1_st.executeUpdate("insert into x values (1), (4), (7)");
+
+        rs = u1_st.executeQuery("select * from y");
+
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"},
+            {"3"},
+            {"4"},
+            {"5"},
+            {"6"},
+            {"7"},
+            {"8"},
+            {"9"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger tgood");
+        u1_st.executeUpdate("drop table x");
+        u1_st.executeUpdate("drop table y");
+
+        u1_st.executeUpdate("create table x (i int, j varchar(10))");
+        u1_st.executeUpdate("create table y (i int, j varchar(10))");
+
+        u1_st.executeUpdate("create trigger tgood after insert on x "
+            + "referencing new as n "
+            + "for each row insert into y values (0, 'X'), (n.i, "
+            + "'Y'), (0, n.j), (n.i,n.j)");
+
+        u1_st.executeUpdate("insert into x values (1,'A'), (2,'B'), (3, 'C')");
+
+        rs = u1_st.executeQuery("select * from y");
+
+        expColNames = new String [] {"I", "J"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"0", "X"},
+            {"1", "Y"},
+            {"0", "A"},
+            {"1", "A"},
+            {"0", "X"},
+            {"2", "Y"},
+            {"0", "B"},
+            {"2", "B"},
+            {"0", "X"},
+            {"3", "Y"},
+            {"0", "C"},
+            {"3", "C"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        u1_st.executeUpdate("drop trigger tgood");
+        u1_st.executeUpdate("drop table x");
+        u1_st.executeUpdate("drop table y");
+        user1_c.close();
+
+        // DERBY-2183 trigger recompilation test
+        Connection user1b_c = openDefaultConnection("user1", "pw");
+        Statement u1b_st = user1b_c.createStatement();
+        CallableStatement u1b_cSt;
+
+        u1b_st.executeUpdate("set schema app");
+
+        assertStatementError(LANG_OBJECT_NOT_FOUND, u1b_st,
+            "drop trigger app.tr1");
+
+        u1b_st.executeUpdate("drop table app.t1");
+        u1b_st.executeUpdate("create table app.t1 (i int, j int)");
+        u1b_st.executeUpdate("insert into app.t1 values (1,10)");
+
+        u1b_st.executeUpdate(
+            "create trigger app.tr1 after update of i on app.t1 "
+            + "referencing old as old for each row update t1 set j "
+            + "= old.j+1");
+
+        assertUpdateCount(u1b_st, 1,
+            "update app.t1 set i=i+1");
+
+        ResultSet u1b_rs = u1b_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"I", "J"};
+        JDBC.assertColumnNames(u1b_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"2", "11"}
+        };
+
+        JDBC.assertFullResultSet(u1b_rs, expRS, true);
+
+        u1b_cSt = prepareCall(
+            "call sqlj.install_jar('file:extin/dcl_emc1.jar', "
+            + "'APP.dcl_emc1', 0)");assertUpdateCount(u1b_cSt, 0);
+
+        Connection user2_c = openDefaultConnection("user2", "pw");
+        Statement u2_st = user2_c.createStatement();
+
+        // ok
+
+        assertUpdateCount(u2_st, 1,
+            "update app.t1 set i=i+1");
+
+        ResultSet u2_rs = u2_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"I", "J"};
+        JDBC.assertColumnNames(u2_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"3", "12"}
+        };
+
+        JDBC.assertFullResultSet(u2_rs, expRS, true);
+
+        CallableStatement u2_cSt = prepareCall(
+            "call sqlj.replace_jar('file:extin/dcl_emc1.jar', 'APP.dcl_emc1')");
+        assertUpdateCount(u2_cSt, 0);
+
+        assertUpdateCount(u2_st, 1,
+            "update app.t1 set i=i+1");
+
+        u2_rs = u2_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"I", "J"};
+        JDBC.assertColumnNames(u2_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"4", "13"}
+        };
+
+        JDBC.assertFullResultSet(u2_rs, expRS, true);
+
+        u2_cSt = prepareCall(
+            "call sqlj.remove_jar('APP.dcl_emc1', 0)");
+        assertUpdateCount(u2_cSt, 0);
+
+        assertUpdateCount(u2_st, 1,
+            "update app.t1 set i=i+1");
+
+        u2_rs = u2_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"I", "J"};
+        JDBC.assertColumnNames(u2_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"5", "14"}
+        };
+
+        JDBC.assertFullResultSet(u2_rs, expRS, true);
+
+        u2_st.executeUpdate("drop trigger app.tr1");
+        u2_st.executeUpdate("drop table app.t1");
+
+        u1b_st.executeUpdate("set schema app");
+
+        u1b_st.executeUpdate("create table app.t1 (id int, i int, j int)");
+        u1b_st.executeUpdate("insert into app.t1 values (1,10, 100)");
+        u1b_st.executeUpdate("insert into app.t1 values (2,20, 200)");
+        u1b_st.executeUpdate("insert into app.t1 values (3,30, 300)");
+
+        u1b_st.executeUpdate("create trigger app.tr1 after update on app.t1 "
+            + "referencing old as oldt  new as newt "
+            + "for each row update t1 set t1.j = CASE WHEN (oldt.j "
+            + "< 100) THEN (oldt.j + 1) ELSE 1 END WHERE"
+            + "((newt.j is null) OR (oldt.j = newt.j)) AND newt.id = t1.id");
+
+        assertUpdateCount(u1b_st, 3,
+            "update app.t1 set i=i+1");
+
+        u1b_rs = u1b_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"ID", "I", "J"};
+        JDBC.assertColumnNames(u1b_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1", "11", "1"},
+            {"2", "21", "1"},
+            {"3", "31", "1"}
+        };
+
+        JDBC.assertFullResultSet(u1b_rs, expRS, true);
+
+        u1b_cSt = prepareCall(
+            "call sqlj.install_jar('file:extin/dcl_emc1.jar', "
+            + "'APP.dcl_emc1', 0)");assertUpdateCount(u1b_cSt, 0);
+
+        // switch to user 2
+
+        // ok
+
+        assertUpdateCount(u2_st, 3,
+            "update app.t1 set i=i+1");
+
+        u2_rs = u2_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"ID", "I", "J"};
+        JDBC.assertColumnNames(u2_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1", "12", "2"},
+            {"2", "22", "2"},
+            {"3", "32", "2"}
+        };
+
+        JDBC.assertFullResultSet(u2_rs, expRS, true);
+
+        u2_cSt = prepareCall(
+            "call sqlj.replace_jar('file:extin/dcl_emc1.jar', 'APP.dcl_emc1')");
+        assertUpdateCount(u2_cSt, 0);
+
+        assertUpdateCount(u2_st, 3,
+            "update app.t1 set i=i+1");
+
+        u2_rs = u2_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"ID", "I", "J"};
+        JDBC.assertColumnNames(u2_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1", "13", "3"},
+            {"2", "23", "3"},
+            {"3", "33", "3"}
+        };
+
+        JDBC.assertFullResultSet(u2_rs, expRS, true);
+
+        u2_cSt = prepareCall(
+            "call sqlj.remove_jar('APP.dcl_emc1', 0)");
+        assertUpdateCount(u2_cSt, 0);
+
+        assertUpdateCount(u2_st, 3,
+            "update app.t1 set i=i+1");
+
+        u2_rs = u2_st.executeQuery("select * from app.t1");
+
+        expColNames = new String [] {"ID", "I", "J"};
+        JDBC.assertColumnNames(u2_rs, expColNames);
+
+        expRS = new String [][]
+        {
+            {"1", "14", "4"},
+            {"2", "24", "4"},
+            {"3", "34", "4"}
+        };
+
+        JDBC.assertFullResultSet(u2_rs, expRS, true);
+
+        u2_st.executeUpdate("drop trigger app.tr1");
+        u2_st.executeUpdate("drop table app.t1");
+        user2_c.close();
+        user1b_c.close();
+    }
+
+    /**
+     * Derby-388: When a set of inserts & updates is performed on a table
+     * and each update fires a trigger that in turn performs other updates,
+     * Derby will sometimes try to recompile the trigger in the middle
+     * of the update process and will throw an NPE when doing so.
+     *
+     * @throws java.sql.SQLException
+     */
+    public static void derby388() throws SQLException
+    {
+        final Connection conn =
+            DriverManager.getConnection("jdbc:default:connection");
+        final boolean needCommit = !conn.getAutoCommit();
+        final Statement s = conn.createStatement();
+
+        // Create our objects.
+        s.execute("CREATE TABLE D388_T1 (ID INT)");
+        s.execute("CREATE TABLE D388_T2 (ID_2 INT)");
+        s.execute(
+            "CREATE TRIGGER D388_TRIG1 AFTER UPDATE OF ID ON D388_T1" +
+            "   REFERENCING NEW AS N_ROW FOR EACH ROW" +
+            "   UPDATE D388_T2" +
+            "   SET ID_2 = " +
+            "     CASE WHEN (N_ROW.ID <= 0) THEN N_ROW.ID" +
+            "     ELSE 6 END " +
+            "   WHERE N_ROW.ID < ID_2"
+        );
+
+        if (needCommit)
+            conn.commit();
+
+        // Statement to insert into D388_T1.
+        final PreparedStatement ps1 = conn.prepareStatement(
+            "INSERT INTO D388_T1 VALUES (?)");
+
+        // Statement to insert into D388_T2.
+        final PreparedStatement ps2 = conn.prepareStatement(
+            "INSERT INTO D388_T2(ID_2) VALUES (?)");
+
+        // Statement that will cause the trigger to fire.
+        final Statement st = conn.createStatement();
+        for (int i = 0; i < 20; i++) {
+
+            for (int id = 0; id < 10; id++) {
+
+                ps2.setInt(1, id);
+                ps2.executeUpdate();
+                ps1.setInt(1, 2*id);
+                ps1.executeUpdate();
+
+                if (needCommit)
+                    conn.commit();
+
+            }
+
+            // Execute an update, which will fire the trigger.
+            // Note that having the update here is important
+            // for the reproduction.  If we try to remove the
+            // outer loop and just insert lots of rows followed
+            // by a single UPDATE, the problem won't reproduce.
+            st.execute("UPDATE D388_T1 SET ID=5");
+            if (needCommit)
+                conn.commit();
+
+        }
+
+        // Clean up.
+        s.execute("DROP TABLE D388_T1");
+        s.execute("DROP TABLE D388_T2");
+
+        if (needCommit)
+            conn.commit();
+
+        st.close();
+        ps1.close();
+        ps2.close();
+    }
+
+    public static String triggerFiresMinimal(String string) throws Throwable
+    {
+        out.println("TRIGGER: " + "<"+string+">");
+        return "";
+    }
+
+    public static String triggerFires(String string) throws Throwable
+    {
+        final TriggerExecutionContext tec = 
+                Factory.getTriggerExecutionContext();
+        out.println(
+            "TRIGGER: " + "<" + string + "> on statement " +
+            tec.getEventStatementText());
+        printTriggerChanges();
+        return "";
+    }
+
+    private static void printTriggerChanges() throws Throwable
+    {
+        final TriggerExecutionContext tec = 
+                Factory.getTriggerExecutionContext();
+        out.println("BEFORE RESULT SET");
+        BaseJDBCTestCase.dumpRs(tec.getOldRowSet(), out);
+        out.println("\nAFTER RESULT SET");
+        BaseJDBCTestCase.dumpRs(tec.getNewRowSet(), out);
+    }
+
+
+    private static void assertTriggerOutput(String expected) {
+        // reset the print stream and compare.
+        assertEquals(expected, outs.toString());
+        outs.reset();
+    }
+
+    
+    // Match TRIGGERACTN_uuid_uuid
+    private boolean matchUUIDs(String raw) {
+        final StringBuilder sb = new StringBuilder();
+        // UUID 1
+        addPat(sb, HEX_DIGIT, 8);
+
+        for (int i = 0; i < 3; i++) {
+            sb.append("-");
+            addPat(sb, HEX_DIGIT, 4);
+        }
+
+        sb.append("-");
+        addPat(sb, HEX_DIGIT, 12);
+        sb.append('_');
+
+        // UUID 2
+        addPat(sb, HEX_DIGIT, 8);
+
+        for (int i = 0; i < 3; i++) {
+            sb.append("-");
+            addPat(sb, HEX_DIGIT, 4);
+        }
+
+        sb.append("-");
+        addPat(sb, HEX_DIGIT, 12);
+
+        final Pattern p = Pattern.compile("TRIGGERACTN_" + sb.toString());
+        final Matcher m = p.matcher(raw);
+        return m.matches();
+    }
+    
+    private void addPat(StringBuilder sb, String pat, int cnt) {
+        for (int i = 0; i < cnt; i++) {
+            sb.append(pat);
+        }
+    }
+}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=1579768&r1=1579767&r2=1579768&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Thu Mar 20 21:38:20 2014
@@ -227,6 +227,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(UserLobTest.suite());
         suite.addTest(OffsetFetchNextTest.suite());
         suite.addTest(TriggerBeforeTrigTest.suite());
+        suite.addTest(TriggerGeneralTest.suite());
         suite.addTest(SequenceTest.suite());
         suite.addTest(SequencePermsTest.suite());
         suite.addTest(SequenceGeneratorTest.suite());

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java?rev=1579768&r1=1579767&r2=1579768&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java Thu Mar 20 21:38:20 2014
@@ -75,82 +75,4 @@ public class userDefMethods
 		pstmt.close();
 	}
 
-	/* ****
-	 * Derby-388: When a set of inserts & updates is performed on a table
-	 * and each update fires a trigger that in turn performs other updates,
-	 * Derby will sometimes try to recompile the trigger in the middle
-	 * of the update process and will throw an NPE when doing so.
-	 */
-	public static void derby388() throws SQLException
-	{
-		System.out.println("Running DERBY-388 Test.");
-		Connection conn = DriverManager.getConnection("jdbc:default:connection");
-		boolean needCommit = !conn.getAutoCommit();
-		Statement s = conn.createStatement();
-
-		// Create our objects.
-		s.execute("CREATE TABLE D388_T1 (ID INT)");
-		s.execute("CREATE TABLE D388_T2 (ID_2 INT)");
-		s.execute(
-			"CREATE TRIGGER D388_TRIG1 AFTER UPDATE OF ID ON D388_T1" +
-			"	REFERENCING NEW AS N_ROW FOR EACH ROW" +
-			"	UPDATE D388_T2" +
-			"	SET ID_2 = " +
-			"	  CASE WHEN (N_ROW.ID <= 0) THEN N_ROW.ID" +
-			"	  ELSE 6 END " +
-			"   WHERE N_ROW.ID < ID_2"
-		);
-
-		if (needCommit)
-			conn.commit();
-
-		// Statement to insert into D388_T1.
-		PreparedStatement ps1 = conn.prepareStatement(
-			"INSERT INTO D388_T1 VALUES (?)");
-
-		// Statement to insert into D388_T2.
-		PreparedStatement ps2 = conn.prepareStatement(
-			"INSERT INTO D388_T2(ID_2) VALUES (?)");
-
-		// Statement that will cause the trigger to fire.
-		Statement st = conn.createStatement();
-		for (int i = 0; i < 20; i++) {
-
-			for (int id = 0; id < 10; id++) {
-
-				ps2.setInt(1, id);
-				ps2.executeUpdate();
-				ps1.setInt(1, 2*id);
-				ps1.executeUpdate();
-
-				if (needCommit)
-					conn.commit();
-
-			}
-
-			// Execute an update, which will fire the trigger.
-			// Note that having the update here is important
-			// for the reproduction.  If we try to remove the
-			// outer loop and just insert lots of rows followed
-			// by a single UPDATE, the problem won't reproduce.
-			st.execute("UPDATE D388_T1 SET ID=5");
-			if (needCommit)
-				conn.commit();
-				
-		}
-
-		// Clean up.
-		s.execute("DROP TABLE D388_T1");
-		s.execute("DROP TABLE D388_T2");
-
-		if (needCommit)
-			conn.commit();
-				
-		st.close();
-		ps1.close();
-		ps2.close();
-
-		System.out.println("DERBY-388 Test Passed.");
-	}
-
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/Triggers.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/Triggers.java?rev=1579768&r1=1579767&r2=1579768&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/Triggers.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/Triggers.java Thu Mar 20 21:38:20 2014
@@ -35,19 +35,6 @@ public class Triggers
 	{
 	}
 
-	public static String triggerFiresMinimal(String string) throws Throwable
-	{
-		System.out.println("TRIGGER: " + "<"+string+">");
-		return "";
-	}
-
-	public static String triggerFires(String string) throws Throwable
-	{
-		TriggerExecutionContext tec = Factory.getTriggerExecutionContext();
-		System.out.println("TRIGGER: " + "<"+string+"> on statement "+tec.getEventStatementText());
-		printTriggerChanges();
-		return "";
-	}
 
 	public static int doNothingInt() throws Throwable
 	{
@@ -129,76 +116,6 @@ public class Triggers
 		while (s.next()) ;
 	}
 
-	private static void printTriggerChanges() throws Throwable
-	{
-		TriggerExecutionContext tec = Factory.getTriggerExecutionContext();
-		System.out.println("BEFORE RESULT SET");
-		dumpRS(tec.getOldRowSet());
-		System.out.println("\nAFTER RESULT SET");
-		dumpRS(tec.getNewRowSet());
-	}
-
-	// lifted from the metadata test	
-	private static void dumpRS(ResultSet s) throws SQLException 
-	{
-		if (s == null)
-		{
-			System.out.println("<NULL>");
-			return;
-		}
-
-		ResultSetMetaData rsmd = s.getMetaData();
-
-		// Get the number of columns in the result set
-		int numCols = rsmd.getColumnCount();
-
-		if (numCols <= 0) 
-		{
-			System.out.println("(no columns!)");
-			return;
-		}
-
-		StringBuffer heading = new StringBuffer("\t ");
-		StringBuffer underline = new StringBuffer("\t ");
-
-		int len;
-		// Display column headings
-		for (int i=1; i<=numCols; i++) 
-		{
-			if (i > 1) 
-			{
-				heading.append(",");
-				underline.append(" ");
-			}
-			len = heading.length();
-			heading.append(rsmd.getColumnLabel(i));
-			len = heading.length() - len;
-			for (int j = len; j > 0; j--)
-			{
-				underline.append("-");
-			}
-		}
-		System.out.println(heading.toString());
-		System.out.println(underline.toString());
-		
-	
-		StringBuffer row = new StringBuffer();
-		// Display data, fetching until end of the result set
-		while (s.next()) 
-		{
-			row.append("\t{");
-			// Loop through each column, getting the
-			// column data and displaying
-			for (int i=1; i<=numCols; i++) 
-			{
-				if (i > 1) row.append(",");
-				row.append(s.getString(i));
-			}
-			row.append("}\n");
-		}
-		System.out.println(row.toString());
-		s.close();
-	}
 
 	public static long returnPrimLong(long  x)
 	{



Mime
View raw message