TriggerWhenClauseTest (Apache Derby V10.14 Test Javadoc)

java.lang.Object
- junit.framework.Assert
- - junit.framework.TestCase
  - - org.apache.derbyTesting.junit.BaseTestCase
    - - org.apache.derbyTesting.junit.BaseJDBCTestCase
      - org.apache.derbyTesting.functionTests.tests.lang.TriggerWhenClauseTest

All Implemented Interfaces:

junit.framework.Test
```
public class TriggerWhenClauseTest
extends BaseJDBCTestCase
```
Tests for the WHEN clause in CREATE TRIGGER statements, added in DERBY-534.

Field Summary

Fields
Modifier and Type	Field and Description
`private static java.lang.String`	`HAS_DEPENDENTS`
`private static java.lang.String`	`HAS_PARAMETER`
`private static java.lang.String`	`JAVA_EXCEPTION`
`private static java.lang.String`	`NO_TABLE_PERMISSION`
`private static java.lang.String`	`NON_SCALAR_QUERY`
`private static java.lang.String`	`NOT_AUTHORIZED`
`private static java.lang.String`	`NOT_BOOLEAN`
`private static java.lang.String`	`NOT_SINGLE_COLUMN`
`private static java.lang.String`	`PROC_USED_AS_FUNC`
`private static java.util.List<java.lang.Integer>`	`procedureCalls` List that tracks calls to `intProcedure()`.
`private static java.lang.String`	`REFERENCES_SESSION_SCHEMA`
`private static java.lang.String`	`SYNTAX_ERROR`
`private static java.lang.String`	`TABLE_DOES_NOT_EXIST`
`private static java.lang.String`	`TRIGGER_RECURSION`
`private static java.lang.String`	`TRUNCATION`
`private static java.lang.String`	`USER_EXCEPTION`

Fields inherited from class org.apache.derbyTesting.junit.BaseTestCase
DEFAULT_DB_DIR, DERBY_LOG, ERRORSTACKTRACEFILE

Constructor Summary

Constructors
Constructor and Description

TriggerWhenClauseTest(java.lang.String name)

Constructors
Constructor and Description
`TriggerWhenClauseTest(java.lang.String name)`

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
Modifier and Type	Method and Description
`protected void`	`initializeConnection(java.sql.Connection conn)` Allow a sub-class to initialize a connection to provide consistent connection state for its tests.
`static void`	`intProcedure(int i)` A procedure that takes an `int` argument and adds it to the `procedureCalls` list.
`protected void`	`setUp()`
`static junit.framework.Test`	`suite()`
`static boolean`	`tableIsEmpty(java.lang.String table)` Stored function used by `testFunctionReadsSQLData()`.
`protected void`	`tearDown()` Tear down this fixture, sub-classes should call super.tearDown().
`void`	`testAggregates()` Verify that aggregates (both built-in and user-defined) can be used in a WHEN clause.
`void`	`testBasicSyntax()`
`void`	`testCloudscapeBug4821()` Verify that Cloudscape bug 4821, which was fixed long before support for the WHEN clause was implemented, does not affect the WHEN clause.
`void`	`testCurrentUser()` Test that a WHEN clause can call the CURRENT_USER function.
`void`	`testDependencies()` Test that dropping objects referenced from the WHEN clause will detect that the trigger depends on the object.
`void`	`testDerby4874()` Verify that DERBY-4874, which was fixed before support for the WHEN clause was implemented, does not affect the WHEN clause.
`void`	`testDerby6783_1_1()` Derby6783_1_1 test, this test has two trigger fields and more than 3 column references in the update statement.
`void`	`testDerby6783_1_2()` 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.
`void`	`testDerby6783_2()` Derby6783_2 test, this test has a single trigger column reference and two column reference in update statement.
`void`	`testDerby6783_3()` Derby6783_3 test, this test referes to different tables in when clause and update clause.
`void`	`testDerby6783()` Test for Derby-6783.
`void`	`testFunctionReadsSQLData()` Test a WHEN clause that invokes a function declared with READ SQL DATA.
`void`	`testGeneratedColumns()` Test generated columns referenced from WHEN clauses.
`void`	`testGrantRevoke()` When SQL authorization is enabled, the trigger action (including the WHEN clause) should execute with definer's rights.
`void`	`testIllegalWhenClauses()` Test various illegal WHEN clauses.
`void`	`testRecursiveTrigger()` Test that a trigger with a WHEN clause can be recursive.
`void`	`testRoutineModifiesSQLData()` 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.
`void`	`testRuntimeException()` Test that the trigger fails gracefully if the WHEN clause throws a RuntimeException.
`void`	`testScalarSubquery()` Test that scalar subqueries are allowed, and that non-scalar subqueries result in exceptions when the trigger fires.
`void`	`testSubqueryInWhenClauseNPE()` A row trigger whose WHEN clause contains a subquery, used to cause a NullPointerException in some situations.
`void`	`testVeryLongWhenClause()` The WHEN clause text is stored in a LONG VARCHAR column in the SYS.SYSTRIGGERS table.
`void`	`testWhenClauseInvalidation()` Verify that the SPS of a WHEN clause is invalidated when one of its dependencies is changed in a way that requires recompilation.

