IDENTITY_VAL_LOCAL

IDENTITY_VAL_LOCAL is a non-deterministic function that returns the most recently assigned value for an identity column.


>>-IDENTITY_VAL_LOCAL--(--)------------------------------------><

The function has no input parameters. The result is a DECIMAL(31,0) regardless of the actual data type of the identity column that the result value corresponds to.

The value returned is the value that was assigned to the identity column of the table identified in the most recent Start of change insert operation (specified in either an INSERT statement or a MERGE statement) End of change . The insert operation has to be issued at the same level; that is, the value has to be available locally within the level at which it was assigned until replaced by the next assigned value. A new level is initiated when a trigger, function, or stored procedure is invoked. A trigger condition is at the same level as the associated triggered action.

The assigned value can be a value supplied by the user (if the identity column is defined as GENERATED BY DEFAULT) or an identity value that was generated by the database manager.

The result can be null. The result is null if an Start of change insert operation End of change has not been issued for a table containing an identity column at the current processing level. This includes invoking the function in a before or after insert trigger.

The result of the IDENTITY_VAL_LOCAL function is not affected by the following statements:

An insert operation for a table which does not contain an identity column
An UPDATE statement
A COMMIT statement
A ROLLBACK statement

Notes

The following notes explain the behavior of the function when it is invoked in various situations:

Invoking the function within the VALUES clause of an insert operation

Expressions in an Start of change

insert operation End of change

are evaluated before values are assigned to the target columns of the Start of change

insert operation End of change

. Thus, when you invoke IDENTITY_VAL_LOCAL in an Start of change

insert operation End of change

, the value that is used is the most recently assigned value for an identity column from a previous Start of change

insert operation End of change

. The function returns the null value if no such Start of change

insert operation End of change

had been executed within the same level as the invocation of the IDENTITY_VAL_LOCAL function.

Invoking the function following a failed insert operation

The function returns an unpredictable result when it is invoked after the unsuccessful execution of an Start of change

insert operation End of change

for a table with an identity column. The value might be the value that would have been returned from the function had it been invoked before the failed Start of change

insert operation End of change

or the value that would have been assigned had the Start of change

insert operation End of change

succeeded. The actual value returned depends on the point of failure and is therefore unpredictable.

Invoking the function within the SELECT statement of a cursor

Because the results of the IDENTITY_VAL_LOCAL function are not deterministic, the result of an invocation of the IDENTITY_VAL_LOCAL function from within the SELECT statement of a cursor can vary for each FETCH statement.

Invoking the function within the trigger condition of an insert trigger

The result of invoking the IDENTITY_VAL_LOCAL function from within the condition of an insert trigger is the null value.

Invoking the function within a triggered action of an insert trigger

Multiple before or after insert triggers can exist for a table. In such cases, each trigger is processed separately, and identity values generated by SQL statements issued within a triggered action are not available to other triggered actions using the IDENTITY_VAL_LOCAL function. This is the case even though the multiple triggered actions are conceptually defined at the same level.

Do not use the IDENTITY_VAL_LOCAL function in the triggered action of a before insert trigger. The result of invoking the IDENTITY_VAL_LOCAL function from within the triggered action of a before insert trigger is the null value. The value for the identity column of the table for which the trigger is defined cannot be obtained by invoking the IDENTITY_VAL_LOCAL function within the triggered action of a before insert trigger. However, the value for the identity column can be obtained in the triggered action by referencing the trigger transition variable for the identity column.

The result of invoking the IDENTITY_VAL_LOCAL function in the triggered action of an after insert trigger is the value assigned to an identity column of the table identified in the most recent Start of change insert operation End of change invoked in the same triggered action for a table containing an identity column. If an insert operation for a table containing an identity column was not executed within the same triggered action before invoking the IDENTITY_VAL_LOCAL function, then the function returns a null value.

Invoking the function following an insert operation with triggered actions

The result of invoking the function after an Start of change

insert operation End of change

that activates triggers is the value actually assigned to the identity column (that is, the value that would be returned on a subsequent SELECT statement). This value is not necessarily the value provided in the Start of change

insert operation End of change

or a value generated by the database manager. The assigned value could be a value that was specified in a SET transition variable statement within the triggered action of a before insert trigger for a trigger transition variable associated with the identity column.

Scope of IDENTITY_VAL_LOCAL

The IDENTITY_VAL_LOCAL value persists until the next Start of change

insert operation End of change

in the current session into a table that has an identity column defined on it, or the application session ends. The value is unaffected by COMMIT or ROLLBACK statements. The IDENTITY_VAL_LOCAL value cannot be directly set and is a result of inserting a row into a table.

A technique commonly used, especially for performance, is for an application or product to manage a set of connections and route transactions to an arbitrary connection. In these situations, the availability of the IDENTITY_VAL_LOCAL value should only be relied on until the end of the transaction.

