I have a question regarding identity columns in SQL Server 7.

Sometimes, after inserting a row into a table using ADO, you would want to
get the identity created for the newly inserted row. For instance, if table
EMPLOYEE has an identity column called EmployeeId, after calling
Recordset.Update, you can read Recordset("EmployeeId").Value and get the
identity value assigned to that newly inserted row. This works okay unless
EMPLOYEE table has an insert-trigger that inserts rows to some other table.
Say, after inserting a new EMPLOYEE row, you must add into the AUDIT table
that "employee so and so has been added." , and the primary key for the
AUDIT table is another identity column (called AuditID). If EMPLOYEE table
has an insert-trigger as described above, after calling Recordset.Update
method, what you read from Recordset("EmployeeID").Value is the number that
was auto-generated for the identity column of AUDIT table, and not the
number that was auto-generated for the identity column of EMPLOYEE table. I

was added, the identity of the AUDIT table was the one that was returned
instead.

So, me and my comworker think that an alternative solution to reading the
identity value after a recordset update is by doing something like "Select
Max(EmployeeID) From EMPLOYEE". This strategy looks like it's going to work
90% of the time. But our concern is, if the database is busy (many
concurrent users doing concurrent inserts on the same tables), the "Select
Max(IdentityColumn)" strategy might not work.

I guess my main question is: Is there an efficient, bullet-proof way of
retrieving the newly created identity column value for a row that was just
inserted into a table that fires a trigger which in turn inserts a row into
another table that also has an identity column?

Any tips, recommendations would be greatly appreciated. Thanks!

-Gabe

HC

To get the correct identity value with an insert trigger, please specify
the dynamic property: Server Data on Insert provided by Microsoft OLE DB
Provider for SQL Server (SQLOLEDB). The property specifies whether an
application can retrieve values from the database for newly inserted rows.
The following sample code may help you to solve the problem:

Note: Please use server cursor instead of client cursor and the property
only works when cursor type is adOpenKeyset.
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   cn.ConnectionString =
"Provider=SQLOLEDB;Server=yourServer;Database=yourDatabase;uid=sa;pwd=;"
   cn.Open
   rs.ActiveConnection = cn        
   rs.Properties("Server Data On Insert").Value = True
   rs.Open "SELECT * FROM Employee", , adOpenKeyset, adLockOptimistic
   rs.AddNew
   rs.Fields(1) = "Test"
   rs.Update
   ' Here should be the correct identity value inserted into Employee Table
instead of Audit Table        

   Debug.Print rs.Fields(0)
   rs.Close
   cn.Close

        For more information, please read the following article:
        Q219029 HOWTO: Retrieving Calculated Fields from SQL Server 7.0
        http://support.microsoft.com/support/kb/articles/q219/0/29.asp

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copy Right, 2001 Microsoft Corporation. All rights reserved.

Regards,
Kally Wang
Microsoft Support Engineer

Subject: Getting the identity value of a newly inserted row
Date: Tue, 16 Oct 2001 16:00:14 -0400
Newsgroups: microsoft.public.vb.database.ado

Hi,

I have a question regarding identity columns in SQL Server 7.

Sometimes, after inserting a row into a table using ADO, you would want to
get the identity created for the newly inserted row. For instance, if table
EMPLOYEE has an identity column called EmployeeId, after calling
Recordset.Update, you can read Recordset("EmployeeId").Value and get the
identity value assigned to that newly inserted row. This works okay unless
EMPLOYEE table has an insert-trigger that inserts rows to some other table.
Say, after inserting a new EMPLOYEE row, you must add into the AUDIT table
that "employee so and so has been added." , and the primary key for the
AUDIT table is another identity column (called AuditID). If EMPLOYEE table
has an insert-trigger as described above, after calling Recordset.Update
method, what you read from Recordset("EmployeeID").Value is the number that
was auto-generated for the identity column of AUDIT table, and not the
number that was auto-generated for the identity column of EMPLOYEE table. I

was added, the identity of the AUDIT table was the one that was returned
instead.

So, me and my comworker think that an alternative solution to reading the
identity value after a recordset update is by doing something like "Select
Max(EmployeeID) From EMPLOYEE". This strategy looks like it's going to work
90% of the time. But our concern is, if the database is busy (many
concurrent users doing concurrent inserts on the same tables), the "Select
Max(IdentityColumn)" strategy might not work.

I guess my main question is: Is there an efficient, bullet-proof way of
retrieving the newly created identity column value for a row that was just
inserted into a table that fires a trigger which in turn inserts a row into
another table that also has an identity column?

Any tips, recommendations would be greatly appreciated. Thanks!

-Gabe