JAVA- the database manager will call the function as a public static method in a Java class.

EXTERNAL NAME string

String describes the Java method to be called when the function is executed, and takes the following form:

class_name.method_name

The External Name cannot have any extraneous spaces.

PARAMETER STYLE

JAVA - The function will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets are returned through additional parameters to the Java method of type java.sql.ResultSet[] that are passed single entry arrays.

Derby does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.

NO SQL, CONTAINS SQL, READS SQL DATA

Indicates whether the function issues any SQL statements and, if so, what type.

CONTAINS SQL

Indicates that SQL statements that neither read nor modify SQL data can be executed by the function. Statements that are not supported in any function return a different error.

NO SQL

Indicates that the function cannot execute any SQL statements

READS SQL DATA

Indicates that some SQL statements that do not modify SQL data can be included in the function. Statements that are not supported in any stored function return a different error. This is the default value.

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT

Specifies whether the function is called if any of the input arguments is null. The result is the null value.

RETURNS NULL ON NULL INPUT

Specifies that the function is not invoked if any of the input arguments is null. The result is the null value.

CALLED ON NULL INPUT

Specifies that the function is invoked if any or all input arguments are null. This specification means that the function must be coded to test for null argument values. The function can return a null or non-null value. This is the default setting.

The function elements may appear in any order, but each type of element can only appear once. A function definition must contain these elements:

A column must not be named more than once in a single CREATE INDEX statement. Different indexes can name the same column, however.

Derby can use indexes to improve the performance of data manipulation statements (see Tuning Derby). In addition, UNIQUE indexes provide a form of data integrity checking.

Index names are unique within a schema. (Some database systems allow different tables in a single schema to have indexes of the same name, but Derby does not.) Both index and table are assumed to be in the same schema if a schema name is specified for one of the names, but not the other. If schema names are specified for both index and table, an exception will be thrown if the schema names are not the same. If no schema name is specified for either table or index, the current schema is used.

By default, Derby uses the ascending order of each column to create the index. Specifying ASC after the column name does not alter the default behavior. The DESC keyword after the column name causes Derby to use descending order for the column to create the index. Using the descending order for a column can help improve the performance of queries that require the results in mixed sort order or descending order and for queries that select the minimum or maximum value of an indexed column.

If a qualified index name is specified, the schema name cannot begin with SYS.

Indexes and Constraints

Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns. Derby has already created it for you with a system-generated name. System-generated names for indexes that back up constraints are easy to find by querying the system tables if you name your constraint. For example, to find out the name of the index that backs a constraint called FLIGHTS_PK:

CREATE INDEX OrigIndex ON Flights(orig_airport);
-- money is usually ordered from greatest to least,
-- so create the index using the descending order
CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY);
-- use a larger page size for the index
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','8192');
CREATE INDEX IXSALE ON SAMP.SALES (SALES);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',NULL);

Page Size and Key Size

Note:

The size of the key columns in an index must be equal to or smaller than half the page size. If the length of the key columns in an existing row in a table is larger than half the page size of the index, creating an index on those key columns for the table will fail. This error only occurs when creating an index if an existing row in the table fails the criteria. After an index is created, inserts may fail if the size of their associated key exceeds the criteria.

Statement Dependency System

Prepared statements that involve SELECT, INSERT, UPDATE, UPDATE WHERE CURRENT, DELETE, and DELETE WHERE CURRENT on the table referenced by the CREATE INDEX statement are invalidated when the index is created. Open cursors on the table are not affected.

CREATE PROCEDURE Statement

The CREATE PROCEDURE statement allows you to create Java stored procedures, which you can then call using the CALL PROCEDURE statement.

Indicates the estimated upper bound of returned result sets for the procedure. Default is no (zero) dynamic result sets.

LANGUAGE

JAVA- the database manager will call the procedure as a public static method in a Java class.

EXTERNAL NAME string

String describes the Java method to be called when the procedure is executed, and takes the following form:

class_name.method_name

The External Name cannot have any extraneous spaces.

PARAMETER STYLE

JAVA - The procedure will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets are returned through additional parameters to the Java method of type java.sql.ResultSet [] that are passed single entry arrays.

Derby does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.

Indicates that SQL statements that neither read nor modify SQL data can be executed by the stored procedure. Statements that are not supported in any stored procedure return a different error. MODIFIES SQL DATA is the default value.

