# IDENTITY_VAL_LOCAL function

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 or a single row UPDATE statement.

## Syntax

    IDENTITY_VAL_LOCAL ( )

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 or UPDATE 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 UPDATE statement or 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 or single row UPDATE statement for a table without an identity column
- A multiple row INSERT statement with a VALUES clause
- A multiple row UPDATE statement
- 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, or an UPDATE trigger defined that updates 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
    ij> update t1 set c1=default where c2=4;
    1 row inserted/updated/deleted
    ij> values IDENTITY_VAL_LOCAL();
    1
    -------------------------------
    9
    1 row selected
    ij> select * from t1;
    C1         |C2
    -----------------------
    1          |8
    2          |1
    3          |8
    4          |9
    5          |1
    6          |2
    7          |3
    9          |4
    8 rows selected
    ij> update t1 set c1=default where c2=8;
    2 rows inserted/updated/deleted
    ij> values IDENTITY_VAL_LOCAL();
    1
    -------------------------------
    9
    1 row selected
    ij> select * from t1;
    C1         |C2
    -----------------------
    10         |8
    2          |1
    11         |8
    4          |9
    5          |1
    6          |2
    7          |3
    9          |4

Parent topic: Built-in functions
Related concepts
Autogenerated keys
Related reference
SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY system function
generationClause