ADOQuery, trigger and requery bug

Question

I have an ADOQuery that inserts a record to SQL Server 2005 table that has trigger inserting data to another table. I use following code to refresh the query and avoid Row cannot be located for updating (There are PKs on each table, UpdateCriteria property are set, Cursors are set to Dynamic, yet I still get the error sometimes. However it's not the question now).

procedure Requery(T: TCustomADODataSet; IDField: string);
var
  i: integer;
begin
  if T.RecordCount > 0 then
    i := T.FieldByName(IDField).AsInteger;
  T.Requery();
  if T.RecordCount > 0 then
    T.Locate(IDField, i, []);
end;

Before requery, I can get value of ID field. However, after requery, ID field returns value of ID field of the other table record insterted by trigger. Both tables has ID field with same name. I have also added SET NOCOUNT ON .... OFF to trigger to avoid Too Many Rows Affected error, however I don't think this affects my problem. I haven't seen errors like these when I was working with Delphi 6 - 7 and SQL Server 2000, hence I'm willing to give SDAC or DAO a try. Would SDAC or DAO solve the problem or is there any solution without changing the ADOQuery?

Answer 1

I am not familiar with ADOQuery but as you are saying that you are getting an ID of the table affected by trigger while expecting to get ID of the original table perhaps it is a matter of using equivalent function to SQL "scope_identity" See Best way to get identity of inserted row?

EDIT

It seems that the problem is related to the fact that the ADO Query itself is useing @@Identity to get the ID of added record while it should have really use scope_identity(), that has implications when you have triggers inserting data into another table which contains identity columns as it is in your case - see link above for details of scope_identity and @@identity. This post has some details of the problem

Answer 2

The one way around this mess that I have used in the past was to add a GUID field to my master table, before saving (if the guid was empty) I would generate a new guid in code, and hold onto that reference. Then I would save the record and re-query for the record containing the known guid. This insured me that I got the exact record that I wanted, and would then proceed to assign the FK to the identity value of the master record. Sure this is slower, but it always statistically works.