<< Maximum and minimum values | CREATE TRIGGER statement >>

generation-clause

<< Maximum and minimum values | CREATE TRIGGER statement >>

Derby Reference Manual

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.

Example

create table greetings
(i int generated by default as identity (START WITH 2, INCREMENT BY 1),
ch char(50));
-- specify value "1":
insert into greetings values (1, 'hi');
-- use generated default
insert into greetings values (DEFAULT, 'salut');
-- use generated default
insert into greetings(ch) values ('bonjour');

generation-clause:

GENERATED ALWAYS AS ( value-expression )

A value-expression is an Expression that resolves to a single value, with some limitations
that are described here. See

SQL expressions

for more information about Expressions.

References

The generation-clause may reference other non-generated columns in the table, but it
must not reference any generated column. The generation-clause must not reference a
column in another table.

Functions

The generation-clause may invoke user-coded functions, if the functions meet the
following requirements:

· The functions must not read or write SQL data.
· The functions must have been declared DETERMINISTIC.
· The functions must not invoke any of the following possibly non-deterministic

system functions:

· CURRENT_DATE
· CURRENT_TIME
· CURRENT_TIMESTAMP
· CURRENT_USER
· CURRENT_ROLE
· CURRENT SCHEMA
· CURRENT SQLID
· SESSION_USER

Subqueries

The generation-clause must not include subqueries.

Foreign keys

If the generated column is part of a foreign key that references another table, the
referential action must not specify SET NULL or SET DEFAULT, and the update rule
must not specify ON UPDATE CASCADE.

Example

CREATE TABLE employee
(
employeeID int,