Methods inherited from class org.apache.derbyTesting.junit.BaseJDBCTestCase
assertCallError, assertCheckTable, assertCommitError, assertCompileError, assertEquals, assertEquals, assertEquals, assertEquivalentDataType, assertErrorCode, assertGetIntError, assertNextError, assertPreparedStatementError, assertResults, assertResults, assertSQLExceptionEquals, assertSQLState, assertSQLState, assertStatementError, assertStatementError, assertStatementError, assertStatementError, assertStatementErrorUnordered, assertTableRowCount, assertUpdateCount, assertUpdateCount, assertWarning, chattyPrepare, chattyPrepareCall, checkAllConsistency, checkEstimatedRowCount, closeStatement, commit, createStatement, createStatement, createStatement, dropTable, dropTable, dropView, dropView, dumpRs, dumpRs, emptyStatementCache, executeQuery, expectCompilationError, expectCompilationError, expectExecutionError, getClientTransactionID, getConnection, getDatabaseProperty, getLastSQLException, goodStatement, goodUpdate, openConnection, openDefaultConnection, openDefaultConnection, openDefaultConnection, openUserConnection, prepareCall, prepareCall, prepareCall, prepareStatement, prepareStatement, prepareStatement, prepareStatement, prepareStatement, prepareStatement, rollback, runBare, runBareOverridable, runScript, runScript, runSQLCommands, setAutoCommit, usingDB2Client, usingDerbyNetClient, usingEmbedded

Methods inherited from class org.apache.derbyTesting.junit.BaseTestCase
alarm, assertDirectoryDeleted, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertExecJavaCmdAsExpected, assertLaunchedJUnitTestMethod, assertLaunchedJUnitTestMethod, assertSameNullness, assertSecurityManager, assertThrowableEquals, currentDirectory, execJavaCmd, execJavaCmd, execJavaCmd, fail, getClassVersionMajor, getEmmaJar, getFailureFolder, getFilesWith, getJavaExecutableName, getSystemProperty, getTestConfiguration, getTestResource, hasInterruptibleIO, isCVM, isIBMJVM, isJ9Platform, isJava5, isJava7, isJava8, isPhoneME, isPlatform, isSunJVM, isWindowsPlatform, newAssertionFailedError, openTestResource, println, printStackTrace, readProcessOutput, removeDirectory, removeDirectory, removeFiles, removeSystemProperty, runsWithEmma, runsWithJaCoCo, setSystemErr, setSystemOut, setSystemProperty, sleep, sleepAtLeastOneTick, traceit, vmAtLeast

Methods inherited from class junit.framework.TestCase
assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertFalse, assertFalse, assertNotNull, assertNotNull, assertNotSame, assertNotSame, assertNull, assertNull, assertSame, assertSame, assertTrue, assertTrue, countTestCases, createResult, fail, fail, failNotEquals, failNotSame, failSame, format, getName, run, run, runTest, setName, toString

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

- Field Detail
  - procedureCalls
```
private static java.util.List<java.lang.Integer> procedureCalls
```
    List that tracks calls to intProcedure(). It is used to verify that triggers have fired.
  - SYNTAX_ERROR
```
private static final java.lang.String SYNTAX_ERROR
```
    See Also:
    
    Constant Field Values
  - REFERENCES_SESSION_SCHEMA
```
private static final java.lang.String REFERENCES_SESSION_SCHEMA
```
    See Also:
    
    Constant Field Values
  - NOT_BOOLEAN
```
private static final java.lang.String NOT_BOOLEAN
```
    See Also:
    
    Constant Field Values
  - HAS_PARAMETER
```
private static final java.lang.String HAS_PARAMETER
```
    See Also:
    
    Constant Field Values
  - HAS_DEPENDENTS
```
private static final java.lang.String HAS_DEPENDENTS
```
    See Also:
    
    Constant Field Values
  - TABLE_DOES_NOT_EXIST
```
private static final java.lang.String TABLE_DOES_NOT_EXIST
```
    See Also:
    
    Constant Field Values
  - TRUNCATION
```
private static final java.lang.String TRUNCATION
```
    See Also:
    
    Constant Field Values
  - NOT_AUTHORIZED
```
private static final java.lang.String NOT_AUTHORIZED
```
    See Also:
    
    Constant Field Values
  - NO_TABLE_PERMISSION
