Generated Keys and Triggers

[Tony Seebregts <to...@cibecs.com>]

Hi,

If I execute a statement that inserts a row into a table that has an 
attached trigger that inserts a row into another table I get anomalous 
results when trying to retrieve the generated keys using 
statement.getGeneratedKeys():

1. If the first table does not generate a key (because the generated key 
column was supplied) then getGeneratedKeys() returns the generated key 
for the row inserted by the trigger.

2. If the first table doe not generate a key (because the generated key 
column was not supplied or has a DEFAULT value) then getGeneratedKeys() 
returns only the generated key for the first table i.e. not for the row 
inserted by the trigger.

The ResultSetMetadata from the getGeneratedKeys does not contain any 
information other than the number of columns i.e. its impossible to 
determine which generated key is being returned.

Does anybody have a workaround for this ? (other than being *really* 
careful about the database design :-) ).

regards

Tony Seebregts