Derby supports
the IDENTITY_VAL_LOCAL function.
Syntax: IDENTITY_VAL_LOCAL ( )
The IDENTITY_VAL_LOCAL function is a non-deterministic function
that returns the most recently assigned value of an identity column for a
connection, where the assignment occurred as a result of a single row INSERT
statement using a VALUES clause.
The IDENTITY_VAL_LOCAL function has no input parameters. The result
is a DECIMAL (31,0), regardless of the actual data type of the corresponding
identity column.
The value returned by the IDENTITY_VAL_LOCAL function, for a connection,
is the value assigned to the identity column of the table identified in the
most recent single row INSERT statement. The INSERT statement must contain
a VALUES clause on a table containing an identity column.
The function returns a null value when a single row INSERT statement with
a VALUES clause has not been issued for a table containing an identity column.
The result of the function is not affected by the following:
- A single row INSERT statement with a VALUES clause for a table without
an identity column
- A multiple row INSERT statement with a VALUES clause
- An INSERT statement with a fullselect
If a table with an identity column has an INSERT trigger defined
that inserts into another table with another identity column, then the IDENTITY_VAL_LOCAL()
function will return the generated value for the statement table, and not
for the table modified by the trigger.
ij> create table t1(c1 int generated always as identity, c2 int);
0 rows inserted/updated/deleted
ij> insert into t1(c2) values (8);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1
1 row selected
ij> select IDENTITY_VAL_LOCAL()+1, IDENTITY_VAL_LOCAL()-1 from t1;
1 |2
-------------------------------------------------------------------
2 |0
1 row selected
ij> insert into t1(c2) values (IDENTITY_VAL_LOCAL());
1 row inserted/updated/deleted
ij> select * from t1;
C1 |C2
-------------------------------
1 |8
2 |1
2 rows selected
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
2
1 row selected
ij> insert into t1(c2) values (8), (9);
2 rows inserted/updated/deleted
ij> -- multi-values insert, return value of the function should not change
values IDENTITY_VAL_LOCAL();
1
-------------------------------
2
1 row selected
ij> select * from t1;
C1 |C2
-------------------------------
1 |8
2 |1
3 |8
4 |9
4 rows selected
ij> insert into t1(c2) select c1 from t1;
4 rows inserted/updated/deleted
-- insert with sub-select, return value should not change
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
2
1 row selected
ij> select * from t1;
C1 |C2
-------------------------------
1 |8
2 |1
3 |8
4 |9
5 |1
6 |2
7 |3
8 |4
8 rows selected
