[eclipselink-dev] Bug 579409 discussion

[William Dazey <dazeydev.3@xxxxxxxxx>]

Hello!

I have been looking into fixing
https://bugs.eclipse.org/bugs/show_bug.cgi?id=579409 and I am having
some troubles coming up with a fix. I wanted to start a discussion on
the topic to get some feedback on a possible support statement by
EclipseLink.

--------------

To summarize the issue quickly, the use case is relying on default JPA
IDENTITY generation:
```
@Table(name = "COFFEE")
public class Coffee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
```
However, 2 non-JPA, SQLServer database objects are also required!

1) SQLServer TRIGGER:
```
CREATE TRIGGER coffeetrigger on COFFEE AFTER INSERT as BEGIN SET
NOCOUNT ON; INSERT INTO COFFEE_AUDIT( ...
```
2) AUDIT table:
```
CREATE TABLE COFFEE_AUDIT (AUDIT_ID NUMERIC(19) IDENTITY NOT NULL ...
```

EclipseLink calls `SELECT @@IDENTITY` to get the COFFEE.ID value, but
due to the TRIGGER, gets back the COFFEE_AUDIT.AUDIT_ID value.

--------------

An alternative strategy could be that users could use a different
Generation method so that the SQLServer TRIGGER and AUDIT table don't
get in the way. For instance, using a SEQUENCE generation instead:
```
@Table(name = "COFFEE")
public class Coffee {

    @Id
    @GeneratedValue( strategy = GenerationType.SEQUENCE, generator =
"seq_coffee" )
    @SequenceGenerator(name = "seq_coffee", allocationSize = 5 )
    private Long id;
```

With this strategy, EclipseLink no longer relies on calling `SELECT
@@IDENTITY` after the INSERT to obtain the COFFEE.ID value. Instead,
EclipseLink expects a SEQUENCE table seq_coffee to exist and calls
`SELECT NEXT VALUE FOR seq_coffee` before the INSERT.

To my question: Could EclipseLink make an official support statement
saying that "IDENTITY generation on SQLServer is not supported with
SQLServer TRIGGERs", or something similar? There are alternatives that
users may implement as well.

Alternatively, if there are thoughts on how this support could be
implemented in EclipseLink, let me know your ideas!

Thanks,
Will Dazey