You can also get away without using the trigger by simply using sequencename.nextval in your insert.
Usually, however, I use a separate query to get the id value, so that the value is readily available for other parts of the application, like so: <cfquery name="getID" datasource="foo"> select sequencename.nextval as id from dual </cfquery> Dual is sort of a dummy table in Oracle that you can use for this sort of thing. I just use the value returned for my subsequent insert queries - handy if you have more than one table you need to insert to using that id value. As for joins, this does depend largely on which version of Oracle you are using - version 8 and earlier, you are in for a big shift since Oracle didn't support the ANSI join statement like INNER JOIN, OUTER JOIN, etc. Oracle 9i and later do support that syntax, but I don't know for sure if it's exactly equivelant to SQLServer. Doug >To do "autonumbering" you have to create a sequence and a trigger, to >the best of my knowledge Toad does not provide a shortcut to creating >these. I just edit a small snippet of code to reflect whatever table >i need it for, such as: > > >CREATE SEQUENCE SEQ_ACTIVITYLOG > START WITH 1 > MAXVALUE 1E27 > MINVALUE 1 > NOCYCLE > CACHE 20 > NOORDER; > >CREATE OR REPLACE TRIGGER ITM.TRG_ACTIVITYLOG > BEFORE INSERT ON ITM.ACTIVITYLOG REFERENCING OLD AS OLD NEW AS > NEW > FOR EACH ROW >DECLARE > x INTEGER; >BEGIN > IF :new.ID is null THEN > SELECT SEQ_ACTIVITYLOG.NEXTVAL INTO x FROM DUAL; > :new.ID := x; > END IF; >END; > >I just dumped those out of Toad, but at least put you down the right >path, you probably can find countless examples of this via a google >search. > >As far as the CFQUERies, your functions are going to be different >between the two databases and the way you do joins might end up >needing some changes. Will depend on what version of Oracle as to how >different joins might need to be done. > >-- >Aaron Rouse >http://www.happyhacker.com/ > >On Fri, 7 Jan 2005 09:13:47 -0500, Stuart Kidd <[EMAIL PROTECTED]> wrote: >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189678 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