```
private static final java.lang.String NO_TABLE_PERMISSION
```
    See Also:
    
    Constant Field Values
  - USER_EXCEPTION
```
private static final java.lang.String USER_EXCEPTION
```
    See Also:
    
    Constant Field Values
  - JAVA_EXCEPTION
```
private static final java.lang.String JAVA_EXCEPTION
```
    See Also:
    
    Constant Field Values
  - NOT_SINGLE_COLUMN
```
private static final java.lang.String NOT_SINGLE_COLUMN
```
    See Also:
    
    Constant Field Values
  - NON_SCALAR_QUERY
```
private static final java.lang.String NON_SCALAR_QUERY
```
    See Also:
    
    Constant Field Values
  - TRIGGER_RECURSION
```
private static final java.lang.String TRIGGER_RECURSION
```
    See Also:
    
    Constant Field Values
  - PROC_USED_AS_FUNC
```
private static final java.lang.String PROC_USED_AS_FUNC
```
    See Also:
    
    Constant Field Values
- Constructor Detail
  - TriggerWhenClauseTest
```
public TriggerWhenClauseTest(java.lang.String name)
```
- Method Detail
  - suite
```
public static junit.framework.Test suite()
```
  - initializeConnection
```
protected void initializeConnection(java.sql.Connection conn)
                             throws java.sql.SQLException
```
    Description copied from class: BaseJDBCTestCase
    Allow a sub-class to initialize a connection to provide consistent connection state for its tests. Called once for each time these method calls open a connection:
    - getConnection()
    - openDefaultConnection()
    - openConnection(database)
    - getDefaultConnection(String connAttrs)
    Default action is to not modify the connection's state from the initialization provided by the data source.
    Overrides:
    
    initializeConnection in class BaseJDBCTestCase
    
    Parameters:
    
    conn - Connection to be intialized
    
    Throws:
    
    java.sql.SQLException - Error setting the initial state.
  - setUp
```
protected void setUp()
```
    Overrides:
    
    setUp in class junit.framework.TestCase
  - tearDown
```
protected void tearDown()
                 throws java.lang.Exception
```
    Description copied from class: BaseJDBCTestCase
    
    Tear down this fixture, sub-classes should call super.tearDown(). This cleans up and closes the connection if it is open and any statement objects returned through the utility methods.
    
    Overrides:
    
    tearDown in class BaseJDBCTestCase
    
    Throws:
    
    java.lang.Exception
  - intProcedure
```
public static void intProcedure(int i)
```
    A procedure that takes an int argument and adds it to the 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.
    
    Parameters:
    
    i - an integer
  - testBasicSyntax
```
public void testBasicSyntax()
                     throws java.sql.SQLException
```
    Throws:
    
    java.sql.SQLException
  - testSubqueryInWhenClauseNPE
```
public void testSubqueryInWhenClauseNPE()
                                 throws java.sql.SQLException
```
    A row trigger whose WHEN clause contains a subquery, used to cause a NullPointerException in some situations.
    
    Throws:
    
    java.sql.SQLException
  - testGeneratedColumns
```
public void testGeneratedColumns()
                          throws java.sql.SQLException
```
    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.
    
    Throws:
    
    java.sql.SQLException
    
    See Also:
    
    GeneratedColumnsTest.test_024_beforeTriggers()
  - testIllegalWhenClauses
```
public void testIllegalWhenClauses()
                            throws java.sql.SQLException
```
    Test various illegal WHEN clauses.
    
    Throws:
    
    java.sql.SQLException
  - testWhenClauseInvalidation
```
public void testWhenClauseInvalidation()
                                throws java.sql.SQLException
```
    Verify that the SPS of a WHEN clause is invalidated when one of its dependencies is changed in a way that requires recompilation.
    
    Throws:
    
    java.sql.SQLException
  - testDependencies
```
public void testDependencies()
                      throws java.sql.SQLException
```
    Test that dropping objects referenced from the WHEN clause will detect that the trigger depends on the object.
    
    Throws:
    
    java.sql.SQLException
  - testDerby4874
```
public void testDerby4874()
                   throws java.sql.SQLException
```
    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.
    
    Throws:
    
    java.sql.SQLException
  - testCloudscapeBug4821
```
public void testCloudscapeBug4821()
                           throws java.sql.SQLException
```
    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.
    
    Throws:
    
    java.sql.SQLException
  - testDerby6783
```
public void testDerby6783()
                   throws java.sql.SQLException
```
    Test for Derby-6783.
    
    Throws:
    
    java.sql.SQLException
  - testDerby6783_1_1
```
public void testDerby6783_1_1()
                       throws java.sql.SQLException
```
    Derby6783_1_1 test, this test has two trigger fields and more than 3 column references in the update statement.
    
    Throws:
    
    java.sql.SQLException
  - testDerby6783_1_2
