No, Access doesn't support stored procs or triggers. In SQL Server the analog for 'autonumber' is 'identity', which imho makes a perfectly good primary key.
There's no real certainty that MAX is going to return *your* last inserted ID. It might be from another user's insert statement. I think you can choke the process down with a name-based cflock, but now you have a cflock and a cftransaction block to go along with a query needed to determine the last-inserted value. If you don't have triggers/stored procs available to you, here's another way to do the job: First, when the db table is created include a text field called UUID or something similar. Then, in your insert operation do a CFSET variables.myvalue=CreateUUID() and insert variables.myvalue into the UUID field of the db. Then, when you need the record next, select with a WHERE filter that looks for the UUID value set in variables.myvalue. *Then* you can grab the much tidier ID field value for future use, if needed. This gets the job done without a CFTRANSACTION, CFLOCK or an extra query to determine the ID value. Its cost is a db field and an (optional, really) table index. --------------------------------------- Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc., www.mysecretbase.com --------------------------------------- ---------- Original Message ---------------------------------- from: "Gyrus" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] date: Mon, 26 Nov 2001 23:22:38 -0000 I'm majorly confused about Primary Keys and Access' 'Autonumber' data type. I've always used 'Autonumber' as seemingly the best and easiest way for unique ID's to be created for PK fields in Access tables. I was aware of some issue with the common SELECT MAX(ID) SQL operation you end up doing to get the ID of something you just inserted. I found this at http://www.defusion.com/articles/index.cfm?ArticleID=72 The guy seems to be responding to someone posting the Fusebox technique of using SELECT MAX(ID) and <cftransaction> as a universal method of creating ID's for PKs, so that apps are portable between RDMS's. He seems to think the problem he mentions only happens on SQL Server 6.5/7, though. There was a response to this at http://www.defusion.com/articles/index.cfm?ArticleID=77 Here it was advised to use stored procedures, "isolation level" on <cftransaction>, or an insert trigger. Then I grab the Max_ID.cfm custom tag by Steve Nelson from fusebox.org, which has a nice suggestion in the documentation, to use an endless loop and <cftry> around the <cftransaction>, to keep trying to get the new ID *and* insert the new record until it gets it right. Questions: - Am I right in thinking SQL Server doesn't have the 'Autonumber' data type? Why not? Is there a grave disadvantage to it unless you're doing kid's stuff in Access? - Is it cool to use the SELECT MAX(ID) with SQL Server 2000 (i.e. do the 6.5/7 problems above not happen now)? - If I put <cftransaction isolation="serializable"> around the SELECT MAX(ID) and INSERT queries (in whatever order), are there any hidden dangers? Does this prevent anything else from changing the table concerned until the new ID is calculated and the nice new record is INSERTed? - You can't use stored procedures or triggers on Access, right? I'm basically trying to get this process *right*, and trying to construct app templates that will be easily portable between different DB systems - so even though I'm still mostly working with Access, I might drop the whole 'Autonumber' thing. Any advice/tips greatly appreciated on this! - Gyrus ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