NO SQL

Indicates that the stored procedure cannot execute any SQL statements

READS SQL DATA

Indicates that some SQL statements that do not modify SQL data can be included in the stored procedure. Statements that are not supported in any stored procedure return a different error.

MODIFIES SQL DATA

Indicates that the stored procedure can execute any SQL statement except statements that are not supported in stored procedures.

The procedure elements may appear in any order, but each type of element can only appear once. A procedure definition must contain these elements:

CREATE TABLE statement

A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).

You can specify a default value for a column. A default value is the value to be inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL. See Column Default.

You can specify storage properties such as page size for a table by calling the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure.

If a qualified table name is specified, the schema name cannot begin with SYS.

The syntaxes of Column-Level-Constraint and Table-Level Constraint are described in CONSTRAINT clause.

The IDENTITY keyword can only be specified if the data type associated with the column is one of the following exact integer types.

SMALLINT

INT

BIGINT

Column Default

For the definition of a default value, a ConstantExpression is an expression that does not refer to any table. It can include constants, date-time special registers, current schemas, users, and null.

Identity column attributes

For SMALLINT, INT, and BIGINT columns with identity attributes, Derby automatically assigns increasing integer values to the column. Identity column attributes behave like other defaults in that when an insert statement does not specify a value for the column, Derby automatically provides the value. However, the value is not a constant; Derby automatically increments the default value every time a row is inserted. Also, unlike other defaults, you are not allowed to insert a value directly into or update an identity column.

By default, the initial value of an identity column is 1, and the amount of the increment is 1. You can specify non-default values for both the initial value and the interval amount when you define the column with the key words START WITH and INCREMENT BY. And if you specify a negative number for the increment value, Derby decrements the value with each insert. If this value is 0, or positive, Derby increments the value with each insert.

The maximum and minimum values allowed in identity columns are determined by the data type of the column. Attempting to insert a value outside the range of values supported by the data type raises an exception.

Automatically generated values in an identity column are unique. Use a primary key or unique constraint on a column to guarantee uniqueness. Creating an identity column does not create an index on the column.

The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value for an identity column. See IDENTITY_VAL_LOCAL for more information.

Note:

Specify the schema, table, and column name using the same case as those names are stored in the system tables--that is, all upper case unless you used delimited identifiers when creating those database objects.

Derby keeps track of the last increment value for a column in a cache. It also stores the value of what the next increment value will be for the column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system table. Rolling back a transaction does not undo this value, and thus rolled-back transactions can leave "gaps" in the values automatically inserted into an identity column. Derby behaves this way to avoid locking a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping concurrency high.

When an insert happens within a triggered-SQL-statement, the value inserted by the triggered-SQL-statement into the identity column is available from ConnectionInfo only within the trigger code. The trigger code is also able to see the value inserted by the statement that caused the trigger to fire. However, the statement that caused the trigger to fire is not able to see the value inserted by the triggered-SQL-statement into the identity column. Likewise, triggers can be nested (or recursive). An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL statement that causes trigger T2 to fire. If both T1 and T2 insert rows into a table that cause Derby to insert into an identity column, trigger T1 cannot see the value caused by T2's insert, but T2 can see the value caused by T1's insert. Each nesting level can see increment values generated by itself and previous nesting levels, all the way to the top-level SQL statement that initiated the recursive triggers. You can only have 16 levels of trigger recursion.

For more examples of CREATE TABLE statements using the various constraints, see CONSTRAINT clause.

CREATE TRIGGER statement

A trigger defines a set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever someone deletes a row or rows from the table.

Along with constraints, triggers can help enforce data integrity rules with actions such as cascading deletes or updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending e-mail, and other useful actions.

You can define any number of triggers for a single table, including multiple triggers on the same table for the same event.

You can create a trigger in any schema except SYS. The trigger need not reside in the same schema as the table on which it is defined.

If a qualified trigger name is specified, the schema name cannot begin with SYS.

Triggers fire after all constraints have been satisfied and after the changes have been applied to the target table. Also called After triggers, they can be either row or statement triggers (see Statement versus Row Triggers).

Insert, Delete, or Update: What Causes the Trigger to Fire

A trigger is fired by one of the following database events, depending on how you define it (in Syntax above, see the third line):

