scope_identity and @@identity are one type of solution. Primary Keys that are autoincremented are hard to follow soemtimes and are a bit tricky to retrieve sometimes on high transactional systems. An easier way would be just create a GUID or UUID that would gurantee uniqueness that is not incremented.
In CF this makes unique tables really simple. You create your UUID in the CF page and then insert the record into the DB. What this enables you to ahve a unique value for a given table that you do not have to call an aggregate function or requery the database to get the primary key. I typically don't make my UUIDs my primary keys are I prefer database to perform binary searchings on numbers and 35 character strings. I do advocate that you put a non-clustered index on the column for easier retrieval as if to treat it like a foreign key. Teddy On 10/6/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > I don't know that I see the wisdom in creating a trigger for every table > in a database just so someone doesn't have to learn how to use a > sequence. > > Practically speaking, sequences are rather easy to use, so it seems like > unnecessary effort to create and maintain a bunch of triggers. > > Philosophically, I am of the mind that developers should take the time > to know the environment they are working in, rather than just trying to > make it behave like something else they are more comfortable with. > > Oracle is different in many ways from SQL Server - you can't shield > yourself from all of it, and some Oracle specific features (connect > by...prior comes to mind) can be really valuable to know. The reverse, > of course, is also true. > > > -----Original Message----- > > From: Aaron Rouse [mailto:[EMAIL PROTECTED] > > Sent: Thursday, October 05, 2006 4:46 PM > > To: CF-Talk > > Subject: Re: locking, I think > > > > For people who are used to SQL server, the best thing to do would be > to > > setup a trigger and that trigger in Oracle could grab the next > sequence > > value and insert it when new data is being inserted into the table. > > > > On 10/5/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > > > > > > > > You can access the sequence in a couple of ways. One is using > > > foo_sq.NextVal directly in the insert statement, as the original > poster > > > was doing. > > > > > > The other is to select it from DUAL, which is a sort of utility > table in > > > Oracle, like so: > > > > > > Select foo_sq.NextVal as id > > > From DUAL > > > > > > > > > > > > -- > > Aaron Rouse > > http://www.happyhacker.com/ > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255784 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
