Triggers have their place and we use them judiciously. You just have to really keep on top of your documentation so you understand what's happening when you do an insert. I'd much rather use a trigger than have to code a whole bunch of extraneous CF to acheive the same goal. The other advantage for triggers is that they operate independantly of the mechanism used to create the entry. A lot of our apps have both a web based interface and an MSAccess interface to the same data.
Cheers, Jeff Garza ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Tuesday, February 01, 2005 8:50 AM Subject: RE: EASY: grabbing the id of a newly created item.. > Best to avoid triggers full stop. > > -----Original Message----- > From: Jeff Garza [mailto:[EMAIL PROTECTED] > Sent: 01 February 2005 15:41 > To: CF-Talk > Subject: Re: EASY: grabbing the id of a newly created item.. > > If you are using SQL Server 2000, I would recommend moving away from using > @@IDENTITY as it can return incorrect information if you have a trigger > set > up on the table you are inserting into. It will return the ID from the > Trigger action rather than the newly inserted row. SCOPE_IDENTITY() does > not have these issues. That's all we use now as I've been burned once > where > > the DBA added a trigger to a table without my knowledge... Took the > longest > time to figure out why that process wasn't working. > > Cheers, > > Jeff Garza > > ----- Original Message ----- > From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> > To: "CF-Talk" <[email protected]> > Sent: Tuesday, February 01, 2005 8:20 AM > Subject: RE: EASY: grabbing the id of a newly created item.. > > >> SQL Server is simply @@IDENTITY >> >> >> >> -----Original Message----- >> From: Ewok [mailto:[EMAIL PROTECTED] >> Sent: 01 February 2005 15:01 >> To: CF-Talk >> Subject: RE: EASY: grabbing the id of a newly created item.. >> >> Wow, too much for so little, Just use Richard's method >> >> RUN YOUR INSERT QUERY >> >> Then directly after the insert get the records ID... >> >> <cfquery name="mid" datasource=""> >> SELECT MAX(TheID) as LatestID FROM TABLENAME >> </cfquery> >> >> The newest records ID from that table will now be in #mid.LatestID# >> >> Since you said "grab the id ( autonum, primary key)"... autonumber being >> an >> Access data type means the above is about your only option. Does >> @@identity >> even work in access? (or does anything else useful work in access for >> that >> matter) >> >> There's also no need to cflock the query from above that I can think of >> with >> an access data source. For the most part, access locks itself when it >> needs >> to. >> >> >> >> -----Original Message----- >> From: Barney Boisvert [mailto:[EMAIL PROTECTED] >> Sent: Monday, January 31, 2005 2:02 PM >> To: CF-Talk >> Subject: Re: EASY: grabbing the id of a newly created item.. >> >> That's a horrible way to do it. Quite inefficient. >> >> A better route is to check your DB's docs and see how they expose the >> last inserted sequence value. In MySQL it's LAST_INSERT_ID(), with MS >> SQL Server its one of three @IDENTITY variables. Run your INSERT, and >> then select the value back out using this mechanism. You may or may >> not need a transaction, depending on the specifics, and you definitely >> won't need CFLOCK. >> >> cheers, >> barneyb >> >> On Mon, 31 Jan 2005 10:24:34 -0800, Richard Colman <[EMAIL PROTECTED]> >> wrote: >>> This is actually pretty easy in ACCESS: >>> >>> 1) put the enire transaction within a CFLOCK block >>> >>> 2) do the insert >>> >>> 3) then select max(id) assuming you have an autonumber field for the id >>> >>> Which will give you the id of the record just inserted. >>> >>> This is so easy that even I can do it. >>> >>> Rick Colman >>> >> >> >> -- >> Barney Boisvert >> [EMAIL PROTECTED] >> 360.319.6145 >> http://www.barneyb.com/ >> >> Got Gmail? I have 6 invites. >> >> >> >> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192568 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
