/**
 *  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.AssertionFailedError;
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 \"APP\".\"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 \"APP\".\"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");

        // Check the locks, but retry the correctness of the result set for
        // a while since we have seen extraneous locks here on several system
        // tables, which should be released (DERBY-6628)
        long millis = 60000;
        boolean ok = false;
        expColNames = new String [] {"TYPE", "MODE", "TABLENAME"};
        expRS = new String [][]
        {
            {"ROW", "X", "PARENT"},
            {"TABLE", "IX", "PARENT"},
            {"ROW", "X", "T1"},
            {"TABLE", "IX", "T1"}
        };


        while (millis >= 0) {
            rs = st.executeQuery("select type, mode, tablename from " +
                    "syscs_diag.lock_table " +
                    "order by tablename, type");

            JDBC.assertColumnNames(rs, expColNames);

            try {
                JDBC.assertFullResultSet(rs, expRS, true);
                ok = true;
                break;
            } catch (AssertionFailedError t) {
                millis -= 2000;
                try {
                    Thread.sleep(2000);
                } catch (InterruptedException e) {}
            }
        }

        if (!ok) {
            // DERBY-6628: get more information
            dumpRs(st.executeQuery(
                    "select * from syscs_diag.lock_table " + 
                    "    order by tablename, type"));
            fail("Unexpected set of locks found");
        }

        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 and 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);
    }   


    /** 
     * Compare the expected output with the fired triggers' output
     * and reset the print stream used for this purpose.
     */
    private static void assertTriggerOutput(String expected) {
        // Windows: get rid of any carriage returns added by println in output 
        // before we compare since our expected output contains only newlines.
        String got = outs.toString().replaceAll("\r", "");
        
        assertEquals(expected, got);
        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);
        }
    }
}