Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Insert using identity column and trigger returns wrong primary key(When inserting into a table which has a trigger which does an insert into another table returns the id of the record inserted into the other table instead)
Insert using identity column and trigger returns wrong primary key [message #1810028] Mon, 29 July 2019 14:18 Go to next message
Francois Thirion is currently offline Francois ThirionFriend
Messages: 3
Registered: July 2019
Junior Member
This is the exact same issue as an unanswered question posted in 2014: https://www.eclipse.org/forums/index.php/m/1462652/?srch=scope_identity#msg_1462652

When persisting an entity that has an identity primary key it returns the value as expected after doing a flush.
If the table has a trigger defined that inserts into another table, then the primary key assigned after the flush is that of the other table instead of this one.

This is easily tested by doing an insert, flush and then comparing the primary key returned to select max(id) of the table.

If I change the persistence provider to OpenJPA then I get the correct value as expected.

This is apparently MS SQL specific and if we were not using a persistence provider the advice would be to use scope_identity() instead of @@Identity to get the correct generated id. I would love to try and fix this myself but I have not idea where to even start.

Is there anything I can do in my project to get the expected behaviour? Unfortunately I can't change or disable the trigger as I'm inserting into a 3rd party database.

Any help would be appreciated.
Re: Insert using identity column and trigger returns wrong primary key [message #1811163 is a reply to message #1810028] Wed, 28 August 2019 03:33 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1330
Registered: July 2009
Senior Member
Turn on logging to see what exactly is issued, and show the DatabasePlatform class you've configured EclpseLink to use.

The DatabasePlatform method buildSelectQueryForIdentity returns the query used for getting the assigned identity. The MySQLPlatform class is set to use "SELECT LAST_INSERT_ID()", while other platform classes (SQLServerPlatform,SybasePlatform etc) are using "SELECT @@IDENTITY". If you need to use something different, you would extend the appropriate class and overwrite this method, and then tell EclipseLink to use your target DatabasePlatform implementation using the eclipselink.target-database persistence property

Best Regards,
Chris
Re: Insert using identity column and trigger returns wrong primary key [message #1811183 is a reply to message #1811163] Wed, 28 August 2019 10:16 Go to previous message
Francois Thirion is currently offline Francois ThirionFriend
Messages: 3
Registered: July 2019
Junior Member
Hi Chris,

Thanks for the reply. I downloaded the Eclipselink source from Git and changed the buildSelectQueryForIdentity method to return "scope_identity()" instead of "SELECT @@IDENTITY" but it then always returns null.
I've found another post on the internet where someone else created their DatabasePlatform as you suggested and did the same change I did and they were also always getting a null result.

I think this is because the scope of the sql body of work has already been closed, which is why the scope_identity() doesn't return a result.
So, for this to work we probably need to call buildSelectQueryForIdentity while still in the correct scope in addition to changing it to return scope_identity() instead of @@IDENTITY.
I just don't know where that has to happen.
Previous Topic:Migration from Toplink to Eclipselink
Next Topic:Multitenant per table and native query
Goto Forum:
  


Current Time: Wed Oct 16 12:30:09 GMT 2019

Powered by FUDForum. Page generated in 0.01695 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top