I agree here too - I've been writing stored procs (and other PL/SQL) for a few things and I'm eventually going to convert everything I have to them (portability is not an issue for us). A lot of things in PL/SQL are similar to CF, in fact - a PL/SQL table works in a similar way to a CF query for example.
-----Original Message----- From: RADEMAKERS Tanguy [mailto:[EMAIL PROTECTED] Sent: Tuesday, 7 June 2005 1:08 To: CF-Talk Subject: RE: Get New Record ID in ORACLE You don't need to use triggers at all if you use stored procedures: ------------------------------setup.sql------------------------------- CREATE TABLE t ( ID INTEGER NOT NULL, NAME VARCHAR2(50) ); CREATE SEQUENCE s; CREATE OR REPLACE PROCEDURE t_insert (newname IN VARCHAR2, newid OUT INT) AS BEGIN INSERT INTO t (ID, NAME) VALUES (s.NEXTVAL, newname) RETURNING ID INTO newid; END; / ------------------------------scribble.cfm---------------------------- <cfloop collection="#cgi#" item="header"> <cfstoredproc procedure="t_insert" datasource="trademak"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="newname" value="#header#"> <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" dbvarname="newid" variable="myNewId"> </cfstoredproc> <cfoutput>#myNewId#: #header#<br></cfoutput> </cfloop> plus, you can use packages to organize your procedures - either one per table with all CRUD code (many tools can auto generate this for you if you wish) or whatever (i'm a fuseboxer, so i create an [APPCODE]_QUERIES package for selects and an [APPCODE]_ACTIONS package for inserts, deletes, and updates). Many people shy away from stored procs because they think they are complicated, but they aren't and they have many benefits over plain cfquery (IMNERHO). /t > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:208751 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
