# IDENTITY_VAL_LOCAL function

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.

Examples:

    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

Parent topic: Built-in functions