Hi Jeremy,
 
I tried a simple test to see what happens in the case you brought up. The value retruned by IDENTITY_VAL_LOCAL is from the statement's table (and not from the table which got modified by the trigger).
 
Following is the ij session results

$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:c:/dellater/db1';
ij> create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
0 rows inserted/updated/deleted
ij> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
0 rows inserted/updated/deleted
ij> create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values (1);
0 rows inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
NULL

1 row selected
ij> insert into t1 (c12) values (1);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
101

1 row selected
ij> select * from t1;
C11        |C12
-----------------------
101        |1

1 row selected
ij> select * from t2;
C21        |C22
-----------------------
201        |1

1 row selected
ij>



 
On 5/13/05, Jeremy Boynes <jboynes@apache.org> wrote:
What does this return if the table has a trigger defined which inserts
into another table which also has an identity defined? Is it the value
from the statement's table or the one modified by the trigger?

I could not see where this is explictly defined in the docs. I have seen
this cause problems with applications using SQL Server databases and so
it is probably worth spelling out.

--
Jeremy

Daniel John Debrunner wrote:
> Mamta Satoor wrote:
>
>
>>The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the value that
>>got into generated for _any_ table with identity column using single row
>>insert with values clause in the current transaction.
>
>
> Except it doesn't behave like that, with respect to the *current
> transaction*. Derby's implementation returns the last identity value for
> a single row INSERT statement within the same connection.
> See the example below, and note auto commit is true.
>
> And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
> that will just return the same value multiple times (once per row in the
> table) and the value will be the last identity value for a single row
> INSERT statement within the same connection.
>
> Dan.
>
> ij> connect 'jdbc:derby:foo;create=true';
> ij> create table t (id int generated always as identity, d int);
> 0 rows inserted/updated/deleted
> ij> insert into t(d) values(88);
> 1 row inserted/updated/deleted
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
> ij> select * from t;
> ID         |D
> -----------------------
> 1          |88
>
> 1 row selected
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
>
>
>
>