You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2014/03/20 22:38:21 UTC
svn commit: r1579768 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ suites/ tests/lang/ util/
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)
{