Thanks to all who replied. In the end I did the inevitable (Johns suggestion) and now fetch a new ID first and include it in the INSERT operation rather than have the trigger.
Phil. ----- Original Message ----- From: "Steve Aish" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Friday, January 14, 2005 10:55 AM Subject: Re: [DUG] concurrent Interbase Transactions > The way I handle this is to insert the record and then immediately do a > search for the record I just inserted. This will give you the value > just inserted. > > e.g. > > insert into debtor_trans (accountnumber, amount, transdate, ...) > > trigger fires and fills in the unique seq_no > > select max(seq_no) from debtor_trans where accountnumber = > accountnumber and amount = amount and transdate ... > > this will return the seq_no from the record you just inserted. > > I don't know if this is the best or most efficient way of doing this > but it has always worked for me. > > Steve > > > > >>> [EMAIL PROTECTED] 14/01/2005 10:16:37 a.m. >>> > MessageI was kind of hoping to avoid that, but it's not a biggie. Is > there no way to fetch the record that has just been added within a > transaction? > > Cheers, > Phil. > ----- Original Message ----- > From: JC > To: 'NZ Borland Developers Group - Delphi List' > Sent: Friday, January 14, 2005 9:27 AM > Subject: RE: [DUG] concurrent Interbase Transactions > > > Hi Phil > > It might be safer to have the generator giving you a new unique > number (on request, not in a trigger) and to use and insert that with > the data in the ExeQuery. > As you indicated, when implemented in a trigger then your fetch, to > get this value, may come too late and you end up with somebody else's > number. > > Cheers, > John > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Phil Middlemiss > Sent: Friday, 14 January 2005 8:42 a.m. > To: NZ Borland Developers Group - Delphi List > Subject: [DUG] concurrent Interbase Transactions > > > Hi everyone - hope you all had a good break. Looks like summer has > finally arrived (now that everyone's back at work)! Anyway... > > I'm using Firebird (and transactions) for the first time. I'm > connecting to a remote database via the Firebird Server with no > problems, but I have a question about transactions: > > When I insert a new record, I use a trigger and a generator to > generate a new unique ID for the new record - I also query the generator > to see what the new value is that was returned by the trigger. The code > snippet is as follows: > > orderTransaction.Active := True; > with orderQuery do //insert the record > begin > ExecQuery; > with IBCurrentGeneratorValueQuery do // this query fetches > the current value of the generator > try > Transaction := orderTransaction; // share the same > transaction as the order > Open; > result:=FieldByName('newID').asInteger; // get the > generator value > except > result:=-1; > end; > orderTransaction.Commit; > end; > > The question is, if multiple clients access the database server at > the same time, will IBCurrentGeneratorValueQuery still return the > correct value, or if another concurrent transaction on another thread > increments the generator, will this query return the wrong value? > > When I was using MySQL (with PHP) I could use mysql_insert_id() to > query the new key value. I guess that if I could get hold of the newly > inserted row I could just get the field value, but the same concurrency > issue applies and I'm not sure how to get the record I just inserted. > > Cheers, > Phil. > > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.832 / Virus Database: 566 - Release Date: 10/01/2005 > > > > > > -------------------------------------------------------------------------- ---- > > > _______________________________________________ > Delphi mailing list > [email protected] > http://ns3.123.co.nz/mailman/listinfo/delphi > _______________________________________________ > Delphi mailing list > [email protected] > http://ns3.123.co.nz/mailman/listinfo/delphi > > _______________________________________________ Delphi mailing list [email protected] http://ns3.123.co.nz/mailman/listinfo/delphi
