Noel, I think you were wrong about this only working for sequences. I ran into http://stackoverflow.com/a/12319823/14731 which states that SET resets the value of SCOPE_IDENTITY() and provides a workaround (which worked for me).
Is there an open bug report for the fact that SET should not reset SCOPE_IDENTITY()? Gili On Friday, September 5, 2014 12:52:15 AM UTC-4, Gili wrote: > > On second thought I think this issue should be reopened. > > > 1. The microsoft documentation doesn't say this function is limited to > sequences. I think it should work on all tables. > 2. I need a way to get the AUTO_INCREMENT column of a row I just > inserted, but my triggers are causing IDENTITY() to return the wrong > value. > All the forums recommend using SCOPE_IDENTITY() for this purpose, but it > doesn't work for H2. > > Is there a workaround I can use in the meantime? > > Thanks, > Gili > > On Thursday, July 3, 2014 10:56:35 AM UTC-4, Noel Grandin wrote: >> >> I don't know since it predates my involvement with H2, but I'm guess it >> was added for compat with SQL-Server. >> see: http://msdn.microsoft.com/en-us/library/ms190315.aspx >> >> Based on that description, it seems to be working largely correctly. >> >> On 2014-07-03 04:48 PM, cowwoc wrote: >> > Thank you. >> > >> > I assume SCOPE_IDENTITY() was added for compatibility with other >> databases. Is this behavior (only reflecting sequence >> > values) consistent with that? Or do you need to open a bug report to >> improve its compatibility? (I'm happy to simply use >> > IDENTITY() but others might care) >> > >> > Gili >> > >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
