In SQL Server 2000 SP 3A, I have a table with an identity primary key. The insert trigger joins the inserted table with 3 other tables and then does inserts into a child table that also has an identity column as its primary key. If I open the table in Enterprise Manager and key in the values for a new row, the row gets inserted and the trigger correctly inserts rows into the child table. However, the value of the identity primary key for the parent row I just entered does NOT appear in the grid. If I try to delete the row in the grid I get the "cannot delete rows with unknown keys" error. If I refresh the grid, the primary key appears and I can delete the row. If I remove the trigger inserts into the child table, the primary key of the inserted row appears in the grid as expected. This is killing me because the data entry form is in an an Access XP ADP project where the unknown value for the primary key is killing me. FYI, here's the guts of the insert trigger.
AmenityID , AmenityTypeAttrDefID , Flg
SELECT i.AmenityID, atad.AmenityTypeAttrDefID, 0 AS Flg
FROM inserted i INNER JOIN tblAmenityTypeAttrDef atad ON i.AmenityTypeID = atad.AmenityTypeID
INNER JOIN tblAmenityAttrDef aad ON aad.AmenityAttrDefID = atad.AmenityAttrDefID
INNER JOIN tblAmenityAttrDefType aadt ON aadt.AmenityAttrDefTypeID = aad.AmenityAttrDefTypeID
WHERE aadt.Code = dbo.udfAttrDefTypeCodeFlg()
I can't duplicate the problem when I create a trivial parent table with a trigger that inserts into a trivial child table. HELP !!!!!