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
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!