Alternative to IDENTITY_VAL_LOCAL:

It is recommended that a SELECT FROM INSERT be used to obtain the assigned value for an identity column. See table-reference for more information. End of change

Examples

Set the variable IVAR to the value assigned to the identity column in the EMPLOYEE table. The value returned from the function in the VALUES Start of change

INTO

statement should be 1.

  CREATE TABLE EMPLOYEE
    (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
     NAME CHAR(30),
     SALARY DECIMAL(5,2),
     DEPTNO SMALLINT)

  INSERT INTO EMPLOYEE
    (NAME, SALARY, DEPTNO)
    VALUES('Rupert', 989.99, 50)

  VALUES IDENTITY_VAL_LOCAL() INTO  :IVAR

Assume two tables, T1 and T2, have an identity column named C1. The database manager generates values 1, 2, 3,...for the C1 column in table T1, and values 10, 11, 12,...for the C1 column in table T2.
```
  CREATE TABLE T1
    (C1 SMALLINT GENERATED ALWAYS AS IDENTITY,
     C2 SMALLINT)

  CREATE TABLE T2
    (C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY ( START WITH 10 ) ,
     C2 SMALLINT)

  INSERT INTO T1 ( C2 ) VALUES(5)

  INSERT INTO T1 ( C2 ) VALUES(5)

  SELECT  * FROM T1
```
C1 C2

1 5

2 5
```
  VALUES IDENTITY_VAL_LOCAL() INTO  :IVAR
```
At this point, the IDENTITY_VAL_LOCAL function would return a value of 2 in IVAR. The following INSERT statement inserts a single row into T2 where column C2 gets a value of 2 from the IDENTITY_VAL_LOCAL function.
```
  INSERT INTO T2 ( C2 ) VALUES( IDENTITY_VAL_LOCAL() )

  SELECT  * FROM T2
    WHERE C1 = DECIMAL( IDENTITY_VAL_LOCAL(), 15, 0)
```
C1 C2

10 2

Invoking the IDENTITY_VAL_LOCAL function after this INSERT would result in a value of 10, which is the value generated by the database manager for column C1 of T2. Assume another single row is inserted into T2. For the following INSERT statement, the database manager assigns a value of 13 to identity column C1 and gives C2 a value of 10 from IDENTITY_VAL_LOCAL. Thus, C2 is given the last identity value that was inserted into T2.
```
  INSERT INTO T2 ( C2, C1 ) VALUES( IDENTITY_VAL_LOCAL(), 13 )

  SELECT  * FROM T2
    WHERE C1 = DECIMAL( IDENTITY_VAL_LOCAL(), 15, 0)
```
C1 C2

13 10
The IDENTITY_VAL_LOCAL function can also be invoked in an INSERT statement that both invokes the IDENTITY_VAL_LOCAL function and causes a new value for an identity column to be assigned. The next value to be returned is thus established when the IDENTITY_VAL_LOCAL function is invoked after the INSERT statement completes. For example, consider the following table definition:
```
  CREATE TABLE T3
    (C1 SMALLINT GENERATED BY DEFAULT AS IDENTITY,
     C2 SMALLINT)
```
For the following INSERT statement, specify a value of 25 for the C2 column, and the database manager generates a value of 1 for C1, the identity column. This establishes 1 as the value that will be returned on the next invocation of the IDENTITY_VAL_LOCAL function.
```
  INSERT INTO T3 ( C2 ) VALUES( 25 )
```
In the following INSERT statement, the IDENTITY_VAL_LOCAL function is invoked to provide a value for the C2 column. A value of 1 (the identity value assigned to the C1 column of the first row) is assigned to the C2 column, and the database manager generates a value of 2 for C1, the identity column. This establishes 2 as the value that will be returned on the next invocation of the IDENTITY_VAL_LOCAL function.
```
  INSERT INTO T3 ( C2 ) VALUES( IDENTITY_VAL_LOCAL() )
```
In the following INSERT statement, the IDENTITY_VAL_LOCAL function is again invoked to provide a value for the C2 column, and the user provides a value of 11 for C1, the identity column. A value of 2 (the identity value assigned to the C1 column of the second row) is assigned to the C2 column. The assignment of 11 to C1 establishes 11 as the value that will be returned on the next invocation of the IDENTITY_VAL_LOCAL function.
```
  INSERT INTO T3 ( C2, C1 ) VALUES( IDENTITY_VAL_LOCAL(), 11 )
```
After the 3 INSERT statements have been processed, table T3 contains the following:

C1 C2

1 25

2 1

11 2

The contents of T3 illustrate that the expressions in the VALUES clause are evaluated before the assignments for the columns of the INSERT statement. Thus, an invocation of an IDENTITY_VAL_LOCAL function invoked from a VALUES clause of an INSERT statement uses the most recently assigned value for an identity column in a previous INSERT statement.