| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.TriggerWhenClauseTest |
| |
| 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.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Savepoint; |
| import java.sql.Statement; |
| import java.util.ArrayList; |
| import java.util.Arrays; |
| import java.util.Collections; |
| import java.util.List; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Tests for the WHEN clause in CREATE TRIGGER statements, added in DERBY-534. |
| */ |
| public class TriggerWhenClauseTest extends BaseJDBCTestCase { |
| |
| /** |
| * List that tracks calls to {@code intProcedure()}. It is used to verify |
| * that triggers have fired. |
| */ |
| private static List<Integer> procedureCalls; |
| |
| private static final String SYNTAX_ERROR = "42X01"; |
| private static final String REFERENCES_SESSION_SCHEMA = "XCL51"; |
| private static final String NOT_BOOLEAN = "42X19"; |
| private static final String HAS_PARAMETER = "42Y27"; |
| private static final String HAS_DEPENDENTS = "X0Y25"; |
| private static final String TABLE_DOES_NOT_EXIST = "42X05"; |
| private static final String TRUNCATION = "22001"; |
| private static final String NOT_AUTHORIZED = "42504"; |
| private static final String NO_TABLE_PERMISSION = "42500"; |
| private static final String USER_EXCEPTION = "38000"; |
| private static final String JAVA_EXCEPTION = "XJ001"; |
| private static final String NOT_SINGLE_COLUMN = "42X39"; |
| private static final String NON_SCALAR_QUERY = "21000"; |
| private static final String TRIGGER_RECURSION = "54038"; |
| private static final String PROC_USED_AS_FUNC = "42Y03"; |
| |
| public TriggerWhenClauseTest(String name) { |
| super(name); |
| } |
| |
| public static Test suite() { |
| return TestConfiguration.sqlAuthorizationDecorator( |
| new CleanDatabaseTestSetup( |
| TestConfiguration.embeddedSuite(TriggerWhenClauseTest.class))); |
| } |
| |
| @Override |
| protected void initializeConnection(Connection conn) throws SQLException { |
| // Run the test cases with auto-commit off so that all changes to |
| // the database can be rolled back in tearDown(). |
| conn.setAutoCommit(false); |
| } |
| |
| @Override |
| protected void setUp() { |
| procedureCalls = Collections.synchronizedList(new ArrayList<Integer>()); |
| } |
| |
| @Override |
| protected void tearDown() throws Exception { |
| procedureCalls = null; |
| super.tearDown(); |
| } |
| |
| /** |
| * A procedure that takes an {@code int} argument and adds it to the |
| * {@link #procedureCalls} list. Can be used as a stored procedure to |
| * verify that a trigger has been called. Particularly useful in BEFORE |
| * triggers, as they are not allowed to modify SQL data. |
| * |
| * @param i an integer |
| */ |
| public static void intProcedure(int i) { |
| procedureCalls.add(i); |
| } |
| |
| public void testBasicSyntax() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(y varchar(20))"); |
| |
| // Create after triggers that should always be executed. Create row |
| // trigger, statement trigger and implicit statement trigger. |
| s.execute("create trigger tr01 after insert on t1 for each row " |
| + "when (true) insert into t2 values 'Executed tr01'"); |
| s.execute("create trigger tr02 after insert on t1 for each statement " |
| + "when (true) insert into t2 values 'Executed tr02'"); |
| s.execute("create trigger tr03 after insert on t1 " |
| + "when (true) insert into t2 values 'Executed tr03'"); |
| |
| // Create corresponding triggers that should never fire (their WHEN |
| // clause is false). |
| s.execute("create trigger tr04 after insert on t1 for each row " |
| + "when (false) insert into t2 values 'Executed tr04'"); |
| s.execute("create trigger tr05 after insert on t1 for each statement " |
| + "when (false) insert into t2 values 'Executed tr05'"); |
| s.execute("create trigger tr06 after insert on t1 " |
| + "when (false) insert into t2 values 'Executed tr06'"); |
| |
| // Create triggers with EXISTS subqueries in the WHEN clause. The |
| // first returns TRUE and the second returns FALSE. |
| s.execute("create trigger tr07 after insert on t1 " |
| + "when (exists (select * from sysibm.sysdummy1)) " |
| + "insert into t2 values 'Executed tr07'"); |
| s.execute("create trigger tr08 after insert on t1 " |
| + "when (exists " |
| + "(select * from sysibm.sysdummy1 where ibmreqd <> 'Y')) " |
| + "insert into t2 values 'Executed tr08'"); |
| |
| // WHEN clause returns NULL, trigger should not be fired. |
| s.execute("create trigger tr09 after insert on t1 " |
| + "when (cast(null as boolean))" |
| + "insert into t2 values 'Executed tr09'"); |
| |
| // WHEN clause contains reference to a transition variable. |
| s.execute("create trigger tr10 after insert on t1 " |
| + "referencing new as new for each row " |
| + "when (new.x <> 2) insert into t2 values 'Executed tr10'"); |
| |
| // WHEN clause contains reference to a transition table. |
| s.execute("create trigger tr11 after insert on t1 " |
| + "referencing new table as new " |
| + "when (exists (select * from new where x > 5)) " |
| + "insert into t2 values 'Executed tr11'"); |
| |
| // Scalar subqueries are allowed in the WHEN clause, but they need an |
| // extra set of parantheses. |
| // |
| // The first set of parantheses is required by the WHEN clause syntax |
| // itself: WHEN ( <search condition> ) |
| // |
| // The second set of parantheses is required by <search condition>. |
| // Follow this path through the SQL standard's syntax rules: |
| // <search condition> -> <boolean value expression> |
| // -> <boolean term> -> <boolean factor> -> <boolean test> |
| // -> <boolean primary> -> <boolean predicand> |
| // -> <nonparenthesized value expression primary> |
| // -> <scalar subquery> -> <subquery> -> <left paren> |
| assertCompileError(SYNTAX_ERROR, |
| "create trigger tr12 after insert on t1 " |
| + "when (values true) insert into t2 values 'Executed tr12'"); |
| assertCompileError(SYNTAX_ERROR, |
| "create trigger tr13 after insert on t1 " |
| + "when (select true from sysibm.sysdummy1) " |
| + "insert into t2 values 'Executed tr13'"); |
| s.execute("create trigger tr12 after insert on t1 " |
| + "when ((values true)) insert into t2 values 'Executed tr12'"); |
| s.execute("create trigger tr13 after insert on t1 " |
| + "when ((select true from sysibm.sysdummy1)) " |
| + "insert into t2 values 'Executed tr13'"); |
| |
| // Now fire the triggers and verify the results. |
| assertUpdateCount(s, 3, "insert into t1 values 1, 2, 3"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select y, count(*) from t2 group by y order by y"), |
| new String[][] { |
| { "Executed tr01", "3" }, |
| { "Executed tr02", "1" }, |
| { "Executed tr03", "1" }, |
| { "Executed tr07", "1" }, |
| { "Executed tr10", "2" }, |
| { "Executed tr12", "1" }, |
| { "Executed tr13", "1" }, |
| }); |
| |
| // Empty t2 before firing the triggers again. |
| s.execute("delete from t2"); |
| |
| // Insert more rows with different values and see that a slightly |
| // different set of triggers get fired. |
| assertUpdateCount(s, 2, "insert into t1 values 2, 6"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select y, count(*) from t2 group by y order by y"), |
| new String[][] { |
| { "Executed tr01", "2" }, |
| { "Executed tr02", "1" }, |
| { "Executed tr03", "1" }, |
| { "Executed tr07", "1" }, |
| { "Executed tr10", "1" }, |
| { "Executed tr11", "1" }, |
| { "Executed tr12", "1" }, |
| { "Executed tr13", "1" }, |
| }); |
| } |
| |
| /** |
| * A row trigger whose WHEN clause contains a subquery, used to cause a |
| * NullPointerException in some situations. |
| */ |
| public void testSubqueryInWhenClauseNPE() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(x int)"); |
| s.execute("create trigger tr1 after insert on t1 for each row " |
| + "when ((values true)) insert into t2 values 1"); |
| |
| // This statement used to result in a NullPointerException. |
| s.execute("insert into t1 values 1,2,3"); |
| } |
| |
| /** |
| * Test generated columns referenced from WHEN clauses. In particular, |
| * test that references to generated columns are disallowed in the NEW |
| * transition variable of BEFORE triggers. See DERBY-3948. |
| * |
| * @see GeneratedColumnsTest#test_024_beforeTriggers() |
| */ |
| public void testGeneratedColumns() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int, y int, " |
| + "z int generated always as (x+y))"); |
| s.execute("create table t2(x int)"); |
| s.execute("create procedure int_proc(i int) language java " |
| + "parameter style java external name '" |
| + getClass().getName() + ".intProcedure' no sql"); |
| |
| // BEFORE INSERT trigger without generated column in WHEN clause, OK. |
| s.execute("create trigger btr1 no cascade before insert on t1 " |
| + "referencing new as new for each row when (new.x < new.y) " |
| + "call int_proc(1)"); |
| |
| // BEFORE INSERT trigger with generated column in WHEN clause, fail. |
| assertCompileError(GeneratedColumnsHelper.BAD_BEFORE_TRIGGER, |
| "create trigger btr2 no cascade before insert on t1 " |
| + "referencing new as new for each row when (new.x < new.z) " |
| + "select * from sysibm.sysdummy1"); |
| |
| // BEFORE UPDATE trigger without generated column in WHEN clause, OK. |
| s.execute("create trigger btr3 no cascade before update on t1 " |
| + "referencing new as new old as old for each row " |
| + "when (new.x < old.x) call int_proc(3)"); |
| |
| // BEFORE UPDATE trigger with generated column in WHEN clause. OK, |
| // since the generated column is in the OLD transition variable. |
| s.execute("create trigger btr4 no cascade before update on t1 " |
| + "referencing old as old for each row when (old.x < old.z) " |
| + "call int_proc(4)"); |
| |
| // BEFORE UPDATE trigger with generated column in NEW transition |
| // variable, fail. |
| assertCompileError(GeneratedColumnsHelper.BAD_BEFORE_TRIGGER, |
| "create trigger btr5 no cascade before update on t1 " |
| + "referencing new as new for each row when (new.x < new.z) " |
| + "select * from sysibm.sysdummy1"); |
| |
| // BEFORE DELETE trigger without generated column in WHEN clause, OK. |
| s.execute("create trigger btr6 no cascade before delete on t1 " |
| + "referencing old as old for each row when (old.x < 3) " |
| + "call int_proc(6)"); |
| |
| // BEFORE DELETE trigger with generated column in WHEN clause. OK, |
| // since the generated column is in the OLD transition variable. |
| s.execute("create trigger btr7 no cascade before delete on t1 " |
| + "referencing old as old for each row when (old.x < old.z) " |
| + "call int_proc(7)"); |
| |
| // References to generated columns in AFTER triggers should always |
| // be allowed. |
| s.execute("create trigger atr1 after insert on t1 " |
| + "referencing new as new for each row " |
| + "when (new.x < new.z) insert into t2 values 1"); |
| s.execute("create trigger atr2 after update on t1 " |
| + "referencing new as new old as old for each row " |
| + "when (old.z < new.z) insert into t2 values 2"); |
| s.execute("create trigger atr3 after delete on t1 " |
| + "referencing old as old for each row " |
| + "when (old.x < old.z) insert into t2 values 3"); |
| |
| // Finally, fire the triggers. |
| s.execute("insert into t1(x, y) values (1, 2), (4, 3)"); |
| s.execute("update t1 set x = y"); |
| s.execute("delete from t1"); |
| |
| // Verify that the before triggers were executed as expected. |
| assertEquals(Arrays.asList(1, 3, 4, 4, 6, 7, 7), procedureCalls); |
| |
| // Verify that the after triggers were executed as expected. |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t2 order by x"), |
| new String[][]{{"1"}, {"1"}, {"2"}, {"3"}, {"3"}}); |
| } |
| |
| /** |
| * Test various illegal WHEN clauses. |
| */ |
| public void testIllegalWhenClauses() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("declare global temporary table temptable (x int) " |
| + "not logged"); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(x int)"); |
| s.execute("create procedure int_proc(i int) language java " |
| + "parameter style java external name '" |
| + getClass().getName() + ".intProcedure' no sql"); |
| |
| // CREATE TRIGGER should fail if the WHEN clause references a table |
| // in the SESSION schema. |
| assertCompileError(REFERENCES_SESSION_SCHEMA, |
| "create trigger tr1 after insert on t1 " |
| + "when (exists (select * from session.temptable)) " |
| + "insert into t2 values 1"); |
| |
| // The WHEN clause expression must be BOOLEAN. |
| assertCompileError(NOT_BOOLEAN, |
| "create trigger tr after insert on t1 " |
| + "when (1) insert into t2 values 1"); |
| assertCompileError(NOT_BOOLEAN, |
| "create trigger tr after update on t1 " |
| + "when ('abc') insert into t2 values 1"); |
| assertCompileError(NOT_BOOLEAN, |
| "create trigger tr after delete on t1 " |
| + "when ((values 1)) insert into t2 values 1"); |
| assertCompileError(NOT_BOOLEAN, |
| "create trigger tr no cascade before insert on t1 " |
| + "when ((select ibmreqd from sysibm.sysdummy1)) " |
| + "call int_proc(1)"); |
| assertCompileError(NOT_BOOLEAN, |
| "create trigger tr no cascade before insert on t1 " |
| + "when ((select ibmreqd from sysibm.sysdummy1)) " |
| + "call int_proc(1)"); |
| assertCompileError(NOT_BOOLEAN, |
| "create trigger tr no cascade before update on t1 " |
| + "referencing old as old for each row " |
| + "when (old.x) call int_proc(1)"); |
| |
| // Dynamic parameters (?) are not allowed in the WHEN clause. |
| assertCompileError(HAS_PARAMETER, |
| "create trigger tr no cascade before delete on t1 " |
| + "when (?) call int_proc(1)"); |
| assertCompileError(HAS_PARAMETER, |
| "create trigger tr after insert on t1 " |
| + "when (cast(? as boolean)) call int_proc(1)"); |
| assertCompileError(HAS_PARAMETER, |
| "create trigger tr after delete on t1 " |
| + "when ((select true from sysibm.sysdummy where ibmreqd = ?)) " |
| + "call int_proc(1)"); |
| |
| // Subqueries in the WHEN clause must have a single column |
| assertCompileError(NOT_SINGLE_COLUMN, |
| "create trigger tr no cascade before insert on t1 " |
| + "when ((values (true, false))) call int_proc(1)"); |
| assertCompileError(NOT_SINGLE_COLUMN, |
| "create trigger tr after update of x on t1 " |
| + "when ((select tablename, schemaid from sys.systables)) " |
| + "call int_proc(1)"); |
| } |
| |
| /** |
| * Verify that the SPS of a WHEN clause is invalidated when one of its |
| * dependencies is changed in a way that requires recompilation. |
| */ |
| public void testWhenClauseInvalidation() throws SQLException { |
| // Statement that checks the validity of the WHEN clause SPS. |
| PreparedStatement spsValid = prepareStatement( |
| "select valid from sys.sysstatements " |
| + "where stmtname like 'TRIGGERWHEN%'"); |
| |
| Statement s = createStatement(); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(x int)"); |
| s.execute("create table t3(x int)"); |
| s.execute("insert into t1 values 1"); |
| |
| s.execute("create trigger tr after insert on t2 " |
| + "referencing new as new for each row " |
| + "when (exists (select * from t1 where x = new.x)) " |
| + "insert into t3 values new.x"); |
| |
| // SPS is initially valid. |
| JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true"); |
| |
| // Compressing the table referenced in the WHEN clause should |
| // invalidate the SPS. |
| PreparedStatement compress = prepareStatement( |
| "call syscs_util.syscs_compress_table(?, 'T1', 1)"); |
| compress.setString(1, TestConfiguration.getCurrent().getUserName()); |
| compress.execute(); |
| JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "false"); |
| |
| // Invoking the trigger should recompile the SPS. |
| s.execute("insert into t2 values 0,1,2"); |
| JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select * from t3"), "1"); |
| } |
| |
| /** |
| * Test that dropping objects referenced from the WHEN clause will |
| * detect that the trigger depends on the object. |
| */ |
| public void testDependencies() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int, y int, z int)"); |
| s.execute("create table t2(x int, y int, z int)"); |
| |
| Savepoint sp = getConnection().setSavepoint(); |
| |
| // Dropping columns referenced via the NEW transition variable in |
| // a WHEN clause should fail. |
| s.execute("create trigger tr after insert on t1 " |
| + "referencing new as new for each row " |
| + "when (new.x < new.y) values 1"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column y restrict"); |
| s.execute("alter table t1 drop column z restrict"); |
| getConnection().rollback(sp); |
| |
| // Dropping columns referenced via the OLD transition variable in |
| // a WHEN clause should fail. |
| s.execute("create trigger tr no cascade before delete on t1 " |
| + "referencing old as old for each row " |
| + "when (old.x < old.y) values 1"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column y restrict"); |
| s.execute("alter table t1 drop column z restrict"); |
| getConnection().rollback(sp); |
| |
| // Dropping columns referenced via either the OLD or the NEW |
| // transition variable referenced in the WHEN clause should fail. |
| s.execute("create trigger tr no cascade before update on t1 " |
| + "referencing old as old new as new for each row " |
| + "when (old.x < new.y) values 1"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column y restrict"); |
| s.execute("alter table t1 drop column z restrict"); |
| getConnection().rollback(sp); |
| |
| // Dropping columns referenced either in the WHEN clause or in the |
| // triggered SQL statement should fail. |
| s.execute("create trigger tr no cascade before insert on t1 " |
| + "referencing new as new for each row " |
| + "when (new.x < 5) values new.y"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column y restrict"); |
| s.execute("alter table t1 drop column z restrict"); |
| getConnection().rollback(sp); |
| |
| // Dropping any column in a statement trigger with a NEW transition |
| // table fails, even if the column is not referenced in the WHEN clause |
| // or in the triggered SQL text. |
| s.execute("create trigger tr after update of x on t1 " |
| + "referencing new table as new " |
| + "when (exists (select 1 from new where x < y)) values 1"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column y restrict"); |
| // Z is not referenced, but the transition table depends on all columns. |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column z restrict"); |
| getConnection().rollback(sp); |
| |
| // Dropping any column in a statement trigger with an OLD transition |
| // table fails, even if the column is not referenced in the WHEN clause |
| // or in the triggered SQL text. |
| s.execute("create trigger tr after delete on t1 " |
| + "referencing old table as old " |
| + "when (exists (select 1 from old where x < y)) values 1"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column y restrict"); |
| // Z is not referenced, but the transition table depends on all columns. |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column z restrict"); |
| getConnection().rollback(sp); |
| |
| // References to columns in other ways than via transition variables |
| // or transition tables should also be detected. |
| s.execute("create trigger tr after delete on t1 " |
| + "referencing old table as old " |
| + "when (exists (select 1 from t1 where x < y)) values 1"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t1 drop column y restrict"); |
| s.execute("alter table t1 drop column z restrict"); |
| getConnection().rollback(sp); |
| |
| // References to columns in another table than the trigger table |
| // should prevent them from being dropped. |
| s.execute("create trigger tr after insert on t1 " |
| + "when (exists (select * from t2 where x < y)) " |
| + "values 1"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t2 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t2 drop column y restrict"); |
| s.execute("alter table t2 drop column z restrict"); |
| |
| // Dropping a table referenced in a WHEN clause should fail and leave |
| // the trigger intact. Before DERBY-2041, DROP TABLE would succeed |
| // and leave the trigger in an invalid state so that subsequent |
| // INSERT statements would fail when trying to fire the trigger. |
| assertStatementError(HAS_DEPENDENTS, s, "drop table t2"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select triggername from sys.systriggers"), "TR"); |
| s.executeUpdate("insert into t1 values (1, 2, 3)"); |
| getConnection().rollback(sp); |
| |
| // Test references to columns in both the WHEN clause and the |
| // triggered SQL statement. |
| s.execute("create trigger tr after update on t1 " |
| + "when (exists (select * from t2 where x < 5)) " |
| + "select y from t2"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t2 drop column x restrict"); |
| assertStatementError(HAS_DEPENDENTS, s, |
| "alter table t2 drop column y restrict"); |
| s.execute("alter table t2 drop column z restrict"); |
| |
| // DROP TABLE should fail because of the dependencies (didn't before |
| // DERBY-2041). |
| assertStatementError(HAS_DEPENDENTS, s, "drop table t2"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select triggername from sys.systriggers"), "TR"); |
| getConnection().rollback(sp); |
| } |
| |
| /** |
| * Verify that DERBY-4874, which was fixed before support for the WHEN |
| * clause was implemented, does not affect the WHEN clause. |
| * The first stab at the WHEN clause implementation did suffer from it. |
| */ |
| public void testDerby4874() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t(x varchar(3))"); |
| s.execute("create trigger tr after update of x on t " |
| + "referencing new as new for each row " |
| + "when (new.x < 'abc') values 1"); |
| s.execute("insert into t values 'aaa'"); |
| |
| // Updating X to something longer than 3 characters should fail, |
| // since it's a VARCHAR(3). |
| assertStatementError(TRUNCATION, s, "update t set x = 'aaaa'"); |
| |
| // Change the type of X to VARCHAR(4) and try again. This time it |
| // should succeed, but it used to fail because the trigger hadn't |
| // been recompiled and still thought the max length was 3. |
| s.execute("alter table t alter x set data type varchar(4)"); |
| assertUpdateCount(s, 1, "update t set x = 'aaaa'"); |
| |
| // Updating it to a longer value should still fail. |
| assertStatementError(TRUNCATION, s, "update t set x = 'aaaaa'"); |
| } |
| |
| /** |
| * Verify that Cloudscape bug 4821, which was fixed long before support |
| * for the WHEN clause was implemented, does not affect the WHEN clause. |
| * The first stab at the WHEN clause implementation did suffer from it. |
| */ |
| public void testCloudscapeBug4821() throws SQLException { |
| // First create a trigger, and immediately perform an ALTER TABLE |
| // statement on the trigger table to make sure the trigger's SPS is |
| // invalid and must be recompiled the first time it's fired. |
| Statement s = createStatement(); |
| s.execute("create table cs4821.t(x int)"); |
| s.execute("create trigger cs4821.tr after insert on cs4821.t " |
| + "when (true) values 1"); |
| s.execute("alter table cs4821.t add column y int"); |
| commit(); |
| |
| // Fire the trigger and leave the transaction open afterwards. |
| s.execute("insert into cs4821.t(x) values 1"); |
| |
| // Now try to read all rows from the SYS.SYSSTATEMENTS table from |
| // another transaction. Used to time out because the transaction |
| // that recompiled the trigger kept the lock on the system table. |
| Connection c2 = openDefaultConnection(); |
| Statement s2 = c2.createStatement(); |
| JDBC.assertDrainResults( |
| s2.executeQuery("select * from sys.sysstatements")); |
| s2.close(); |
| JDBC.cleanup(c2); |
| |
| // Remove all tables and triggers created by this test case. |
| JDBC.dropSchema(getConnection().getMetaData(), "CS4821"); |
| } |
| |
| /** |
| * Test for Derby-6783. |
| */ |
| |
| public void testDerby6783() throws SQLException { |
| Statement s = createStatement(); |
| |
| s.execute("CREATE TABLE tabDerby6783(id INTEGER, result VARCHAR(10), status CHAR(1))"); |
| |
| s.execute("CREATE TRIGGER trigger6783 AFTER UPDATE OF status ON tabDerby6783 " |
| + "REFERENCING NEW AS newrow FOR EACH ROW WHEN (newrow.status='d') " |
| + "UPDATE tabDerby6783 SET result='completed' WHERE id=newrow.id"); |
| s.execute("insert into tabDerby6783 values (1, null, 'a')"); |
| // Fire the trigger. |
| s.execute("UPDATE tabDerby6783 SET status='d'"); |
| |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("SELECT result FROM tabDerby6783"), |
| "completed"); |
| } |
| |
| /** |
| * Derby6783_1_1 test, this test has two trigger fields and |
| * more than 3 column references in the update statement. |
| */ |
| |
| public void testDerby6783_1_1() throws SQLException |
| { |
| Statement s = createStatement(); |
| |
| s.execute("CREATE TABLE tabDerby6783_1_1(ID INTEGER, GRADE1 char(1), GRADE2 char(1)," |
| + " MARKS1 integer, MARKS2 integer, TOTAL_MARKS integer)"); |
| |
| s.execute("CREATE TRIGGER trigger6783_1 AFTER UPDATE OF GRADE1, GRADE2 ON tabDerby6783_1_1" |
| + " REFERENCING NEW AS newrow OLD AS oldrow" |
| + " FOR EACH ROW WHEN (oldrow.GRADE1 <> newrow.GRADE1 OR oldrow.GRADE2 <> newrow.GRADE2)" |
| + " UPDATE tabDerby6783_1_1 SET TOTAL_MARKS = oldrow.MARKS1 + oldrow.MARKS2 where id=newrow.id"); |
| |
| s.execute("INSERT INTO tabDerby6783_1_1 VALUES (1, 'a', 'b', 30, 50, 0)"); |
| // Fire the trigger. |
| s.execute("UPDATE tabDerby6783_1_1 SET GRADE1='b'"); |
| |
| |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("SELECT TOTAL_MARKS FROM tabDerby6783_1_1"), |
| "80"); |
| } |
| |
| /** |
| * Derby6783_1_2 test, is a less complex version of Derby6783_1_1 |
| * It has only one column reference in trigger part and in update part. |
| */ |
| |
| public void testDerby6783_1_2() throws SQLException |
| { |
| Statement s = createStatement(); |
| |
| s.execute("CREATE TABLE tabDerby6783_1_2(ID INTEGER, GRADE1 char(1), GRADE2 char(1)," |
| + " MARKS1 integer, MARKS2 integer, FINAL_GRADE char(1))"); |
| |
| s.execute("CREATE TRIGGER trigger6783_1 AFTER UPDATE OF MARKS1 ON tabDerby6783_1_2 " |
| + " REFERENCING NEW AS newrow OLD AS oldrow" |
| + " FOR EACH ROW WHEN (oldrow.MARKS1 <> newrow.MARKS1)" |
| + " UPDATE tabDerby6783_1_2 SET FINAL_GRADE = oldrow.GRADE1 where id=newrow.id"); |
| |
| s.execute("INSERT INTO tabDerby6783_1_2 VALUES (1, 'a', 'b', 30, 50, 'c')"); |
| |
| s.execute("UPDATE tabDerby6783_1_2 SET MARKS1=20"); |
| |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("SELECT FINAL_GRADE FROM tabDerby6783_1_2"), |
| "a"); |
| } |
| |
| /** |
| * Derby6783_2 test, this test has a single trigger column reference |
| * and two column reference in update statement. Also the when clause |
| * has a different column reference than the trigger reference |
| */ |
| |
| public void testDerby6783_2() throws SQLException |
| { |
| Statement s = createStatement(); |
| s.execute("CREATE TABLE tabDerby6783_2(ACC_NUMBER INT, BALANCE FLOAT, RATE REAL," |
| + " INTEREST REAL)"); |
| |
| s.execute("CREATE TRIGGER trigger_2 AFTER UPDATE OF BALANCE ON tabDerby6783_2 " |
| + " REFERENCING NEW AS newrow OLD AS oldrow" |
| + " FOR EACH ROW WHEN (oldrow.RATE < 10.0)" |
| + " UPDATE tabDerby6783_2 SET INTEREST = oldrow.balance + newrow.BALANCE * RATE"); |
| |
| s.execute("INSERT INTO tabDerby6783_2 VALUES (123, 12383.4534, 8.98, 2340)"); |
| |
| s.execute("UPDATE tabDerby6783_2 SET BALANCE=22383.4543"); |
| |
| s.execute("select INTEREST from tabDerby6783_2"); |
| |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("SELECT INTEREST FROM tabDerby6783_2"), |
| "213386.86"); |
| } |
| |
| /** |
| * Derby6783_3 test, this test referes to different tables in |
| * when clause and update clause. |
| */ |
| |
| public void testDerby6783_3() throws SQLException |
| { |
| Statement s = createStatement(); |
| s.execute("CREATE TABLE tabDerby6783_3_1(FIELD1 VARCHAR(10)," |
| + " FIELD2 DOUBLE)"); |
| |
| s.execute("INSERT INTO tabDerby6783_3_1 VALUES ('helloworld', 5454567)"); |
| |
| s.execute("CREATE TABLE tabDerby6783_3_2(FIELD3 NUMERIC (7,1))"); |
| |
| s.execute("INSERT INTO tabDerby6783_3_2 VALUES (3.143)"); |
| |
| s.execute("CREATE TRIGGER TRIGGER_3 AFTER UPDATE OF FIELD1 ON tabDerby6783_3_1" |
| + " REFERENCING NEW AS newrow OLD AS oldrow" |
| + " FOR EACH ROW WHEN (newrow.FIELD2 > 3000)" |
| + " UPDATE tabDerby6783_3_2 SET FIELD3 = newrow.FIELD2 / 10"); |
| |
| s.execute("UPDATE tabDerby6783_3_1 set FIELD1='hello'"); |
| |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("SELECT FIELD3 FROM tabDerby6783_3_2"), |
| "545456.7"); |
| } |
| |
| /** |
| * When SQL authorization is enabled, the trigger action (including the |
| * WHEN clause) should execute with definer's rights. Verify that it is |
| * so. |
| */ |
| public void testGrantRevoke() throws SQLException { |
| Connection c1 = openDefaultConnection("u1", "dummy"); |
| c1.setAutoCommit(true); |
| Statement s1 = c1.createStatement(); |
| |
| s1.execute("create table t1(x varchar(20))"); |
| s1.execute("create table t2(x varchar(200))"); |
| s1.execute("create table t3(x int)"); |
| s1.execute("create function is_true(s varchar(128)) returns boolean " |
| + "deterministic language java parameter style java " |
| + "external name 'java.lang.Boolean.parseBoolean' no sql"); |
| |
| // Trigger that fires on T1 if inserted value is 'true'. |
| s1.execute("create trigger tr1 after insert on t1 " |
| + "referencing new as new for each row " |
| + "when (is_true(new.x)) insert into t2(x) values new.x"); |
| |
| // Trigger that fires on T1 on insert if T3 has more than 1 row. |
| s1.execute("create trigger tr2 after insert on t1 " |
| + "when (exists (select * from t3 offset 1 row)) " |
| + "insert into t2(x) values '***'"); |
| |
| // Allow U2 to insert into T1, but nothing else on U1's schema. |
| s1.execute("grant insert on table t1 to u2"); |
| |
| Connection c2 = openDefaultConnection("u2", "dummy"); |
| c2.setAutoCommit(true); |
| Statement s2 = c2.createStatement(); |
| |
| // User U2 is not authorized to invoke the function IS_TRUE, but |
| // is allowed to insert into T1. |
| assertStatementError(NOT_AUTHORIZED, s2, "values u1.is_true('abc')"); |
| assertUpdateCount(s2, 4, |
| "insert into u1.t1(x) values 'abc', 'true', 'TrUe', 'false'"); |
| |
| // Verify that the trigger fired. Since the trigger runs with |
| // definer's rights, it should be allowed to invoke IS_TRUE in the |
| // WHEN clause even though U2 isn't allowed to invoke it directly. |
| JDBC.assertFullResultSet(s1.executeQuery("select * from t2 order by x"), |
| new String[][] {{"TrUe"}, {"true"}}); |
| s1.execute("delete from t2"); |
| |
| // Now test that TR1 will also fire, even though U2 isn't granted |
| // SELECT privileges on the table read by the WHEN clause. |
| s1.execute("insert into t3 values 1, 2"); |
| assertUpdateCount(s2, 2, "insert into u1.t1(x) values 'x', 'y'"); |
| JDBC.assertSingleValueResultSet( |
| s1.executeQuery("select * from t2 order by x"), "***"); |
| s1.execute("delete from t2"); |
| |
| // Now invalidate the triggers and make sure they still work after |
| // recompilation. |
| s1.execute("alter table t1 alter column x set data type varchar(200)"); |
| assertUpdateCount(s2, 2, "insert into u1.t1(x) values 'true', 'false'"); |
| JDBC.assertFullResultSet(s1.executeQuery("select * from t2 order by x"), |
| new String[][] {{"***"}, {"true"}}); |
| s1.execute("delete from t2"); |
| |
| // Revoke U2's insert privilege on T1. |
| s1.execute("revoke insert on table t1 from u2 "); |
| |
| // U2 should not be allowed to insert into T1 anymore. |
| assertStatementError(NO_TABLE_PERMISSION, s2, |
| "insert into u1.t1(x) values 'abc'"); |
| |
| // U1 should still be allowed to do it (since U1 owns T1), and the |
| // triggers should still be working. |
| assertUpdateCount(s1, 2, "insert into t1(x) values 'true', 'false'"); |
| JDBC.assertFullResultSet(s1.executeQuery("select * from t2 order by x"), |
| new String[][] {{"***"}, {"true"}}); |
| s1.execute("delete from t2"); |
| |
| // Now try to define a trigger in U2's schema that needs to invoke |
| // U1.IS_TRUE. Should fail because U2 isn't allowed to invoke it. |
| s2.execute("create table t(x varchar(200))"); |
| assertStatementError(NOT_AUTHORIZED, s2, |
| "create trigger tr after insert on t " |
| + "referencing new as new for each row " |
| + "when (u1.is_true(new.x)) values 1"); |
| |
| // Try again after granting execute permission to U2. |
| s1.execute("grant execute on function is_true to u2"); |
| s2.execute("create trigger tr after insert on t " |
| + "referencing new as new for each row " |
| + "when (u1.is_true(new.x)) values 1"); |
| |
| // Fire trigger. |
| assertUpdateCount(s2, 3, "insert into t values 'ab', 'cd', 'ef'"); |
| |
| // Revoking the execute permission will fail because the trigger |
| // depends on it. |
| assertStatementError(HAS_DEPENDENTS, s1, |
| "revoke execute on function is_true from u2 restrict"); |
| |
| s1.close(); |
| s2.close(); |
| |
| c2.setAutoCommit(false); |
| JDBC.dropSchema(c2.getMetaData(), "U2"); |
| c1.setAutoCommit(false); |
| JDBC.dropSchema(c1.getMetaData(), "U1"); |
| } |
| |
| /** |
| * Test that the trigger fails gracefully if the WHEN clause throws |
| * a RuntimeException. |
| */ |
| public void testRuntimeException() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create function f(x varchar(10)) returns int " |
| + "deterministic language java parameter style java " |
| + "external name 'java.lang.Integer.parseInt' no sql"); |
| s.execute("create table t1(x varchar(10))"); |
| s.execute("create table t2(x varchar(10))"); |
| s.execute("create trigger tr after insert on t1 " |
| + "referencing new as new for each row " |
| + "when (f(new.x) < 100) insert into t2 values new.x"); |
| |
| // Insert a value that causes Integer.parseInt() to throw a |
| // NumberFormatException. The NFE will be wrapped in two SQLExceptions. |
| assertStatementError(new String[] {USER_EXCEPTION, JAVA_EXCEPTION}, s, |
| "insert into t1 values '1', '2', 'hello', '3', '121'"); |
| |
| // The statement should be rolled back, so nothing should be in |
| // either of the tables. |
| assertTableRowCount("T1", 0); |
| assertTableRowCount("T2", 0); |
| |
| // Now try again with values that don't cause exceptions. |
| assertUpdateCount(s, 4, "insert into t1 values '1', '2', '3', '121'"); |
| |
| // Verify that the trigger fired this time. |
| JDBC.assertFullResultSet(s.executeQuery("select * from t2 order by x"), |
| new String[][] {{"1"}, {"2"}, {"3"}}); |
| } |
| |
| /** |
| * Test that scalar subqueries are allowed, and that non-scalar subqueries |
| * result in exceptions when the trigger fires. |
| */ |
| public void testScalarSubquery() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(x int)"); |
| s.execute("create table t3(x int)"); |
| |
| s.execute("insert into t3 values 0,1,2,2"); |
| |
| s.execute("create trigger tr1 after insert on t1 " |
| + "referencing new as new for each row " |
| + "when ((select x > 0 from t3 where x = new.x)) " |
| + "insert into t2 values 1"); |
| |
| // Subquery returns no rows, so the trigger should not fire. |
| s.execute("insert into t1 values 42"); |
| assertTableRowCount("T2", 0); |
| |
| // Subquery returns a single value, which is false, so the trigger |
| // should not fire. |
| s.execute("insert into t1 values 0"); |
| assertTableRowCount("T2", 0); |
| |
| // Subquery returns a single value, which is true, so the trigger |
| // should fire. |
| s.execute("insert into t1 values 1"); |
| assertTableRowCount("T2", 1); |
| |
| // Subquery returns multiple values, so an error should be raised. |
| assertStatementError(NON_SCALAR_QUERY, s, "insert into t1 values 2"); |
| assertTableRowCount("T2", 1); |
| } |
| |
| /** |
| * Test that a WHEN clause can call the CURRENT_USER function. |
| */ |
| public void testCurrentUser() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(x varchar(10))"); |
| |
| // Create one trigger that should only fire when current user is U2, |
| // and one that should only fire when current user is different from |
| // U2. |
| s.execute("create trigger tr01 after insert on t1 " |
| + "when (current_user = 'U2') " |
| + "insert into t2 values 'TR01'"); |
| s.execute("create trigger tr02 after insert on t1 " |
| + "when (current_user <> 'U2') " |
| + "insert into t2 values 'TR02'"); |
| s.execute("grant insert on t1 to u2"); |
| |
| commit(); |
| |
| // Used to get an assert failure or a NullPointerException here before |
| // DERBY-6348. Expect it to succeed, and expect TR02 to have fired. |
| s.execute("insert into t1 values 1"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select * from t2"), "TR02"); |
| |
| rollback(); |
| |
| // Now try the same insert as user U2. |
| Connection c2 = openUserConnection("u2"); |
| c2.setAutoCommit(true); |
| Statement s2 = c2.createStatement(); |
| s2.execute("insert into " |
| + JDBC.escape(TestConfiguration.getCurrent().getUserName(), "T1") |
| + " values 1"); |
| s2.close(); |
| c2.close(); |
| |
| // Since the insert was performed by user U2, expect TR01 to have fired. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select * from t2"), "TR01"); |
| |
| // Cleanup. |
| dropTable("T1"); |
| dropTable("T2"); |
| commit(); |
| } |
| |
| /** |
| * Test that a trigger with a WHEN clause can be recursive. |
| */ |
| public void testRecursiveTrigger() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t(x int)"); |
| s.execute("create trigger tr1 after insert on t " |
| + "referencing new as new for each row " |
| + "when (new.x > 0) insert into t values new.x - 1"); |
| |
| // Now fire the trigger. This used to cause an assert failure or a |
| // NullPointerException before DERBY-6348. |
| s.execute("insert into t values 15, 1, 2"); |
| |
| // The row trigger will fire three times, so that the above statement |
| // will insert the values { 15, 14, 13, ... , 0 }, { 1, 0 } and |
| // { 2, 1, 0 }. |
| String[][] expectedRows = { |
| {"0"}, {"0"}, {"0"}, {"1"}, {"1"}, {"1"}, {"2"}, {"2"}, {"3"}, |
| {"4"}, {"5"}, {"6"}, {"7"}, {"8"}, {"9"}, {"10"}, {"11"}, |
| {"12"}, {"13"}, {"14"}, {"15"} |
| }; |
| |
| JDBC.assertFullResultSet(s.executeQuery("select * from t order by x"), |
| expectedRows); |
| |
| // Now fire the trigger with a value so that the maximum trigger |
| // recursion depth (16) is exceeded, and verify that we get the |
| // expected error. |
| assertStatementError(TRIGGER_RECURSION, s, "insert into t values 16"); |
| |
| // The contents of the table should not have changed, since the |
| // above statement failed and was rolled back. |
| JDBC.assertFullResultSet(s.executeQuery("select * from t order by x"), |
| expectedRows); |
| } |
| |
| /** |
| * The WHEN clause text is stored in a LONG VARCHAR column in the |
| * SYS.SYSTRIGGERS table. This test case verifies that the WHEN clause |
| * is not limited to the usual LONG VARCHAR maximum length (32700 |
| * characters). |
| */ |
| public void testVeryLongWhenClause() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(x int)"); |
| |
| // Construct a WHEN clause that is more than 32700 characters. |
| StringBuilder sb = new StringBuilder("(values /* a very"); |
| for (int i = 0; i < 10000; i++) { |
| sb.append(", very"); |
| } |
| sb.append(" long comment */ true)"); |
| |
| String when = sb.toString(); |
| assertTrue(when.length() > 32700); |
| |
| s.execute("create trigger very_long_trigger after insert on t1 " |
| + "when (" + when + ") insert into t2 values 1"); |
| |
| // Verify that the WHEN clause was stored in SYS.SYSTRIGGERS. |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select whenclausetext from sys.systriggers " |
| + "where triggername = 'VERY_LONG_TRIGGER'"), |
| when); |
| |
| // Verify that the trigger fires. |
| s.execute("insert into t1 values 1"); |
| assertTableRowCount("T1", 1); |
| assertTableRowCount("T2", 1); |
| } |
| |
| /** |
| * Test a WHEN clause that invokes a function declared with READ SQL DATA. |
| */ |
| public void testFunctionReadsSQLData() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create function f(x varchar(10)) returns boolean " |
| + "language java parameter style java external name '" |
| + getClass().getName() + ".tableIsEmpty' reads sql data"); |
| |
| s.execute("create table t1(x varchar(10))"); |
| s.execute("create table t2(x varchar(10))"); |
| s.execute("create table t3(x int)"); |
| s.execute("create table t4(x int)"); |
| s.execute("insert into t3 values 1"); |
| |
| s.execute("create trigger tr after insert on t1 " |
| + "referencing new as new for each row " |
| + "when (f(new.x)) insert into t2 values new.x"); |
| |
| s.execute("insert into t1 values 'T3', 'T4', 'T3', 'T4', 'T3', 'T4'"); |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery("select x, count(x) from t2 group by x"), |
| new String[][] {{"T4", "3"}}); |
| } |
| |
| /** |
| * Stored function used by {@link #testFunctionReadsSQLData()}. It |
| * checks whether the given table is empty. |
| * |
| * @param table the table to check |
| * @return {@code true} if the table is empty, {@code false} otherwise |
| */ |
| public static boolean tableIsEmpty(String table) throws SQLException { |
| Connection c = DriverManager.getConnection("jdbc:default:connection"); |
| Statement s = c.createStatement(); |
| ResultSet rs = s.executeQuery("select * from " + JDBC.escape(table)); |
| boolean empty = !rs.next(); |
| |
| rs.close(); |
| s.close(); |
| c.close(); |
| |
| return empty; |
| } |
| |
| /** |
| * <p> |
| * SQL:2011, part 2, 11.49 <trigger definition>, syntax rule 11 |
| * says that the WHEN clause shall not contain routines that possibly |
| * modifies SQL data. Derby does not currently allow functions to be |
| * declared as MODIFIES SQL DATA. It does allow procedures to be declared |
| * as MODIFIES SQL DATA, but the current grammar does not allow procedures |
| * to be invoked from a WHEN clause. So there's currently no way to |
| * invoke routines that possibly modifies SQL data from a WHEN clause. |
| * </p> |
| * |
| * <p> |
| * This test case verifies that it is not possible to declare a function |
| * as MODIFIES SQL DATA, and that it is not possible to call a procedure |
| * from a WHEN clause. If support for any of those features is added, |
| * this test case will start failing as a reminder that code must be |
| * added to prevent routines that possibly modifies SQL data from being |
| * invoked from a WHEN clause. |
| * </p> |
| */ |
| public void testRoutineModifiesSQLData() throws SQLException { |
| // Functions cannot be declared as MODIFIES SQL DATA currently. |
| // Expect a syntax error. |
| assertCompileError(SYNTAX_ERROR, |
| "create function f(x int) returns int language java " |
| + "parameter style java external name 'java.lang.Math.abs' " |
| + "modifies sql data"); |
| |
| // Declare a procedure as MODIFIES SQL DATA. |
| Statement s = createStatement(); |
| s.execute("create procedure p(i int) language java " |
| + "parameter style java external name '" |
| + getClass().getName() + ".intProcedure' no sql"); |
| |
| // Try to call that procedure from a WHEN clause. Expect it to fail |
| // because procedure invocations aren't allowed in a WHEN clause. |
| s.execute("create table t(x int)"); |
| assertCompileError(SYNTAX_ERROR, |
| "create trigger tr after insert on t when (call p(1)) values 1"); |
| assertCompileError(PROC_USED_AS_FUNC, |
| "create trigger tr after insert on t when (p(1)) values 1"); |
| } |
| |
| /** |
| * Verify that aggregates (both built-in and user-defined) can be used |
| * in a WHEN clause. |
| */ |
| public void testAggregates() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table t1(x int)"); |
| s.execute("create table t2(y varchar(10))"); |
| s.execute("create derby aggregate mode_int for int " |
| + "external name '" + ModeAggregate.class.getName() + "'"); |
| |
| s.execute("create trigger tr1 after insert on t1 " |
| + "referencing new table as new " |
| + "when ((select max(x) from new) between 0 and 3) " |
| + "insert into t2 values 'tr1'"); |
| |
| s.execute("create trigger tr2 after insert on t1 " |
| + "referencing new table as new " |
| + "when ((select count(x) from new) between 0 and 3) " |
| + "insert into t2 values 'tr2'"); |
| |
| s.execute("create trigger tr3 after insert on t1 " |
| + "referencing new table as new " |
| + "when ((select mode_int(x) from new) between 0 and 3) " |
| + "insert into t2 values 'tr3'"); |
| |
| s.execute("insert into t1 values 2, 4, 4"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select * from t2 order by y"), |
| "tr2"); |
| |
| s.execute("delete from t2"); |
| |
| s.execute("insert into t1 values 2, 2, 3, 1, 0"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t2 order by y"), |
| new String[][] {{"tr1"}, {"tr3"}}); |
| } |
| } |
