db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Sat, 14 May 2005 14:30:21 GMT
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
> >
> >
> >
> >
> 
>

Mime
View raw message