```
public void testDerby6783_1_2()
                       throws java.sql.SQLException
```
    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.
    
    Throws:
    
    java.sql.SQLException
  - testDerby6783_2
```
public void testDerby6783_2()
                     throws java.sql.SQLException
```
    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
    
    Throws:
    
    java.sql.SQLException
  - testDerby6783_3
```
public void testDerby6783_3()
                     throws java.sql.SQLException
```
    Derby6783_3 test, this test referes to different tables in when clause and update clause.
    
    Throws:
    
    java.sql.SQLException
  - testGrantRevoke
```
public void testGrantRevoke()
                     throws java.sql.SQLException
```
    When SQL authorization is enabled, the trigger action (including the WHEN clause) should execute with definer's rights. Verify that it is so.
    
    Throws:
    
    java.sql.SQLException
  - testRuntimeException
```
public void testRuntimeException()
                          throws java.sql.SQLException
```
    Test that the trigger fails gracefully if the WHEN clause throws a RuntimeException.
    
    Throws:
    
    java.sql.SQLException
  - testScalarSubquery
```
public void testScalarSubquery()
                        throws java.sql.SQLException
```
    Test that scalar subqueries are allowed, and that non-scalar subqueries result in exceptions when the trigger fires.
    
    Throws:
    
    java.sql.SQLException
  - testCurrentUser
```
public void testCurrentUser()
                     throws java.sql.SQLException
```
    Test that a WHEN clause can call the CURRENT_USER function.
    
    Throws:
    
    java.sql.SQLException
  - testRecursiveTrigger
```
public void testRecursiveTrigger()
                          throws java.sql.SQLException
```
    Test that a trigger with a WHEN clause can be recursive.
    
    Throws:
    
    java.sql.SQLException
  - testVeryLongWhenClause
```
public void testVeryLongWhenClause()
                            throws java.sql.SQLException
```
    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).
    
    Throws:
    
    java.sql.SQLException
  - testFunctionReadsSQLData
```
public void testFunctionReadsSQLData()
                              throws java.sql.SQLException
```
    Test a WHEN clause that invokes a function declared with READ SQL DATA.
    
    Throws:
    
    java.sql.SQLException
  - tableIsEmpty
```
public static boolean tableIsEmpty(java.lang.String table)
                            throws java.sql.SQLException
```
    Stored function used by testFunctionReadsSQLData(). It checks whether the given table is empty.
    
    Parameters:
    
    table - the table to check
    
    Returns:
    
    true if the table is empty, false otherwise
    
    Throws:
    
    java.sql.SQLException
  - testRoutineModifiesSQLData
```
public void testRoutineModifiesSQLData()
                                throws java.sql.SQLException
```
    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.
    
    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.
    
    Throws:
    
    java.sql.SQLException
  - testAggregates
```
public void testAggregates()
                    throws java.sql.SQLException
```
    Verify that aggregates (both built-in and user-defined) can be used in a WHEN clause.
    
    Throws:
    
    java.sql.SQLException

Class TriggerWhenClauseTest

Field Summary

Fields inherited from class org.apache.derbyTesting.junit.BaseTestCase

Constructor Summary

Method Summary

Methods inherited from class org.apache.derbyTesting.junit.BaseJDBCTestCase

Methods inherited from class org.apache.derbyTesting.junit.BaseTestCase

Methods inherited from class junit.framework.TestCase

Methods inherited from class java.lang.Object

Field Detail

procedureCalls

SYNTAX_ERROR

REFERENCES_SESSION_SCHEMA

NOT_BOOLEAN

HAS_PARAMETER

HAS_DEPENDENTS

TABLE_DOES_NOT_EXIST

TRUNCATION

NOT_AUTHORIZED

NO_TABLE_PERMISSION

USER_EXCEPTION

JAVA_EXCEPTION

NOT_SINGLE_COLUMN

NON_SCALAR_QUERY

TRIGGER_RECURSION

PROC_USED_AS_FUNC

Constructor Detail

TriggerWhenClauseTest

Method Detail

suite

initializeConnection

setUp

tearDown

intProcedure

testBasicSyntax

testSubqueryInWhenClauseNPE

testGeneratedColumns

testIllegalWhenClauses

testWhenClauseInvalidation

testDependencies

testDerby4874

testCloudscapeBug4821

testDerby6783

testDerby6783_1_1

testDerby6783_1_2

testDerby6783_2

testDerby6783_3

testGrantRevoke

testRuntimeException

testScalarSubquery

testCurrentUser

testRecursiveTrigger

testVeryLongWhenClause

testFunctionReadsSQLData

tableIsEmpty

testRoutineModifiesSQLData

testAggregates