I generated some entity beans (using NetBeans) from my database tables in SQL Server. I'm using GenerationType.IDENTITY for the primary key columns, and this has been working perfectly. I'm also using toplink as the JPA provider.
However, I recently set a trigger on one of my tables, to turn whenever a new row is inserted. The trigger, in turn, inserts a row into another table. When I persist a new object to the database, the primary key/identity that is returned is the identity of the row inserted *by the trigger*, instead of the identity of the row inserted by the statement. The object is persisted to the database just fine, it's the return value that is incorrect. Does anyone know how I could fix this, so that my object is given the correct identity and the trigger is still used?
Some example/pseudo code to help illustrate the problem:
This is what my auto-generated bean looks like. I added the @GeneratedValue annotation.
@Entity
@Table(name = "Table1")
public class Table1 implements Serializable {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "PK_COL", nullable = false)
private Integer pkCol;
......
Here is an example of the trigger:
CREATE TRIGGER Trigger1 ON dbo.Table1 AFTER INSERT, UPDATE AS
DECLARE @updatedID INTEGER
SET @updatedID = (SELECT PK_COL FROM Inserted)
INSERT INTO dbo.DifferentTable(Referenced_ID) VALUES(@updatedID)
Here is the Java code...
Table1 newObj = new Table1();
EntityManager em = getEntityManager();
em.getTransaction().begin();
em.persist( newObj );
em.getTransaction().commit();
newObj.pkCol now matches the identity of the row inserted from the trigger, instead of the row inserted in Table1. If I remove the trigger, then newObj has the correct identity from Table1.
[Message sent by forum member 'rld' (rld)]
http://forums.java.net/jive/thread.jspa?messageID=331348