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