After insert statement, how do I get column value populated by DB trigger

[Andy East]

The primary key is generated by a database trigger for a table. A business object supplies the remainder of the column values.
I need to update my business object with the actual primary key that was generated by the DB after the insert. Any suggestions. I'm trying to avoid running a query after the insert to get it.

[Brian Nice]

How are you doing the insert? I was thinking maybe if you used a stored procedure to do the insert it might work. In the stored procedure it could get the next value for the sequence, use it for the key, do the insert with parameters that are passed to the stored procedure, and then the stored procedure could return the value of the sequence to the calling program.
Not sure if that would work, maybe someone else would know for sure. HTH.
Brian

[rani bedi]

Try to write a before insert trigger which would actually generate the primary key and then you can insert the data.

[Chandrasekhar Nadella]

Yes u can do it. here is the way.
1. create the connection
2. setAutoCommit(false), if it already fasle don't do it.
3. do the insert & close the statement
4. use another sql to get the primary key value just inserted. for e.g.DB2(select distinct IDENTITY_VAL_LOCAL from your_table).
Oracle (select your_created_table_sequence.currval from dual).
5. commit the connection using connection.commit.
this logic is worked in both orcale/DB2, bcoz I've coded this in my previous projects.
Hope i have answered ur question.

Originally posted by Andy East:
The primary key is generated by a database trigger for a table. A business object supplies the remainder of the column values.
I need to update my business object with the actual primary key that was generated by the DB after the insert. Any suggestions. I'm trying to avoid running a query after the insert to get it.

[Andy East]

Thanks everyone for your comments and suggestions.

[Cigdem Cemgil]

In Java 2, SE 1.4 there is a solution for it:
The following executeUpdate() method of the Statement class makes sure that the generated key is returned in the result set:
public int executeUpdate(String sql,
int autoGeneratedKeys)
Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
Parameters:
sql - ...
autoGeneratedKeys - a flag indicating whether auto-generated keys should be made available for retrieval; one of the following constants:
Statement.RETURN_GENERATED_KEYS statement.NO_GENERATED_KEYS
After this method, you can call getGeneratedKeys method of the Statement class to get the generated keys.
public ResultSet getGeneratedKeys()
[This message has been edited by Cigdem Cemgil (edited August 23, 2001).]

[Jamie Robertson]

Originally posted by Chandrasekhar Nadella:
Yes u can do it. here is the way.
1. create the connection
2. setAutoCommit(false), if it already fasle don't do it.
3. do the insert & close the statement
4. use another sql to get the primary key value just inserted. for e.g.DB2(select distinct IDENTITY_VAL_LOCAL from your_table).
Oracle (select your_created_table_sequence.currval from dual).
5. commit the connection using connection.commit.
this logic is worked in both orcale/DB2, bcoz I've coded this in my previous projects.
Hope i have answered ur question.

Do you ever encounter this problem-->
How do you ensure that no one else has inserted a record between your insert statement and the retrieval of the current sequence value? you could retrieve someone else's primary key!
This is the way we work around that problem-->
In oracle you can "select Sequence.nextval from dual" which will return the new primary key to you. Perform the insert using the value you retrieved from the above sequence. This way, there is no room for error because once you retrieve the next value, it is yours!
I was just wondering if you ever encountered that problem?
As to the original topic-->trigger returnval-->we had to change to a stored procedure because on many occasions we needed a return value.
Jamie