Re: Identity error?? Please help

From: Andre Greyling (agreyling_at_optusnet.com.au)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 22:16:13 +1000

Thanks for the help - yes the problem is @@IDENTITY related. It is still
strange how Delphi behaves though. The code is simply:
(1) qry1.append;
(2) qry1.fieldbyname('ID').asinteger

qry1 does a simple SELECT on table Master. Master has a trigger (on INSERT)
which creates detail tables
each table has an identity column.
line (2) then returns the identity value of the last detail table - odd !

However, what is the best way to create a master record and retrieve it's
identity value ? Should the code be in a stored procedure or is there a
simpler way ?
Any ideas ?

Bye

"Brian Hollister" <bhollisterATfuturaintlDOTcom> wrote in message
news:40844810$1@newsgroups.borland.com...
> Check out @@SCOPE_IDENTITY in SQL Servers BOL maybe that will help
>
> "Morpheus" <morpheusNOPSAM@redpoint.co.za> wrote in message
> news:4083b79b@newsgroups.borland.com...
> > Hi Andre,
> >
> > Sounds like you're an expat now living in Aus ;-).
> >
> > I do know that in SQL Server, if you get the value of an IDENTITY column
> in
> > a trigger, depending upon which global variable you look at, you will
> always
> > get the last IDENTITY value inserted. This would be consistent with the
> > value that you are gettting back. I am not too sure how Delphi handles
> this,
> > but how are you getting the last inserted identoty value? A bit of
detail
> > might help you solve the problem.
> > Regards,
> > Norman
> >
> > "Andre Greyling" <agreyling@optusnet.com.au> wrote in message
> > news:40837755@newsgroups.borland.com...
> > > Hi all
> > >
> > > I have an appplication that does an insert into a table that has an
> > identity
> > > column. In the afterpost event of the adoquery i want to get the newly
> > > inserted indentity value. Normally i don't have a problem. However,
when
> I
> > > have a detail dataset hanging off it (relationship is defined using
the
> > > Datasource property of the detail query) AND I have trigger on the
> master
> > > table that inserts into the detail table(which also has an identity
> > column),
> > > the value I get in my afterpost event is the value of the details
> > identity
> > > column!!!!!!!!!! What the...??? As soon as I remove the insert in the
> > > trigger, I get the correct value back (i.e. the identity value of the
> > master
> > > record).
> > >
> > > How do I overcome this problem? Any suggestions on what is the best
way
> to
> > > retrieve an identity value given the above situation (I may have
> multiple
> > > detail tables of master table) or generally.
> > >
> > > Any help would be greatly appreciated
> > >
> > > TIA
> > > Andre
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Getting autonumber after insert
    ... If the identity column is included in your recordset on the master, ... > I just need someway to capture that number. ...
    (borland.public.delphi.database.ado)
  • Re: Identity error?? Please help
    ... >strange how Delphi behaves though. ... Master has a trigger ... Marjan Venema - BJM Software ...
    (borland.public.delphi.database.ado)
  • Re: Transaction and RowState problem
    ... In master table after inserting I return the Identity column. ... If I set the UpdatedRowSource InsertCommand to UpdateRowSource.None it ... keeps the RowState but dosen't update Identoty column. ... I have a master detail table which I use them in Transaction to Update ...
    (microsoft.public.dotnet.languages.csharp)
  • how can i know column have identity true in runtimes
    ... I have stuck at a strange point.in Database some master table have ... identity column and some havenot. ...
    (microsoft.public.dotnet.languages.csharp)