Java Persistence and MS SQL IDENTITY with Trigger

IDE: NetBeans 6.5

When using JTable to bind data from MS SQL, IDENTITY column will have some problem if you write  trigger and insert another table with IDENTITY column.

Java Persistence use SELECT @@IDENTITY to get ID and update java object.

But unfortunaly, SELECT @@IDENTITY is by session, to get the actually new id you should use  SELECT IDENT_CURRENT(‘TABLENAME’).

I don’t know how to modify source code, and lazy to find. so I think a cheating code. list below:

create trigger dbo.mytable_trigger on mytable
for insert ,update, delete
as
declare @old_id int
–select @old_id = @@IDENTITY
SELECT @old_id = IDENT_CURRENT(‘mytable’)

— your code here

–end of your code

create table #t(a int identity(1,1), b int)
set @old_id = @old_id – 1
dbcc checkident(‘#t’, reseed, @old_id)
insert into #t (b) values(0)

It’s work.

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

What is 3 + 11 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)