INSERT

UPDATE

DELETE

You can define any number of triggers for a given event on a given table. For update, you can specify columns.

Referencing Old and New Values: The Referencing Clause

Many triggered-SQL-statements need to refer to data that is currently being changed by the database event that caused them to fire. The triggered-SQL-statement might need to refer to the new (post-change or "after") values.

Derby provides you with a number of ways to refer to data that is currently being changed by the database event that caused the trigger to fire. The easiest way to refer to the changed data in the triggered-SQL-statement is use the transition variables or transition tables.

The referencing clause allows you to provide a correlation name or alias for these transition variables by specifying OLD/NEW AS correlation-Name .

For example, if you add the following clause to the trigger definition:

REFERENCING OLD AS DELETEDROW

you can then refer to this correlation name in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id

The OLD and NEW transition variables map to a java.sql.ResultSet with a single row.

For statement triggers, transition tables serve as a table identifier for the triggered-SQL-statement or the trigger qualification. The referencing clause allows you to provide a correlation name or alias for these transition tables by specifying OLD_TABLE/NEW_TABLE AS correlation-Name

For example:

REFERENCING OLD_TABLE AS DeletedHotels

allows you to use that new identifier (DeletedHotels) in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id IN
(SELECT hotel_id FROM DeletedHotels)

The old and new transition tables map to a java.sql.ResultSet with cardinality equivalent to the number of rows affected by the triggering event.

The referencing clause can designate only one new correlation or identifier and only one old correlation or identifier. Row triggers cannot designate an identifier for a transition table and statement triggers cannot designate a correlation for transition variables.

Statement versus Row Triggers

You must specify whether a trigger is a statement trigger or a row trigger:

statement triggers

A statement trigger fires once per triggering event and regardless of whether any rows are modified by the insert, update, or delete event.

row triggers

A row trigger fires once for each row affected by the triggering event. If no rows are affected, the trigger does not fire.

Note:

An update that sets a column value to the value that it originally contained (for example, UPDATE T SET C = C) causes a row trigger to fire, even though the value of the column is the same as it was prior to the triggering event.

Triggered-SQL-statement

The action defined by the trigger is called the triggered-SQL-statement (in Syntax above, see the last line). It has the following limitations:

It must not contain any dynamic parameters (?).

It must not create, alter, or drop the table upon which the trigger is defined.

It must not add an index to or remove an index from the table on which the trigger is defined.

It must not add a trigger to or drop a trigger from the table upon which the trigger is defined.

It must not commit or roll back the current transaction or change the isolation level.

It must not execute a CALL statement.

The triggered-SQL-statement can reference database objects other than the table upon which the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be successfully recompiled upon the next execution, the invocation throws an exception and the statement that caused it to fire will be rolled back.

For more information on triggered-SQL-statements, see the Derby Developer's Guide.

Order of Execution

When a database event occurs that fires a trigger, Derby performs actions in this order:

When multiple triggers are defined for the same database event for the same table for the same trigger time (before or after), triggers are fired in the order in which they were created.

Examples

-- Statements and after triggers:
CREATE TRIGGER FLIGHTSDELETE
AFTER DELETE ON FLIGHTS
REFERENCING OLD_TABLE AS DELETEDFLIGHTS
FOR EACH STATEMENT MODE DB2SQL
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
(SELECT FLIGHT_ID FROM DELETEDFLIGHTS);
CREATE TRIGGER FLIGHTSDELETE3
AFTER DELETE ON FLIGHTS
REFERENCING OLD AS OLD
FOR EACH ROW MODE DB2SQL
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;

Note:

You can find more examples in the Derby Developer's Guide.

Trigger Recursion

The maximum trigger recursion depth is 16.

Related Information

Special system functions that return information about the current time or current user are evaluated when the trigger fires, not when it is created. Such functions include:

A view definition can contain an optional view column list to explicitly name the columns in the view. If there is no column list, the view inherits the column names from the underlying query. All columns in a view must be uniquely named.

View definitions are dependent on the tables and views referenced within the view definition. DML (data manipulation language) statements that contain view references depend on those views, as well as the objects in the view definitions that the views are dependent on. Statements that reference the view depend on indexes the view uses; which index a view uses can change from statement to statement based on how the query is optimized. For example, given: