SELECT @@IDENTITY in A2010 with Triggers
in [General]
|
From: David W. Fenton on 10 Apr 2010 20:56 Over on StackOverflow someone was asking about how to get the last Autonumber value, and I suggested SELECT @@IDENTITY in the same connection as the insert is made, and said "just like SQL Server." Someone responded that in SQL Server you should use the SCOPE_IDENTITY() function instead, so I looked it up: http://msdn.microsoft.com/en-us/library/ms190315.aspx ....and that made me think that in A2010 with table-level data macros, SELECT @@IDENTITY might end up not being reliable if the insert causes a trigger to insert a record in another table (as described in the MSDN article). Does anyone working with A2010 have any idea what MS has implemented to address this? -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Banana on 10 Apr 2010 21:17 David W. Fenton wrote: > Does anyone working with A2010 have any idea what MS has implemented > to address this? Did you see this blog post about ReturnVar? http://blogs.msdn.com/access/archive/2010/03/19/access-2010-returnvars-in-data-macros.aspx Seem to me that would be analogous to opening an AppendOnly DAO Recordset, adding a new record and getting the inserted value via ..LastModified bookmark. This should then be pretty reliable and very close to SCOPE_IDENTITY() which I personally use over @@IDENTITY when using SQL Server anyway.
From: David W. Fenton on 11 Apr 2010 16:26 Banana <Banana(a)Republic> wrote in news:4BC12330.8000004(a)Republic: > David W. Fenton wrote: >> Does anyone working with A2010 have any idea what MS has >> implemented to address this? > > Did you see this blog post about ReturnVar? > > http://blogs.msdn.com/access/archive/2010/03/19/access-2010-returnv > ars-in-data-macros.aspx > > Seem to me that would be analogous to opening an AppendOnly DAO > Recordset, adding a new record and getting the inserted value via > .LastModified bookmark. This should then be pretty reliable and > very close to SCOPE_IDENTITY() which I personally use over > @@IDENTITY when using SQL Server anyway. I've never before seen anyone recommend SCOP_IDENTITY() before, but it seems to me that it ought to be the default, since you never know when a trigger might get added. Seems to me that the ReturnVars in a data macro is moving backwards. I just don't see it as a good thing to be locking the record that long. Maybe it doesn't matter in reality, but I can't help but think that table-level data macros are going to slow things down a lot. We'll see. Your suggestion is a workaround to avoid the issue, but it doesn't really address the actual problem in my question. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Banana on 12 Apr 2010 08:34 David W. Fenton wrote: > I've never before seen anyone recommend SCOP_IDENTITY() before, but > it seems to me that it ought to be the default, since you never know > when a trigger might get added. I _think_ it was relatively recent addition (2005?) but yes, I agree that SCOPE_IDENTITY() ought to be the first thing to reach for cases where we need last inserted key. > Seems to me that the ReturnVars in a data macro is moving backwards. > I just don't see it as a good thing to be locking the record that > long. I realized I had forgotten to point out that when you run, for instance, After Insert macro, you still can access the modified values via the Old object and <name of table modified> (It doesn't seem it has a New object which it should to make clear we are actually referring to same row but using the modified table's name seems to have the desired effect anyway). So you can grab the newly inserted value by setting a ReturnVar variable to <Name of table>.<Name of primary key column>. So that wouldn't require going back to the record to find out the key and you'd be at least to run other operations that may need the new key. So not really analogous to doing a .LastModified & retrieving the value from recordset field after all. Also, I don't know for a fact whether After <whatever> macros actually lock the table or not. I would expect this to be the case in Before <whatever> macros but when you use Before <whatever>, the list of allowed actions is very restricted - no inserts in other tables are allowed for example. You can only do this in After <whatever>. I'd be surprised if locking behavior were identical before & after, FWIW. > Maybe it doesn't matter in reality, but I can't help but think > that table-level data macros are going to slow things down a lot. > > We'll see. I should also point out that there will be two applications using the same thing - Access itself and Sharepoint. I wouldn't be very surprised if we were to find that the performance were quite different between a regular Access database running some data macros vs. a published database running the same macros (which is now transformed into Sharepoint workflow, I believe but I may be wrong on that point.) > Your suggestion is a workaround to avoid the issue, but it doesn't > really address the actual problem in my question. The implication here is that there's really no SCOPE_IDENTITY() built in to Jet. This is probably the closest you can get, I'd believe. Would love to be shown wrong.
From: David W. Fenton on 12 Apr 2010 14:26 Banana <Banana(a)Republic> wrote in news:4BC31369.6070906(a)Republic: > David W. Fenton wrote: >> I've never before seen anyone recommend SCOP_IDENTITY() before, >> but it seems to me that it ought to be the default, since you >> never know when a trigger might get added. > > I _think_ it was relatively recent addition (2005?) I thought so, too, but found a Knowledge Base article about it for SQL Server 2000, so not exactly new. > but yes, I agree > that SCOPE_IDENTITY() ought to be the first thing to reach for > cases where we need last inserted key. See, I didn't know this, and I've seen lots of people recommend @@IDENTITY, and people who I see as SQL Server gurus. >> Seems to me that the ReturnVars in a data macro is moving >> backwards. I just don't see it as a good thing to be locking the >> record that long. > > I realized I had forgotten to point out that when you run, for > instance, After Insert macro, you still can access the modified > values via the Old object and <name of table modified> (It doesn't > seem it has a New object which it should to make clear we are > actually referring to same row but using the modified table's name > seems to have the desired effect anyway). So you can grab the > newly inserted value by setting a ReturnVar variable to <Name of > table>.<Name of primary key column>. So that wouldn't require > going back to the record to find out the key and you'd be at least > to run other operations that may need the new key. So not really > analogous to doing a .LastModified & retrieving the value from > recordset field after all. But, as you said, it's basically equivalent to the old DAO .AddNew method of inserting records. > Also, I don't know for a fact whether After <whatever> macros > actually lock the table or not. I would expect this to be the case > in Before ><whatever> macros but when you use Before <whatever>, the list of > allowed actions is very restricted - no inserts in other tables > are allowed for example. You can only do this in After <whatever>. > I'd be surprised if locking behavior were identical before & > after, FWIW. I guess I'm prejudiced in favor of SQL inserts, and handling them myself in code. Obviously if I'm writing an app that won't have VBA code, the macro would be the way I'd do it, anyway, but I'm still foggy on whether or not I'm going to be avoiding VBA in new development or not. How will we decide whether or not future web deployability is worth avoiding VBA? >> Maybe it doesn't matter in reality, but I can't help but think >> that table-level data macros are going to slow things down a lot. >> >> We'll see. > > I should also point out that there will be two applications using > the same thing - Access itself and Sharepoint. I wouldn't be very > surprised if we were to find that the performance were quite > different between a regular Access database running some data > macros vs. a published database running the same macros (which is > now transformed into Sharepoint workflow, I believe but I may be > wrong on that point.) Oh, I expect Sharepoint to be quite fast. I'm planning to use Sharepoint for some projects, but not by any means a majority of them. But table-level data macros are a win for all pure-Access apps, so I'm interested in knowing what they do to performance. Relative performance is not important to me at all, since I won't be using them as a way of developing for Sharepoint except in a few cases. >> Your suggestion is a workaround to avoid the issue, but it >> doesn't really address the actual problem in my question. > > The implication here is that there's really no SCOPE_IDENTITY() > built in to Jet. This is probably the closest you can get, I'd > believe. Would love to be shown wrong. I think Jet/ACE needs it now that there are the equivalent of triggers. Can anyone ask that question of the powers that be at Microsoft? -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
|
Next
|
Last
Pages: 1 2 Prev: What is the key code for Microsoft Office 2010 Next: Vista and 2002-2003 database |