Basically only because @@identity may return an identity from a table outside the scope of your current session. If you are in a data entry multi user scenario I reckon it is pretty essential - otherwise POSSIBLY unnecessary but best practise or at least received wisdom as I understand it. You might think it fairly unfeasible unless you are unlucky to get an ID from the wrong table but why mess with chance?
MSDN quoth: "IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT. SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope." Oracle - pass. Not touched since about 1999 (phew). But I am pretty sure I recall Oracle can do no worries. Will ask Oracle friend if liked. If you are always adding one parent and child together then one SP will happily do the trick (see my earlier point) but of course if you use several children you'll be better off with 2, getting the ID from the parent insertion for the child insertion. Cheers, Iain -----Original Message----- From: Ben Kloosterman [mailto:[EMAIL PROTECTED]] Sent: 09 July 2002 04:07 To: [EMAIL PROTECTED] Subject: Re: [ADVANCED-DOTNET] ADO.NET: Parent-child relationship and auto-increment columns Why SCOPE_INDENTITY() rather than @@IDENTITY ? Also is there an Oracle equivalent ? Ben -----Original Message----- From: Moderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED]]On Behalf Of Iain Smallwood Sent: Monday, 8 July 2002 4:46 PM To: [EMAIL PROTECTED] Subject: Re: [ADVANCED-DOTNET] ADO.NET: Parent-child relationship and auto-increment columns In answer to your second paragraph Ian I should say that there is no other method of accomplishing the desired related records AFAIK - I cannot currently conceive of how there could be. I would be very interested if anyone could sort that one out. However it should be feasible to accomplish this with a single stored procedure in T-SQL that can INSERT, SELECT @@IDENTITY (or preferably SCOPE_IDENTITY() if using SQL 2000) and then use the value to populate the child record. This should considerably cut down the round trips. You would have to use output parameters to get back both identities if needed of course. If you wished to just see the records you have added you could use for your selection criteria something like 'select fieldlist from table where ID > (select IDENT_CURRENT('table'))' assuming you had retrieved and maintained IDENT_CURRENT programmatically prior to retrieval. Then rows you add will be in the new scope and a simple (re) fill will pull the necessary rows into your dataset. HTH, Iain Ben Kloosterman <[EMAIL PROTECTED]> wrote: > > The Microsoft recommendation is to autogenerate a -1 number with a -1 > increment . (search on Autoincrement identity) . When you pass the > data do NOT pass the indentity let the DB generate a new identiy > during inserts. The insert shoucl do a select at the end and > re-populate the record for any changes and the Constraints will update > all foreiign keys. Unlike DB's you can easily change autoincrement > identity collumsn in datasets. I can see how that might work for inserting rows into a single table, but I don't understand how it addresses the poster's original quetion, which involved adding a *new* parent row, and a *related* child row. Since the DB will invent its own identity column, how on earth do you indicate that the newly-added child should be related to the newly added child. Is the normal technique to do several round trips to the DB? (I.e. update the parent, retrieve the new key, update the DataSet to reflect the chosen key, then update the child table.) Is that workable? Also, what does the SELECT statement that retrieves the key that the DB chose look like in practice? If the DB has invented a new primary key, how on earth do I tell it which record I'm looking for? Don't I need to know what the new primary key was before I can ask for the record? Or are you using the magic @@IDENTITY value? (And how are you pushing it back to the DataSet? Manually? Or is there a way of automating this with a DataAdapter?) -- Ian Griffiths DevelopMentor You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. Disclaimer This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification not withstanding, any comments or opinions expressed are those of the originator, not of Taylor Made Computer Solutions, unless otherwise explicitly stated. You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. Disclaimer This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification not withstanding, any comments or opinions expressed are those of the originator, not of Taylor Made Computer Solutions, unless otherwise explicitly stated. You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
