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