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 statement for a table containing an identity column. The INSERT statement 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 statement 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:
The following notes explain the behavior of the function when it is invoked in various situations:
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 statement invoked in the same triggered action for a table containing an identity column. If an INSERT statement 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.
CREATE TABLE EMPLOYEE (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY, NAME CHAR(30), SALARY DECIMAL(5,2), DEPT SMALLINT) INSERT INTO EMPLOYEE (NAME, SALARY, DEPTNO) VALUES('Rupert', 989.99, 50) VALUES IDENTITY_VAL_LOCAL() INTO :IVAR
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 |
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.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.