How about using a Stored Procedure instead of a trigger? I know that the
"limitation"/functionality of the @@IDENTITY returning the last inserted
value can be a problem, but if you can use a SP then you could insert into
the first table, grab the @@IDENTITY, then insert into the second table, and
return the original @@IDENTITY value.
Regards,
Bevan
----- Original Message -----
From: "Edwin Das" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Tuesday, July 01, 2003 6:57 PM
Subject: [DUG]: ClientDataset inserts with MSSQL Identity column problem
hi...
I have a client dataset / MSSQL Server problem with Identities..
For Example:
I have the following table definition
CREATE TABLE GROUPTABLE (
SEQNO INTEGER IDENTITY(1,1) PRIMARY KEY,
GROUPNAME VARCHAR(30) NULL)
Note : This table has a trigger which inserts into another table
which also has an IDENTITY column.
Then Lets say i have a TCLientDataset on my form with a commandtext property
value of
"SELECT * FROM GROUPTABLE WHERE SEQNO=-2'
And in code i do the following.
ClientDataset.Insert.
ClientDataset.FieldbyName ('GROUPNAME').asString := 'TEST';
ClientDataset.Post;
ClientDataset.Applyupdates;
My question is how do i return / view the just inserted identity value for
the SEQNO Field in the GROUPTABLE without re-opening the clientdataset.
I cannot seem to rely on the "SELECT @@IDENTIFY" statement as this will
return the value of the identity value inserted by the trigger and not by
the inserted row of the GROUPTABLE.
Is there a way of returning an just inserted identity value from client
dataset with some sort of SQL Statement or hooking into a certain Provider
event or something.
Surely inserting into MSSQL tables with IDENTITY Columns using
TClientDatasets and returning a the IDENTITY Column value
back to the code or clientdataset to be used in a master/detail relationship
has been done before.
Any help or info would be appreciated..
Thanks heaps.
Edwin Das
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
