IDENTITY_VAL_LOCAL
IDENTITY_VAL_LOCAL is a non-deterministic function that returns the most recently assigned value for an identity column.
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 insert operation (specified in either an INSERT statement
or a MERGE statement)
. 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 insert operation
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
insert operation
are evaluated before values are assigned to the target columns of the
insert operation
. Thus, when you invoke IDENTITY_VAL_LOCAL in an
insert operation
, the value that is used is the most recently assigned value for an identity column from a previous
insert operation
. The function returns the null value if no such
insert operation
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
insert operation
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
insert operation
or the value that would have been assigned had the
insert operation
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
insert operation
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
insert operation
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
insert operation
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
insert operation
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.
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
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.